October 13, 2010 at 5:25 am
On one of the prod servers(2000), app owners faced acute slowness wherein the server took ages to respond to user's query request.
1. 1st time the cause was memory leak. server had to be restarted.
2. 2nd time the cause was disk issue. batteries of one of the controllers had failed which was replaced and things looked better. We also ran pssdiag as suggested by microsoft and they further suggested to move tempdb to D drive which was done.
-------------
Now app team have given me a list of queries which is taking more than 10000ms in production box. Thats a long list:crying:
i ran some of the queries on dev and found its running fine there <100 ms which is ok. Dev db is almost same as prod (backup from prod was restored a month back)
On Dev, I also noticed that auto create statistics and auto update statistics were off so i turned it on and response was better.(i had got warning abt missing stats from execution plan).
These settings are also off on prod.
I have suggested app team to run perfmon to see hardware contention issue.
I think its more like hardware issue coz queries were running really fast on DEV.
Except for statistics settings issue, i did not find any intensive operation in the execution plans. no table scans.
Senior management is blaming the slowness is due to poorly written queries and trying to delay getting new hardware.
what do you think?
October 13, 2010 at 5:40 am
Most probably reason is stale statistics. Unless you have a very good reason, turn both auto_create and auto_update on. If you do decide to leave them off, then there MUST be a manual stats update job running regularly enough that the stats don't get too stale.
Poorly written queries is probably the second-most likely cause. That they're fast on dev doesn't mean they're well written, they could be written badly and be causing lots of blocking with multiple users and you wouldn't see that on dev because there's less activity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2010 at 5:52 am
Thanks Gail.
I've suggested app owners abt turning on statistics settings asap for which they should be raising an RFC which will be actioned in the next available green zone. [We cannot change any settings on prod without an rfc even if it doesnt need a downtime]
I have also asked them that i need to run some of the select queries on prod to see how they perform there.
Also, There has been no blocking in recent past on this server coz we get instant alert if there is any blockings.
October 13, 2010 at 6:32 am
Then schedule an 'update statistics ... with full scan' on all tables as soon as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2010 at 4:01 pm
We cannot change any settings on prod without an rfc even if it doesnt need a downtime
If this is the case then you should stress to your management that the RFC is in response to an operational/maintenance issue that is currently ongoing. Hopefully you can get the right backing for an expidited process like an emergency CAB meeting to approve.
Joie Andrew
"Since 1982"
October 13, 2010 at 11:17 pm
Yes, i got positive reply from app owners but they are reluctant to do anything outside greenzone. hopefully we do it this weekend.
Besides enabling the statistics, i'll run update statistics with full scan as suggested by Gail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply