SQL Server 诊断查询-(1)

  SQL and OS Version information for current instance

SELECT @@SERVERNAME AS  [Server Name],  @@VERSIONAS  [ SQL  Server  and  OS Version Info];

Query #2 is Core Counts. 

  Get socket, physical core and logical core count from the SQL Server Error log.

EXEC  sys.xp_readerrorlog 0, 1, N ‘detected‘ , N ‘socket‘ ;

Query #3 is Server Properties.

   Get selected 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],                -- 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

-- Some columns will be NULL on older SQL Server builds

Query #4 is Configuration Values. 

-- Get instance-level configuration values for instance

SELECT  name,  value , value_in_use, minimum, maximum, [description], is_dynamic, is_advanced

FROM  sys.configurations  WITH  (NOLOCK)

ORDERBY  name  OPTION  (RECOMPILE);

-- 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 Global Trace Flags

-- Returns a list of all global trace flags that are enabled (Query 5) (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

-- The behavior of TF 1118 and 2371 are enabled in SQL Server 2016 by default

Query #6 Process Memory

-- 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)  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  arenotunder  internal memory pressure

Query #7 SQL Server Services Info

-- 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)  OPTION  (RECOMPILE);

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

 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

 -- //msdn.microsoft.com/en-us/library/ms189817.aspx

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)

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

Query #10 Windows Info

-- 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) OPTION (RECOMPILE);

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

   -- https://msdn.microsoft.com/en-us/library/bb500433(v=sql.130).aspx

   -- Hardware and Software Requirements for Installing SQL Server 2016

   -- https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).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

Query #11 SQL Server NUMA Info

-- SQL Server NUMA Node information

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

--

avg_load_balance :Average number of tasks per scheduler on this node.

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

-- http://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

Query #12 System Memory

-- Good basic information about OS memory amounts and state

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)  OPTION  (RECOMPILE);

-- You want to see "Available physical memory is high" for System Memory State

-- This indicates that you  are not under external  memory pressure

---------------------------------------------------------------------------------------------------

1. 本文内容来自 Glenn Berry  ，原文中对查询的作了一些简单的解析和说明，而且是针对SQL Server 2016的。

2. 从事DBA工作一些年后，每个人都会有自己的Toolkit。我在整理脚本时，发现这系列的脚本很实用和具有启发性，就整理和汇总了一下。

3. 关于系统DMV和DMF的使用，需要知道:

   (a)这些数据都是上次实例启动以来的积累数据

   (b)利用它们来诊断时，很多情况下需要运行多次收集数据，再分析。

   (c)对于性能指标，不要迷信所谓的推荐值。你的系统运行正常，满足你的用户，满足企业要求，就是正常值。所以说平时收集基线数据，是一件很重要的事情。

本文出自 “Joe TJ” 博客，请务必保留此出处http://joetang.blog.51cto.com/2296191/1740953

原文：http://joetang.blog.51cto.com/2296191/1740953