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.

Finding the size of Index in SQL Server 2005/2008

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats
comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes   and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database,ObjectId,Objectname,IndexId,IndexDescription,Size of Index in MB,Last Updated Statistics Date and Avg Fragmentation.

select DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,CONVERT(DECIMAL(16,1),(sum(avg_record_size_in_bytes * record_count)/ (1024.0 * 1024)))as [Size of Index(MB)]
,last_updated as [Statistic last Updated],Avg_Fragmentation_In_Percent
from ( SELECT Distinct DB_Name(Database_id) as'DatabaseName',Object_ID as ObjectId,Object_Name(Object_id) as ObjectName,Index_ID as IndexId,Index_Type_Desc as Index_Description,avg_record_size_in_bytes , record_count,STATS_DATE(object_id,index_id) as 'last_updated',Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) as 'Avg_Fragmentation_In_Percent'FROM sys.dm_db_index_physical_stats (db_id('PM_Db'), NULL, NULL, NULL, 'detailed')
Where Object_id is not null and Avg_Fragmentation_In_Percent <> 0) T
group by DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,last_updated,Avg_Fragmentation_In_Percent

Database Projects in VS 2010 - Deploy database with values in master tables

Recently went across a situation where I need to deploy a database in SQL Server 2008  using database projects of VS2010.Things were simple since when you build solution with  database projects in VS2010 it creates     db schema,db deploy  manifest file, db script file etc.When database project is deployed using VS 2010 it uses deployment API available in .NET.The link below describes how you can achieve your own solution

http://blogs.msdn.com/b/bahill/archive/2010/05/04/leveraging-the-visual-studio-2010-database-deployment-api.aspx

The same functionality can be achieved using sqlcmd.You can execute these sql scripts created using sqlcmd (sqlcmd gets installed when SQL Server 2005/2008 is installed).

I guess same results can be achieved with VSDBCMD also.Never tested that :-).

Now things looked challenging when I had to insert data in some tables  while deploying the database projects.In simple words if the database is deployed it should create database,database objects also it need to insert some data in master tables.
Thereafter I found out you can put all your insert statements in Post Deployment Script File.The database projects in VS 2010 will have a file Script.PostDeployment.sql , just put your insert or delete  statements in that file and when you deploy the project it will insert data in master tables.