How to avoid recompilation

- Don’t interleave DDL and DML statements.

- Avoid recompilation caused by statistics changes: use the KEEPFIXED PLAN option or disable the auto update statistics on the relevant table.

- Use table variables instead of temporary tables (as statistics are not created for table variables)

- Avoid changing SET options within the stored procedure.

- Use the OPTIMIZE FOR query hint that uses parameter values
supplied by you to compile the plan, regardless of the values of the parameter passed in by the calling application. It is recommended to use it in case you have a set of parameters that are "representative" from statistics point of view. This way you'll have a plan that works best most of the time.

@CustomerId INT
       dbo.Sales S
       INNER JOIN ...
WHERE S.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1)) ;

- Using plan guides. A plan guide allows you to use query hint or other optimization techniques without having to modify the query or procedure text. For example when the query is part of a third-party application and you are not able to modify it by including OPTION (OPTIMIZE FOR).
Example of creating a plan guide:

EXEC sp_create_plan_guide
       @name = N'MyGuide',
       @stmt = N'SELECT ...
                                 dbo.Sales S
                                 INNER JOIN ...
                                 WHERE S.CustomerId = @CustomerId'
       @type = N'OBJECT',
       @module_or_batch = N'dbo.CustomerList',
       @params = NULL,
       @hints = N'OPTION (OPTIMIZE FOR (@CustomerId = 1))' ;

Causes of recompilations

- The schema of regular tables, temporary tables, or views referred to in the stored procedure statement have changed. Schema changes include changes to the metadata of the table or the indexes on the table.

- Bindings (such as defaults) to the columns of regular or temporary tables have changed.

- Statistics on the table indexes or columns have changed past a certain threshold.

- An object did not exist when the stored procedure was compiled, but it was created during execution. This is called deferred object resolution.

- SET options have changed. (SET NOCOUNT doesn’t cause stored procedure recompilation)

- The execution plan was aged and deallocated.

- An explicit call of sp_recompile on a table, view, trigger or stored procedure

- There was an explicit use of the RECOMPILE hint:
CREATE PROCEDURE <spName> WITH RECOMPILE => prevents the caching of the stored procedure plan
EXEC <spname> WITH RECOMPILE; => a new plan is generated temporarily, without being cached of affecting any other existing cached plan
OPTION (RECOMPILE)</sql> => causes statement recompilation

DMVs used to analyze the execution plan cache

You can obtain a lot of information about the execution plans in the procedure cache by accessing various dynamic management objects.

sys.dm_exec_cached_plans: obtain information about the execution plans in the plan cache.

sys.dm_exec_query_plan(plan_handle): retrieve the XML representation of the plan.

sys.dm_exec_sql_text(plan_handle): retrieve the original query text.

sys.dm_exec_query_stats: get aggregate performance metrics about the cached plan.

sys.dm_exec_requests: see execution plans for queries that are currently executing.

Index fragmentation resolutions in SQL Server

After you detected fragmentation of an index, you can "resolve" fragmentation in an index by rearranging the index rows and pages so that their physical and logical orders match.

You achieve this through the following techniques:
1. Dropping and re-creating the index
If the index being dropped is a clustered index, then all the nonclustered indexes on the table have to be rebuilt after the cluster is dropped.

2. Creating the index with the DROP_EXISTING clause

3. Executing the ALTER INDEX REBUILD statement on the index
To be performed in case the fragmentation is over 30%.

ALTER INDEX <IX_IndexName> ON <TableName>

or rebuild all indexes of a specified table:


4. Executing the ALTER INDEX REORGANIZE statement on the index
To be performed in case the index fragmentation is between 5% and 30%

ALTER INDEX <IX_IndexName> ON <TableName>

Characteristics of Four Defragmentation Techniques

Blocking High High Medium Low
Defragment index with constraints Highly complex Moderately Easy Easy
Defragment multiple indexes together No No Yes Yes
Degree of defragmentation High High High Moderate to low
Statistics are updated Yes Yes Yes No
Apply new fill factor Yes Yes Yes No

Determining when a query was last run

Running the T-SQL script given will identify the top 20 times a given query was run, as ordered by last_execution_time. You can identify the relevant query by supplying part of it as a filter via the WHERE clause.

For example, determine when a specific stored procedure was last time changed:

        DB_NAME(QT.dbid) AS DatabaseName
        , QT.text as sql_statement
        , QS.last_execution_time
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as QT
        QT.dbid = DB_ID()               -- limit search on current database
        AND QT.text LIKE '%CREATE PROCEDURE%GetEmployees%'
ORDER BY QS.last_execution_time DESC;

Finding the queries that use the most I/O

Running the SQL script given in the following listing will identify the top 20 queries that use the most I/O.

        DB_NAME(QT.dbid) as database_name
        , QS.total_logical_reads + QS.total_logical_writes as total_io
        , QS.execution_count   
        , 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) as statement_text
        , QT.text as parent_statement_text     
        , O.type_desc
        , QP.query_plan
        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
        LEFT JOIN sys.objects O ON O.object_id = QT.objectid
WHERE QT.dbid = DB_ID()
ORDER BY total_io DESC

Find missing indexes using DMVs

The following query determines which are the missing indexes and displays their column details based on dm_db_missing_index_group_stats DMV.

USE <DatabaseName>;

        REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '') as table_name,
        CAST(S.avg_total_user_cost * S.avg_user_impact * S.user_seeks as INT) as [score],
        ID.equality_columns, ID.inequality_columns, ID.included_columns,               
        'CREATE INDEX '
        + 'IX_' + OBJECT_NAME(ID.object_id) + '_' + CAST(ID.index_handle as VARCHAR)
        + ' ON ' + REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '')
        + ' (' + ISNULL (ID.equality_columns,'')
        + CASE
                WHEN ID.equality_columns IS NOT NULL AND ID.inequality_columns IS NOT NULL
                THEN ', ' + ID.inequality_columns
                ELSE ''
        + ')'
        + ISNULL (' INCLUDE (' + ID.included_columns + ')', '') AS create_index_statement
        sys.dm_db_missing_index_details ID
        INNER JOIN sys.dm_db_missing_index_groups G
                ON ID.index_handle = G.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats S
                ON G.index_group_handle = S.group_handle
        ID.database_id = DB_ID()
        AND OBJECTPROPERTY(ID.[object_id], 'IsMsShipped') = 0  

Performance Monitor counters to analyze disk bottleneck

Counter Description Expected Value
% Disk Time Percentage of time disk was busy with read/write activities. Average value < 85%
Current Disk Queue Length Number of outstanding disk requests at the time performance data is collected Average value < 2 per disk
Avg. Disk Queue Length Average number of queued disk requests during the sample interval Average value < 2 per disk
Disk Transfers/sec Rate of read/write operations on disk Maximum value < 400 per disk
Disk Bytes/sec The rate at which bytes are transferred to or from the disk during read or write operations.
If the amount of data transfer exceeds the capacity of the disk subsystem, then a backlog starts developing on the disk subsystem, as reflected by the Disk Queue Length counter.
Maximum value < 800MB per second
Avg. Disk Sec/Read Average time in ms to read from disk Average value < 10 ms
Avg. Disk Sec/Write Average time in ms to write to disk Average value < 10 ms

Performance Monitor counters to analyze memory pressure

Counter Description Expected Value
Available Bytes Free physical memory Should not be too low
Pages/sec Rate of hard page faults.

A page fault occurs when a process requires data that is not in its space in physical memory. If the faulted page is found elsewhere in physical memory, then it is called a soft page fault. A hard page fault occurs when a process requires data that must be retrieved from disk.
The speed of a disk access is in the order of milliseconds, whereas a memory access is in the order of nanoseconds.

Average Value < 50
Page Faults/sec Rate of total page faults (soft page faults plus hard page faults) Compare with its baseline value for trend analysis
SQLServer:Buffer Manager
Buffer cache hit ratio Percentage of requests served out of buffer cache.
A low value indicates that few requests could be served out of the buffer cache, with the rest of the requests being served from disk.
Average Value >= 90% in an OLTP system.
Page Life Expectancy Time page spends in buffer cache without being referenced. A low value means that pages are being removed from the buffer, lowering the efficiency of the cache and indicating the possibility of memory pressure.
Checkpoint Pages/sec Pages written to disk by a checkpoint operation.
A dirty page is one that is modified while in the buffer. When it’s modified, it’s marked as dirty and will get written back to the disk during the next checkpoint.
Average Value < 30
Lazy writes/sec Dirty aged pages flushed from buffer. Average Value < 20
SQLServer:Memory Manager
Memory Grants Pending Number of processes waiting for memory grant. If this counter value is high, then SQL Server is short of memory.
See sys.dm_ exec_query_memory_grants dmv.
Average value = 0.
Target Server Memory (KB) Indicates the total amount of dynamic memory SQL Server is willing to consume. Close to size of physical Memory
Total Server Memory (KB) Amount of physical memory currently assigned to SQL. Close to Target server Memory (KB)
Private Bytes Size, in bytes, of memory that this process has allocated that can’t be shared with other processes

When you get out-of-memory issues, they are recorded in sys.dm_os_ring_buffers dmv:

FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type='RING_BUFFER_OOM';

Get database restore history

Returns last 10 restores performed on your server:

        RH.destination_database_name AS [Database],
        RH.user_name AS [Restored By],
        RH.restore_date AS [Restore Started],
        BMF.physical_device_name AS [Restored From],
        RF.destination_phys_name AS [Restored To],
        msdb.dbo.restorehistory RH
        INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
        INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
        INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DESC


Subscribe to SQLhint RSS