SQL Server Profiler - sp_cursorexecute - High read values

anvar

Hello,

We have a little preformance issue with a navision db.
The Navision has a own written querry that shows all historical data of a customer purchases. When we open this querry it can last up to more than a minute before the screen starts to build up.
When we look with SQL Profiler to the made reads/writes we see that our querry takes up to 30000 reads for 6 a 10 times.

In the profiler we see 4 items that repeats x times.

1st:

exec sp_cursorunprepare 1073742291

0 cpu 0 reads 0 writes
2nd:

declare @p1 int
set @p1=1073742504
declare @p5 int
set @p5=12290
declare @p6 int
set @p6=8193
exec sp_cursorprepare @p1 output,N'@P1 varchar(20),@P2 int,@P3 varchar(20)',N'SELECT  * FROM "Customername(2009)"."dbo"."CustomerName (5_0 SP1)$Sales Line" WITH (READUNCOMMITTED)  WHERE (("Bill-to Customer No_"=@P1)) AND  "Document Type"=@P2 AND "Document No_">@P3 ORDER BY "Document Type","Document No_","Line No_" ',1,@p5 output,@p6 output
select @p1, @p5, @p6

0 CPU 0 reads 0 writes
3th:

declare @p2 int
set @p2=180151025
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursorexecute 1073742504,@p2 output,@p3 output,@p4 output,@p5 output,'4857',0,''
select @p2, @p3, @p4, @p5

265 CPU 117633 Reads 0 Writes
4nd:

exec sp_cursorclose 180150797

0 CPU 0 Reads 0 Writes

Now the problem is that we don't know why the third command takes so many reads & cpu and also why its been repeated x many times.

A little word about the hardware & config to state that this isn't the problem.
Dedicated server for SQL
Xeon Quad Core
8GB DDR2
RAID1 OS&SQL
RAID10 Data files
RAID1 LOG
(I know that it would be better to place our log also on a RAID10)
10k 146GB SAS RAID disks
Shipping translog every 15min
Every night update stats
Every night full backup
This setup works allmost on all our customers server without notable slow downs.

Anyone an idea?

stryk

Hi!

N'SELECT * FROM "Customername(2009)"."dbo"."CustomerName (5_0 SP1)$Sales Line" WITH (READUNCOMMITTED) WHERE (("Bill-to Customer No_"=@P1)) AND "Document Type"=@P2 AND "Document No_">@P3 ORDER BY "Document Type","Document No_","Line No_"

I think the problem is this: the filter (WHERE) and sorting (ORDER BY) do not fully match. If you are using NAV 5.0 SP1 before build 29118 the here a "Dynamic Cursor" (DC) should be used. DC are optimizing for the ORDER BY clause, thus it could happen that this cursor processing is forcing the Server to use index $0 (Primary Key = ORDER BY).
"Bill-to Customer No_" is part of several other indexes, but maybe it couold help to create an optimized one for that query:

CREATE INDEX test01 on "CustomerName (5_0 SP1)$Sales Line"
("Bill-to Customer No_", "Document Type", "Document No_")

If the index alone does not help, maybe you have to create a full "Key" plus "SQL Index" in NAV and make sure this query is using this "Key" for the sorting SETCURRENTKEY).

If you still encounter problems and if you see quite different amounts of "Reads" then maybe the whole thing is related to a "Parameter Sniffing" problem; in this case you might set a RECOMPILE hint ...

Hope this helps a little.

Regards,
Jörg

kriki

You did mention statistics update.
But do you do index rebuilds on a regular basis? If you do that each night, you don't need a statistics update anymore as the indexrebuild as creates statistics.