My database is slow, can you fix this? Don’t you just hate this question! Because there’s no definition in what slow is, when it happens, what the impact is and what you’re allowed to do to fix it.
First thing i asked the client is “when does this slowness occur”. Because you can check if there are scheduled jobs running at that time that interfere with other jobs. Maybe an index rebuild is blocking some transactions. Easy fix is to change the index rebuild schedule to a less crucial time for the transactions.
I could go on with a lot of examples, but i want to point you to a great way to check and log what is happening on your server. And those tools are freeware, brought to you by some really smart minds in the SQL Server community.
The first one is the famous sp_whoisactive stored procedure. You can download the latest version here. This stored procedure will help you to check out what is running on your instance. The procedure is well documented and has helped me out a number of times when i wanted to see what’s going on. And in all cases, it came up with results i could use. The best result i got was the pointer to a missing index. It didn’t show in the missing index hints or anywhere else, but i did get a load of locks in the procedure, even resulting in deadlocks. Because of the logging, i could take my time and analyze the results, add an index and fix a very irritating problem. This resulted in a happy client.
The second tool is a script you can add to your instance to actually log the results. Though Adam Machanic (T) has some basic scripts on how to log the results of sp_whoisactive to a table, i couldn’t get it to work. But the smart people out there do know how to do this. You can either look at the script of Kendra Little (T) here, or the script by Brent Ozar (T) here. Both scripts work great.
So, will these scripts solve everything? Nope! You will have to read the results, understand what’s happening and take a course of action. In my opinion you make a choice and monitor. If results improve, great! If not, try and find out why, be honest to your client or boss or whatever, and change your way of thinking. I think most people don’t mind if you make a wrong turn somewhere. As long as you’re honest about it. We all fail and learn a lot by failing.
Thanks for reading!
Reitse's blog 