Slow GL Drilldown

bbrown

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?

DenSter

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'

bbrown

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.

DenSter

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.

bbrown

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.

Alex_Chow

Make sure there are no BLOB fields with values. I had this problem with the slow item list.

bbrown

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.

DenSter

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/

bbrown

Thanks. I will let you know.

kriki

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/).

bbrown

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.

kriki

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.

bbrown

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.

stryk

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?

bbrown

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.

ara3n

I didn't see any mention of the sql server spec and configuration.

bbrown

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.

bbrown

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.

DenSter

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.

bbrown

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.

ara3n

have you run maintenance on your box. rebuild the indexes and update the stats?

bbrown

ara3n wrote:

have you run maintenance on your box. rebuild the indexes and update the stats?

yup.

bbrown

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.

Alex_Chow

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.

ara3n

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?

bbrown

yes

NaviDeveloper_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:

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

kriki

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.

NaviDeveloper_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.

bbrown

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