SQL Server 诊断查询-(4)
Query #41 Memory Clerk Usage-- Memory Clerk Usage for instance
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(10) mc. AS ,
CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.
ORDER BY SUM(mc.pages_kb) DESCOPTION (RECOMPILE);
-- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory
-- CACHESTORE_SQLCP SQL Plans
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
-- Watch out for high values for CACHESTORE_SQLCP
-- CACHESTORE_OBJCP Object Plans
-- These are compiled plans for stored procedures, functions and triggers
Query #42 Ad hoc Queries
-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache
SELECT TOP(50) AS ,cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
-- Enabling forced parameterization for the database can help, but test first!
-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
-- http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
Query #43 Top Logical Reads Queries
-- Get top total logical reads queries for entire instance
SELECT TOP(50) DB_NAME(t.) AS , LEFT(t.text, 50) AS ,
qs.total_logical_reads AS ,
qs.min_logical_reads AS ,
qs.total_logical_reads/qs.execution_count AS ,
qs.max_logical_reads AS ,
qs.min_worker_time AS ,
qs.total_worker_time/qs.execution_count AS ,
qs.max_worker_time AS ,
qs.min_elapsed_time AS ,
qs.total_elapsed_time/qs.execution_count AS ,
qs.max_elapsed_time AS ,
qs.execution_count AS , qs.creation_time AS
--,t. AS , qp.query_plan AS -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
-- Helps you find the most expensive queries from a memory perspective across the entire instance
-- Can also help track down parameter sniffing issues
Query #44 File>
-- Individual File> SELECT f.name AS , f.physical_name AS ,
CAST((f.size/128.0) ASDECIMAL(15,2)) AS ,
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') ASint)/128.0 ASDECIMAL(15,2))
AS , , fg.name AS ,
f.is_percent_growth, f.growth
FROM sys.database_files AS f WITH (NOLOCK)
LEFTOUTERJOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);
-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log isnotfull!!
Query #45 IO Stats By File
-- I/O Statistics by file for the current database
SELECT DB_NAME(DB_ID()) AS , df.name AS , vfs., df.type_desc,
df.physical_name AS , CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(10, 2)) AS ,
vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,
CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS ,
CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS ,
(vfs.num_of_reads + vfs.num_of_writes) AS ,
CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS ,
CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS ,
CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS ,
CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.= df. OPTION (RECOMPILE);
-- This helps you characterize your workload better from an I/O perspective for this database
-- It helps you determine whether you has an OLTP or DW/DSS type of workload
Query #46 Query Execution Counts
-- Get most frequently executed queries for this database
SELECT TOP(50) LEFT(t., 50) AS , qs.execution_count AS ,
qs.total_logical_reads AS ,
qs.total_logical_reads/qs.execution_count AS ,
qs.total_worker_time AS ,
qs.total_worker_time/qs.execution_count AS ,
qs.total_elapsed_time AS ,
qs.total_elapsed_time/qs.execution_count AS ,
qs.creation_time AS
--,t. AS , qp.query_plan AS -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
#47 SP Execution Counts
-- Top Cached SPs By Execution Count
SELECT TOP(100) p.name AS , qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS ,
qs.total_worker_time/qs.execution_count AS , qs.total_worker_time AS ,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS ,
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload
Query #48 SP Avg Elapsed Time
-- Top Cached SPs By Avg Elapsed Time
SELECT TOP(25) p.name AS , qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS ,
qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS ,
qs.total_worker_time/qs.execution_count AS ,
qs.total_worker_time AS , qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);
-- This helps you find high average elapsed time cached stored procedures that
-- may be easy to optimize with standard query tuning techniques
Query #49 SP Worker Time
-- Top Cached SPs By Total Worker time. Worker time relates to CPU cost
SELECT TOP(25) p.name AS , qs.total_worker_time AS ,
qs.total_worker_time/qs.execution_count AS , qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS ,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS , qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure
Query #50 SP Logical Reads
-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure
SELECT TOP(25) p.name AS , qs.total_logical_reads AS ,
qs.total_logical_reads/qs.execution_count AS ,qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS ,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS , qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure
Query #51 SP Physical Reads
-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure
SELECT TOP(25) p.name AS ,qs.total_physical_reads AS ,
qs.total_physical_reads/qs.execution_count AS , qs.execution_count,
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS , qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure
Query #52 SP Logical Writes
-- Top Cached SPs By Total Logical Writes
-- Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) p.name AS , qs.total_logical_writes AS ,
qs.total_logical_writes/qs.execution_count AS , qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS ,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS ,
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p. = qs.
WHERE qs.database_id = DB_ID()
AND qs.total_logical_writes > 0
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure
Query #53 Top IO Statements
-- Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid, dbid) AS ,
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS , qs.execution_count AS ,
SUBSTRING(qt.,qs.statement_start_offset/2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) AS
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt. = DB_ID()
ORDER BY DESC OPTION (RECOMPILE);
-- Helps you find the most expensive statements for I/O by SP
Query #54 Bad NC Indexes
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.) AS , i.name AS , i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS , user_seeks + user_scans + user_lookups AS ,
user_updates - (user_seeks + user_scans + user_lookups) AS
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s. = i.
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY DESC, DESC, ASC OPTION (RECOMPILE);
-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!
Query #55 Missing Indexes
-- Missing Indexes for current database by Index Advantage
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS ,
migs.last_user_seek, mid. AS ,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
OBJECT_NAME(mid.) AS , p.rows AS
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON p. = mid.
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC OPTION (RECOMPILE);
-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
Query #56 Missing Index Warnings
-- Find missing index warnings for cached plans in the current database
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS ,
query_plan, cp.objtype, cp.usecounts, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
-- Helps you connect missing indexes to specific stored procedures or queries
-- This can help you decide whether to add them or not
---
页:
[1]