ch15,Delivering a SQL Server Health Check--PART 1

  一、前言

      做为DBA，在接手一台全新的或是不熟悉的SQL Server DB服务器时，首先需要做的，就是尽可能详尽的收集这台服务器的相关信息，这包括硬件信息和存储子系统以及OS相关信息，SQL server instance本身的信息等。做为数据库专业人士，尤其是对于一个专业的DBA，这是必须知道的基本信息。

     对于可以接直接登录到DB server OS的DBA，则可以通过登录到OS直接收集相关信或使用DMV来收集，而对于不能或是不被允许直接登录到OS的DBA，则只有通过使用DMV/DMF了。

 

 

二，使用DMV/DMF收集DB SERVER相关信息

     A，使用@@VERSION

-- SQL and OS Version information for current instance
SELECT @@VERSION AS [SQL Server and OS Version Info];

 

   B，通过DMV sys.dm_os_windows_info 获得windows 信息。

-- Windows information (SQL Server 2012)
SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

 

我这里返回：

windows_release windows_service_pack_level   windows_sku   os_language_version
6.1                        Service Pack 1                         4                       2052

 

以下为windows_sku说明：

http://msdn.microsoft.com/en-us/library/ms724358.aspx

 

以下为os_language_version说明：

http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx

 

 

 

C，查看硬件信息，使用 sys.dm_os_sys_info ：

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], 
affinity_type_desc, virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

 

sys.dm_os_sys_info 说明：

 

 

 D,查看硬件型号

EXEC xp_readerrorlog 0,1,"Manufacturer";

 

E，查看处理器信息

-- Get processor description from Windows Registry

EXEC xp_instance_regread

'HKEY_LOCAL_MACHINE',

'HARDWARE\DESCRIPTION\System\CentralProcessor\0',

'ProcessorNameString';

 

 

F,查看SQL server services 信息

-- SQL Server Services information from SQL Server 2012

SELECT servicename, startup_type_desc, status_desc,

last_startup_time, service_account, is_clustered, cluster_nodename

FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

 

G,查看SQL Server Error Log 信息

 

--SQL Server Error Log information

SELECT is_enabled, [path], max_size, max_files

FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) 

OPTION (RECOMPILE);

 

H,查看OS集群信息

--Operating system cluster information

-- Get information about your OS cluster

--(if your database server is in a cluster)

SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,

SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout

FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

 

I,查看集群节点及其状态信息

SELECT NodeName, status_description, is_current_owner

FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);

 

J,查看SQl  server instance配置信息

SELECT name, value, value_in_use, [description] 

FROM sys.configurations WITH (NOLOCK)

ORDER BY name OPTION (RECOMPILE);

 

主要关注以下各项：

 

-- Focus on

-- backup compression default

-- clr enabled (only enable if it is needed)

-- lightweight pooling (should be zero)

-- max degree of parallelism 

-- max server memory (MB) (set to an appropriate value)

-- optimize for ad hoc workloads (should be 1)

 

 

 

-- priority boost (should be zero)

 

 

K,查看TCP监听信息

 

SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_time

FROM sys.dm_tcp_listener_states WITH (NOLOCK) OPTION (RECOMPILE);

 

返回包含各个 TCP 侦听器的动态信息的行。

 

L,查看sql server 注册表信息

SELECT registry_key, value_name, value_data

FROM sys.dm_server_registry WITH (NOLOCK) OPTION (RECOMPILE);

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/101629/viewspace-761473/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101629/viewspace-761473/