非常全面的SQL Server巡检脚本来自sqlskills团队的Glenn Berry 大牛

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 [