mibuso.com

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

from original post:



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

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.

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

yup.

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.

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.

I'm assuming this is 64 bit OS and 64 bit sql?

yes

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:

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.

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.