SQL Server Profiler - sp_cursorexecute - High read values

anvaranvar Member Posts: 30
edited 2009-10-24 in SQL Performance
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?

Comments

  • strykstryk Member Posts: 645
    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
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • krikikriki Member, Moderator Posts: 9,094
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.