Extremely slow ISEMPTY, even with a good index???

pdj

SQL2008 (build 10.0.1600) with NAV5SP1-Upd1 (build 27191)
I have a lot of ISEMPTYs that shows up in the Profiler as very expensive which I find rather strange.

Example:
SELECT TOP 1 NULL FROM "MyDatabase"."dbo"."MyCompany$Item Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Prod_ Order No_"=@P1)) AND (("Prod_ Order Line No_"=@P2)) AND (("Entry Type"=@P3))

The Item Ledger Entry has an SQL Index with "Prod_ Order No_" and "Prod_ Order Line No_" first. However; it takes more than 3 seconds and six million reads each time! (And our production orders are NOT that big...)

I tried getting the EP from the "simple" statement in the query window, which just shows a simple IndexSeek and IndexLookup – each estimated as 50%. NAV is using a Dynamic cursor for normal SELECT statements, as described in another tread. I don’t think it makes much sense to make any kind of cursor for an ISEMPTY but I tried making this statement after all:
declare @P1 varchar(20),@P2 int,@P3 int
declare C CURSOR DYNAMIC FOR
SELECT TOP 1 NULL FROM "MyDatabase"."dbo"."MyCompany$Item Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Prod_ Order No_"=@P1)) AND (("Prod_ Order Line No_"=@P2)) AND (("Entry Type"=@P3))
Now the EP became very complex. In the upper right corner it is still an IndexSeek and KeyLookup with the proper index, but now only estimated as 9% and 18% of the statement. There are also a lot of steps with CWT_PrimaryKey and a lot of other “strange” boxes. I have no idea what they are doing, and basically don’t care. But why the h… does it take more than 3 seconds and six million reads to execute this simple query???

ara3n

I have seen this as well.
It is called during adjust to check for cyclic application.
The problem is that it is called for every Entry.

I've added code to exit if "production Order No." is blank.


I'll have to look where that code was.

pdj

Ok, that was one way to solve it
I have several other ISEMTPY statements in other tables, but this is clearly the worst example. I thought it could be solved by some setting or update to either SQL or NAV. But I might just end up having to fix it in C/AL.

ara3n

You can add a index filter for that Key to only have Production No that are not blank.
This would dramatically improve the index seek.

garak

Do you use indexhint or recompile?

ara3n

I reread your post and you mentioned that the query plan does an index seek. and then lookups the record.
You can add a index filter for that Key to only have Production No that are not blank.
This would dramatically improve the index seek.

pdj

garak wrote:

Do you use indexhint or recompile?

No.

pdj

ara3n wrote:

I reread your post and you mentioned that the query plan does an index seek. and then lookups the record.
You can add a index filter for that Key to only have Production No that are not blank.
This would dramatically improve the index seek.

You are suggesting to use the new SQL2008 feature that makes it possible to set filters on an index, correct?
First of all I would like to avoid making these kind of changes directly in SSMS, as they are overwritten when the object table is re-imported. But if it the only way...
Secondly I didn't think filtered indexes were a good match with the NAV way to prevent parameter sniffing. When NAV wishes to send a new SELECT statement it asks the SQL server to generate an EP without providing the filter values. Then SQL has no idea if it can use any filtered indexes or not. Then it would surprise me if it actually plan to use it. But maybe the SQL has some intelligence I don't know about... Have you tried it, and which version of NAV were you using?

But back to my question: How can it be that SQL Server seems to use a perfect index, and still makes more than 6 million reads?

garak

pdj wrote:

garak wrote:

Do you use indexhint or recompile?

No.

did you test it? We had some several problems, and with the recompile this statement runs very well.

pdj

Mystery solved! (I hope)

I recently created the Index and right after I flushed the EP cache with “DBCC FREEPROCCACHE”
I then expected that all EPs would get recompiled the next time. But I guess that several NAV clients were open at the time, and might have had a cursor using the old EP or a previously prepared SQL statement in memory. Either way I guess this has made SQL to ignore my attempt to force a complete re-compile of the EPs.
We are restarting the SQL server in a few days, and then I’ll check if it re-appears in the Profiler.