SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

If you see messages like the above in your SQL Server error logs then you know you have a problem.
This means that the cache is being flushed and all the plans and data will have to cached in again. This is a painful process and will result in an immediate but a temporary hit in performance.

Following are some of the reasons that can cause this

1) The database is set to AUTOCLOSE = ON, an operation that initiates an automatic database  shutdown or disconnects from the database, clears the plan cache for the instance of SQL Server.
2) Some of the ALTER DATABASE commands.
3) DROP DATABASE [dbSnapshot]
4) Restore database operation
5) Detach database operation
6) DBCC FREEPROCCACHE
7) DBCC FREESYSTEMCACHE

In most of the cases the commands are not likely to be executed regularly so most of them are not much of a concern.
The most frequent reason that I have seen is the AUTOCLOSE property.

If you see messages like the following one in the error logs, then it is AUTOCLOSE that is causing the issue.

Starting up database 'yourDBname'

Check if you have any database that has AUTOCLOSE set to true. You can see that in the database propertise 'OPTIONS' tab. Or run the following query to identify any database with that set to TRUE.

select name, is_auto_close_on from sys.databases where is_auto_close_on=1

If you do find any database with that property to to TRUE then I would suggest that you turn it off by using the following commands.

USE [master]
GO
ALTER DATABASE [yourDBname] SET AUTO_CLOSE OFF WITH NO_WAIT
GO
ALTER DATABASE [yourDBname] SET AUTO_CLOSE OFF
GO

This should fix the problem if the AUTOCLOSE property is indeed the issue. Else you will have to start a trace and capture any event where it says 'Plan Cache Flush'. I am still investigating the event Id for this so please leave a comment if you know that.

Get I/O - CPU intensive queries

Firstly runs some Perfmon and identify if you have a CPU issue or an I/O issue.

Then find the most I/O intensive queries using the following query.

SELECT TOP(100)CONVERT(VARCHAR,(total_logical_reads/execution_count)) as avg_logical_reads,
       CONVERT(VARCHAR,(total_logical_writes/execution_count)) as avg_logical_writes,
       CONVERT(VARCHAR,(total_physical_reads/execution_count)) as avg_physical_reads,
       CONVERT(VARCHAR,execution_count) as execution_count,
       (SELECT SUBSTRING(TEXT, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX),TEXT)) * 2
                ELSE statement_end_offset
            END - statement_start_offset)/2) as Query
     FROM sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_query_stats
ORDER BY avg_physical_reads DESC;

Then find the most CPU intensive queries using the following query.

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
     WHEN -1 THEN DATALENGTH(qt.TEXT) 

     ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1), 

qs.execution_count, qs.total_worker_time,
qs.total_worker_time/qs.execution_count as avg_worker_time, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan
FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC -- CPU time


Make sure you analyze the data carefully. The execution count is a really important number. So check both the 'total_worker_time' and avg_worker_time. You might have queries that run for 1 or 2 secs but may be they run several times. 


SQL Server Get I/O intensive queries
SQL Server Get CPU intensive queries.

Capturing Deadlock information or simulating deadlock

SQL Server 2005/2008

You can always run a profiler trace to capture the deadlock graph that gives details of the SPID and queries involved. But I would rather turn on the traces for deadlock and capture the info in the Error logs.

To do this run the following commands

DBCC TRACEON (1204, 01)

DBCC TRACEON (1222, -1)

The output from each of them are different so check the output before you decide which one you prefer. I like the output from the second one which was introduced in SQL Server 2005 and gives the info for each process involved.

If you want to test the output then take the following steps to simulate a deadlock and check the output.

1) CREATE TABLE a (i int); 

    CREATE TABLE b (i int);

   

    INSERT a SELECT 1;

    INSERT b SELECT 9;

2) In a new window
    BEGIN TRAN

    UPDATE a SET i = 11  

    WHERE i = 1 

    WAITFOR DELAY '00:00:10' 

    UPDATE b SET i = 99 

    WHERE i = 9 

    COMMIT

3) In another window
    BEGIN TRAN 

    UPDATE b SET i = 99 

    WHERE i = 9 

    WAITFOR DELAY '00:00:10' 

    UPDATE a SET i = 11 

    WHERE i = 1 

    COMMIT

If you want to retest the deadlock then drop the tables and rerun the steps.

Move Primary key index to new filegroup

SQL Server Move index to a new filegroup
Recreating Primary key index

Today I had the task of moving the primary key index to a new file group. But before I explain the procedure, it is interesting to note the necessity to do this.

During the planning phase, we had recommended that the table data and the index data be placed on two different LUNs. This would give the best IO performance. But unfortunately the vendor made a slight error in giving us the DDL for the tables and indexes.

The CREATE table command also added the primary key constraints and specified the filegroup reserved for indexes for the primary key. I guess they thought since our recommendation was to have all indexes on a separate filegroup, they would create the primary key index also on the index filegroup.

But as most of the DBAs know the primary key index is nothing but the table itself. So when the primary key was created on the index filegroup it moved the data from the data filegroup to the index filegroup. So essentially we ended with both the data and the index on the same filegroup. The data filegroup was unused after this change.

But unfortunately the non clustered indexes had been created and the data was inserted.

Now if I tried to drop and recreate the primary key index then the non-clustered indexes would be rebuilt twice. So I had drop all indexes and then recreate them and this way I could avoid rebuilding the non clustered indexes twice.

Following is the summary of commands that I ran to achieve this.

1)      Script out the CREATE statements for the table including the CREATE statements for the indexes. (Always have a backup plan, if it helps take a backup of the database.

2)      Drop all the non clustered indexes.

            DROP INDEX [AK_Department_Name] ON [HumanResources].[Department]

WITH ( ONLINE = OFF )

3)      Drop the primary key index.

ALTER TABLE [HumanResources].[Department] DROP CONSTRAINT [PK_Department_DepartmentID]

4)      Create the new primary key index

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

            [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Do not forget to mention the new filegroup for the primary key index.

5)      Create all the remaining non clustered indexes and specify the index filegroup.

CREATE NONCLUSTERED INDEX [AK_Department_Name] ON [HumanResources].[Department]

(

            [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

      ALLOW_PAGE_LOCKS  = ON) ON [INDEXES]

Named instance perfmon counters

Collecting or adding perfmon counters for named instance for SQL Server.

Collecting and analyzing Performance Monitor (perfmon) counters for SQL Server and operating system is an integral part for the performance tuning. You can find SQL Server counters under the object ‘SQL Server’.

But on several occasions my colleagues have reported that they do not find the objects ‘SQL Server’.  This might be the case if the SQL Server has a named instance. In such scenarios the counters do not appear under SQL Server. To find the actual name run the following query.

use MASTER

select * from sysperfinfo where object_name like '%Buffer Manager%'

And you will find the actual counter names in the fist column. In the following screenshot the counter name is MSSQL$MIROR and you should find that in performance monitor.

All named instance counters objects start with ‘MSSQL$’ and followed by the name of the instance.

So look for counters starting with ‘MSSQL’ and not under ‘SQL Server’

Find missing indexes

SQL Server 2005/2008 : Find missing indexes

Missing index report
Starting from SQL Server 2005 there were some DMVs introduced that stored information regarding what indexes would enhance the performance of the queries. Following query will give you a list of indexes that SQL Server recommends. Use this report wisely and do not create all the indexes listed in the report.


SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
 [Table] = [statement],
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
  + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
  + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC