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 [Server Name],
@@VERSION AS [SQL Server and OS Version Info];
-- 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 [Server Name], create_date
AS [SQL Server Install Date]
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 [MachineName], SERVERPROPERTY(
‘ServerName‘)
AS [ServerName],
SERVERPROPERTY(‘InstanceName‘)
AS [Instance], SERVERPROPERTY(
‘IsClustered‘)
AS [IsClustered],
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS‘)
AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(‘Edition‘)
AS [Edition], SERVERPROPERTY(
‘ProductLevel‘)
AS [ProductLevel],
SERVERPROPERTY(‘ProductVersion‘)
AS [ProductVersion], SERVERPROPERTY(
‘ProcessID‘)
AS [ProcessID],
SERVERPROPERTY(‘Collation‘)
AS [Collation], SERVERPROPERTY(
‘IsFullTextInstalled‘)
AS [IsFullTextInstalled],
SERVERPROPERTY(‘IsIntegratedSecurityOnly‘)
AS [IsIntegratedSecurityOnly],
SERVERPROPERTY(‘IsHadrEnabled‘)
AS [IsHadrEnabled], SERVERPROPERTY(
‘HadrManagerStatus‘)
AS [HadrManagerStatus];
-- 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 [JobName], sj.
[description] AS [JobDescription],
SUSER_SNAME(sj.owner_sid)
AS [JobOwner],
sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name
AS [CategoryName],
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,
[enabled], 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, [filename]
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 [Logical CPU Count], scheduler_count, hyperthread_ratio
AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio
AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb
/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc
AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc
AS [Virtual Machine Type]
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,
[path], 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,
[description], 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 [filename], creation_time, size_in_bytes
/1048576.0 AS [Size (MB)]
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(
[database_id])
AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
is_percent_growth, growth,
CONVERT(
bigint, growth
/128.0)
AS [Growth in MB],
CONVERT(
bigint, size
/128.0)
AS [Total Size in MB]
FROM sys.master_files
WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME(
[database_id])
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 [Total Size (GB)],
CONVERT(
DECIMAL(
18,
2),vs.available_bytes
/1073741824.0)
AS [Available Size (GB)],
CAST(
CAST(vs.available_bytes
AS FLOAT)
/ CAST(vs.total_bytes
AS FLOAT)
AS DECIMAL(
18,
2))
* 100 AS [Space Free %]
FROM sys.master_files
AS f
WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.
[file_id])
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.
[Drive], tab.volume_mount_point
AS [Volume Mount Point],
CASE
WHEN num_of_reads
= 0 THEN 0
ELSE (io_stall_read_ms
/num_of_reads)
END AS [Read Latency],
CASE
WHEN io_stall_write_ms
= 0 THEN 0
ELSE (io_stall_write_ms
/num_of_writes)
END AS [Write Latency],
CASE
WHEN (num_of_reads
= 0 AND num_of_writes
= 0)
THEN 0
ELSE (io_stall
/(num_of_reads
+ num_of_writes))
END AS [Overall Latency],
CASE
WHEN num_of_reads
= 0 THEN 0
ELSE (num_of_bytes_read
/num_of_reads)
END AS [Avg Bytes/Read],
CASE
WHEN io_stall_write_ms
= 0 THEN 0
ELSE (num_of_bytes_written
/num_of_writes)
END AS [Avg Bytes/Write],
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 [Avg Bytes/Transfer]
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.
[file_id])
AS vs
GROUP BY LEFT(
UPPER(mf.physical_name),
2), vs.volume_mount_point)
AS tab
ORDER BY [
