[Gelöst]Curse of the Cursor

Angeregt von einer SQl-Performance Schulung und Stryks Beitrag: msdynamicsworld.com - Microsoft Dynamics NAV-SQL Challenge: The Curse of the Cursor, haben wir die bei uns verwendeten Cursortypen versucht zu ermitteln (genauer gesagt unsere IT).
Das klappt bei recht vielen Versionen, die wir im Einsatz haben.

Leider nicht bei dieser:
4.0.3 Build 25143aus der Installation 4.0 SP3
Dort können wir nix im Trace zu sp_cursorprepare finden

Frage1:
Wer kann weiterhelfen, warum lässt sich der Cursor nicht ermitteln?
bzw. evtl. weiß einer von Euch den Cursortyp?

Frage2:
Wann ist welcher Cursor der bessere / von welchen Faktoren hängt das ab?



BTW: Bei der Version 4.0.3.26954 wird ein Dynamischer Cursor verwendet.
Was haltet Ihr von den Vorschlag, das wir zu den einzelnen Versionen hier im Forum einen Sammelbeitrag (zu den verwendeten Cursortypen) eröffnen?

Also alternativ kann man die Cursor-Typen auch direkt aus den SQL Caches lesen; das "NAV Sustained Engineering Team" hat hierzu folgendes BEispiel in seinem BLOG veröffentlicht:

Code:

-- MS Dynamics NAV Sustained Engineering Team
-- http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/10/14/simple-query-to-check-the-recent-performance-history-ii-now-including-query-plan-information.aspx
-- PLEASE READ THE BLOG BEFORE USING THE FEATURE!

SELECT TOP 100
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text,
execution_count,
case
when execution_count = 0 then null
else total_logical_reads/execution_count
end as avg_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
plan_handle,
ph.query_plan,

-- Query Plan Information
case when
ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]') = 0
then '' else
ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]','nvarchar (max)')
end as cursor_type

-- Missing Indexes
,case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup)[1]') = 0
then ''
else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/@Impact)[1]','nvarchar (max)')
end as missing_index_impact,
case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]') = 0
then ''
else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]','nvarchar(max)')
end as missing_index_table,
case when ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]') = 0
then ''
else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]','nvarchar(max)')
end as missing_index_field

FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as ph
ORDER BY max_logical_reads DESC


Hier ein Beispiel (Profiler Event "SP: RPC Completed") - allerdings NAV 4.0.3.30033:

Code:

declare @p1 int
set @p1=1073741837
declare @p5 int

set @p5=12290  -- >> Dynamic Cursor

declare @p6 int
set @p6=8193
exec sp_cursorprepare @p1 output,N'@P1 tinyint,@P2 varchar(20)',N'SELECT  *,DATALENGTH("Outlook Calendar StoreID") FROM "Navision"."dbo"."CRONUS 403$Salesperson_Purchaser" WITH (READUNCOMMITTED)   WHERE (("Enable Synchronization"=@P1)) AND (("Navision User ID"=@P2)) ORDER BY "Code" ',1,@p5 output,@p6 output
select @p1, @p5, @p6

Zu Frage 2:
Hier die MS Argumentation zu"Dynamic Cursor":
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2009/02/20/cursor-types.aspx
Da in der Praxis aber eben Filter und Sortierung nicht immer 100%ig zusammen passen, sind mir die "Fast Forward Cursor" lieber, weil ggf. Probleme einfacher lösbar sind ...

Gibt es irgendeine Möglichkeit zwischen DC bzw. FFC zu wählen? Worum geht es mir: Unsere Anwender suchen leider oft ohne vorher einen Schlüssel zu wählen. Nach dem Upgrade (technn.) auf 2009SP1 kommt es nun dank der DC öfters zu "weißen Bildschirmen" als früher (da half mir ein passender Index auf dem SQL server oder ein hint oder ein Execution plan) Nun haben wir letzte Woche migriert und bei diversen Fenstern geht nix mehr, bzw. nur wenn die Sortierung vorher geändert wird. Meine kollegen filtern oft, eigentlich fast nur nach dem suchbegriff (hier Artikel) und dort sehr viel mit Wildcard. Bsp. WAE*&@*28* (also alle WAE mit ner 28) und dann züsätlich noch nach einem anderen Feld. Leider ist die Sortierung auf dem PK (hier no.) was dann, im gegensatz zu früher zu einer verdammt hohen READ anzahl auf dem Server führt (früher ca 1500 bei dieser Filterung, heute über 10000). An dem Subsystem (Hardware) wurde nix verändert da der Server "bombe" ist und auch richtig konfig ist. Ich bin nun am überlegen, da bei DC ja die order by Clausel abgefragt wird, entweder die dazu zu bewegen einen passenden Schlüssel vorher zu wählen, was auch nervig ist oder im Code von den besagten routinen mir einen "FindBestKey" Algorithmus auszudenken und diesen dann als setcurrentkey hart zu setzen.

Gibt es also von MS eine lösung oder anderes gefragt. Wie habt ihr solche Probleme gelöst (ihr seid ja nicht nur so wie ich Endanwender sondern habt ja, glücklicherweise, einen höheren "Wirkungskreis" da div. Kundenumstellungen).

Über eine anwort würde ich mich freuen und sag schon mal danke.

PS was macht eigentlich genau der Wert 1048576, den man in der dbproperty setzen kann? Ist der zuständig für das "alte" ParameterSniffing bei RPC und kann man den, wie die Parameter in der DBconfig reinnehmen->testen->rausnehmen ohne dass die DB konvertiert oder sonstiges mit ihr passiert und man sie nicht mehr geöffnet bekommt?

Erstmal ein kurzer Nachtrag; so kann man die aktuellen Cursor-Typen ebenfalls erkennen:

Code:

SELECT qs.[session_id], si.[login_name], si.[host_name], si.[program_name], [cursor_id], [properties], [creation_time], [is_open], qs.[reads],
       SUBSTRING(qt.[text],qs.[statement_start_offset]/2+1, (case when qs.[statement_end_offset] = -1
                                                         then len(convert(nvarchar(max), qt.[text])) * 2
                                                         else qs.[statement_end_offset] end -qs.[statement_start_offset])/2)
       as [statement]
FROM sys.dm_exec_cursors(0) qs -- 0 = all sessions; or use specific SPID
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) as qt
LEFT JOIN sys.dm_exec_sessions si on si.[session_id] = qs.[session_id]
ORDER BY [reads] DESC

Im Moment gibt es kein Feature den Cursor-Type zu definieren; ein solcher Wunsch ist aber bereits an MS addressiert worden ...

Der "diagnostics" Schalter "1048576" schaltet lediglich die "Cursor Preparation" ab, was best. "Parameter Sniffing" Problemen vorbeugen kann (wohlgemerkt: kann!).

Letzten Endes sind wir mit "Dynamic Cursor" ziemlich am A...
Ein "FindBestKey" Algorithmus ist nicht unbedingt empfehlenswert, da sowas ja die Abfragezeit verlängert, erheblich mehr Programmieraufwand bedeutet und man die Sortierung ja oft nicht so ohne weiteres ändern kann ...

Wie ich in dem "NAV/SQL Quickie" beschrieben habe, so ist of die "einfachste" Lösung einen "über-optimalen Index" SQL Server seitig zu bauen: Zunächst auf Basis der Felder in der WHERE Clause, dann im Anschluss alle Felder des ORDER BY (soweit noch nicht vorhanden); ggf. mit INCLUDED Columns arbeiten. Solche Indexe können zwar ziemlich häßlich werden, können aber helfen ..

Schönes WE,
Jörg

Danke Stryk,

genau das wollte ich vermeiden mit den zusätzlichen Indizs. Hab das auf dem testsytem gestern haben auch schon ausprobiert das ich auch, muss ein Geistesblitz gewesen sein , die gleiche Idee hatte.
Bin dann aber zu dem Schluss gekommen, brinkt nur was, wenn die User tatsächlich immer so filtern und nicht anders. Also auch nich optimal. Warum kommt MS eigentlich darauf? Die müssen doch wissen, dass diverse Endanwender nicht "so" filtern, wie MS es gerne hätte. Oft will man nach dem PK (z.b. Artikelnummer sortiert haben und eben nicht nach, was weiß ich, regalnummer).

Unschön, echt unschön. Na schaun mer mal .... danke dir nochmal für die antwort.
Was hat MS eigentlich gesagt zu diesem thema, wenn es an die schon herangetragen wurde?

...
Was hat MS eigentlich gesagt zu diesem thema, wenn es an die schon herangetragen wurde?

Noch nix ...

Hier gibt's ne aktuelle Diskussion zu diesem Thema, hier werden auch die Cursor Unterschiede aufgezeigt: http://www.mibuso.com/forum/viewtopic.php?f=34&t=39960&sid=1cfc73509ac3a23668d29f392b2f2469

danke. Und wie löst du solche Probleme? Es kann doch nicht die Lösung sein, Indizes zu hauf anzulegen (die wollen ja bei Schreibtransaktionen auch gepflegt werden)

danke. Und wie löst du solche Probleme? Es kann doch nicht die Lösung sein, Indizes zu hauf anzulegen (die wollen ja bei Schreibtransaktionen auch gepflegt werden)

Oh doch - genau so! Die Alternativen heißen "Mit dem Problem leben" oder den "C/AL Code ändern" (was noch aufwendiger wäre).
Und, nun ja, in einem ERP System ist die "Lese-Optimierung" meist wichtiger als die "Schreib-Optimierung" ... wenn das System nun mal den einen oder anderen Index braucht, dann soll es ihn halt haben ...

Deshalb führe ich ja vorher intensive Profiler Tracings durch, wo die Probleme identifiziert, qualifiziert und quantifiziert (also gezählt) werden. So kann ich zuverlässig abwägen ob/was ich einbaue ... oder eben nicht ...

mhm ok, und wenn du queries wie diesen: Select timestamp, No-, usw from Database$Item WHERE (("DecimalField">0)) AND (("Search Description" LIKE '%WAT%' AND "Search Description" LIKE '%28%')) AND "No_"<'23456789012345' ORDER BY "No_" DESC OPTION (RECOMPILE)

liest, der so oder so ähnlich permanent losgelassen wird legst du dann einen Index an mit: Search Description,DecimalFeld und included coloumns No_ an?

mhm ok, und wenn du queries wie diesen: Select timestamp, No-, usw from Database$Item WHERE (("DecimalField">0)) AND (("Search Description" LIKE '%WAT%' AND "Search Description" LIKE '%28%')) AND "No_"<'23456789012345' ORDER BY "No_" DESC OPTION (RECOMPILE)

liest, der so oder so ähnlich permanent losgelassen wird legst du dann einen Index an mit: Search Description,DecimalFeld und included coloumns No_ an?

Also wenn LIKE im Spiel ist - also "Wildcard" Abfragen -, dann kann man eh fast nixt tun: SQL Server kann keinen Entry- und Exit-Point eines Indexes bestimmen und wird den Index daher IMMER scannen ...
Die Minimalanforderung im obigen Beispiel wäre:

Code:

CREATE INDEX myidx01 ON [dbo].[Company$Item]
("Search Description", "No_")
INCLUDE
("DecimalField")

(Übrigens: Dezimal-Felder sollte man nie in den B-Tree legen, sonder stets als INCLUDE anhängen: Beträge, Mengen & Co. können aufgrund sehr hoher Unterschiedlichkeit einen Index stark vergrößern. SOwas macht nur sinn, wenn dann auch explizit auf einen bestimmten Wert gefiltert wird; z.B. "Amount" = 123.45 - meistens lauten solche Filter aber "Amount" <> 0 (wie auch in deinem Bsp.); deshalb ab in den INCLUDE)

Mehr ist da nicht zu machen. Man bedenke, dass speziell bei Item das BLOB Feld "Picture" auch problematisch ist (darum wird's im nächsten "NAV/SQL Quickie" gehen )
Wenn man hauptamptlich mit "Search Name" arbeiten muss, dann könnte man einen Index anlegen auf "Search Name" und ALLE anderen Felder in den INCLUDE legen (geht nur wenn "Picture" disabled ist); d.h. man kopiert quasi die Item-Tabelle in der anderen Sortierung. Auch häßlich, aber manchmal hilfts ...

Eine Alternative kann in solchen Fällen auch die Verwendendung der "Full Text Search" des SQL Servers sein; da CONTAINS Abfragen sportlicher ablaufen als LIKE ...
SIehe dazu auch http://dynamicsuser.net/blogs/stryk/archive/2009/09/26/using-sql-server-full-text-search-with-nav.aspx

Danke, ich werde es gleich mal ausprobieren. Hab ja heute eh nix zu tun und der Testserver muss ja schließlich auch was zu tun haben

Das BLOB hab ich schon in eine separate Tabelle ausgelagert und in der Item deaktivert. Dazu hat mir auch mal der user garak (der war/ist?) auch auf mibuso aktiv, diverse Tips gegeben. Zu diesem thema meinte er auch, dass ich evtl. mal die Volltextsuche mir anschauen sollte. Aber als erstes mir mal einen Schlüssel ala: Suchbegriff, Nr. anlegen soll mit den Feldern die sonst noch so abgefragt werden und mehrere Werte beinhalten (integer, decimal) als include. Quasi der selbe Tip wie der von Dir. Wenn mir also schon 2 den Tip geben, ist es mehr als einen Versich wert.

Kann ich die Volltextsuche nur über ADO nutzen? Vermute mal ja oder?

PS: Danke für dein Zeit und Hilfe.

Ja, "garak" ist ein aufmerksamer Leser meines BLOGS (u.a.)
In meinem BLOG zur "Full Text Search" sind die entsprechenden Beispiele dabei, inkl. ADO Anbindung in NAV.

Was das "nur ADO" angeht, so wäre meinen Gegenfrage "wie sonst"? Technisch gibt's sicher andere Möglichkeiten, aber ADO ist was in NAV am einfachsten zu integrieren ist ...

Und da issa der garak

1. ich vermute mal stark, dass in deinem (selbst kreierten?) Form, diverse C/AL "Bugs" vorhanden sind (Filter)
2. aus der Hüfte herus würde ich sagen, mach eine Sortierung nach Search Description deinen Benutzern schmackhaft
3. wenn man das partu nicht will, probier bitte auf dem SQL Server folgenden Index aus: No_, Search Descripton, [Andere Felder die sonst ggf. in dieser kombi gefiltert werden], Include Coloumn --> dein DecimalFeld.

4. Die Volltextsuche ist schon supi, nur musst du halt alles mit ADO abfragen. Dies geht natürlich nur ,wenn du eine separate Form, extra für solch eine Sucherei in NAv erstellst.

Gruß
Garak

PS an Stryk: Bin immer wieder von deinem Blog begeistert. Der bestätigt mich meist in meinen Ansichten bzw. gibt mir auch mal neue Ideen / Denkanstöße. Wäre ich nicht permanent mit meinen Kunden und meiner Familie beschäftigt, würde ich auch bloggen ...

Danke euch beiden. Mein Problem wurde tatsächlich mit dem zusätzlichen Index behoben. Das Teil ist jetzt sogar schneller als früher.
Danke nochmals

Super!

@mikka: können wir diesen Thread als [GELÖST] schließen? Sind ein wenig von Deinem eigentlichen Beitrag abgeschweift ...

Hallo Jörg,
sorry, ich bin vor lauter Arbeit nicht mehr zum Thread zurückgekommen.
Ich muß mit unserer IT sprechen, bis dahin setzte ich das Thema auf gelöst.

Danke Euch allen