Wednesday, February 17, 2010

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.


Anonymous said...

Good post at the right time for me. Please could you also suggest ways for checking a high value of paging. Is there any way in which I can find the list of objects & their size using tempdb, or can I log the same into some file.

Nagaraj Venkatesan said...

Thank you. High values of paging and memory stress can be checked using the counters Memory:Pages/Sec,Buffer Cache hit ratio. I will be covering the same in my next post coming up in 2 days.

On objects using tempdb, if you are on SQL 2005, DMV sys.dm_db_task_space_usage can help. But if on SQL 2000 there is no easy way of doing it. But anyways, I will present the options available on 2000 and 2k5 on my next few posts.