Super-Networking Blog

So I am not a DBA, I have never done much work on SQL servers other than back them up, patch them or install new copies of them. At my new job I have started to take on some of the tasks of a DBA including troubleshooting poor performing SQL boxes.

Don’t expect me to be adding in a ton of query statements of SQL traces because I try to stay out of most of that stuff at this point of the game. The first thing I did to aid me in maintaining and troubleshooting SQL was to install SQL Server Management Studio from the SQL 2005 install media. This allows you to connect to SQL instances without having to login to the console of the box.

Two places to take a look right away to see what is going on in that SQL instance is go under SQL Server Agent - > Job Activity Monitor. In here you will be able to see if any scheduled jobs are running, the history of the jobs etc… The other place to look right away is under Management go into Activity Monitor. In here you will see what Processes are currently running. You can see pretty much in real time what is using resources like disk, memory, and CPU. You can also see what processes might have locks.

Now getting back to high SQL on your SQL box, unless it is one job or one long running query that is using all the CPU the Activity Monitor isn’t going to really help you. It could be that a short running query is running 100s or 1000s of times a minute and this is causing the CPU issues. One easy way to tell what is taking of the CPU is built in reports of SQL 2005. Right click on the Instance name in Management Studio -> Go to Reports - > Standard Reports - > Find “Performance - Top Queries by Total CPU” This will give a break down of what queries being running again this instance are using the most CPU and are being run the most times. These stats are being taking all the time by SQL 2005 and so you aren’t taking up for resources to monitor the instance.

This is just one place to start looking but it gives you a lot of good info.