SQL Server 诊断查询-(4)

hgjgh

　　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.[type] AS [Memory Clerk Type],
　　CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
　　FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
　　GROUP BY mc.[type]
　　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)[text] AS [QueryText],cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB]
　　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.[dbid]) AS [Database Name], LEFT(t.text, 50) AS [Short Query Text],
　　qs.total_logical_reads AS [Total Logical Reads],
　　qs.min_logical_reads AS [Min Logical Reads],
　　qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
　　qs.max_logical_reads AS [Max Logical Reads],
　　qs.min_worker_time AS [Min Worker Time],
　　qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
　　qs.max_worker_time AS [Max Worker Time],
　　qs.min_elapsed_time AS [Min Elapsed Time],
　　qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
　　qs.max_elapsed_time AS [Max Elapsed Time],
　　qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
　　--,t. AS [Complete Query Text], qp.query_plan AS [Query Plan] -- 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 [File Name] , f.physical_name AS [Physical Name],
　　CAST((f.size/128.0) ASDECIMAL(15,2)) AS [Total Sizein MB],
　　CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') ASint)/128.0 ASDECIMAL(15,2))
　　AS [Available SpaceIn MB], [file_id], fg.name AS [Filegroup Name],
　　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 [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,
　　df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Size on Disk (MB)],
　　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 [IO Stall Reads Pct],
　　CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
　　(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads],
　　CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
　　CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
　　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 [Read Bytes Pct],
　　CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
　　FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
　　INNER JOIN sys.database_files AS df WITH (NOLOCK)
　　ON vfs.[file_id]= df.[file_id] 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 [Short Query Text], qs.execution_count AS [Execution Count],
　　qs.total_logical_reads AS [Total Logical Reads],
　　qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
　　qs.total_worker_time AS [Total Worker Time],
　　qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
　　qs.total_elapsed_time AS [Total Elapsed Time],
　　qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
　　qs.creation_time AS [Creation Time]
　　--,t. AS [Complete Query Text], qp.query_plan AS [Query Plan] -- 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 [SP Name], qs.execution_count,
　　ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
　　qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],
　　qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
　　qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
　　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 [Calls/Minute],
　　qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
　　qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name], qs.total_worker_time AS [TotalWorkerTime],
　　qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
　　ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
　　qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
　　AS [avg_elapsed_time], qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
　　qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
　　ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
　　qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
　　AS [avg_elapsed_time], qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
　　qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
　　qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
　　AS [avg_elapsed_time], qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],
　　qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
　　ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
　　qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
　　qs.cached_time
　　FROM sys.procedures AS p WITH (NOLOCK)
　　INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
　　ON p.[object_id] = qs.[object_id]
　　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 [SP Name],
　　(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], qs.execution_count AS [Execution Count],
　　SUBSTRING(qt.[text],qs.statement_start_offset/2,
　　(CASE
　　WHEN qs.statement_end_offset = -1
　　THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
　　ELSE qs.statement_end_offset
　　END - qs.statement_start_offset)/2) AS [Query Text]
　　FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
　　WHERE qt.[dbid] = DB_ID()
　　ORDER BY [Avg IO] 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.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
　　i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
　　user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
　　user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
　　FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
　　INNER JOIN sys.indexes AS i WITH (NOLOCK)
　　ON s.[object_id] = i.[object_id]
　　AND i.index_id = s.index_id
　　WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
　　AND s.database_id = DB_ID()
　　AND user_updates > (user_seeks + user_scans + user_lookups)
　　AND i.index_id > 1
　　ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] 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 [index_advantage],
　　migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
　　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.[object_id]) AS [Table Name], p.rows AS [Table Rows]
　　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.[object_id] = mid.[object_id]
　　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 [ObjectName],
　　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
　　---