query-plan

Find missing index warnings in cached query plans

Find missing index warnings for cached plans in the current database:

SELECT TOP 20
        OBJECT_NAME(objectid) as object_name,
        CP.objtype, CP.usecounts,
        QP.query_plan  
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        CAST(QP.query_plan as NVARCHAR(MAX)) LIKE N'%MissingIndex%'
        AND dbid = DB_ID()
ORDER BY CP.usecounts DESC

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.

Finding queries that are using table scans

The T-SQL script used to discover queries that are using table scans by searching within query plan for TableScan tag:

SELECT TOP 20
        DB_NAME(ST.dbid)AS database_name
        , ST.text as sql_Statement
        , CP.usecounts
        , QP.query_plan
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        ST.dbid = DB_ID()
        AND CAST(QP.query_plan AS NVARCHAR(MAX)) LIKE '%<TableScan%'
ORDER BY CP.usecounts 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.

SELECT TOP 20
        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.name
        , O.type_desc
        , 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
        LEFT JOIN sys.objects O ON O.object_id = QT.objectid
WHERE QT.dbid = DB_ID()
ORDER BY total_io DESC

Find out waiting tasks - sys.dm_os_waiting_tasks

sys.dm_os_waiting_tasks dmv shows all threads that are currently suspended:

SELECT
        DB_NAME(ER.database_id) DatabaseName,
        OT.[session_id],
        OT.[exec_context_id],
        OT.[wait_duration_ms],
        OT.[wait_type],
        OT.[blocking_session_id],
        OT.[resource_description],
        ES.[program_name],
        ST.[text],
        ES.[cpu_time],
        ES.[memory_usage],
        QP.[query_plan]
FROM
        sys.dm_os_waiting_tasks OT
        INNER JOIN sys.dm_exec_sessions ES ON OT.[session_id] = ES.[session_id]
        INNER JOIN sys.dm_exec_requests ER ON ES.[session_id] = ER.[session_id]
        OUTER APPLY sys.dm_exec_sql_text (ER.[sql_handle]) ST
        OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
WHERE ES.[is_user_process] = 1
ORDER BY OT.[session_id], OT.[exec_context_id];
GO

How do I obtain a Query Execution Plan?

Estimated plan (compile):

  1. SET SHOWPLAN_XML
  2. Graphical Showplan or Ctrl-L in SSMS
  3. sys.dm_exec_query_plan, sys.dm_exec_text_query_plan

Actual plan (runtime):

  1. SET STATISTICS XML
  2. Graphical Showplan or Ctrl-M in SSMS
  3. SQL Sentry Plan Explorer (free or pro edition)

On deprecation path:

  • SET SHOWPLAN_TEXT
  • SET SHOWPLAN_ALL
  • SET STATISTICS PROFILE

View state of plan cache - verifying for Plan Cache Pollution

SELECT
        CP.[objtype] as [Cache Type]
        , COUNT (*) as [# Plans]
        , SUM (CASE WHEN CP.[usecounts] = 1  THEN 1 ELSE 0 END) as [# Plans - UseCount1]        
        , SUM (CAST(size_in_bytes as DECIMAL)) / 1024 / 1024 as [Space_MB)]
        , SUM (CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END ) /1024/1024  as [Space_MB_UseCount_1]
        , AVG (CP.[usecounts]) as [Avg Use Counts]
FROM [sys].[dm_exec_cached_plans] CP
GROUP BY CP.[objtype]
ORDER BY [Space_MB_UseCount_1] DESC;

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

SELECT
        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
FROM
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
WHERE
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

How to delete or invalidate a query plan

Reboot / Restart
When SQL Server is rebooted, or the service is stopped and restarted, any DMV data is lost.

Flushed Cache
The hard way, delete all query plans from cache:

-- Never execute this on your production server
DBCC FREEPROCCACHE

or you can target specific plans:
DBCC FREEPROCCACHE(plan_handle)

Memory pressures
Keeping a large number of cached plans can quickly fill the available memory, especially in systems that create lots of SQL queries that differ subtly (for example, having different environment settings), resulting in separate cached plans for each one.
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache. If a stored procedure is not re-executed for a long time, the age field of the execution plan can come down to 0, and the plan can be removed from the cache when SQL Server observes a pressure on its memory.

Recompiling
Recompile all queries related to a specific table or view:

EXEC sp_recompile <tablename>

Delete all query plans associated with a stored procedure.

-- Manually mark a stored procedure for recompilation, so it will be recompiled next time it is executed.
EXEC sp_recompile <procname>

Or you can enforce a query recompilation each time, then add a query hint to the end of the query:

OPTION (RECOMPILE)

Add the WITH RECOMPILE option when a stored proc is created:

CREATE PROCEDURE <ProcedureName>
WITH RECOMPILE
AS
...

When statistics are updated
More details about when statistics change.

Simple parametrization
Setting configuration option optimize for ad hoc workloads to 1, will store only query_hash into the cache and and on second execution (if it happens), the plan itself will be placed in cache.

Forced parametrization
Parametrize all queries like in case of simple parametrization

ALTER DATABASE <dbname> SET PARAMETRIZATION FORCED;

Pages

Subscribe to RSS - query-plan