Database Server is slow. Can you please check?

As a DBA, you are going to hear the title of this many many times. This post will discuss the quick list of things a DBA does when such a question is thrown.

Step 1 - Task Manager: This one is very basic. Check the task manager whether SQL Server consuming most of the CPU/Memory. A overall CPU usage exceeding 65%, with SQL contributing the majority ( excess of 40% ) is perhaps a high usage. SQL can use only 50% of the RAM available( unless AWE is enabled).Any usage less than the maximum allowed implies that your server is doing okay on memory front.

Step 2 - SQL Error log: : Check SQL Servers error log for any abnormalities. Note that by default errors with Severity level between 19 and 25 are logged in sql error log.


Step 3 - Query sysprocesses/sys.dm_exec_query_stats: If your step 1 indicates high CPU or Memory pressure then check sysprocesses right away. Check the number of connections that are using SQL Server. This can be found by querying sysprocesses for spid greater than 50. On an average OLTP systems, I would expect 150 - 250 odd connections.Also Check for likely connection leaks with the script provided here.

After connections, find the list of long running queries.If on SQL 2000 then observe the connection(spid) with status = 'runnable' on sysprocesses. Fire DBCC inputbuffer(spid) to check the current statement running. If on sql 2k5 use dm_exec_query_stats DMV to identify the longest query running using the script provided here.

Also, note the connections that are consuming high CPU/IO/Memory using sysprocesses.

Step 4 - Blocking queries: Identify blocking queries, if any. If on SQL 2005 use the script provided here.If on SQL 2k use sp_lock/sp_who2 .

If you follow the above listed steps,it is very likely that you will find the reason for slowdown on performance. But in most of the cases, one is not at the DB server to identify the cause for the problem. By the time the DBA checks it, the server is back to normal, and the cause for the problem goes unidentified. Fortunately, Microsoft gives really good tools to counter such problems which I will be covering in my next post.