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.

 

Windows OS versioning and problem with @@version

Today I came across a problem in getting the OS name with @@version variable in SQL Server.The @@version  variable returns the build of NT engine(say Windows NT 5.2,6.0).If its 5.2 then the OS would be windows 2003 server, 6.0 indicates Vista.

Now if you are running on Windows7 or Windows2008 R2 then @@version will return Windows NT 6.1.
This is confusing since DBA can't make out whether the operating System is Windows 7 or Windows2008 R2.The reason is because Windows 2008 R2 uses the same engine of Windows 7.
The solution is you use command line utility systeminfo.exe which will display detailed configuration information about a computer and its operating system, including operating system configuration, security information, product ID, and hardware properties, such as RAM, disk space, and network cards.

If you are running on SQL Server 2005 or SQL Server 2008 then this utility can be executed using xp_cmdshell.
Please don't forget to enable xp_cmdshell  using sp_configure

Below attached is the sample script that will get you OS Name.

create table #tmpOSName
(Details varchar(8000))

insert into #tmpOSName(Details)

exec master..xp_cmdshell 'systeminfo'

select rtrim(ltrim(SUBSTRING(Details,9,LEN(Details)))) from #tmpOSName where Details like 'OS Name%'
drop table #tmpOSName



Please note this is dependent on xp_cmdshell ; so that needs to be enabled first.

SSIS job fails:The affinity mask and affinity I/O mask configuration should not conflict

I was called today to help an issue with maintenance plan in SQL Server 2005.The Integrity Job created with SSIS Package was failing. Neither the output file nor Job history had much detail except the error:

The package execution returned DTSER_FAILURE

To debug the issue I decided to capture trace with all events under Errors and Warning selected along with SQL:StmtStarting,SQL:StmtCompleted,SQL:BatchStarting,SQL:BatchCompleted ,RPC:Started
and RPC:Completed events selected.

After analyzing the trace file the error in which I was interested was Error: 5834, Severity: 16, State: 1 and  just below that it had entry

The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

On checking the configuration of the server it was found that  the same processor was affinitize for both SQL Server worker thread scheduling and for I/O processing.

Under this configuration when SQL Server executes RECONFIGURE statements it throws an exception.

I changed these values , set it separately ,restarted SQL Server (and of course SQL Agent)and tried executing  job .The integrity job succeeded without any error.These affinity value shouldn’t be shared for same processors or you should set  Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors option.

Help SQL Server to identify each other across internet

Recently I bought a new laptop and had SQL Server 2008 installed on that. The old one carried SQL Server 2005 Express edition.

Thought why not try to identify each of these SQL Server instances  when both of them are connected to internet or try registering SQL Server express edition instance in the laptop that carry SQL Server 2008.

The first step that you need to do is make sure that the instance which you want to register runs in a static port. There are two types of ports which can be used by SQL Server (Static and Dynamic).Static ports once set are not changed when SQL Server is restarted. Dynamic ports are changed every time SQL Server restarts.

To set a static port Open SQL Server Configuration Manager ->SQL Server Network Configuration->Select the Protocols for your instance->Select TCP/IP on the right side and open its properties.Go to IP Addresses tab, scroll down and check if under IP All category you can find TCP Dynamic Ports and TCP Port.Make sure that you don't have any value in TCP Dynamic Ports and enter a value for TCP Port (say 2315). Now restart SQL Server instance. SQL Server instance would use run on this port and it woudn't change regardless the number of time SQL instance gets restarted.

The second step is to make sure that an exception is added in firewall settings for the port underwhich SQL Server runs.In our case it would be 2315.

To add exception you need to go to run and execute firewall.cpl. This will open the firewall window using which you can set exception.Click on allow a program through firewall.Go to the exception tab and click on add port.A new window will pop up. Just enter the Port Number and associate it with a name.Click on OK and then apply.Thats all you are all set to access this SQL Server from other computer in internet.

 To verify go to the other computer and open SQL Server Management studio.Try registering the SQL Server for which you have added exception by  giving IP address and port number.For example if the IP of SQL Server is 167.56.23.45 and port number was 2315 then try accessing it  as 167.56.23.45,2315.If you want to hide IP and
access SQL Server instance with name please add an alias in Client Configuration Utility.