Thursday, November 18, 2010

What to do when you have performance issues in SQL Server

Till now as DBA one of the issue which I love the most to work or solve is performance issues in SQL Server.  I do enjoy when queries goes mad  and misbehave.Based on the production issues which I have seen in last 5 years as DBA I have listed some of the steps that you can follow when there is severe issues with performance of SQL Database server.

1) Check the hardware configuration (CPU, RAM).If the server is running on a low RAM or CPU.
2) Execute sp_who2   to see if there is blocked process in SQL Server. If there are blocking then please note the spid and execute DBCC INPUTBUFFER (spid) that will give you the SQL Statement that is executed. Kill the blocking process to resolve the issue. Always remember that killing a process can create data integrity issues in application if not handled with care.
3) Start a profiler to capture queries that are taking max duration to run.Please also note the column CPU and IO on profiler.A high CPU/IO consuming queries need to be looked on.
4) Once you have the query you need to find out why it’s taking long time. There are couples of reasons for that.
    Bad Execution Plan
    Statistics out of date
    Index heavily fragmented
5) Check the query plan of those queries for any table scan/clustered index scan etc. If possible create appropriate index to avoid the same.
6) Check the statistics of tables if they are out of date. If so execute Update Statistics command to update the statistics
7) Check the fragmentation of indexes. If tables are heavily fragmented then you may need to rebuild the same. If it’s a heap table then a clustered index might need to be created and then dropped.
8) Try to capture windows counters and sort if it’s a memory, CPU or disk issue. If it’s a memory issue and if you are running on a 32 bit system check if you can enable AWE to allocate more memory to SQL Server otherwise add more RAM. Remember SQL Server, Exchange server are crazy about RAM. They will try to have as much as you can supply.
9) Check if any of the scheduled jobs are running on the server.Backup jobs or other maintenance jobs will eat up CPU cycles.
10)If SQL Servers are running  in a  clustered environment then check if two or more instances are running on  the same node.If so please check if you can failover one to the other node.
These are some of the preliminary steps that you can carry out to find the root cause of issue.Depending on the issue you can involve an expert DBA to sort out the problem.

No comments:

Post a Comment