This website requires JavaScript.

MSSQL: SQL Server 诊断信息查询

Query #1 – 版本信息

-- SQL and OS Version information for current instance (Query 1) (Version Info)
,       @@VERSION AS [SQL Server and OS Version Info];
-- SQL Server 2016 RTM Branch Builds
 -- Build Description Release Date
 -- CTP 2.0 5/26/2015
 -- 13.0.300.44 CTP 2.1 6/14/2015
 -- 13.0.407.1 CTP 2.2 7/28/2015
 -- 13.0.500.53 CTP 2.3 9/4/2015
 -- 13.0.600.65 CTP 2.4 9/30/2015
 -- 13.0.700.242 CTP 3.0 10/29/2015
 -- 13.0.900.73 CTP 3.2 12/12/2015
 -- Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads
 -- How to determine the version, edition and update level of SQL Server and its components

Query #2 – 内核数

-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
-- 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

Query #3 服务器属性

-- Get selected server properties (Query 3) (Server Properties)
 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], -- What servicing branch (RTM/SP/CU)
 SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied
 SERVERPROPERTY('ProductVersion') AS [ProductVersion],
 SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
 SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
 SERVERPROPERTY('ProductBuild') AS [ProductBuild],
 SERVERPROPERTY('ProductBuildType') AS [ProductBuildType], -- Is this a GDR or OD hotfix (NULL if on a CU build)
 SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
 SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
 SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
 SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
 SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
 SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
 SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
 SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
 SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled], -- New for SQL Server 2016
 SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
 SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
 SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];
 -- This gives you a lot of useful information about your instance of SQL Server,
 -- such as the ProcessID for SQL Server and your collation
 -- Note: Some columns will be NULL on older SQL Server builds

Query #4  配置信息

-- Get instance-level configuration values for instance (Query 4) (Configuration Values)
 SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
 FROM sys.configurations WITH (NOLOCK)

-- Focus on these settings:
 -- automatic soft-NUMA disabled (should be 0 in most cases)
 -- backup checksum default (should be 1)
 -- 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 and hardware)
 -- 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)

-- New options for SQL Server 2016
 -- hadoop connectivity
 -- polybase network encryption
 -- remote data archive (to enable Stretch Databases)

Query #5 全局跟踪标示

-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)
 -- 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
 -- The behavior of TF 1118 and 2371 are enabled in SQL Server 2016 by default
 -- SQL Server query optimizer hotfix trace flag 4199 servicing model
  • TF 1118  – This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. As Paul Randal says, “Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on”. In fact, for SQL Server 2016, Microsoft has now included the behavior from TF 1118 in the product by default, so you don’t need to enable it for SQL Server 2016.

  • TF 2371 – This trace flag changes the threshold for automatic statistics updates from the old default of needing 20% of the rows in a table to have been updated to trigger a statistics update to a dynamic % that decreases as the table row count increases. This means that you will get more frequent automatic statistics updates on larger tables. Even with this change, it is still a good idea to use SQL Server Agent jobs to periodically trigger manual statistics updates on your most volatile tables. Personally, I think there is no downside to having this enabled. Someone at Microsoft must agree, since for SQL Server 2016, they have now included the behavior from TF 2371 in the product by default, so you don’t need to enable it for SQL Server 2016.

  • TF 3226 – This trace flag prevents SQL Server from writing information to the SQL Server Error log after every successful database backup (which includes Full, Differential and Log backups). It will still log failed log backups, which is what you really care about anyway. Records of all database backups are still stored in the msdb system database after enabling this trace flag. Personally, I think there is no downside to having this enabled, and it makes it much easier to find more relevant information in the SQL Server Error log when this trace flag is enabled.

Query #6  内存使用情况

-- SQL Server Process Address space info (Query 6) (Process Memory)
 -- (shows whether locked pages is enabled, among other things)
SELECT  physical_memory_in_use_kb / 1024 AS [SQL Server Memory Usage (MB)]
 ,       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 )

-- 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
SQL Server Memory Usage (MB), 显示了当前数据库线程用的内存数量.

注意locked_page_allocations_kb 如果locked pages in memory (LPIM) 的值比0大,那么表明系统开启了这个特性.我建议把LPIM打开(只要你设置了合适的最大可用内存,操作系统就没什么内存压力了)

Query #7 SQL Server 服务信息

-- SQL Server Services information (Query 7) (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 )
 -- Tells you the account being used for the SQL Server Service and the SQL Agent Service
 -- Shows the process_id, when they were last started, and their current status
 -- Shows whether you are running on a failover cluster instance

Query #8 SQL Server Agent Jobs

-- Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs)
 ,       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
 , 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

-- 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

Query #9 SQL Server Agent Alerts

-- Get SQL Server Agent Alert Information (Query 9) (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

-- 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:
我估计90%的人都没有看到过这个[SQL Server Agent Alerts]( 的警告内容.因为很多DBAs不知道这玩意有什么用.

从名字来看跟SQL Server Agent jobs很相近,很多DBA以为SQL Server Agent Alerts是SQL Server Agent jobs 任务失败的错误信息.实际上这个是用来检测错误,或者特定条件事件(如SQL Server performance condition 或 WMI event alerts)的.

你可以用SQL Server Agent 执行一个Job来监控它,出了问题可以及时发送邮件告知我们.这篇博文详解SQL Server Agent Alerts的细节方面,你可以下载T-SQL脚本,来帮助你监控你的服务器

Query #10 显示操作系统信息

-- Windows information (Query 10) (Windows Info)
 SELECT  windows_release
 ,       windows_service_pack_level
 ,       windows_sku
 ,       os_language_version
 FROM    sys.dm_os_windows_info WITH ( NOLOCK )

-- Gives you major OS version, Service Pack, Edition, and language info for the operating system
 -- 10.0 is either Windows 10 or Windows Server 2016
 -- 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 2014 requires Windows Server 2012 or newer

-- Quick-Start Installation of SQL Server 2016

-- Hardware and Software Requirements for Installing SQL Server 2016

-- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments

Query #11 SQL Server NUMA Info

-- SQL Server NUMA Node information (Query 11) (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'
 -- 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
 -- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 16 physical cores
 -- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

Query #12 系统内存信息

-- Good basic information about OS memory amounts and state (Query 12) (System Memory)
SELECT  total_physical_memory_kb / 1024 AS [Physical Memory (MB)]
 ,       available_physical_memory_kb / 1024 AS [Available Memory (MB)]
 ,       total_page_file_kb / 1024 AS [Total Page File (MB)]
 ,       available_page_file_kb / 1024 AS [Available Page File (MB)]
 ,       system_cache_kb / 1024 AS [System Cache (MB)]
 ,       system_memory_state_desc AS [System Memory State]
 FROM    sys.dm_os_sys_memory WITH ( NOLOCK )
 -- You want to see "Available physical memory is high" for System Memory State
-- This indicates that you are not under external memory pressure
比较重要的是system_memory_state_desc ,分别有 “Available physical memory is high”, “Available physical memory is steady”, or “Available physical memory is low”,

Query #13 SQL Server 错误日志

-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured (Query 13) (SQL Server Error Log)
SELECT  is_enabled
,       [path]
 ,       max_size
 ,       max_files
FROM    sys.dm_os_server_diagnostics_log_configurations WITH ( NOLOCK )

-- Knowing this information is important for troubleshooting purposes
 -- Also shows you the location of other error and diagnostic log files
显示SQL Server错误日志的目录和相关配置

Query #14 群集节点属性 Cluster Node Properties

-- Get information about your cluster nodes and their status (Query 14) (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 )
 -- 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

Query #15 AlwaysOn AG Cluster

-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
SELECT  cluster_name
,       quorum_type_desc
,       quorum_state_desc
 FROM    sys.dm_hadr_cluster WITH ( NOLOCK )
 -- 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


本文直接汇总了该Blog的7篇SQL Server Diagnostic Information Queries Detailed 文章.