SQL Server query plans in cache

07 January,2011 by Jack Vamvas

 If you want to view the objects within the cache  use the query below. This  will give you an  insight into how SQL optimizer and SQL storage engine reached the plan.

 

SELECT [qpc].[refcounts]

, [qpc].[usecounts]

, [qpc].[objtype]

, [stx].[dbid]

, [stx].[objectid]

, [stx].[text]

, [qpl].[query_plan]

FROM sys.dm_exec_cached_plans qpc

CROSS APPLY sys.dm_exec_sql_text ( qpc.plan_handle ) stx

CROSS APPLY sys.dm_exec_query_plan ( qpc.plan_handle ) qpl ;

Key Column meanings:

[qpc].[refcounts] = Number of cached objects referencing the object

[qpc].[usecounts] = Times used since started

[qpl].[query_plan] = compile-time Showplan

 Read More

How to troubleshoot a slow running query in SQL Server

Current queries executing on SQL Server

Find high impact queries with sys.dm_exec_query_stats  


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server query plans in cache


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer