Dynamic Cursor trouble (WAS: How-to re-create NAV SQL?)

pdj

NAV5SP1 introduces prepared SQL statements to prevent parameter sniffing, but how do I re-create it in a SSMS Query Window?
I have used the T-SQL statement from http://blogs.msdn.com/microsoft_dynamic ... story.aspx and found several statements with “room for improvements”.
One of the queries seems quite simple:

(@P1 int,@P2 int,@P3 varchar(20))
SELECT  * FROM "MyDatabase"."dbo"."MyCompany$Registered Whse_ Activity Line" WITH (READUNCOMMITTED)   
WHERE (("Activity Type"=@P1)) AND (("Source Type"=@P2)) AND (("Source No_"=@P3)) 
ORDER BY "Activity Type","Source Type","Source No_","Registering Date","No_","Line No_"

I had already reduced the indexes of the table a lot, but I have the original primary key marked as Clustered and have an Index purely containing the “Source No_”. All in all I don’t see any reason why this shouldn’t work smoothly. However the statistics say its avg_logical_reads is 11.086.082 !!
The query runs fine when executed in the old fashioned style, but apparently it doesn’t when NAV is using its prepare/execute logic. So how do I re-create the query the-NAV-way, or can someone suggest me what the problem might be? :?:

Thanks in advance

edit: Changed thread subject from "How to re-create a NAV SQL statement (with Prepare/Execute)?" to reflect the latest discussion...

garak

SQL2005 (i think so) with which TraceFlags and which version?
Indexhint / Recompile is not activated?
Do you have maintenace jobs on the sql server? If yes, which and how often runs these jobs?
Do you know where in NAV this statement is fired?

"Activity Type","Source Type","Source No_","Registering Date","No_","Line No_" is not the Primary key and it's not a default key. So it's a customized key. Which index use the SQL server if this query is executed (Index$0,$1,$2, and so on)?

Also i belave, that this key ("Activity Type","Source Type","Source No_","Registering Date","No_","Line No_") is not very effective for the sql server You filter on the Source No. so, why do doesn't begin you key with this identifer (for your query example where you need all the lines for a specific Source no)?
So it's more effective for the server to find the result set so fast as possible ....

viewtopic.php?f=34&t=32657

Regards

pdj

Ahh, sorry. Using NAV5SP1 on SQL2005SP3, and the only trace flag set is the mandatory 4616. I have not enabled index hints or re-compile. No maintenance jobs either (yet!). It is a new server and has only been used for some preliminary tests. No, I don’t know what CA/L that generates the statement.

When I execute the statement directly (without prepare/execute) it uses the “Source No_” index as expected, so no problem. But apparently NAV makes it use the clustered primary index instead. That’s why I would like to be able to replicate the way NAV is executing the SQL statement.

I agree that the sort order is not the most optimal, but if SQL just used the proper index it shouldn’t be a problem for it to sort the 5-10 records on-the-fly before presenting it to NAV.

garak

NAV use RPC to "talk" with the sql server. So, the sql server makes a "parameter sniffing". So it's possible that he use a wrong execution plan. Create before 2 maintenance jobs.

sp_updatestats
go
sp_createstats 'indexonly' '<- stats only for indexfields should be enough

and a ReBuildIndex maint. job.
Now, it's very helpful to know from where in NAV this query is executed (you can search for it with the DEVTol) and run then this query in NAV (after the jobs are executed). Start also the sql profiler to record the queries (how to setup the sql profiler follow the link above)

Regards

pdj

Thanks and I basically agree. However; you seem to miss my main question:

NAV5SP1 introduces a new way to execute SELECT statements to prevent parameter sniffing. How do I do the same in a query window?

Marije_Brummel

If you ask for the expected execution plan based on parameters you should get the same plan as executed with the parameter sniffing solution. So just copy and paste the query and declare the variables without assigning values.

The prepare solution is working briliant at our customers. I cannot remember the last time I created an index hint. We are removing them now...

pdj

Ahh, of course! - The obvious solution is easily overlooked.... :oops: Thanks.

However, the statement still behaves as expected (9% on the “Source No_” index, 61% on Key Lookup and 30% on sorting). But why does the Execution Plan Cache have information about 13 executions with an average of more than 11 million reads? (And why does it keep reporting only 13 executions, when I have executed it multiple times in a query window??)

And I agree about the Index Hints – I actually expected we needed to hint the indexes starting with Open, but it rarely seems necessary.

Marije_Brummel

How many records are in the filter on source no? In SQL2005, the sorting is sometimes very expensive compared to SQL2000. We often add non selective fields to the indexes in order to allow top 1 statements to work with as few reads as possible. If you do a top 1 on a wide filter you need a wide index otherwise SQL needs to read the CI to determine the top record of the set.

Hope this makes sense...

pdj

It makes sense alright, but I doubt this it is the problem here. There are typical only 4-20 lines for each Source No, but a few are up to 150-200 lines. This seems to be an ordinary read of all lines with the same Source No.

Could the problem simply be the query from Lars that returns misleading results? (Having read most of Lars’ entries I doubt it, but you never know :-))

Marije_Brummel

That is something I cannot answer since I never use his stuff, but our own.

stryk

Hi!

Well, you mentioned you have removed several indexes. Actually this it not necessarily an improvement: you should only remove indexes which are not used - and this you have to KNOW, not just guess ...

There is a difference when executing the "same" TSQL statement via NAV or SSMS: with NAV this is a "cursor operation", with SSMS it isn't; this alone could be reason for getting different Execution Plans, besides any Parameter Sniffing issues.

This huge number of reads actually indicates that the table/clustered index is scanned, so I suggest to add this index:

create index test on"dbo"."MyCompany$Registered Whse_ Activity Line"
("Activity Type","Source Type","Source No_")

(or implement something similar in NAV) Anything changes?

Something general about the "prepare statements":
actually it works like this: first the SHOWPLAN_ALL is set to ON, so the SQL Server will not process the following query but just generate an execution plan. Then the query is sent - an EP is generated - then SHOWPLAN_ALL is set to OFF.
This gives the SQL Server a chance to generate a good EP, not using an old cached one and doing something stupid.
This preparation solves lot of problems, but not all. In some cases you might still need RECOMPILE hints to really get the best EP ...

pdj

Hi Stryk,

I know randomly removing indexes isn’t the way to optimize a NAV solution, but I’ll claim I have a bit of knowledge about SQL and the customers NAV usage so I’m quite sure my changes all in all are for the better :-)
(And I would even say, that just because an index is currently used it doesn’t mean it is best to keep it as it is, but that is a different discussion)
Regarding trying to add an index, then I’m simple examining a server that has been used for preliminary test before going live (got 20 users to run extensive test for more than half a day). So I need to extract all the information I can based on the current statistics and the saved traces of long transactions. (And deadlocks, but that is also a different discussion)

Thanks for the information about SHOWPLAN_ALL, but it doesn’t seem to be the way NAV5SP1 is working. I’m quite sure I have seen it demonstrated that first a SELECT statement got prepared and SQL returned some sort of ID of the statement. Next the parameters were set and the statement was executed by referencing to the ID provided from the initial call. But Mark’s suggestion of simply getting the suggested EP seems to give a usable answer.

I tried experimenting executing a cursor operation like this:

declare
@P1 int,@P2 int,@P3 varchar(20)
declare C CURSOR FAST_FORWARD FOR
    SELECT  * 
    FROM "MyDatabase"."dbo"."MyCompany$Registered Whse_ Activity Line" WITH (READUNCOMMITTED)   
    WHERE (("Activity Type"=@P1)) AND (("Source Type"=@P2)) AND (("Source No_"=@P3)) 
    ORDER BY "Activity Type","Source Type","Source No_","Registering Date","No_","Line No_"

This just resulted in lot of additional information in the EP, but the usage of indexes seems to be exactly the same as for non-cursor operations. (Isn’t NAV5SP1 using a fast-forward cursor, or has that been changed as well?)

Any other suggestion why the EP seems ok, but the cache statistics seems to be so bad?

stryk

OK, obviously I misunderstood, you were referring to the "sp_prepare" thing, thats different method, but with the same effect (the SHOWPLAN thing should be implemented in 5.0 SP1 - maybe it depends on the build number ... or do I err?)
But even though you have "optimized" the indexes - while KNOWING what you were doing - is there any change once you add my proposed index? Have you tried a RECOMPILE hint here?

pdj

I doubt it would make any change if I added the index or added an recompile, as I'm still unable to reproduce any problems with the statement in SSMS. It works fine without making any unnessesary reads. But the statistics claims there were a problem when the users were working in NAV. :?

stryk

Well, my guess is, that when executing the query from NAV then SQL Server recalls a cached - bad - Execution Plan, scanning an index or something.
If that is indeed the case, you could only fix this by providing an optimized index to avoid generating such a bad EP, and/or applying a RECOMPILE hint to give SQL a chance to create a better EP than the cached one (if that still fails I would go for an INDEX hint).

pdj

pdj wrote:

I tried experimenting executing a cursor operation like this:

declare
@P1 int,@P2 int,@P3 varchar(20)
declare C CURSOR FAST_FORWARD FOR
    SELECT  * 
    FROM "MyDatabase"."dbo"."MyCompany$Registered Whse_ Activity Line" WITH (READUNCOMMITTED)   
    WHERE (("Activity Type"=@P1)) AND (("Source Type"=@P2)) AND (("Source No_"=@P3)) 
    ORDER BY "Activity Type","Source Type","Source No_","Registering Date","No_","Line No_"

This just resulted in lot of additional information in the EP, but the usage of indexes seems to be exactly the same as for non-cursor operations. (Isn’t NAV5SP1 using a fast-forward cursor, or has that been changed as well)

NAV5SP1 is not using FAST FORWARD cursors, but DYNAMIC cursors! This is the reason for the poor results in the EP cache. Simply by changing "FAST FORWARD" to "DYNAMIC" in the above statement it uses an unsuitable index with a very high number of reads. ](*,)

More info can be found here:
http://blogs.msdn.com/microsoft_dynamic ... types.aspx
http://blogs.msdn.com/nav_developer/arc ... 0-sp1.aspx

I find this change to have bigger drawbacks than benefits. Any comments? :-k

stryk

See also http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2009/02/20/cursor-types.aspx
--- Edit ---
Ups, sorry, just realized you've mentioned this one ... :oops:


I think with DYNAMIC cursors it's somewhat "trickier" to create really good indexes, to find the fair compromise between WHERE and ORDER BY optimization ... but so far I have not seen too many severe problems caused by this; and if so, usually I could fix it by adding/changing an index ...

pdj

stryk wrote:

I think with DYNAMIC cursors it's somewhat "trickier" to create really good indexes

Now I have been fighting with them a few days and I still think SQL is simply acting more stupid when using dynamic cursors. #-o

Take my query as an example:
WHERE (("Activity Type"=@P1)) AND (("Source Type"=@P2)) AND (("Source No_"=@P3))
ORDER BY "Activity Type","Source Type","Source No_","Registering Date","No_","Line No_"

Here SQL picks this index: "Activity Type","Source Type","Registering Date"
Ok, then I tried to change the SQLIndex of this key to simply be "Registering Date".
It still picked this index, eventhough it has an index just with the "Source No." !! ](*,)
I really don't see how this could be better than picking the "Source No." index, and then sort the 5-15 lines afterwards...
Well, then I just have to make the "Activity Type","Source Type","Source No_" index to get SQL happy (but rather stupid IMHO)

pdj

pdj wrote:

Well, then I just have to make the "Activity Type","Source Type","Source No_" index to get SQL happy (but rather stupid IMHO)

That was a bit too optimistic. I had to create and index with "Activity Type","Source Type","Source No_","Registering Date" before it prefers it.
I rest my case... ](*,)

stryk

Hi Peter,

meanwhile I really agree that the DYNAMIC cursors are a pain ... ](*,)

Previously I could fix most problems by adding somewhat optimized indexes which where a fair compromise between WHERE and ORDER optimization.

But just recently I've been working on a system which REALLY suffers from this cursor stuff ... SQL Server ALWAYS picking the index related to the ORDER BY / Key used ... thus, stupidly scanning indexes (in this case it tool 2.000.000 Reads and 2.500 msec).

After fiddling a lot with this (Indexes, RECOMPILE, etc.), I finally could only fix this by providing an index the contained ALL fields from the WHERE clause plus ALL field from the ORDER BY ... of course, this created supersize indexes, but I could cut down the Reads to 6, Duration to 1 msec ...

But this CANNOT be the general solution for this; I think it would be smart if MS provides us a feature to swith back to FAST_FORWARD cursors ... :-k

pdj

Thanks Stryk,

I was starting to think I was the only one with this problem.
We really need MS to make it optional in some way, or a way to tell SQL to use a better EP for dynamic cursors...

I have been told by MS that dynamic cursors results in fewer locking problems, eventhough they result in more reads. I don't see why this should be the case, but I don't know enough about how SQL handle the different cursor types (except for the poor EP for Dynamic cursors). Do you know anything about that?

stryk

Hmmm ... I cannot imagine how the cursor type directly affects the blocking issues ... except this:

If a "bad" cursors causes an index scan - especially clustered index scan - then this scan has a higher probability of getting blocked (e.g. if another process is writing data into this index) than if performing a straight index seek ...

But this issue should happen in any case - fast forward and dynamic cursors ...

I posted a comment on the related SE BLOG, waiting for reply ...

pdj

Please check http://mibuso.com/forum/viewtopic.php?t=37836
Thanks.