SQL Server Profiler - sp_cursorexecute - High read values

anvar
Member Posts: 30
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:
2nd:
3th:
4nd:
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?
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 10737422910 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, @p60 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, @p5265 CPU 117633 Reads 0 Writes
4nd:
exec sp_cursorclose 1801507970 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?
0
Comments
-
Hi!
"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örgJörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool0 -
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!0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions