Buffer cache usage summary
SELECT
D.name
,MBUsed = COUNT(*) * 8 / 1024
,MBEmpty = SUM(CAST([free_space_in_bytes] AS BIGINT)) / (1024 * 1024)
FROM
sys.dm_os_buffer_descriptors B
INNER JOIN
sys.databases D
ON
B.database_id = D.database_id
GROUP BY
D.name
Current memory status
--https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/dbcc-memorystatus-monitor-memory-usage
DBCC MEMORYSTATUS;
Index usage
SELECT
DatabaseName = DB_NAME()
,TableName = o.name
,IndexName = i.name
,s.index_id
,i.type
,i.is_primary_key
,i.is_unique_constraint
,s.last_user_seek
,s.user_seeks
,SeekPercentage = CASE s.user_seeks WHEN 0 THEN 0 ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END
,s.last_user_scan
,s.user_scans
,ScanPercentage = CASE s.user_scans WHEN 0 THEN 0 ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END
,s.last_user_lookup
,s.user_lookups
,s.last_user_update
,s.user_updates
,s.last_system_seek
,s.last_system_scan
,s.last_system_lookup
,s.last_system_update
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.[OBJECT_ID] = i.[OBJECT_ID]
AND s.index_id = i.index_id
INNER JOIN
sys.objects o
ON
i.object_id = o.object_id
AND O.is_ms_shipped = 0
WHERE
s.database_id = DB_ID()
ORDER BY
s.user_seeks DESC;
Memory grants
SELECT
[session_id],
[request_id],
[scheduler_id],
[dop],
[request_time],
[grant_time],
[requested_memory_kb],
[granted_memory_kb],
[required_memory_kb],
[used_memory_kb],
[max_used_memory_kb],
[query_cost],
[timeout_sec],
[resource_semaphore_id],
[queue_id],
[wait_order],
[is_next_candidate],
[wait_time_ms],
[plan_handle],
[sql_handle],
[group_id],
[pool_id],
[is_small],
[ideal_memory_kb]
FROM
sys.[dm_exec_query_memory_grants];
Missing index
SELECT
improvement_measure = migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)
,create_index_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 + ')', '')
,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;
Most expensive queries
-- Based on code by Jimmy May
--http://www.SQLskills.com
SELECT TOP 25
qs.execution_count
, round (convert (float, qs.total_worker_time) / convert (float, qs.execution_count) / 1000.0, 1) as avg_worker_ms
, qs.total_physical_reads / qs.execution_count as avg_phys_rds
, qs.total_logical_reads / qs.execution_count as avg_log_rds
, qs.total_logical_writes / qs.execution_count as avg_log_wrts
, round (convert (float, qs.total_elapsed_time) / convert (float, qs.execution_count) / 1000.0, 1) as avg_elapsed_ms
-- the following four columns are NULL for ad hoc and prepared batches
, DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number
, qp.query_plan --the query plan can be *very* useful; enable if desired
, qt.text
, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time , qs.last_execution_time , qs.total_clr_time / qs.execution_count as avg_clr_time
, qs.total_worker_time , qs.last_worker_time , qs.min_worker_time , qs.max_worker_time
, qs.total_physical_reads , qs.last_physical_reads , qs.min_physical_reads , qs.max_physical_reads
, qs.total_logical_reads , qs.last_logical_reads , qs.min_logical_reads , qs.max_logical_reads
, qs.total_logical_writes , qs.last_logical_writes , qs.min_logical_writes , qs.max_logical_writes
, qs.total_elapsed_time , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time
, qs.total_clr_time , qs.last_clr_time , qs.min_clr_time , qs.max_clr_time
--, qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset
, qs.plan_generation_num -- , qp.encrypted
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
--WHERE...
where qs.total_worker_time / qs.execution_count > 100000
ORDER BY qs.execution_count DESC --Frequency
--ORDER BY qs.total_worker_time DESC --CPU
--ORDER BY qs.total_elapsed_time DESC --Durn
--ORDER BY qs.total_logical_reads DESC --Reads
--ORDER BY qs.total_logical_writes DESC --Writes
--ORDER BY qs.total_physical_reads DESC --PhysicalReads
--ORDER BY avg_worker_time DESC --AvgCPU
--ORDER BY avg_elapsed_time DESC --AvgDurn
--ORDER BY avg_logical_reads DESC --AvgReads
--ORDER BY avg_logical_writes DESC --AvgWrites
--ORDER BY avg_physical_reads DESC --AvgPhysicalReads
--sample WHERE clauses
--WHERE last_execution_time > '20070507 15:00'
--WHERE execution_count = 1
-- WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
-- ((CASE statement_end_offset
-- WHEN -1 THEN DATALENGTH(qt.text)
-- ELSE qs.statement_end_offset END
-- - qs.statement_start_offset)/2) + 1)
-- LIKE '%MyText%'
Plan cache summary
SELECT
objtype AS [cache_type]
,count_big(*) AS [total_plan_count]
,sum(cast(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [total_size_mbytes]
,avg(usecounts) AS [avg_use_counts]
,sum(cast((
CASE
WHEN usecounts = 1
THEN size_in_bytes
ELSE 0
END
) AS DECIMAL(18, 2))) / 1024 / 1024 AS [single_use_size_mbytes]
,sum(CASE
WHEN usecounts = 1
THEN 1
ELSE 0
END) AS [single_use_plan_count]
FROM
sys.dm_exec_cached_plans
GROUP BY
objtype
ORDER BY
[single_use_size_mbytes] DESC;
Retrive query plan via query hash
SELECT
p.[query_plan]
FROM
sys.[dm_exec_query_stats] AS q
CROSS APPLY
sys.[dm_exec_query_plan](q.[plan_handle]) AS p
WHERE
q.[query_hash] = 0xEB3F21FCCE406BBE;
Retrive query text and plan for a specific session
SELECT
[t].[text]
,[p].[query_plan]
FROM
[sys].[dm_exec_sessions] AS s
LEFT OUTER JOIN
[sys].[dm_exec_requests] AS r
ON
[s].[session_id] = [r].[session_id]
OUTER APPLY [sys].[dm_exec_sql_text]([r].[sql_handle]) AS t
OUTER APPLY [sys].[dm_exec_query_plan]([r].[plan_handle]) AS p
WHERE
[s].[session_id] = 77;
Running queries
SELECT [r].[session_id]
,[s].[host_name]
,[s].[login_name]
,[r].[start_time]
,[r].[sql_handle]
,[st].[text]
,[r].[wait_type]
,[r].[blocking_session_id]
,[r].[reads]
,[r].[writes]
,[r].[cpu_time]
,[t].[user_objects_alloc_page_count]
,[t].[internal_objects_alloc_page_count]
FROM
[sys].[dm_exec_requests] AS [r]
INNER JOIN
[sys].[dm_exec_sessions] AS [s]
ON
[s].[session_id] = [r].[session_id]
INNER JOIN
[sys].[dm_db_task_space_usage] AS [t]
ON
[s].[session_id] = [t].[session_id]
AND [r].[request_id] = [t].[request_id]
CROSS APPLY
[sys].[dm_exec_sql_text](r.[sql_handle]) AS [st]
WHERE
[r].[status] IN (
'running'
,'runnable'
,'suspended'
);
Top IO queries
SELECT
q.[query_hash]
,SUBSTRING(t.TEXT, (q.[statement_start_offset] / 2) + 1, (
(
CASE q.[statement_end_offset]
WHEN - 1
THEN DATALENGTH(t.[text])
ELSE q.[statement_end_offset]
END - q.[statement_start_offset]
) / 2
) + 1)
,SUM(q.[total_physical_reads]) AS [total_physical_reads]
FROM
sys.[dm_exec_query_stats] AS q
CROSS APPLY
sys.[dm_exec_sql_text](q.sql_handle) AS t
GROUP BY
q.[query_hash]
,SUBSTRING(t.TEXT, (q.[statement_start_offset] / 2) + 1, (
(
CASE q.[statement_end_offset]
WHEN - 1
THEN DATALENGTH(t.[text])
ELSE q.[statement_end_offset]
END - q.[statement_start_offset]
) / 2
) + 1)
ORDER BY
SUM(q.[total_physical_reads]) DESC;
Transaction lock info
SELECT
SchemaName = object_schema_name(ISNULL(P.object_id,TRY_CONVERT(INT,T.resource_associated_entity_id)))
,TableName = object_name(ISNULL(P.object_id,TRY_CONVERT(INT,T.resource_associated_entity_id)))
,object_id = ISNULL(P.object_id,TRY_CONVERT(INT,T.resource_associated_entity_id))
,I.name
,I.index_id
,P.partition_id
,P.partition_number
,P.rows
,P.filestream_filegroup_id
,P.data_compression
,P.filestream_filegroup_id
,T.resource_type
,T.resource_description
,T.resource_lock_partition
,T.request_mode
,T.request_status
,T.request_lifetime
,T.request_session_id
FROM
sys.dm_tran_locks T
LEFT JOIN
sys.partitions P
ON
P.hobt_id = T.resource_associated_entity_id
LEFT JOIN
sys.indexes I
ON
I.object_id = P.object_id
AND I.index_id = P.index_id
WHERE
T.resource_associated_entity_id > 0
AND T.resource_database_id = db_id()
AND I.object_id IS NOT NULL
--AND I.object_id = OBJECT_ID('tablename')
--AND T.request_session_id <> @@SPID
ORDER BY
TableName
,SchemaName
,T.resource_type
,T.request_mode
,T.request_status
,T.request_session_id