Tuning advice sought
-
How do you find a problem where between 8am-9am the application is in use and performance is fine, then from 9am on it grinds down, but according to the customer its your application is the issue not one of the other 20.
Do you mean that from 9am - 5pm the application performs poorly? Or, is there a point in the day where the application's performance becomes acceptable again?
If it becomes a problem at the point where the maximum number of users begin to use it and stays that way the rest of the day I would have to think you are experiencing locking and blocking issues. Basically everything you have been able to demonstrate so far is that the query in question performs adequately when under limited load, but when there is more activity it performs poorly which leads to locking blocking. Is the query you started the post with the only part of the application that is performing poorly is the entire application performing poorly?
One way to check to see if the issue is locking\blocking is to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for the query. This is essentially like applying the nolock hint for each table. This will give you dirty and potentially phantom reads, but if it improves performance it will help isolate the issue to locking\blocking.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi,
I have asked the customer to actually record when the application is slow during the day, they are doing this today so hopefully I will get a better picture of what is happening.
We have this application installed at close to 100 sites and this is the only one with issues like this. However on all the other sites our application is on a server by itself, normally a dual cpu with 2GB of memory.
I did some testing yesterday with the query at the start of this thread and I am even more confused now. I ran the query simulataneously several times on two different PC's using query analyzer and on one PC the query consistently took 30-40 seconds and on the other 200-300 seconds.
Ronnie
-
Hi,
Ask customer for some perfmon logs, showing disk queues and read/write times for the database disk. The customer hardware people say there is nothing wrong on the cluster but I would be interested in an indepdent opinion.
The samples where taken every 15 minutes over 4.57 hours.
They say that you cannot use Disk Queue as an indication of a problem
They say that this type of read performance is OK. I do not believe that a system that can take upto 4 seconds for a read is healthy.
They also say the writes are OK, but the maximum was 15 seconds for a write to complete ?
Thanks
Ronnie
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply