This is a look at what happens for a DBA when the DB Server starts to run slow.
4:00 a.m. Phone starts ringing with High Priority messages.
4:05 a.m. Helpdesk calls to tell you people are complaining of slowness and timeout’s happening in applications.
4:10 a.m. wearily walk down to computer/office/chair and try not tripping over toys and other dangerous parts of the house like stairs!
04:15 a.m. get logged in and start to troubleshoot.
Here are the steps I commonly use.
1. Ask where specifically they are seeing slowness. Giving me an Idea of the DB and Server you’re dealing with. I also try and find out as close to specific times as possible. While asking for info if you have Change control process’s or system change control ask if anything has changed or if something is running in your operations department.
2. Once you find out this info I typically use a trace that I always have running. My trace captures anything that runs longer than 2 seconds to a table in the DBA database. By using this I can see all the procedures that are currently running slow. Once I find these procedures it’s usually a matter of me knowing my data and I know that some procedures take a little longer while others should have ran faster. This also tells me who may be taking more resources than others since I capture the Reads, Writes, CPU and Duration.
3. In parallel with # 2 above or if you don’t have traces running I open Perfmon and start capturing stats from the server. The common ones I grab first are Processor %( just total is fine) and Current Disk Que length for the Drives that house the data and log files. If I’m going to trouble shoot longer then I grab other counters such as Page life expectancy to check for memory pressure.
4. I would also check a sp_who2 Active to see who is running what and trying to gauge how much blocking is going on. Recently Adam Machanic posted a great new script on who is doing what I love it! Thanks Adam! So I’ll add the link here if you want a great way to get the information. http://sqlblog.com/blogs/adam_machanic/archive/2007/12/31/a-gift-of-script-for-2008-who-s-active-what-are-they-doing-and-who-is-blocked.aspx
5. I would be checking on Perfmon to see if there are spikes and noting the times they happen. If they are consistent on some sort of schedule find out what is running in your jobs at that time or on that schedule (every 5 minutes, 10minutes, etc…).
6. If you have a baseline (which you should) Compare how your server normally runs to what it is doing now. This should give you an idea of how bad the problem is
All these things should point you in the right direction. This is by far not the only things you could do and I’m sure others could comment on additional steps. These are the quick common ones that I look into knowing your data and your DB Servers really helps with any performance problem. With Performance issues you cannot always give a smoking gun as to what caused the issue but you should aim to do so. How much data you have in the past and how much you have now will determine how well you can get to that smoking gun. Hopefully this gets you started on the right track.
Happy Performance Hunting