Slow GL Drilldown

bbrown
Member Posts: 3,268
We have a site experiencing unacceptable performance when drilling down on GL Accounts. The database is not that large (25 GB) and I'd consider table 17 (4 million entries) rather small. Drilling down on an account with about 200,000 entries can take 20 seconds or longer to open and display. Issue exist with both classic and RTC.
Client sytem details:
NAV 2009 SP1 (build 32416)
SQL 2008
The client server might be considered somewhat light but we've also got a copy of this database on our internal server. It is showing the same behavior. So I don't think hardware is my issue.
Here's the query from profiler:
Thoughts?
Client sytem details:
NAV 2009 SP1 (build 32416)
SQL 2008
The client server might be considered somewhat light but we've also got a copy of this database on our internal server. It is showing the same behavior. So I don't think hardware is my issue.
Here's the query from profiler:
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int', N'SELECT * FROM "Production"."dbo"."CompanyName$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">=@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_" OPTION (OPTIMIZE FOR UNKNOWN,FAST 10)',@p5 output,@p6 output,@p7 output,'11100','11100','2004-04-30 00:00:00',7790 select @p1, @p2, @p5, @p6, @p7
Thoughts?
There are no bugs - only undocumented features.
0
Comments
-
Here are the obvious questions:
- Is there a key for Account number and posting date?
- Is MaintainSQLIndex turned on?
- Has the index been rebuilt recently?
- Do they have up-to-date stats?
- Any strange looking SQLIndex values?
- Is the drilldown not trying to use a different sort order? Descending order?
- Is the SourceTablePlacement property set to 'Saved'? Try changing that to 'Last'
0 -
Thanks for the reply:
Is there a key for Account number and posting date?
Yes
Is MaintainSQLIndex turned on?
Yes
Has the index been rebuilt recently?
Yes, rebuilt as part of troubleshooting. No noticable difference
Do they have up-to-date stats?
Yes, see above
Any strange looking SQLIndex values?
Not that I've noticed.
Is the drilldown not trying to use a different sort order? Descending order?
No. Both table 17 and form 20 are unmodified.
Is the SourceTablePlacement property set to 'Saved'? Try changing that to 'Last'
Since the form was not modified this was the default of <Saved>. I tried both 'Last' and 'First' with no noticable change.There are no bugs - only undocumented features.0 -
Strange, one of those is usually the culprit. Have you tried running that query directly in SSMS? What about trying different machines? Is it maybe a wireless connection? Any blocks while the query runs?
What's the NAV version? What about SQL Server? Have the latest SP's and hotfixes been applied?
At convergence I did a session with the NAV support guys and they mentioned that there were some specific SQL Server updates that made a huge difference in performance. I'll see if I can get in touch with one of them and ask.0 -
I agree. That's why I'm somewhat banging my head over this one. No wireless involved. Running the query in SSMS, over a 1 GB connection, takes 23 seconds to return 180,000 records. The same query directly on the server takes 14 seconds.
I was testing on the client system early this morning with only a few users on. I didn't look explicitly for locks but don't think so. The database on our server I was the only user. So that would not be a locking issue.
The NAV version is 2009 SP1. Neither of the SQL Servers is fully up to date. So I won't rule that out. The client is 10.0.4000.0 which is SQL 2008. The client kept insisting they were running R2. Had to break the news.
Our internal server is 10.50.1600 which is the R2 RTM release.
If you can get any feedback from your NAV support guys, that would be great.There are no bugs - only undocumented features.0 -
Make sure there are no BLOB fields with values. I had this problem with the slow item list.Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
Alex Chow wrote:Make sure there are no BLOB fields with values. I had this problem with the slow item list.
Nope, this is standard (ummodified) table 17 data. I saw your post on that issue when I was searching before posting.There are no bugs - only undocumented features.0 -
Got a reply:It shows them on SQL 2008, so they should make sure they are on SP1 or SP2 and then grab the newest cumulative update as the cumulative update has seemed to help a lot. They could move to SQL 2008 R2 also, but still would need to grab the latest cumulative update and R2 would require a little more work to update to.
I believe the latest for SQL 2008 R2 is cumulative update 7
SQL 2008 SP2 is at cumulative update 3
SQL 2008 SP1 is at cumulative update 13
Here is a good blog to show latest cumulative updates based on which SP you are on, but I would recommend being on SP2 or R2.
http://blogs.msdn.com/b/sqlreleaseservices/0 -
Thanks. I will let you know.There are no bugs - only undocumented features.0
-
Alex Chow wrote:Make sure there are no BLOB fields with values. I had this problem with the slow item list.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
kriki wrote:Alex Chow wrote:Make sure there are no BLOB fields with values. I had this problem with the slow item list.
Yes, but table 17 doesn't have any.There are no bugs - only undocumented features.0 -
bbrown wrote:kriki wrote:Alex Chow wrote:Make sure there are no BLOB fields with values. I had this problem with the slow item list.
Yes, but table 17 doesn't have any.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
Well, what is the Clustered Index of "G/L Entry"? Still "Entry No."?
In some - actually many - cases it is feasible to set the "Clustered" on the second "Key" ("G/L Account No.", "Posting Date"), as then the physical order of the records matches better to most of the NAV queries ...
Could this help?Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool0 -
stryk wrote:Well, what is the Clustered Index of "G/L Entry"? Still "Entry No."?
In some - actually many - cases it is feasible to set the "Clustered" on the second "Key" ("G/L Account No.", "Posting Date"), as then the physical order of the records matches better to most of the NAV queries ...
Could this help?
It is still the default "Entry No.".
I can see this if we were talking about a much larger table. But this is tiny. Less then 4 million records. But anyways I'll give this a try in my test copy. Just to see if it does make a difference.There are no bugs - only undocumented features.0 -
I didn't see any mention of the sql server spec and configuration.0
-
ara3n wrote:I didn't see any mention of the sql server spec and configuration.
from original post:The client server might be considered somewhat light but we've also got a copy of this database on our internal server. It is showing the same behavior. So I don't think hardware is my issue
Since I'm able to easily duplicate this behavior on our internal somewhat larger server, I'm not focused on hardware as the major issue. This is a site that was recently upgraded for 5.0 SP1. The old system does not have this issue.
Just for the record, the production server is a virtual server with dedicated physical disk for the database.
The server on which I am testing:
CPU: 2 quad core 2.00 GHZ Xeon
RAM: 16 GB
Data Disk: 10x RAID 10
Log disk: 6x RAID 10
I'd consider this more then adequate for a 25 GB database.There are no bugs - only undocumented features.0 -
stryk wrote:Well, what is the Clustered Index of "G/L Entry"? Still "Entry No."?
In some - actually many - cases it is feasible to set the "Clustered" on the second "Key" ("G/L Account No.", "Posting Date"), as then the physical order of the records matches better to most of the NAV queries ...
Could this help?
I went ahead and tested the clustered index change. The result was about a 20% increase in speed. 16 seconds vs. 20 seconds to open the test account. Still considerably slow but maybe part of the solution. I'll see what MS comes back with.There are no bugs - only undocumented features.0 -
I know this is a longshot, you already know this, but I thought I'd mention it anyway. 5.0 SP1 exe's means that SQLIndex is now essentially obsolete. Create a new form based on the Key virtual table, filter the SQLIndex column on <>'' and make a list of keys that have a SQLIndex. If you have any, remove all of them, and see if that helps.0
-
DenSter wrote:I know this is a longshot, you already know this, but I thought I'd mention it anyway. 5.0 SP1 exe's means that SQLIndex is now essentially obsolete. Create a new form based on the Key virtual table, filter the SQLIndex column on <>'' and make a list of keys that have a SQLIndex. If you have any, remove all of them, and see if that helps.
Thanks. One of the things I checked. Looking to see if SQL was running off and using some weird index.There are no bugs - only undocumented features.0 -
have you run maintenance on your box. rebuild the indexes and update the stats?0
-
ara3n wrote:have you run maintenance on your box. rebuild the indexes and update the stats?
The one thing I haven't had a chance to do yet is the SQL updates. I'm going to revisit this once I've had a change to get those in.There are no bugs - only undocumented features.0 -
If it's SQL related, I can't imagine the problem to only be specific to only G/L Drilldowns. You should see the same slowness when you drill down item ledger, cust ledger, etc.Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
bbrown wrote:ara3n wrote:I didn't see any mention of the sql server spec and configuration.
from original post:The client server might be considered somewhat light but we've also got a copy of this database on our internal server. It is showing the same behavior. So I don't think hardware is my issue
Since I'm able to easily duplicate this behavior on our internal somewhat larger server, I'm not focused on hardware as the major issue. This is a site that was recently upgraded for 5.0 SP1. The old system does not have this issue.
Just for the record, the production server is a virtual server with dedicated physical disk for the database.
The server on which I am testing:
CPU: 2 quad core 2.00 GHZ Xeon
RAM: 16 GB
Data Disk: 10x RAID 10
Log disk: 6x RAID 10
I'd consider this more then adequate for a 25 GB database.
I'm assuming this is 64 bit OS and 64 bit sql?0 -
yesThere are no bugs - only undocumented features.0
-
If you have a long running query, you can use the dmo sys.dm_exec_requests to get information about requests that are currently executing within SQL Server. Using a cross apply or outer apply on sys.dm_exec_query_plan can give you the execution plan (without runtime values). With this information it is normally possible to see why it is slow.
Simple example:SELECT r.session_id ,status ,command ,qt.text ,r.cpu_time ,r.total_elapsed_time ,r.reads ,r.writes ,r.logical_reads ,pt.query_plan from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) as qt cross apply sys.dm_exec_query_plan(plan_handle) as pt where r.session_id > 50
0 -
ara3n wrote:have you run maintenance on your box. rebuild the indexes and update the stats?
If you do an index defrag, an update of the stats is needed.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
kriki wrote:ara3n wrote:have you run maintenance on your box. rebuild the indexes and update the stats?
If you do an index defrag, an update of the stats is needed.
'Column statistics' are never updated when rebuilding an index.0 -
:oops:
I think I may have found the issue. I generally tend to doublecheck things that clients tell me. I had checked the server and sure enough it had the 16 GB of memory the client said was installed. However what I didn't notice at the time was that someone had set SQL maximum memory to 3 GB. When I brought this to the cleint's attention they were surprised it was set like that and raised it. Now the drilldown is much faster. About 5 seconds on something that was taking 20. Not blindign fast but an improvement where it becomes much more usable.
I'll keep an eye on it and see how it settles in.There are no bugs - only undocumented features.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