sqlserver_sheck.sql 微软官方巡检脚本
-- SQL Server 2012 Diagnostic Information Queries-- Glenn Berry
-- April 2015
-- Last Modified: April 27, 2015
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry
-- Please listen to my Pluralsight courses
-- http://www.pluralsight.com/author/glenn-berry
-- Many of these queries will not work if you have databases in 80 compatibility mode
-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server
--******************************************************************************
--* Copyright (C) 2015 Glenn Berry, SQLskills.com
--* All rights reserved.
--*
--* For more scripts and sample code, check out
--* http://sqlskills.com/blogs/glenn
--*
--* You may alter this code for your own *non-commercial* purposes. You may
--* republish altered code as long as you include this copyright and give due credit.
--*
--*
--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--* PARTICULAR PURPOSE.
--*
--******************************************************************************
-- Check the major product version to see if it is SQL Server 2012
IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '11%')
BEGIN
DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));
RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);
END
ELSE
PRINT N'You have the correct major version of SQL Server for this diagnostic information script';
-- Instance level queries *******************************
-- SQL and OS Version information for current instance(Query 1) (Version Info)
SELECT @@SERVERNAME AS , @@VERSION AS ;
-- SQL Server 2012 RTM Branch Builds SQL Server 2012 SP1 Branch Builds SQL Server 2012 SP2 Branch Builds
-- Build Description Release Date Build Description Release Date Build Description Release Date
-- 11.0.2100 RTM 3/6/2012
-- 11.0.2316 RTM CU1 4/12/2012
-- 11.0.2325 RTM CU2 6/18/2012 --> 11.0.3000 SP1 RTM 11/7/2012
-- 11.0.2332 RTM CU3 8/31/2012
-- 11.0.2376 RTM CU3 + QFE 10/9/2012
-- 11.0.2383 RTM CU4 10/15/2012 --> 11.0.3321 SP1 CU1 11/20/2012
-- 11.0.2395 RTM CU5 12/17/2012 --> 11.0.3339 SP1 CU2 1/21/2013
-- 11.0.2401 RTM CU6 2/18/2013 --> 11.0.3349 SP1 CU3 3/18/2013
-- 11.0.2405 RTM CU7 4/15/2013 --> 11.0 3368 SP1 CU4 5/30/2013
-- 11.0.2410 RTM CU8 6/17/2013 --> 11.0.3373 SP1 CU5 7/15/2013
-- 11.0.2419 RTM CU9 8/20/2013 --> 11.0.3381 SP1 CU6 9/16/2013
-- 11.0.2420 RTM CU10 10/21/2013 --> 11.0.3393 SP1 CU7 11/18/2013
-- 11.0.2424 RTM CU11 12/16/2003 --> 11.0.3401 SP1 CU8 1/20/2014
-- 11.0.3412 SP1 CU9 3/17/2014 --> 11.0.5058 SP2 RTM 6/10/2014
-- 11.0.3431 SP1 CU10 5/19/2014
-- 11.0.3449 SP1 CU11 7/21/2014 --> 11.0.5532 SP2 CU1 7/23/2014
-- 11.0.3470 SP1 CU12 9/15/2014 --> 11.0.5548 SP2 CU2 9/15/2014
-- 11.0.3482 SP1 CU13 11/17/2014--> 11.0.5556 SP2 CU3 11/17/2014
-- 11.0.3486 SP1 CU14 1/19/2015 --> 11.0.5569 SP2 CU4 1/19/2015
-- 11.0.5571 SP2 CU4 + COD HF 2/4/2015(this includes the AlwaysOn AG hotfix that is in SP2 CU5)
-- 11.0.3487 SP1 CU15 3/16/2015 11.0.5582 SP2 CU5 3/16/2015
-- The SQL Server 2012 builds that were released after SQL Server 2012 was released
-- http://support.microsoft.com/kb/2692828
-- The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
-- http://support.microsoft.com/kb/2772858
-- SQL Server 2012 SP2 build versions (new format for the build list KB article)
-- http://support.microsoft.com/kb/2983249
-- Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads
-- http://support.microsoft.com/kb/2964518/EN-US
-- Performance and Stability Related Fixes in Post-SQL Server 2012 SP2 Builds
-- http://www.sqlskills.com/blogs/glenn/performance-and-stability-related-fixes-in-post-sql-server-2012-sp2-builds/
-- When was SQL Server installed(Query 2) (SQL Server Install Date)
SELECT @@SERVERNAME AS , create_date AS
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE' OPTION (RECOMPILE);
-- Tells you the date and time that SQL Server was installed
-- It is a good idea to know how old your instance is
-- Get selected server properties (SQL Server 2012)(Query 3) (Server Properties)
SELECT SERVERPROPERTY('MachineName') AS , SERVERPROPERTY('ServerName') AS ,
SERVERPROPERTY('InstanceName') AS , SERVERPROPERTY('IsClustered') AS ,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ,
SERVERPROPERTY('Edition') AS , SERVERPROPERTY('ProductLevel') AS ,
SERVERPROPERTY('ProductVersion') AS , SERVERPROPERTY('ProcessID') AS ,
SERVERPROPERTY('Collation') AS , SERVERPROPERTY('IsFullTextInstalled') AS ,
SERVERPROPERTY('IsIntegratedSecurityOnly') AS ,
SERVERPROPERTY('IsHadrEnabled') AS , SERVERPROPERTY('HadrManagerStatus') AS ;
-- This gives you a lot of useful information about your instance of SQL Server,
-- such as the ProcessID for SQL Server and your collation
-- The last two columns are new for SQL Server 2012
-- Get SQL Server Agent jobs and Category information (Query 4) (SQL Server Agent Jobs)
SELECT sj.name AS , sj. AS , SUSER_SNAME(sj.owner_sid) AS ,
sj.date_created, sj., sj.notify_email_operator_id, sj.notify_level_email, sc.name AS ,
js.next_run_date, js.next_run_time
FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
ON sj.category_id = sc.category_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)
ON sj.job_id = js.job_id
ORDER BY sj.name OPTION (RECOMPILE);
-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured
-- Look for Agent jobs that are not owned by sa
-- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)
-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
--
-- MSDN sysjobs documentation
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx
-- Get SQL Server Agent Alert Information (Query 5) (SQL Server Agent Alerts)
SELECT name, event_source, message_id, severity, , has_notification,
delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)
-- Read more about Agent Alerts here: http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/
-- Returns a list of all global trace flags that are enabled (Query 6) (Global Trace Flags)
DBCC TRACESTATUS (-1);
-- If no global trace flags are enabled, no results will be returned.
-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.
-- Common trace flags that should be enabled in most cases
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
-- TF 1118 - Helps alleviate allocation contention in tempdb, SQL Server allocates full extents to each database object,
-- thereby eliminating the contention on SGAM pages (more important with older versions of SQL Server)
-- Recommendations to reduce allocation contention in SQL Server tempdb database
-- http://support2.microsoft.com/kb/2154845
-- Windows information (SQL Server 2012)(Query 7) (Windows Info)
SELECT windows_release, windows_service_pack_level,
windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);
-- Gives you major OS version, Service Pack, Edition, and language info for the operating system
-- 6.3 is either Windows 8.1 or Windows Server 2012 R2
-- 6.2 is either Windows 8 or Windows Server 2012
-- 6.1 is either Windows 7 or Windows Server 2008 R2
-- 6.0 is either Windows Vista or Windows Server 2008
-- Windows SKU codes
-- 4 is Enterprise Edition
-- 7 is Standard Server Edition
-- 8 is Datacenter Server Edition
-- 10 is Enterprise Server Edition
-- 48 is Professional Edition
-- 1033 for os_language_version is US-English
-- SQL Server 2012 requires Windows Server 2008 SP2 or newer
-- Hardware and Software Requirements for Installing SQL Server 2012
-- http://msdn.microsoft.com/en-us/library/ms143506.aspx
-- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments
-- http://support.microsoft.com/kb/2681562
-- SQL Server Services information (SQL Server 2012) (Query 8) (SQL Server Services Info)
SELECT servicename, process_id, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename,
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
-- Tells you the account being used for the SQL Server Service and the SQL Agent Service
-- Shows the processid, when they were last started, and their current status
-- Shows whether you are running on a failover cluster instance
-- SQL Server NUMA Node information(Query 9) (SQL Server NUMA Info)
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
-- Gives you some useful information about the composition and relative load on your NUMA nodes
-- You want to see an equal number of schedulers on each NUMA node
-- Hardware information from SQL Server 2012(Query 10) (Hardware Info)
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS , scheduler_count, hyperthread_ratio AS ,
cpu_count/hyperthread_ratio AS ,
physical_memory_kb/1024 AS , committed_kb/1024 AS ,
committed_target_kb/1024 AS ,
max_workers_count AS , affinity_type_desc AS ,
sqlserver_start_time AS , virtual_machine_type_desc AS
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
-- Gives you some good basic hardware information about your database server
-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
-- It merely indicates that you have a hypervisor running on your host
-- Get System Manufacturer and model number from(Query 11) (System Manufacturer)
-- SQL Server Error log. This query might take a few seconds
-- if you have not recycled your error log recently
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
-- This can help you determine the capabilities
-- and capacities of your database server
-- Get socket, physical core and logical core count from (Query 12) (Core Counts)
-- SQL Server Error log. This query might take a few seconds
-- if you have not recycled your error log recently
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
-- It can also help you confirm your SQL Server licensing model
-- Be on the lookout for this message "using 20 logical processors based on SQL Server licensing" which means grandfathered Server/CAL licensing
-- Note: If you recycle your error logs frequently and your instance has been running long enough,
-- this query may not return any results, since the original startup information from the first error log
-- when SQL Server was last started will have been overwritten
-- Get processor description from Windows Registry(Query 13) (Processor Description)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
-- Gives you the model number and rated clock speed of your processor(s)
-- Your processors may be running at less that the rated clock speed due
-- to the Windows Power Plan or hardware power management
-- You can skip the next four queries if you know you don't
-- have a clustered instance
-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured(Query 14) (SQL Server Error Log)
SELECT is_enabled, , max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);
-- Knowing this information is important for troubleshooting purposes
-- Also shows you the location of other error and diagnostic log files
-- Get information about your OS cluster (if your database server is in a cluster)(Query 15) (Cluster Properties)
SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);
-- You will see no results if your instance is not clustered
-- Get information about your cluster nodes and their status(Query 16) (Cluster Node Properties)
-- (if your database server is in a failover cluster)
SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
-- Knowing which node owns the cluster resources is critical
-- Especially when you are installing Windows or SQL Server updates
-- You will see no results if your instance is not clustered
-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 17) (AlwaysOn AG Cluster)
SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
-- You will see no results if your instance is not using AlwaysOn AGs
-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
-- http://support.microsoft.com/kb/2920151
-- Get configuration values for instance(Query 18) (Configuration Values)
SELECT name, value, value_in_use, minimum, maximum, , is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Focus on these settings:
-- backup compression default (should be 1 in most cases)
-- clr enabled (only enable if it is needed)
-- cost threshold for parallelism (depends on your workload)
-- lightweight pooling (should be zero)
-- max degree of parallelism (depends on your workload)
-- max server memory (MB) (set to an appropriate value, not the default)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)
-- remote admin connections (should be 1)
-- Get information about TCP Listener for SQL Server(Query 19) (TCP Listener States)
SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_time
FROM sys.dm_tcp_listener_states WITH (NOLOCK)
ORDER BY listener_id OPTION (RECOMPILE);
-- Helpful for network and connectivity troubleshooting
-- Get information on location, time and size of any memory dumps from SQL Server(Query 20) (Memory Dump Info)
SELECT , creation_time, size_in_bytes/1048576.0 AS
FROM sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY creation_time DESC OPTION (RECOMPILE);
-- This will not return any rows if you have
-- not had any memory dumps (which is a good thing)
-- File names and paths for TempDB and all user databases in instance(Query 21) (Database Filenames and Paths)
SELECT DB_NAME() AS ,
, name, physical_name, type_desc, state_desc,
is_percent_growth, growth,
CONVERT(bigint, growth/128.0) AS ,
CONVERT(bigint, size/128.0) AS
FROM sys.master_files WITH (NOLOCK)
WHERE > 4
AND <> 32767
OR = 2
ORDER BY DB_NAME() OPTION (RECOMPILE);
-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Is there only one TempDB data file?
-- Are all of the TempDB data files the same size?
-- Are there multiple data files for user databases?
-- Is percent growth enabled for any files (which is bad)?
-- Volume info for all LUNS that have database files on the current instance (Query 22) (Volume Info)
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type,
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS ,
CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS ,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.) AS vs OPTION (RECOMPILE);
--Shows you the total and free space on the LUNs where you have database files
-- Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs (Query 23) (IO Warnings)
CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults
EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';
SELECT LogDate, ProcessInfo, LogText
FROM #IOWarningResults
ORDER BY LogDate DESC;
DROP TABLE #IOWarningResults;
-- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of
-- poor I/O performance (which might have many different causes)
-- Drive level latency information (Query 24) (Drive Level Latency)
-- Based on code from Jimmy May
SELECT tab., tab.volume_mount_point AS ,
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (io_stall_read_ms/num_of_reads)
END AS ,
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (io_stall_write_ms/num_of_writes)
END AS ,
CASE
WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE (io_stall/(num_of_reads + num_of_writes))
END AS ,
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (num_of_bytes_read/num_of_reads)
END AS ,
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (num_of_bytes_written/num_of_writes)
END AS ,
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 AS
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.) AS vs
GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
ORDER BY OPTION (RECOMPILE);
-- Shows you the drive-level latency for reads and writes, in milliseconds
-- Latency above 20-25ms is usually a problem
-- Calculates average stalls per read, per write, and per total input/output for each database file(Query 25) (IO Stalls by File)
SELECT DB_NAME(fs.database_id) AS , CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS ,
CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS ,
CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS ,
CONVERT(DECIMAL(18,2), mf.size/128.0) AS , mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,
fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS , fs.num_of_reads + fs.num_of_writes AS
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs. = mf.
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
-- Helps determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location or perhaps improve your I/O performance
-- Recovery model, log reuse wait description, log file size, log usage size(Query 26) (Database Properties)
-- and compatibility level for all databases on instance
SELECT db. AS , db.recovery_model_desc AS , db.state_desc,
db.log_reuse_wait_desc AS ,
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS , CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS ,
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS ,
db. AS ,
db.page_verify_option_desc AS , db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);
-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs ?
-- What compatibility level are the databases on?
-- What is the Page Verify Option? (should be CHECKSUM)
-- Is Auto Update Statistics Asynchronously enabled?
-- Make sure auto_shrink and auto_close are not enabled!
-- Missing Indexes for all databases by Index Advantage(Query 27) (Missing Indexes All Databases)
SELECT 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
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
ORDER BY index_advantage DESC OPTION (RECOMPILE);
-- Getting missing index information for all of the databases on the instance is very useful
-- Look at last user seek time, number of user seeks to help determine source and importance
-- Also look at avg_user_impact and avg_total_user_cost to help determine importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
-- Get VLF Counts for all databases on the instance (Query 28) (VLF Counts)
-- (adapted from Michelle Ufford)
CREATE TABLE #VLFInfo (RecoveryUnitID int, FileIDint,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, bigint,
Parity bigint, CreateLSN numeric(38));
CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
EXEC sp_MSforeachdb N'Use [?];
INSERT INTO #VLFInfo
EXEC sp_executesql N''DBCC LOGINFO([?])'';
INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;
TRUNCATE TABLE #VLFInfo;'
SELECT DatabaseName, VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;
-- High VLF counts can affect write performance
-- and they can make database restores and recovery take much longer
-- Try to keep your VLF counts under 200 in most cases
-- Get CPU utilization by database (Query 29) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS , SUM(qs.total_worker_time/1000) AS
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY DESC) AS ,
, AS ,
CAST( * 1.0 / SUM() OVER() * 100.0 AS DECIMAL(5, 2)) AS
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY OPTION (RECOMPILE);
-- Helps determine which database is using the most CPU resources on the instance
-- Get I/O utilization by database (Query 30) (IO Usage By Database)
WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS ,
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS , , io_in_mb AS ,
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS
FROM Aggregate_IO_Statistics
ORDER BY OPTION (RECOMPILE);
-- Helps determine which database is using the most I/O resources on the instance
-- Get total buffer usage by database for current instance(Query 31) (Total Buffer Usage by Database)
-- This make take some time to run on a busy instance
WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS ,
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))AS
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS , , CachedSize AS ,
CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS
FROM AggregateBufferPoolUsage
ORDER BY OPTION (RECOMPILE);
-- Tells you how much memory (in the buffer pool)
-- is being used by each database on the instance
-- Clear Wait Stats with this command
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or wait statistics clear(Query 32) (Top Waits)
WITH
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS ,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ,
signal_wait_time_ms / 1000.0 AS ,
waiting_tasks_count AS ,
100.0 *wait_time_ms / SUM (wait_time_ms) OVER() AS ,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE 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')
AND waiting_tasks_count > 0)
SELECT
MAX (W1.wait_type) AS ,
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS ,
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS ,
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS ,
MAX (W1.WaitCount) AS ,
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS ,
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS ,
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS ,
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS
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) < 99 -- percentage threshold
OPTION (RECOMPILE);
-- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure
-- The SQL Server Wait Type Repository
-- http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx
-- Wait statistics, or please tell me where it hurts
-- http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
-- SQL Server 2005 Performance Tuning using the Waits and Queues
-- http://technet.microsoft.com/en-us/library/cc966413.aspx
-- sys.dm_os_wait_stats (Transact-SQL)
-- http://msdn.microsoft.com/en-us/library/ms179984(v=sql.120).aspx
-- Signal Waits for instance(Query 33) (Signal Waits)
SELECT 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);
-- 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
--Get logins that are connected and how many sessions they have (Query 34) (Connection Counts)
SELECT login_name, , COUNT(session_id) AS
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name,
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);
-- This can help characterize your workload and
-- determine whether you are seeing a normal level of activity
-- Get a count of SQL connections by IP address (Query 35) (Connection Counts by IP Address)
SELECT ec.client_net_address, es., es., es.login_name,
COUNT(ec.session_id) AS
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es., es., es.login_name
ORDER BY ec.client_net_address, es. OPTION (RECOMPILE);
-- This helps you figure where your database load is coming from
-- and verifies connectivity from other machines
-- Get Average Task Counts (run multiple times)(Query 36) (Avg Task Counts)
SELECT AVG(current_tasks_count) AS ,
AVG(runnable_tasks_count) AS ,
AVG(pending_disk_io_count) AS
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
-- Sustained values above 10 suggest further investigation in that area
-- High Avg Task Counts are often caused by blocking/deadlocking or other resource contention
-- Sustained values above 1 suggest further investigation in that area
-- High Avg Runnable Task Counts are a good sign of CPU pressure
-- High Avg Pending DiskIO Counts are a sign of disk pressure
-- Get CPU Utilization History for last 256 minutes (in one minute intervals)(Query 37) (CPU Utilization History)
-- This version works with SQL Server 2012
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(256) SQLProcessUtilization AS ,
SystemIdle AS ,
100 - SystemIdle - SQLProcessUtilization AS ,
DATEADD(ms, -1 * (@ts_now - ), GETDATE()) AS
FROM (SELECT record.value('(./Record/@id)', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int')
AS ,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int')
AS ,
FROM (SELECT , CONVERT(xml, record) AS
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
-- Look at the trend over the entire period.
-- Also look at high sustained Other Process CPU Utilization values
-- Get top total worker time queries for entire instance (Query 38) (Top Worker Time Queries)
SELECT TOP(50) DB_NAME(t.) AS , LEFT(t., 255) AS ,
qs.total_worker_time AS , qs.min_worker_time AS ,
qs.total_worker_time/qs.execution_count AS ,
qs.max_worker_time AS , qs.execution_count AS ,
qs.total_elapsed_time/qs.execution_count AS ,
qs.total_logical_reads/qs.execution_count AS ,
qs.total_physical_reads/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_worker_time DESC OPTION (RECOMPILE);
-- Helps you find the most expensive queries from a CPU perspective across the entire instance
-- Good basic information about OS memory amounts and state(Query 39) (System Memory)
SELECT total_physical_memory_kb/1024 AS ,
available_physical_memory_kb/1024 AS ,
total_page_file_kb/1024 AS ,
available_page_file_kb/1024 AS ,
system_cache_kb/1024 AS ,
system_memory_state_desc AS
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
-- SQL Server Process Address space info(Query 40) (Process Memory)
-- (shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb/1024 AS ,
large_page_allocations_kb, locked_page_allocations_kb, page_fault_count,
memory_utilization_percentage, available_commit_limit_kb,
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
-- Page Life Expectancy (PLE) value for each NUMA node in current instance(Query 41) (PLE by NUMA Node)
SELECT @@SERVERNAME AS , , instance_name, cntr_value AS
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Watch the trend over time, not the absolute value.
-- This will only return one row for non-NUMA systems.
-- Memory Grants Pending value for current instance(Query 42) (Memory Grants Pending)
SELECT @@SERVERNAME AS , , cntr_value AS
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure
-- Memory Clerk Usage for instance(Query 43) (Memory Clerk Usage)
-- 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) DESC OPTION (RECOMPILE);
-- MEMORYCLERK_SQLBUFFERPOOL is new for SQL Server 2012. It should be your highest consumer of memory
-- CACHESTORE_SQLCPSQL 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_OBJCPObject Plans
-- These are compiled plans for stored procedures, functions and triggers
-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache(Query 44) (Ad hoc Queries)
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!
-- Database specific queries *****************************************************************
-- **** Switch to a user database *****
USE YourDatabaseName; -- make sure to change to an actual database on your instance
GO
-- Individual File Sizes and space available for current database(Query 45) (File Sizes and Space)
SELECT f.name AS , f.physical_name AS ,
CAST((f.size/128.0) AS DECIMAL(15,2)) AS ,
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS , , fg.name AS
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN 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 is not full!!
-- I/O Statistics by file for the current database(Query 46) (IO Stats By File)
SELECT DB_NAME(DB_ID()) AS , df.name AS , vfs.,
df.physical_name 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
-- Top cached queries by Execution Count (SQL Server 2012)(Query 47) (Query Execution Counts)
SELECT TOP (100) qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
total_worker_time, total_logical_reads,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(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 qs.execution_count DESC OPTION (RECOMPILE);
-- Uses several new rows returned columns to help troubleshoot performance problems
-- Top Cached SPs By Execution Count (SQL Server 2012)(Query 48) (SP Execution Counts)
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
-- Top Cached SPs By Avg Elapsed Time (SQL Server 2012)(Query 49) (SP Avg Elapsed Time)
SELECT TOP(25) p.name AS , qs.total_elapsed_time/qs.execution_count AS ,
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 long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques
-- Top Cached SPs By Avg Elapsed Time with execution time variability (SQL Server 2012)(Query 50) (SP Avg Elapsed Variable Time)
SELECT TOP(25) p.name AS , qs.execution_count, qs.min_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS ,
qs.max_elapsed_time, qs.last_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. = qs.
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);
-- This gives you some interesting information about the variability in the
-- execution time of your cached stored procedures, which is useful for tuning
-- Top Cached SPs By Total Worker time (SQL Server 2012). Worker time relates to CPU cost(Query 51) (SP Worker Time)
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
-- Top Cached SPs By Total Logical Reads (SQL Server 2012). Logical reads relate to memory pressure(Query 52) (SP Logical Reads)
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
-- Top Cached SPs By Total Physical Reads (SQL Server 2012). Physical reads relate to disk I/O pressure(Query 53) (SP Physical Reads)
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
-- Top Cached SPs By Total Logical Writes (SQL Server 2012)(Query 54) (SP 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
-- Lists the top statements by average input/output usage for the current database(Query 55) (Top IO Statements)
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
-- Possible Bad NC Indexes (writes > reads)(Query 56) (Bad NC Indexes)
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!
-- Missing Indexes for current database by Index Advantage(Query 57) (Missing Indexes)
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!!!
-- Find missing index warnings for cached plans in the current database(Query 58) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS ,
query_plan, cp.objtype, cp.usecounts
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
-- Breaks down buffers used by current database by object (table, index) in the buffer cache(Query 59) (Buffer Usage)
-- Note: This query could take some time on a busy instance
SELECT OBJECT_NAME(p.) AS , p.index_id,
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS ,
COUNT(*) AS , p.Rows AS ,
p.data_compression_desc AS
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p. > 100
GROUP BY p., p.index_id, p.data_compression_desc, p.
ORDER BY DESC OPTION (RECOMPILE);
-- Tells you what tables and indexes are using the most memory in the buffer cache
-- It can help identify possible candidates for data compression
-- Get Table names, row counts, and compression status for clustered index or heap(Query 60) (Table Sizes)
SELECT OBJECT_NAME(object_id) AS ,
SUM(Rows) AS , data_compression_desc AS
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);
-- Gives you an idea of table sizes, and possible data compression opportunities
-- Get some key table properties (Query 61) (Table Properties)
SELECT , create_date, lock_on_bulk_load, is_replicated, has_replication_filter,
is_tracked_by_cdc, lock_escalation_desc
FROM sys.tables WITH (NOLOCK)
ORDER BY OPTION (RECOMPILE);
-- Gives you some good information about your tables
-- Detect blocking (run multiple times)(Query 62) (Detect Blocking)
SELECT t1.resource_type AS , DB_NAME(resource_database_id) AS ,
t1.resource_associated_entity_id AS ,t1.request_mode AS ,--- lock requested
t1.request_session_id AS , t2.wait_duration_ms AS , -- spid of waiter
(SELECT FROM sys.dm_exec_requests AS r WITH (NOLOCK) -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.)
WHERE r.session_id = t1.request_session_id) AS ,
(SELECT SUBSTRING(qt.,r.statement_start_offset/2,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.)) * 2
ELSE r.statement_end_offset END - r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.) AS qt
WHERE r.session_id = t1.request_session_id) AS , -- statement blocked
t2.blocking_session_id AS , -- spid of blocker
(SELECT FROM sys.sysprocesses AS p -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.)
WHERE p.spid = t2.blocking_session_id) AS
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
-- Helps troubleshoot blocking and deadlocking issues
-- The results will change from second to second on a busy system
-- You should run this query multiple times when you see signs of blocking
-- When were Statistics last updated on all indexes?(Query 63) (Statistics Update)
SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS , o.type_desc AS ,
i.name AS , STATS_DATE(i., i.index_id) AS ,
s.auto_created, s.no_recompute, s.user_created, s.is_temporary,
st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o. = i.
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i. = s.
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o. = st.
AND i. = st.
WHERE o. IN ('U', 'V')
AND st.row_count > 0
ORDER BY STATS_DATE(i., i.index_id) DESC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active
-- Look at most frequently modified indexes and statistics (Query 64) (Volatile Indexes)
SELECT o.name AS , o., o.type_desc, s.name AS ,
s.stats_id, s.no_recompute, s.auto_created,
sp.modification_counter, sp.rows, sp.rows_sampled, sp.last_updated
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.type_desc NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);
-- Get fragmentation info for all indexes above a certain size in the current database(Query 65) (Index Fragmentation)
-- Note: This query could take some time on a very large database
SELECT DB_NAME(ps.database_id) AS , OBJECT_NAME(ps.OBJECT_ID) AS ,
i.name AS , ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent,
ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps. = i.
AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
-- Helps determine whether you have framentation in your relational indexes
-- and how effective your index maintenance strategy is
--- Index Read/Write stats (all tables in current DB) ordered by Reads(Query 66) (Overall Index Usage - Reads)
SELECT OBJECT_NAME(s.) AS , i.name AS , i.index_id,
user_seeks + user_scans + user_lookups AS , s.user_updates AS ,
i.type_desc AS , i.fill_factor AS , i.has_filter, i.filter_definition,
s.last_user_scan, s.last_user_lookup, s.last_user_seek
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s. = i.
WHERE OBJECTPROPERTY(s.,'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY user_seeks + user_scans + user_lookups DESC OPTION (RECOMPILE); -- Order by reads
-- Show which indexes in the current database are most active for Reads
--- Index Read/Write stats (all tables in current DB) ordered by Writes(Query 67) (Overall Index Usage - Writes)
SELECT OBJECT_NAME(s.) AS , i.name AS , i.index_id,
s.user_updates AS , user_seeks + user_scans + user_lookups AS ,
i.type_desc AS , i.fill_factor AS , i.has_filter, i.filter_definition,
s.last_system_update, s.last_user_update
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s. = i.
WHERE OBJECTPROPERTY(s.,'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes
-- Show which indexes in the current database are most active for Writes
-- Get lock waits for current database (Query 68) (Lock Waits)
SELECT o.name AS , i.name AS , ios.index_id, ios.partition_number,
SUM(ios.row_lock_wait_count) AS ,
SUM(ios.row_lock_wait_in_ms) AS ,
SUM(ios.page_lock_wait_count) AS ,
SUM(ios.page_lock_wait_in_ms) AS ,
SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON ios. = o.
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ios. = i.
AND ios.index_id = i.index_id
WHERE o. > 100
GROUP BY o.name, i.name, ios.index_id, ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);
-- This query is helpful for troubleshooting blocking and deadlocking issues
-- Look at recent Full backups for the current database (Query 69) (Recent Full Backups)
SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS , bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS ,
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS ,
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS ,
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS ,
bs.backup_finish_date AS
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
AND bs.backup_size > 0
AND bs. = 'D' -- Change to L if you want Log backups
AND database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
-- Are your backup sizes and times changing over time?
-- Are you using backup compression?
-- These three Pluralsight Courses go into more detail about how to run these queries and interpret the results
-- SQL Server 2014 DMV Diagnostic Queries Part 1
-- http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part1
-- SQL Server 2014 DMV Diagnostic Queries Part 2
-- http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part2
-- SQL Server 2014 DMV Diagnostic Queries Part 3
-- http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part3
页:
[1]