Dave Ballantyne (12/19/2012)
The writes could be due to a memory spillhttp://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx
In terms of what else *could* (pure guesswork 🙂 ) be happening here:
The "delay" may not even be in SQL Server, quite often the client app is slow at reading the data.
As you have gazzilions of , presumabley, wide rows this could well be the case.
An easy test is to run the query in SSMS but turn on the "discard results after execution option" , you will probably find a massive improvement 🙂
Use the "Include client statistics" to quantify ( in bytes ) the result set size.
Also this query is *wrong*. Why use option (RECOMPILE) AND OPTIMIZE for Uknown , doesent make sense?!?
Optimize for unknown prevents usage of the varaiable value to build an execution plan but you are taking the hit of recompile anyway so why not use the value.
Thanks for the link; I check it out ASAP.
The NAV client for sure is a problem, but on top of that query. That NAV is still 32bit and actually incapable to deal with such a huge amount of records - but again: zero chance to change this (except for upghrading the whole NAV thingy).
Regarding RECOMPILE and OPTIMIZE FOR UNKNOWN:
NAV automatically adds OFU to all SELECT queries ... no chance to change that, but usually it works fine!
On some queries on that table we experienced problems in the past, where SQL Server was doing something "unxexpected" even due to the OFU, so we tried to "overwrite this with a RECOMPILE hint we can only specify these hints on table level (or we would have to use more precise Plan Guides). NAV cannot consolidate this and is firing now both ...
My customer reported, that this problem occurs randomly, it feels like just every two weeks ... obviously it arises as suddenly as it then - somehow? - disappears ... ???
Riddle after riddle ... :crazy:
Jörg A. Stryk
MVP - MS Dynamics NAV