Slow GL Drilldown

bbrownbbrown Member Posts: 3,268
edited 2011-05-09 in SQL Performance
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:
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.

Comments

  • DenSterDenSter Member Posts: 8,304
    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'
  • bbrownbbrown Member Posts: 3,268
    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.
  • DenSterDenSter Member Posts: 8,304
    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.
  • bbrownbbrown Member Posts: 3,268
    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.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Make sure there are no BLOB fields with values. I had this problem with the slow item list.
  • bbrownbbrown Member Posts: 3,268
    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.
  • DenSterDenSter Member Posts: 8,304
    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/
    He also asked me to plug their blog: http://blogs.msdn.com/b/nav/
  • bbrownbbrown Member Posts: 3,268
    Thanks. I will let you know.
    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,086
    Alex Chow wrote:
    Make sure there are no BLOB fields with values. I had this problem with the slow item list.
    Even BLOB's that are empty can give problems (http://mibuso.com/blogs/kriki/2010/03/09/did-you-know-table-36-has-a-blob-field/).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • bbrownbbrown Member Posts: 3,268
    kriki wrote:
    Alex Chow wrote:
    Make sure there are no BLOB fields with values. I had this problem with the slow item list.
    Even BLOB's that are empty can give problems (http://mibuso.com/blogs/kriki/2010/03/09/did-you-know-table-36-has-a-blob-field/).

    Yes, but table 17 doesn't have any.
    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,086
    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.
    Even BLOB's that are empty can give problems (http://mibuso.com/blogs/kriki/2010/03/09/did-you-know-table-36-has-a-blob-field/).

    Yes, but table 17 doesn't have any.
    I wasn't refering to T17 but to "BLOB fields with values": no need to have values in them to have problems.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • bbrownbbrown Member Posts: 3,268
    kriki wrote:
    I wasn't refering to T17 but to "BLOB fields with values": no need to have values in them to have problems.

    got that. Thanks for the heads up for the future.
    There are no bugs - only undocumented features.
  • strykstryk Member Posts: 645
    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 Tool
  • bbrownbbrown Member Posts: 3,268
    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.
  • ara3nara3n Member Posts: 9,255
    I didn't see any mention of the sql server spec and configuration.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrownbbrown Member Posts: 3,268
    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.
  • bbrownbbrown Member Posts: 3,268
    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.
  • DenSterDenSter Member Posts: 8,304
    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.
  • bbrownbbrown Member Posts: 3,268
    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.
  • ara3nara3n Member Posts: 9,255
    have you run maintenance on your box. rebuild the indexes and update the stats?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrownbbrown Member Posts: 3,268
    ara3n wrote:
    have you run maintenance on your box. rebuild the indexes and update the stats?

    yup.
    There are no bugs - only undocumented features.
  • bbrownbbrown Member Posts: 3,268
    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.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    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.
  • ara3nara3n Member Posts: 9,255
    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?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrownbbrown Member Posts: 3,268
    yes
    There are no bugs - only undocumented features.
  • NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
    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
    
  • krikikriki Member, Moderator Posts: 9,086
    ara3n wrote:
    have you run maintenance on your box. rebuild the indexes and update the stats?
    Small remark on this: if you run a rebuild index, there is no need to update the stats. The rebuildindex automatically updates the stats (unless you specify explicitly to NOT 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!


  • NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
    kriki wrote:
    ara3n wrote:
    have you run maintenance on your box. rebuild the indexes and update the stats?
    Small remark on this: if you run a rebuild index, there is no need to update the stats. The rebuildindex automatically updates the stats (unless you specify explicitly to NOT 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.
  • bbrownbbrown Member Posts: 3,268
    :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.
Sign In or Register to comment.