[gelöst] SQL Performance NAV 2013

Ich habe mit einem guten Freund telefoniert der mir noch empfohlen hat die Treiber für die VMware Tools und Netzwerkadapter zu aktualisieren. Das werde ich noch probireren.

Das war letztendlich das, was ich mit meinem englischen Text vorgeschlagen habe

@Carsten werde ich gleich ausprobieren. Hab ich wohl nicht richtig "übersetzt" :)

..., möchte aber auch darauf hinweisen, dass der SQL Server Speicher begrenzt werden sollte, so dass der NAV Server genug Luft zum atmen hat. Im allgemeinen, sicherlich auch abhängig von der Anzahl an Benutzern, empfehlen wir 8-12 GB Speicher für den Dynamics NAV Server zu reservieren (also dem SQL Server zu klauen).

Absolut! Darauf bin ich nicht eingegangen. Aber 8 bis 12 GB für einen NST???

Durchaus! Das max. war zwar letztendlich schon inkl. OS, aber je nach Benutzeranzahl und Traffic kann ein Dynamics NAV 2013 NST schon Mal eben Richtung 6-8/9 GB rutschen.

Wobei unsere "offiziellen" Zahlen hier etwas andere Anforderungen zeigen: On Premise single tenant Sizing guide NAV 2013 R2

Memory Requirements
At the peak load of 500 concurrent users the memory consumed by the Dynamics NAV Server process was approximately 3.3GB. Each user session that connects to a Microsoft Dynamics NAV Server was calculated to need approximately 5 MB of RAM per user, this takes into account the operating system and initial server memory requirements.
The following table shows approximate memory usage requirements based on the performance counters collected during the test run.

Windows Server 2012 R2 Initial Memory 1.5 GB
Microsoft Dynamics NAV Server Initial Memory 0.5 GB
Estimated Peak Memory Use for 500 User Sessions 2.5 GB
Total Memory used at 500 User Session 4.5 GB

The need of processor cores versus memory
The test showed that adding RAM to the server is not sufficient to scale out to more users; however, other scenarios like reporting might benefit from additional RAM. In our scenarios, the limiting factor on the virtual machine size is not RAM, but the amount of available cores. In our tests, we used 4 cores on 1 virtual machine to support 500 concurrent users.
A rule of thumb for sizing is that each processor core is capable of handling up to 150 users at the loads described.

Ich schau mal ob ich eine Grafik mit den Ergebnissen unserer eigenen Lasttests noch finde...

Ich habe soeben die VMware Tools aktualisiert, das hat leider auch nichts geholfen.

Pablo1985 und ich haben uns heute Vormittag das Problem mal "live" angesehen - hier das Ergebnis:

Die Plattform ist schon richtig dimensioniert und konfiguriert; d.h. der SQL Server arbeitet so wie er soll. Von den 18GB RAM verwendet er gerade mal 10GB und ist glücklich damit. Der NST nutzt trotz 1GB DataCache nur ca. 600MB. CPU Auslastung unter 5%; LAN Anbinding in Ordnung.

Das Problem ist letztlich eine "Monster-Abfrage", die NAV beim öffnen der Artikel-Page absendet: hier werden sämtliche Zusatzinfos/FlowFields per APPLY Joins eingebunden und das erzeugt in einigen Fällen erhebliche Index-Scans. Wir haben also folgendes gemacht:
[list=]
[*]SQL Profiling auf das Öffnen der Artikel-Page
[*]Auswerten des "Execution Plans" - ermitteln der teuren Operation anhand der CPU Kostenverteilung
[*]Ergänzende Indizierung für die problematischsten Sub-Queries
[/list]

So sieht diese Abfrage (formatiert 240 Zeilen!) aus; die betroffenen APPLY Joins und CREATE INDEX Statements sind eingerückt:

Code:

SELECT TOP (50) ISNULL ("Item"."timestamp", @0) AS "timestamp",
                ISNULL ("Item"."No_", @1) AS "No_",
                ISNULL ("Item"."No_ 2", @2) AS "No_ 2",
                ISNULL ("Item"."Description", @3) AS "Description",
                ISNULL ("Item"."Search Description", @4) AS "Search Description",
                ISNULL ("Item"."Description 2", @3) AS "Description 2",
                ISNULL ("Item"."Base Unit of Measure", @5) AS "Base Unit of Measure",
                ISNULL ("Item"."Price Unit Conversion", @6) AS "Price Unit Conversion",
                ISNULL ("Item"."Inventory Posting Group", @7) AS "Inventory Posting Group",
                ISNULL ("Item"."Shelf No_", @8) AS "Shelf No_",
                ISNULL ("Item"."Item Disc_ Group", @9) AS "Item Disc_ Group",
                ISNULL ("Item"."Allow Invoice Disc_", @10) AS "Allow Invoice Disc_",
                ISNULL ("Item"."Statistics Group", @6) AS "Statistics Group",
                ISNULL ("Item"."Commission Group", @6) AS "Commission Group",
                ISNULL ("Item"."Unit Price", @11) AS "Unit Price",
                ISNULL ("Item"."Price_Profit Calculation", @12) AS "Price_Profit Calculation",
                ISNULL ("Item"."Profit _", @11) AS "Profit _",
                ISNULL ("Item"."Costing Method", @12) AS "Costing Method",
                ISNULL ("Item"."Unit Cost", @11) AS "Unit Cost",
                ISNULL ("Item"."Standard Cost", @11) AS "Standard Cost",
                ISNULL ("Item"."Last Direct Cost", @11) AS "Last Direct Cost",
                ISNULL ("Item"."Indirect Cost _", @11) AS "Indirect Cost _",
                ISNULL ("Item"."Cost is Adjusted", @10) AS "Cost is Adjusted",
                ISNULL ("Item"."Allow Online Adjustment", @10) AS "Allow Online Adjustment",
                ISNULL ("Item"."Vendor No_", @13) AS "Vendor No_",
                ISNULL ("Item"."Vendor Item No_", @3) AS "Vendor Item No_",
                ISNULL ("Item"."Lead Time Calculation", @14) AS "Lead Time Calculation",
                ISNULL ("Item"."Reorder Point", @11) AS "Reorder Point",
                ISNULL ("Item"."Maximum Inventory", @11) AS "Maximum Inventory",
                ISNULL ("Item"."Reorder Quantity", @11) AS "Reorder Quantity",
                ISNULL ("Item"."Alternative Item No_", @15) AS "Alternative Item No_",
                ISNULL ("Item"."Unit List Price", @11) AS "Unit List Price",
                ISNULL ("Item"."Duty Due _", @11) AS "Duty Due _",
                ISNULL ("Item"."Duty Code", @16) AS "Duty Code",
                ISNULL ("Item"."Gross Weight", @11) AS "Gross Weight",
                ISNULL ("Item"."Net Weight", @11) AS "Net Weight",
                ISNULL ("Item"."Units per Parcel", @11) AS "Units per Parcel",
                ISNULL ("Item"."Unit Volume", @11) AS "Unit Volume",
                ISNULL ("Item"."Durability", @17) AS "Durability",
                ISNULL ("Item"."Freight Type", @18) AS "Freight Type",
                ISNULL ("Item"."Tariff No_", @19) AS "Tariff No_",
                ISNULL ("Item"."Duty Unit Conversion", @11) AS "Duty Unit Conversion",
                ISNULL ("Item"."Country_Region Purchased Code", @20) AS "Country_Region Purchased Code",
                ISNULL ("Item"."Budget Quantity", @11) AS "Budget Quantity",
                ISNULL ("Item"."Budgeted Amount", @11) AS "Budgeted Amount",
                ISNULL ("Item"."Budget Profit", @11) AS "Budget Profit",
                ISNULL ("Item"."Blocked", @10) AS "Blocked",
                ISNULL ("Item"."Last Date Modified", @21) AS "Last Date Modified",
                ISNULL ("Item"."Price Includes VAT", @10) AS "Price Includes VAT",
                ISNULL ("Item"."VAT Bus_ Posting Gr_ (Price)", @22) AS "VAT Bus_ Posting Gr_ (Price)",
                ISNULL ("Item"."Gen_ Prod_ Posting Group", @23) AS "Gen_ Prod_ Posting Group",
                DATALENGTH ("Item"."Picture") AS "Picture",
                ISNULL ("Item"."Country_Region of Origin Code", @24) AS "Country_Region of Origin Code",
                ISNULL ("Item"."Automatic Ext_ Texts", @10) AS "Automatic Ext_ Texts",
                ISNULL ("Item"."No_ Series", @25) AS "No_ Series",
                ISNULL ("Item"."Tax Group Code", @26) AS "Tax Group Code",
                ISNULL ("Item"."VAT Prod_ Posting Group", @27) AS "VAT Prod_ Posting Group",
                ISNULL ("Item"."Reserve", @12) AS "Reserve",
                ISNULL ("Item"."Global Dimension 1 Code", @28) AS "Global Dimension 1 Code",
                ISNULL ("Item"."Global Dimension 2 Code", @29) AS "Global Dimension 2 Code",
                ISNULL ("Item"."Application Wksh_ User ID", @30) AS "Application Wksh_ User ID",
                ISNULL ("Item"."Assembly Policy", @12) AS "Assembly Policy",
                ISNULL ("Item"."Low-Level Code", @6) AS "Low-Level Code",
                ISNULL ("Item"."Lot Size", @11) AS "Lot Size",
                ISNULL ("Item"."Serial Nos_", @31) AS "Serial Nos_",
                ISNULL ("Item"."Last Unit Cost Calc_ Date", @21) AS "Last Unit Cost Calc_ Date",
                ISNULL ("Item"."Rolled-up Material Cost", @11) AS "Rolled-up Material Cost",
                ISNULL ("Item"."Rolled-up Capacity Cost", @11) AS "Rolled-up Capacity Cost",
                ISNULL ("Item"."Scrap _", @11) AS "Scrap _",
                ISNULL ("Item"."Inventory Value Zero", @10) AS "Inventory Value Zero",
                ISNULL ("Item"."Discrete Order Quantity", @6) AS "Discrete Order Quantity",
                ISNULL ("Item"."Minimum Order Quantity", @11) AS "Minimum Order Quantity",
                ISNULL ("Item"."Maximum Order Quantity", @11) AS "Maximum Order Quantity",
                ISNULL ("Item"."Safety Stock Quantity", @11) AS "Safety Stock Quantity",
                ISNULL ("Item"."Order Multiple", @11) AS "Order Multiple",
                ISNULL ("Item"."Safety Lead Time", @14) AS "Safety Lead Time",
                ISNULL ("Item"."Flushing Method", @12) AS "Flushing Method",
                ISNULL ("Item"."Replenishment System", @12) AS "Replenishment System",
                ISNULL ("Item"."Rounding Precision", @11) AS "Rounding Precision",
                ISNULL ("Item"."Sales Unit of Measure", @32) AS "Sales Unit of Measure",
                ISNULL ("Item"."Purch_ Unit of Measure", @33) AS "Purch_ Unit of Measure",
                ISNULL ("Item"."Time Bucket", @14) AS "Time Bucket",
                ISNULL ("Item"."Reordering Policy", @12) AS "Reordering Policy",
                ISNULL ("Item"."Include Inventory", @10) AS "Include Inventory",
                ISNULL ("Item"."Manufacturing Policy", @12) AS "Manufacturing Policy",
                ISNULL ("Item"."Rescheduling Period", @14) AS "Rescheduling Period",
                ISNULL ("Item"."Lot Accumulation Period", @14) AS "Lot Accumulation Period",
                ISNULL ("Item"."Dampener Period", @14) AS "Dampener Period",
                ISNULL ("Item"."Dampener Quantity", @11) AS "Dampener Quantity",
                ISNULL ("Item"."Overflow Level", @11) AS "Overflow Level",
                ISNULL ("Item"."Manufacturer Code", @34) AS "Manufacturer Code",
                ISNULL ("Item"."Item Category Code", @35) AS "Item Category Code",
                ISNULL ("Item"."Created From Nonstock Item", @10) AS "Created From Nonstock Item",
                ISNULL ("Item"."Product Group Code", @36) AS "Product Group Code",
                ISNULL ("Item"."Service Item Group", @37) AS "Service Item Group",
                ISNULL ("Item"."Item Tracking Code", @38) AS "Item Tracking Code",
                ISNULL ("Item"."Lot Nos_", @39) AS "Lot Nos_",
                ISNULL ("Item"."Expiration Calculation", @14) AS "Expiration Calculation",
                ISNULL ("Item"."Special Equipment Code", @40) AS "Special Equipment Code",
                ISNULL ("Item"."Put-away Template Code", @41) AS "Put-away Template Code",
                ISNULL ("Item"."Put-away Unit of Measure Code", @42) AS "Put-away Unit of Measure Code",
                ISNULL ("Item"."Phys Invt Counting Period Code", @43) AS "Phys Invt Counting Period Code",
                ISNULL ("Item"."Last Counting Period Update", @21) AS "Last Counting Period Update",
                ISNULL ("Item"."Next Counting Period", @3) AS "Next Counting Period",
                ISNULL ("Item"."Use Cross-Docking", @10) AS "Use Cross-Docking",
                ISNULL ("Item"."Kostenträgercode", @44) AS "Kostenträgercode",
                ISNULL ("Item"."Kostenstellencode", @45) AS "Kostenstellencode",
                ISNULL ("Item"."Charge Item", @10) AS "Charge Item",
                ISNULL ("Item"."Item Group", @46) AS "Item Group",
                ISNULL ("Item"."Inv_ Value_Piece p_ Year End", @11) AS "Inv_ Value_Piece p_ Year End",
                ISNULL ("Item"."Inv_ Value_Piece p_Month End", @11) AS "Inv_ Value_Piece p_Month End",
                ISNULL ("Item"."Cost Account Volume", @47) AS "Cost Account Volume",
                ISNULL ("Item"."Cost Account Volume Umplattung", @48) AS "Cost Account Volume Umplattung",
                ISNULL ("Item"."Cost Account Stock", @49) AS "Cost Account Stock",
                ISNULL ("Item"."Cost Account Stock Umplattung", @50) AS "Cost Account Stock Umplattung",
                ISNULL ("Item"."Cost Account Changing", @51) AS "Cost Account Changing",
                ISNULL ("Item"."Cost Account Changing Umplatt_", @52) AS "Cost Account Changing Umplatt_",
                ISNULL ("Item"."Shptm_ Plan Group Code", @53) AS "Shptm_ Plan Group Code",
                ISNULL ("Item"."UEBA-Zeichen Code", @54) AS "UEBA-Zeichen Code",
                ISNULL ("Item"."Predefined Location Code", @55) AS "Predefined Location Code",
                ISNULL ("Item"."Inv_ Value2_Piece p_ Year End", @11) AS "Inv_ Value2_Piece p_ Year End",
                ISNULL ("Item"."Inv_ Value2_Piece p_Month End", @11) AS "Inv_ Value2_Piece p_Month End",
                ISNULL ("Item"."Cost Account 2 Volume", @56) AS "Cost Account 2 Volume",
                ISNULL ("Item"."Cost Account 2 Volume Umplattg", @57) AS "Cost Account 2 Volume Umplattg",
                ISNULL ("Item"."Cost Account 2 Stock", @58) AS "Cost Account 2 Stock",
                ISNULL ("Item"."Cost Account 2 Stock Umplattg", @59) AS "Cost Account 2 Stock Umplattg",
                ISNULL ("Item"."Cost Account 2 Changing", @60) AS "Cost Account 2 Changing",
                ISNULL ("Item"."Cost Account 2 Changing Umplat", @61) AS "Cost Account 2 Changing Umplat",
                ISNULL ("Item"."Ausschussartikel", @10) AS "Ausschussartikel",
                ISNULL ("Item"."Form Type", @62) AS "Form Type",
                ISNULL ("Item"."Privat Tie to No_", @63) AS "Privat Tie to No_",
                ISNULL ("Item"."Concrete Item", @10) AS "Concrete Item",
                ISNULL ("Item"."Second Choice", @10) AS "Second Choice",
                ISNULL ("Item"."Sale to", @12) AS "Sale to",
                ISNULL ("Item"."Type", @12) AS "Type",
                ISNULL ("Item"."Alte Artikelnr_", @64) AS "Alte Artikelnr_",
                ISNULL ("Item"."Ursprungsmandant", @65) AS "Ursprungsmandant",
                ISNULL ("Item"."Qualifying for Cash Discount", @10) AS "Qualifying for Cash Discount",
                ISNULL ("Item"."Qualifying for Provision", @10) AS "Qualifying for Provision",
                ISNULL ("Item"."Inventory Warning", @10) AS "Inventory Warning",
                ISNULL ("Item"."Routing No_", @66) AS "Routing No_",
                ISNULL ("Item"."Production BOM No_", @67) AS "Production BOM No_",
                ISNULL ("Item"."Single-Level Material Cost", @11) AS "Single-Level Material Cost",
                ISNULL ("Item"."Single-Level Capacity Cost", @11) AS "Single-Level Capacity Cost",
                ISNULL ("Item"."Single-Level Subcontrd_ Cost", @11) AS "Single-Level Subcontrd_ Cost",
                ISNULL ("Item"."Single-Level Cap_ Ovhd Cost", @11) AS "Single-Level Cap_ Ovhd Cost",
                ISNULL ("Item"."Single-Level Mfg_ Ovhd Cost", @11) AS "Single-Level Mfg_ Ovhd Cost",
                ISNULL ("Item"."Overhead Rate", @11) AS "Overhead Rate",
                ISNULL ("Item"."Rolled-up Subcontracted Cost", @11) AS "Rolled-up Subcontracted Cost",
                ISNULL ("Item"."Rolled-up Mfg_ Ovhd Cost", @11) AS "Rolled-up Mfg_ Ovhd Cost",
                ISNULL ("Item"."Rolled-up Cap_ Overhead Cost", @11) AS "Rolled-up Cap_ Overhead Cost",
                ISNULL ("Item"."Order Tracking Policy", @12) AS "Order Tracking Policy",
                ISNULL ("Item"."Critical", @10) AS "Critical",
                ISNULL ("Item"."Common Item No_", @68) AS "Common Item No_",
                ISNULL ("SUB$Assembly BOM"."Assembly BOM$BOM Component$EXISTS", @71) AS "Assembly BOM",
                ISNULL ("SUB$Inventory"."Inventory$Item Ledger Entry$SUM$Quantity", @11) AS "Inventory",
                ISNULL ("SUB$Qty_ on Purch_ Order"."Qty_ on Purch_ Order$Purchase Line$SUM$Outstanding Qty_ (Base)", @11) AS "Qty_ on Purch_ Order",
                ISNULL ("SUB$Qty_ on Prod_ Order"."Qty_ on Prod_ Order$Prod_ Order Line$SUM$Remaining Qty_ (Base)", @11) AS "Qty_ on Prod_ Order",
                ISNULL ("SUB$Qty_ on Component Lines"."Qty_ on Component Lines$Prod_ Order Component$SUM$Remaining Qty_ (Base)", @11) AS "Qty_ on Component Lines",
                ISNULL ("SUB$Qty_ on Sales Order"."Qty_ on Sales Order$Sales Line$SUM$Outstanding Qty_ (Base)", @11) AS "Qty_ on Sales Order",
                ISNULL ("SUB$Qty_ on Sales Quote (Adv)"."Qty_ on Sales Quote (Adv)$Sales Line$SUM$Outstanding Qty_ (Base)", @11) AS "Qty_ on Sales Quote (Adv)",
                ISNULL ("SUB$Qty_ on Service Order"."Qty_ on Service Order$Service Line$SUM$Outstanding Qty_ (Base)", @11) AS "Qty_ on Service Order",
                ISNULL ("SUB$Qty_ on Assembly Order"."Qty_ on Assembly Order$Assembly Header$SUM$Remaining Quantity (Base)", @11) AS "Qty_ on Assembly Order",
                ISNULL ("SUB$Qty_ on Asm_ Component"."Qty_ on Asm_ Component$Assembly Line$SUM$Remaining Quantity (Base)", @11) AS "Qty_ on Asm_ Component",
                CAST (CASE ISNULL ("SUB$Cost is Posted to G_L"."Cost is Posted to G_L$Post Value Entry to G_L$EXISTS", @92)
                      WHEN 0 THEN 1
                          ELSE 0
                      END AS tinyint) AS "Cost is Posted to G_L",
                ISNULL ("SUB$Net Invoiced Qty_"."Net Invoiced Qty_$Item Ledger Entry$SUM$Invoiced Quantity", @11) AS "Net Invoiced Qty_",
                ISNULL ("SUB$Output (Qty_)"."Output (Qty_)$Item Ledger Entry$SUM$Quantity", @11) AS "Output (Qty_)",
                -ISNULL ("SUB$Sales (Qty_)"."Sales (Qty_)$Value Entry$SUM$Invoiced Quantity", @11) AS "Sales (Qty_)",
                ISNULL ("SUB$Stockkeeping Unit Exists"."Stockkeeping Unit Exists$Stockkeeping Unit$EXISTS", @99) AS "Stockkeeping Unit Exists",
                ISNULL ("SUB$Last Phys_ Invt_ Date"."Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry$MAX$Posting Date", @21) AS "Last Phys_ Invt_ Date",
                ISNULL ("SUB$Identifier Code"."Identifier Code$Item Identifier$LOOKUP$Code", @103) AS "Identifier Code"
  FROM
       "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Item" AS "Item" WITH (READUNCOMMITTED) OUTER APPLY (

SELECT TOP (1) @69 AS "Assembly BOM$BOM Component$EXISTS",
ISNULL ("Assembly BOM$BOM Component"."Parent Item No_", @70) AS "Assembly BOM$BOM Component$Parent Item No_",
ISNULL ("Assembly BOM$BOM Component"."Line No_", @6) AS "Assembly BOM$BOM Component$Line No_"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$BOM Component" AS "Assembly BOM$BOM Component" WITH (READUNCOMMITTED)
WHERE("Assembly BOM$BOM Component"."Parent Item No_" = "Item"."No_")
ORDER BY "Assembly BOM$BOM Component$Parent Item No_" ASC, "Assembly BOM$BOM Component$Line No_" ASC) AS "SUB$Assembly BOM" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Inventory$Item Ledger Entry"."SUM$Quantity") , @11) AS "Inventory$Item Ledger Entry$SUM$Quantity"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Item Ledger Entry$VSIFT$3" AS "Inventory$Item Ledger Entry" WITH (READUNCOMMITTED, NOEXPAND)
WHERE("Inventory$Item Ledger Entry"."Item No_" = "Item"."No_")) AS "SUB$Inventory" OUTER APPLY (

   SELECT TOP (1) ISNULL (SUM ("Qty_ on Purch_ Order$Purchase Line"."Outstanding Qty_ (Base)") , @11) AS "Qty_ on Purch_ Order$Purchase Line$SUM$Outstanding Qty_ (Base)"
   FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Purchase Line" AS "Qty_ on Purch_ Order$Purchase Line" WITH (READUNCOMMITTED)
   WHERE(ISNULL ("Qty_ on Purch_ Order$Purchase Line"."Document Type", @12) = @72 AND
   ISNULL ("Qty_ on Purch_ Order$Purchase Line"."Type", @12) = @73 AND "Qty_ on Purch_ Order$Purchase Line"."Type" = "Item"."No_")) AS "SUB$Qty_ on Purch_ Order" OUTER APPLY ( -- ??? Link über Type, nicht No. ???

   /*
   CREATE INDEX ssi01_20141110 ON "Kons_ Betonwerke GmbH & Co KG$Purchase Line"
   ("Document Type", "Type", "No_")
   INCLUDE
   ("Outstanding Qty_ (Base)")
   WITH (MAXDOP = 64)
   GO
   */

SELECT TOP (1) ISNULL (SUM ("Qty_ on Prod_ Order$Prod_ Order Line"."SUM$Remaining Qty_ (Base)") , @11) AS "Qty_ on Prod_ Order$Prod_ Order Line$SUM$Remaining Qty_ (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Prod_ Order Line$VSIFT$2" AS "Qty_ on Prod_ Order$Prod_ Order Line" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Prod_ Order$Prod_ Order Line"."Status", @12) >= @74 AND ISNULL ("Qty_ on Prod_ Order$Prod_ Order Line"."Status", @12) <= @75 AND "Qty_ on Prod_ Order$Prod_ Order Line"."Item No_" = "Item"."No_")) AS "SUB$Qty_ on Prod_ Order" OUTER APPLY (

   SELECT TOP (1) ISNULL (SUM ("Qty_ on Component Lines$Prod_ Order Component"."Remaining Qty_ (Base)") , @11) AS "Qty_ on Component Lines$Prod_ Order Component$SUM$Remaining Qty_ (Base)"
   FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Prod_ Order Component" AS "Qty_ on Component Lines$Prod_ Order Component" WITH (READUNCOMMITTED)
   WHERE(ISNULL ("Qty_ on Component Lines$Prod_ Order Component"."Status", @12) >= @76 AND ISNULL ("Qty_ on Component Lines$Prod_ Order Component"."Status", @12) <= @77
    AND "Qty_ on Component Lines$Prod_ Order Component"."Item No_" = "Item"."No_")) AS "SUB$Qty_ on Component Lines" OUTER APPLY (

    /*
   CREATE INDEX ssi01_20141110 ON "Kons_ Betonwerke GmbH & Co KG$Prod_ Order Component"
   ("Item No_", "Status")
   INCLUDE
   ("Remaining Qty_ (Base)")
      WITH (MAXDOP = 64)
   GO
   */

SELECT TOP (1) ISNULL (SUM ("Qty_ on Sales Order$Sales Line"."SUM$Outstanding Qty_ (Base)") , @11) AS "Qty_ on Sales Order$Sales Line$SUM$Outstanding Qty_ (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Sales Line$VSIFT$2" AS "Qty_ on Sales Order$Sales Line" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Sales Order$Sales Line"."Document Type", @12) = @78 AND ISNULL ("Qty_ on Sales Order$Sales Line"."Type", @12) = @79 AND "Qty_ on Sales Order$Sales Line"."No_" = "Item"."No_" AND ISNULL ("Qty_ on Sales Order$Sales Line"."Reversed", @10) = @80)) AS "SUB$Qty_ on Sales Order" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Qty_ on Sales Quote (Adv)$Sales Line"."SUM$Outstanding Qty_ (Base)") , @11) AS "Qty_ on Sales Quote (Adv)$Sales Line$SUM$Outstanding Qty_ (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Sales Line$VSIFT$2" AS "Qty_ on Sales Quote (Adv)$Sales Line" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Sales Quote (Adv)$Sales Line"."Document Type", @12) = @81 AND ISNULL ("Qty_ on Sales Quote (Adv)$Sales Line"."Type", @12) = @82 AND "Qty_ on Sales Quote (Adv)$Sales Line"."No_" = "Item"."No_" AND ISNULL ("Qty_ on Sales Quote (Adv)$Sales Line"."Reversed", @10) = @83 AND ISNULL ("Qty_ on Sales Quote (Adv)$Sales Line"."Advance Notice", @10) = @84 AND ISNULL ("Qty_ on Sales Quote (Adv)$Sales Line"."Document Processed", @10) = @85)) AS "SUB$Qty_ on Sales Quote (Adv)" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Qty_ on Service Order$Service Line"."SUM$Outstanding Qty_ (Base)") , @11) AS "Qty_ on Service Order$Service Line$SUM$Outstanding Qty_ (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Service Line$VSIFT$7" AS "Qty_ on Service Order$Service Line" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Service Order$Service Line"."Document Type", @12) = @86 AND ISNULL ("Qty_ on Service Order$Service Line"."Type", @12) = @87 AND "Qty_ on Service Order$Service Line"."No_" = "Item"."No_")) AS "SUB$Qty_ on Service Order" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Qty_ on Assembly Order$Assembly Header"."SUM$Remaining Quantity (Base)") , @11) AS "Qty_ on Assembly Order$Assembly Header$SUM$Remaining Quantity (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Assembly Header$VSIFT$1" AS "Qty_ on Assembly Order$Assembly Header" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Assembly Order$Assembly Header"."Document Type", @12) = @88 AND "Qty_ on Assembly Order$Assembly Header"."Item No_" = "Item"."No_")) AS "SUB$Qty_ on Assembly Order" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Qty_ on Asm_ Component$Assembly Line"."SUM$Remaining Quantity (Base)") , @11) AS "Qty_ on Asm_ Component$Assembly Line$SUM$Remaining Quantity (Base)"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Assembly Line$VSIFT$2" AS "Qty_ on Asm_ Component$Assembly Line" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Qty_ on Asm_ Component$Assembly Line"."Document Type", @12) = @89 AND ISNULL ("Qty_ on Asm_ Component$Assembly Line"."Type", @12) = @90 AND "Qty_ on Asm_ Component$Assembly Line"."No_" = "Item"."No_")) AS "SUB$Qty_ on Asm_ Component" OUTER APPLY (

   SELECT TOP (1) @91 AS "Cost is Posted to G_L$Post Value Entry to G_L$EXISTS",
   ISNULL ("Cost is Posted to G_L$Post Value Entry to G_L"."Value Entry No_", @6) AS "Cost is Posted to G_L$Post Value Entry to G_L$Value Entry No_"
   FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Post Value Entry to G_L" AS "Cost is Posted to G_L$Post Value Entry to G_L" WITH (READUNCOMMITTED)
   WHERE("Cost is Posted to G_L$Post Value Entry to G_L"."Item No_" = "Item"."No_")
   ORDER BY "Cost is Posted to G_L$Post Value Entry to G_L$Value Entry No_" ASC) AS "SUB$Cost is Posted to G_L" OUTER APPLY (

   /*
   CREATE INDEX ssi01_20141110 ON "Kons_ Betonwerke GmbH & Co KG$Post Value Entry to G_L"
   ("Item No_", "Value Entry No_")
      WITH (MAXDOP = 64)
   GO
   */
   

SELECT TOP (1) ISNULL (SUM ("Net Invoiced Qty_$Item Ledger Entry"."SUM$Invoiced Quantity") , @11) AS "Net Invoiced Qty_$Item Ledger Entry$SUM$Invoiced Quantity"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Item Ledger Entry$VSIFT$3" AS "Net Invoiced Qty_$Item Ledger Entry" WITH (READUNCOMMITTED, NOEXPAND)
WHERE("Net Invoiced Qty_$Item Ledger Entry"."Item No_" = "Item"."No_")) AS "SUB$Net Invoiced Qty_" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Output (Qty_)$Item Ledger Entry"."SUM$Quantity") , @11) AS "Output (Qty_)$Item Ledger Entry$SUM$Quantity"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Item Ledger Entry$VSIFT$3" AS "Output (Qty_)$Item Ledger Entry" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Output (Qty_)$Item Ledger Entry"."Entry Type", @12) = @93 AND "Output (Qty_)$Item Ledger Entry"."Item No_" = "Item"."No_")) AS "SUB$Output (Qty_)" OUTER APPLY (

SELECT TOP (1) ISNULL (SUM ("Sales (Qty_)$Value Entry"."SUM$Invoiced Quantity") , @11) AS "Sales (Qty_)$Value Entry$SUM$Invoiced Quantity"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Value Entry$VSIFT$13" AS "Sales (Qty_)$Value Entry" WITH (READUNCOMMITTED, NOEXPAND)
WHERE(ISNULL ("Sales (Qty_)$Value Entry"."Item Ledger Entry Type", @12) = @94 AND "Sales (Qty_)$Value Entry"."Item No_" = "Item"."No_")) AS "SUB$Sales (Qty_)" OUTER APPLY (

SELECT TOP (1) @95 AS "Stockkeeping Unit Exists$Stockkeeping Unit$EXISTS",
ISNULL ("Stockkeeping Unit Exists$Stockkeeping Unit"."Location Code", @96) AS "Stockkeeping Unit Exists$Stockkeeping Unit$Location Code",
ISNULL ("Stockkeeping Unit Exists$Stockkeeping Unit"."Item No_", @97) AS "Stockkeeping Unit Exists$Stockkeeping Unit$Item No_",
ISNULL ("Stockkeeping Unit Exists$Stockkeeping Unit"."Variant Code", @98) AS "Stockkeeping Unit Exists$Stockkeeping Unit$Variant Code"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Stockkeeping Unit" AS "Stockkeeping Unit Exists$Stockkeeping Unit" WITH (READUNCOMMITTED)
WHERE("Stockkeeping Unit Exists$Stockkeeping Unit"."Item No_" = "Item"."No_")
ORDER BY "Stockkeeping Unit Exists$Stockkeeping Unit$Location Code" ASC, "Stockkeeping Unit Exists$Stockkeeping Unit$Item No_" ASC, "Stockkeeping Unit Exists$Stockkeeping Unit$Variant Code" ASC) AS "SUB$Stockkeeping Unit Exists" OUTER APPLY (

SELECT TOP (1) ISNULL (MAX ("Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry"."Posting Date") , @21) AS "Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry$MAX$Posting Date"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Phys_ Inventory Ledger Entry" AS "Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry" WITH (READUNCOMMITTED)
WHERE("Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry"."Item No_" = "Item"."No_" AND (ISNULL ("Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry"."Phys Invt Counting Period Type", @12) = @100 OR ISNULL ("Last Phys_ Invt_ Date$Phys_ Inventory Ledger Entry"."Phys Invt Counting Period Type", @12) = @101))) AS "SUB$Last Phys_ Invt_ Date" OUTER APPLY (

SELECT TOP (1) ISNULL ("Identifier Code$Item Identifier"."Code", @102) AS "Identifier Code$Item Identifier$LOOKUP$Code"
FROM "Navision_Test"."dbo"."Kons_ Betonwerke GmbH & Co KG$Item Identifier" AS "Identifier Code$Item Identifier" WITH (READUNCOMMITTED)
WHERE("Identifier Code$Item Identifier"."Item No_" = "Item"."No_")
ORDER BY "Identifier Code$Item Identifier$LOOKUP$Code" ASC) AS "SUB$Identifier Code"

WHERE(ISNULL ("Item"."No_", @104) < @105)
  ORDER BY "No_" DESC
  OPTION (OPTIMIZE FOR UNKNOWN, FAST 50) ;

Ergebnis: vorher 30 bis 90 Sekunden für das öffnen der Page, nachher - nach nur 3 ergänzten Indexen! - ca. eine Sekunde!

Ich möchte mich hier noch mal bei Jörg ganz Herzlich für die Unterstützung bedanken und das alles so toll geklappt hat.

Vielen Dank auch anden anderen die hier mitgeholfen haben. Ich setze somit das Thema als [gelöst]