You should be familiar with the basic performance objects and counters and be able to use that information to diagnose problems within a SQL Server instance. SQL Server is typically affected by the following bottlenecks:
- CPU
- Memory
- File I/O
- Indexes
- Locking, waits, blocking, or deadlocking
- Plans, queries
- Network
You can use Performance Monitor to identify how these potential bottlenecks might affect
SQL Server. When SQL Server is not the only process using the resources on a server, you can also use performance counters to determine whether that application is negatively affecting the performance on your SQL Server instance.
CPU pressure
-- Signal Waits for instance (Signal Waits)
-- Signal Waits above 10-15% is usually a confirming sign of CPU pressure
-- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure
-- Resource waits are non-CPU related waits
SELECT @@SERVERNAME, CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Signal (CPU) Waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') OPTION (RECOMPILE);
Check Memory
-- Good basic information about OS memory amounts and state (System Memory)
SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)],
available_physical_memory_kb / 1024 AS [Available Memory (MB)],
total_page_file_kb / 1024 AS [Total Page File (MB)],
available_page_file_kb / 1024 AS [Available Page File (MB)],
system_cache_kb / 1024 AS [System Cache (MB)],
system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION(RECOMPILE);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure
SELECT @@SERVERNAME AS 'INSTANCE',
[object_name],
[counter_name],
CASE
WHEN [counter_name] = 'Page life expectancy'
THEN
(
SELECT DATEDIFF(MI, MAX([login_time]), GETDATE())
FROM sys.dm_exec_sessions DMES
INNER JOIN sys.dm_exec_requests DMER ON [DMES].[session_id] = [DMER].[session_id]
WHERE [command] = 'LAZY WRITER'
)
ELSE ''
END AS 'UPTIME_MIN',
[cntr_value] AS 'PLE_SECS',
[cntr_value] / 60 AS 'PLE_MINS',
[cntr_value] / 3600 AS 'PLE_HOURS',
[cntr_value] / 86400 AS 'PLE_DAYS'
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy';
File Latency
--virtual file latency
SELECT [ReadLatency] = CASE
WHEN [num_of_reads] = 0
THEN 0
ELSE([io_stall_read_ms] / [num_of_reads])
END,
[WriteLatency] = CASE
WHEN [num_of_writes] = 0
THEN 0
ELSE([io_stall_write_ms] / [num_of_writes])
END,
[Latency] = CASE
WHEN([num_of_reads] = 0
AND [num_of_writes] = 0)
THEN 0
ELSE([io_stall] / ([num_of_reads] + [num_of_writes]))
END,
--avg bytes per IOP
[AvgBPerRead] = CASE
WHEN [num_of_reads] = 0
THEN 0
ELSE([num_of_bytes_read] / [num_of_reads])
END,
[AvgBPerWrite] = CASE
WHEN [io_stall_write_ms] = 0
THEN 0
ELSE([num_of_bytes_written] / [num_of_writes])
END,
[AvgBPerTransfer] = CASE
WHEN([num_of_reads] = 0
AND [num_of_writes] = 0)
THEN 0
ELSE(([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))
END,
LEFT([mf].[physical_name], 2) AS [Drive],
DB_NAME([vfs].[database_id]) AS [DB],
--[vfs].*,
[mf].[physical_name]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO
Read write
SELECT DB_NAME(database_id) AS 'Database_Name',
CASE
WHEN file_id = 2
THEN 'Log'
ELSE 'Data'
END AS 'File_Type',
((size_on_disk_bytes / 1024) / 1024.0) AS 'Size_On_Disk_in_MB',
io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms',
CASE
WHEN file_id = 2
THEN CASE
WHEN io_stall_read_ms / num_of_reads < 5
THEN 'Good'
WHEN io_stall_read_ms / num_of_reads < 15
THEN 'Acceptable'
ELSE 'Unacceptable'
END
ELSE CASE
WHEN io_stall_read_ms / num_of_reads < 10
THEN 'Good'
WHEN io_stall_read_ms / num_of_reads < 20
THEN 'Acceptable'
ELSE 'Unacceptable'
END
END AS 'Average_Read_Performance',
io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms',
CASE
WHEN file_id = 2
THEN CASE
WHEN io_stall_write_ms / num_of_writes < 5
THEN 'Good'
WHEN io_stall_write_ms / num_of_writes < 15
THEN 'Acceptable'
ELSE 'Unacceptable'
END
ELSE CASE
WHEN io_stall_write_ms / num_of_writes < 10
THEN 'Good'
WHEN io_stall_write_ms / num_of_writes < 20
THEN 'Acceptable'
ELSE 'Unacceptable'
END
END AS 'Average_Write_Performance'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0
AND num_of_writes > 0
Index_fragmentation
SET DEADLOCK_PRIORITY HIGH;
GO
SELECT dbschemas.[name] AS 'Schema', dbtables.[name] AS 'Table', dbindexes.[name] AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, NULL
) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbindexes.[name] IS NOT NULL
AND indexstats.page_count > 1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC;
-- 25 missing indexes that will improve performance the most
SELECT TOP 25 dm_mid.database_id AS DatabaseID,
DB_NAME(dm_mid.database_id) AS [Database],
dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS [Avg Estimated Impact],
dm_migs.last_user_seek AS [Last Usage Time],
OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table],
'CREATE INDEX [IDX_'+OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id)+'_'+REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')+CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END+REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '')+']'+' ON '+dm_mid.statement+' ('+ISNULL(dm_mid.equality_columns, '')+CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END+ISNULL(dm_mid.inequality_columns, '')+')'+ISNULL(' INCLUDE ('+dm_mid.included_columns+')', '') AS [Create Index Statement]
FROM sys.dm_db_missing_index_groups AS dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats AS dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dm_mid ON dm_mig.index_handle = dm_mid.index_handle
--WHERE DB_NAME(dm_mid.database_id) ='ARCS2G_LIVE'
WHERE dm_mid.database_id > 4
ORDER BY [Avg Estimated Impact] DESC;
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated,
*
FROM sys.indexes
ORDER BY STATS_DATE(OBJECT_ID, index_id) DESC;
Locks
DECLARE @dbid INT; --what's the dbid for DBNAME?
SET @dbid = DB_ID();
DECLARE @objectid INT; --what's objectid for our demo table?
SET @objectid = OBJECT_ID(N'TABLENAME');
--look at locking in the DB
SELECT resource_type,
resource_database_id,
resource_associated_entity_id,
request_mode,
request_type,
request_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id = @dbid;
--limit the results to only the offending table
SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = @dbid
AND resource_associated_entity_id = @objectid;
----Locks
SELECT db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text( ec1.most_recent_sql_handle ) AS h1
CROSS APPLY sys.dm_exec_sql_text( ec2.most_recent_sql_handle ) AS h2;
GO
Waits
;WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT',
N'QDS_SHUTDOWN_QUEUE')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95 -- percentage threshold
GO
Blocks
SELECT *
FROM sys.sysprocesses
WHERE blocked > 1;
GO
Sp_who2
--Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer.
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
EXEC sp_configure
'show advanced options',
1;
GO
RECONFIGURE;
GO
EXEC sp_configure
'blocked process threshold (s)',
20;
GO
RECONFIGURE;
GO
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
EXEC sp_configure
'show advanced options',
1;
GO
RECONFIGURE;
GO
EXEC sp_configure
'blocked process threshold (s)',
20;
GO
RECONFIGURE;
GO
--Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.
--•It should contain one event: Errors and Warnings -> Blocked Process Report.
--•It needs just two columns: Text, spid
--This sample server side trace will run for five minutes and automatically stop. You must change the path to a valid directory SQL Server has permission to write to.
--Created by: SQL Server 2012 Profiler
--Create a Queue
DECLARE @rc INT;
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT;
DECLARE @DateTime DATETIME;
--Added a function here:
SET @DateTime = DATEADD(mi, 5, GETDATE());
/* Run for five minutes */
SET @maxfilesize = 5;
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
--Set my filename here:
EXEC @rc = sp_trace_create
@TraceID OUTPUT,
0,
N'S:\Traces\BlockedProcessReportDemo',
@maxfilesize,
@Datetime;
IF(@rc != 0)
GOTO error;
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT;
SET @on = 1;
EXEC sp_trace_setevent
@TraceID,
137,
1,
@on;
EXEC sp_trace_setevent
@TraceID,
137,
12,
@on;
-- Set the Filters
DECLARE @intfilter INT;
DECLARE @bigintfilter BIGINT;
-- Set the trace status to start
EXEC sp_trace_setstatus
@TraceID,
1;
-- display trace id for future references
SELECT TraceID = @TraceID;
GOTO finish;
error:
SELECT ErrorCode = @rc;
finish:
GO
-- Created by: SQL Server 2012 Profiler
-- Create a Queue
DECLARE @rc INT;
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT;
DECLARE @DateTime DATETIME;
--Added a function here:
SET @DateTime = DATEADD(mi, 5, GETDATE());
/* Run for five minutes */
SET @maxfilesize = 5;
-- Please replace the text InsertFileNameHere, with an appropriate
/* Filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
will be appended to the filename automatically. If you are writing from
Remote server to local drive, please use UNC path and make sure server has Write access to your network share */
--Set my filename here:
EXEC @rc = sp_trace_create
@TraceID OUTPUT,
0,
N'S:\Traces\BlockedProcessReportDemo',
@maxfilesize,
@Datetime;
IF(@rc != 0)
GOTO error;
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT;
SET @on = 1;
EXEC sp_trace_setevent
@TraceID,
137,
1,
@on;
EXEC sp_trace_setevent
@TraceID,
137,
12,
@on;
-- Set the Filters
DECLARE @intfilter INT;
DECLARE @bigintfilter BIGINT;
-- Set the trace status to start
EXEC sp_trace_setstatus
@TraceID,
1;
-- display trace id for future references
SELECT TraceID = @TraceID;
GOTO finish;
error:
SELECT ErrorCode = @rc;
finish:
GO
--Step 3: Look at running traces and get the trace id:
SELECT *
FROM sys.traces;
GO
--Stop a trace, if needed
--Plug in the correct traceid from the query above
EXEC sp_trace_setstatus
@traceid = 2,
@status = 0; -- stop trace (assuming its trace ID 2)
GO
EXEC sp_trace_setstatus
@traceid = 2,
@status = 2; -- close trace (assuming its trace ID 2)
GO
--Plug in the correct traceid from the query above
EXEC sp_trace_setstatus
@traceid = 2,
@status = 0; -- stop trace (assuming its trace ID 2)
GO
EXEC sp_trace_setstatus
@traceid = 2,
@status = 2; -- close trace (assuming its trace ID 2)
GO
--Step 4: Read the blocked process report data.
EXEC dbo.sp_blocked_process_report_viewer
@Source = 'S:\Traces\BlockedProcessReportDemo.trc';
GO
EXEC dbo.sp_blocked_process_report_viewer
@Source = 'S:\Traces\BlockedProcessReportDemo.trc';
GO
--Step 5: Clean up!
--Don't forget this step.
--Make sure your trace is gone
SELECT *
FROM sys.traces;
GO
--Turn off the blocked process report when you are not using it.
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
EXEC sp_configure
'blocked process threshold (s)',
0;
GO
RECONFIGURE;
GO
EXEC sp_configure
'blocked process threshold (s)';
GO
--Make sure your trace is gone
SELECT *
FROM sys.traces;
GO
--Turn off the blocked process report when you are not using it.
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
EXEC sp_configure
'blocked process threshold (s)',
0;
GO
RECONFIGURE;
GO
EXEC sp_configure
'blocked process threshold (s)';
GO
Plan Cache Usage by Object Type
;WITH CACHE_ALLOC
AS (
SELECT objtype AS [CacheType],
COUNT_BIG(objtype) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE
WHEN usecounts = 1
THEN size_in_bytes
ELSE 0
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs - USE Count 1],
CASE
WHEN(GROUPING(objtype) = 1)
THEN COUNT_BIG(objtype)
ELSE 0
END AS GTOTAL
FROM sys.dm_exec_cached_plans
GROUP BY objtype)
SELECT [CacheType],
[Total Plans],
[Total MBs],
[Avg Use Count],
[Total MBs - USE Count 1],
CAST([Total Plans] * 1.0 / SUM([Total Plans]) OVER() * 100.0 AS DECIMAL(5, 2)) AS Cache_Alloc_Pct
FROM CACHE_ALLOC
ORDER BY [Total Plans] DESC;
From my SQL Server DBA handbook.