mibuso.com

Microsoft Business Solutions online community
It is currently Mon May 20, 2013 2:41 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 30 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 3:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8699
Location: 3rd rock from sun
Country: United States (us)
I didn't see any mention of the sql server spec and configuration.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 3:46 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
ara3n wrote:
I didn't see any mention of the sql server spec and configuration.


from original post:

Quote:
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.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 4:02 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 4:18 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 4:31 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 5:51 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8699
Location: 3rd rock from sun
Country: United States (us)
have you run maintenance on your box. rebuild the indexes and update the stats?

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 5:53 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
ara3n wrote:
have you run maintenance on your box. rebuild the indexes and update the stats?


yup.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 5:58 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 6:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Jun 11, 2004 9:05 pm
Posts: 4856
Location: Los Angeles, CA
Country: United States (us)
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


Top
 Profile E-mail WWW  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 7:06 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8699
Location: 3rd rock from sun
Country: United States (us)
bbrown wrote:
ara3n wrote:
I didn't see any mention of the sql server spec and configuration.


from original post:

Quote:
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?

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 7:39 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
yes

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Fri May 06, 2011 11:06 pm 
Offline

Joined: Fri May 06, 2005 3:26 pm
Posts: 37
Location: Netherlands
Country: Netherlands (nl)
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:
Code: Select all
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


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Mon May 09, 2011 4:13 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7493
Location: Milan
Country: Italy (it)
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
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Mon May 09, 2011 4:25 pm 
Offline

Joined: Fri May 06, 2005 3:26 pm
Posts: 37
Location: Netherlands
Country: Netherlands (nl)
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.


Top
 Profile  
 
 Post subject: Re: Slow GL Drilldown
PostPosted: Mon May 09, 2011 5:04 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
: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.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 30 posts ]  Go to page Previous  1, 2

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: