February 6, 2010 at 4:49 am
We have a SQL 2005 instance running on a dedicated server. And the last week or so we have experienced huge performance problems. Our web requests have piled up and the source is our db where our SPROCs are taking forever to complete.
We can see that the our IO queue and wait are huge.
I have just looked at the "Memory: Pages/sec" performance counter - and it is very strange.
When our site is unreachable (and we are seeing massive amounts of SQL timeouts), the Pages/sec counter is just plain zero.
When our site becomes reachable the counter starts to fluctuate with average around 9 and spikes up to 50.
This I don't understand....
CPU usage is 12% and memory consumption is 2/5 gigs.
Any ideas?
February 6, 2010 at 5:52 am
did u check with the query run in production machine?
can u give the system configuration.
🙂
February 6, 2010 at 7:04 am
Ignore the pages/sec. 9-50 is pretty low. I'm going to hazard a guess that your bottleneck is IO probably due to poor indexing (and poor queries) possibly exasperated by non-optimal IO config.
You need to identify the worst performing queries, tune them then find the next worst performing and repeat until performance is acceptable.
This may help you get started. If it's critical and there's no one inhouse that knows how to tune queries, consider getting someone in.
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
February 6, 2010 at 2:49 pm
Ok. Thanks for the replies. Much appreciated.
February 8, 2010 at 2:41 am
I had recently the same problem. The main "culprit" was a query which executed repeatedly (each time a product detail page was visited) and required thousands of physical reads. Reviewing and reimplementing the indexing and some "fine tuning" afterwards fixed the issue.
I started by running a server side trace using the "duration" template. This was a good starting point for me.
February 8, 2010 at 3:31 am
Yes, sometime poor maintenance of the database is also result into similar scenario.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 9, 2010 at 2:22 am
What's the average disk queue length when it's being really slow? We have a database which had no indexes in it at all, and we were seeing disk queue lengths peaking at more than 70 during heavy load...needless to say, performance wasn't all it might have been! Improved a lot once we added some indexes to fix the heaviest queries.
February 9, 2010 at 9:04 am
rohdester (2/6/2010)
We have a SQL 2005 instance running on a dedicated server. And the last week or so we have experienced huge performance problems. Our web requests have piled up and the source is our db where our SPROCs are taking forever to complete.We can see that the our IO queue and wait are huge.
I have just looked at the "Memory: Pages/sec" performance counter - and it is very strange.
When our site is unreachable (and we are seeing massive amounts of SQL timeouts), the Pages/sec counter is just plain zero.
When our site becomes reachable the counter starts to fluctuate with average around 9 and spikes up to 50.
This I don't understand....
CPU usage is 12% and memory consumption is 2/5 gigs.
Any ideas?
Here are my ideas:
1) what changed??
2) How long has it been operational before it started crapping out?
3) Are you doing any form of maintenance?
4) MAIN POINT: if I had a production system that was experiencing "huge performance problems" I sure as heck wouldn't be waiting a WEEK to get either Microsoft Support involved or a performance tuning consultant involved. I bet the latter could identify an array of issues with a matter of minutes or hours that could open up all kinds of headroom on your system. There are numerous regulars on this site that are qualified to remote in and help you out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 9, 2010 at 9:17 am
TheSQLGuru (2/9/2010)
rohdester (2/6/2010)
We have a SQL 2005 instance running on a dedicated server. And the last week or so we have experienced huge performance problems. Our web requests have piled up and the source is our db where our SPROCs are taking forever to complete.We can see that the our IO queue and wait are huge.
I have just looked at the "Memory: Pages/sec" performance counter - and it is very strange.
When our site is unreachable (and we are seeing massive amounts of SQL timeouts), the Pages/sec counter is just plain zero.
When our site becomes reachable the counter starts to fluctuate with average around 9 and spikes up to 50.
This I don't understand....
CPU usage is 12% and memory consumption is 2/5 gigs.
Any ideas?
Here are my ideas:
1) what changed??
2) How long has it been operational before it started crapping out?
3) Are you doing any form of maintenance?
4) MAIN POINT: if I had a production system that was experiencing "huge performance problems" I sure as heck wouldn't be waiting a WEEK to get either Microsoft Support involved or a performance tuning consultant involved. I bet the latter could identify an array of issues with a matter of minutes or hours that could open up all kinds of headroom on your system. There are numerous regulars on this site that are qualified to remote in and help you out.
Hi sql guru,
Thanks for your post.
Well the problems build up over a week or so, and hit the ceiling yesterday. Anyway we started moving some things around, and when we moved our email system to a separate sql server everything went to normal again. So now we are assessing the current system and how to handle this in the future.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply