SQL Server健康检查的重要性


摘要:
对于DBA刚接收一台服务器时,一定要对服务器做一个安全性检查,对DB的大致情况做一个了解,下文将讲述我们对服务器的配置信息初步了解。
实验环境:sqlserver 2012


1.掌握sqlserver版本信息

  SELECT  @@VERSION  as [sqlserver及操作系统版本信息]

2.掌握windows版本及更新信息

  SELECT  windows_release,windows_service_pack_level,windows_sku,os_language_version  FROM  sys.dm_os_windows_info  WITH(NOLOCK)  OPTION  (RECOMPILE)
 

3.掌握CPU,内存的相关信息

  SELECT  cpu_count  AS  [逻辑CPU  数量],hyperthread_ratio  AS  [HyperthreadRatio],cpu_count / hyperthread_ratio  AS  [物理CPU  数量],  
physical_memory_kb / 1024  AS  [物理内存(MB)],affinity_type_desc,virtual_machine_type_desc,sqlserver_start_time  
FROM  sys.dm_os_sys_info  WITH  (NOLOCK)  OPTION  (RECOMPILE);
 

4.获取系统制造商

 EXEC  xp_readerrorlog 0,1,“制造商” ;  

5.sql服务的运行信息

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

6.获取SQL Server错误日志的位置以及配置方式

  SELECT  is_enabled,[path],max_size,max_files  
FROM  sys.dm_os_server_diagnostics_log_configurations  WITH  (NOLOCK)  OPTION  (RECOMPILE);
 

7.获取操作系统的集群信息

  SELECT  VerboseLogging,SqlDumperDumpFlags,SqlDumperDumpPath,SqlDumperDumpTimeOut,FailureConditionLevel,HealthCheckTimeout  
FROM  sys.dm_os_cluster_properties  WITH  (NOLOCK)  OPTION  (RECOMPILE); 
 

8. 获取有关群集节点及其状态的信息(如果您的数据库服务器位于群集中)

SELECT  NodeName,status_description,is_current_owner  
FROM  sys.dm_os_cluster_nodes  WITH  (NOLOCK)  OPTION  (RECOMPILE);  

9. 获取实例的配置值

SELECT *  FROM  sys.configurations  WITH  (NOLOCK)  ORDER BY 名称OPTION  (RECOMPILE);      

10. 获取有关SQL Server的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);  

11. SQL Server注册表信息

SELECT  registry_key,value_name,value_data  
FROM  sys.dm_server_registry  WITH  (NOLOCK)  OPTION  (RECOMPILE);  

11.从SQL Server获取有关内存转储的位置,时间和大小的信息

SELECT  [filename],creation_time,size_in_bytes  
FROM  sys.dm_server_memory_dumps  WITH  (NOLOCK)  OPTION  (RECOMPILE);  

12. Tempdb和实例中的所有用户数据库的文件名和路径

SELECT  DB_NAME([database_id])AS  [dbname],   
[file_id],name,Physicalpath,type_desc,state_desc,  
CONVERT (  BIGINT ,  大小/128.0)  AS  [总  大小在 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);   

13.实例上所有数据库的兼容级别

SELECT  db。[ name ]  AS  [ 数据库名称],db.recovery_model_desc  AS  [恢复模型],   
db.log_reuse_wait_desc  AS  [日志重用等待描述],  
ls.cntr_value  AS  [Log  Size  (KB)],lu.cntr_value  AS  [Log Used(KB)],  
CAST (CAST (lu.cntr_value  AS FLOAT )/  CAST (ls.cntr_value  AS FLOAT )AS DECIMAL (18,2))*     
100  AS  [use%],db。[compatibility_level]  AS  [数据库兼容  级别],  
db.page_verify_option_desc  AS  [页面验证  选项],db.is_auto_create_stats_on,  
db.is_auto_update_stats_on,db.is_auto_update_stats_async_on,  
db.is_parameterization_forced,  
db.snapshot_isolation_state_desc,db.is_read_committed_snapshot_on,  
is_auto_shrink_on,is_auto_close_on  
FROM  sys.databases  AS  db  WITH  (NOLOCK)  
INNER JOIN  sys.dm_os_performance_counters  AS  lu  WITH  (NOLOCK)   
ON  db。name  = lu.instance_name  
INNER JOIN  sys.dm_os_performance_counters  AS  ls  WITH  (NOLOCK)   
ON  db。name  = ls.instance_name  
WHERE  lu.counter_name  LIKE  N '使用的日志文件大小(KB)%'  
AND  ls.counter_name  LIKE  N '日志文件大小(KB)%'  
AND  ls.cntr_value> 0  OPTION  (RECOMPILE);  

14.计算每次读取,每次写入的平均停顿时间,
– 以及每个数据库文件的总输入/输出。

SELECT  DB_NAME(fs.database_id)  AS  [ 数据库名称],mf.physical_name,   
io_stall_read_ms,num_of_reads,  
CAST (io_stall_read_ms /(1.0 + num_of_reads)  AS NUMERIC (10,1))  AS   
[avg_read_stall_ms],io_stall_write_ms,  
num_of_writes,CAST (io_stall_write_ms /(1.0 + num_of_writes)  AS NUMERIC (10,1))  AS   
[avg_write_stall_ms]  
io_stall_read_ms + io_stall_write_ms  AS  [io_stalls],num_of_reads + num_of_writes  
AS  [total_io],  
CAST ((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes)  AS  
NUMERIC (10,1))  
AS  [avg_io_stall_ms]  
FROM  sys.dm_io_virtual_file_stats(null ,null )  AS  fs  
INNER JOIN  sys.master_files  AS  mf  WITH  (NOLOCK)   
ON  fs.database_id = mf.database_id  
AND  fs。[file_id] = mf。[file_id]  
ORDER BY  avg_io_stall_ms  DESC OPTION  (RECOMPILE);    

15. 获取当前实例的数据库总缓冲区使用情况

SELECT  DB_NAME(database_id)  AS  [ 数据库名称],   
COUNT (*)* 8 / 1024.0  AS  [缓存大小MB]  
FROM  sys.dm_os_buffer_descriptors  WITH  (NOLOCK)  
WHERE  database_id> 4  - 系统数据库  
AND  database_id <> 32767  - ResourceDB  
GROUP BY  DB_NAME(database_id)   
ORDER BY  [缓存大小MB]  DESC OPTION  (RECOMPILE);    

16.查询数据库大小:

SELECT name AS  [File  Name ],physical_name  AS  [Physical  Name ],  size /128.0  AS  [Total    
大小以 MB为单位],   
大小/128.0 -  CAST (FILEPROPERTY(名称,  'SpaceUsed' )  AS int )/128.0  AS  [可用  空间   
在 MB]中,[file_id]  
FROM  sys.database_files  WITH  (NOLOCK)  OPTION  (RECOMPILE);  

17.获取当前数据库的事务日志大小和空间信息

SELECT  DB_NAME(database_id)  AS  [ 数据库名称],database_id,   
CAST ((total_log_size_in_bytes / 1048576.0)  AS DECIMAL (10,1))   
AS  [Total_log_size(MB)],  
CAST ((used_log_space_in_bytes / 1048576.0)  AS DECIMAL (10,1))   
AS  [Used_log_space(MB)],  
CAST (used_log_space_in_percent  AS DECIMAL (10,1))  AS  [Used_log_space(%)]   
FROM  sys.dm_db_log_space_usage  WITH  (NOLOCK)  OPTION  (RECOMPILE);  

18.当前数据库的文件I / O统计信息

SELECT  DB_NAME(DB_ID())  AS  [ Database Name ],[file_id],num_of_reads,num_of_writes,   
io_stall_read_ms,io_stall_write_ms,  
CAST (100. * io_stall_read_ms /(io_stall_read_ms + io_stall_write_ms)  
AS DECIMAL (10,1))  AS  [IO Stall Reads Pct],   
CAST (100. * io_stall_write_ms /(io_stall_write_ms + io_stall_read_ms)  
AS DECIMAL (10,1))  AS  [IO Stall Writer Pct],   
(num_of_reads + num_of_writes)  AS  [Writes + Reads],num_of_bytes_read,  
num_of_bytes_written,  
CAST (100. * num_of_reads /(num_of_reads + num_of_writes)  AS DECIMAL (10,1))   
AS  [#读取Pct],  
CAST (100. * num_of_writes /(num_of_reads + num_of_writes)  AS DECIMAL (10,1))   
AS  [#Write Pct],  
CAST (100. * num_of_bytes_read /(num_of_bytes_read + num_of_bytes_written)  
AS DECIMAL (10,1))  AS  [ Read  Bytes Pct],   
CAST (100. * num_of_bytes_written /(num_of_bytes_read + num_of_bytes_written)  
AS DECIMAL (10,1))  AS  [Written Bytes Pct]   
FROM  sys.dm_io_virtual_file_stats(DB_ID(),  NULL )  OPTION  (RECOMPILE);  

19. 执行计数最高的缓存查询(SQL Server 2012)

SELECT  qs.execution_count,qs.total_rows,qs.last_rows,qs.min_rows,qs.max_rows,  
qs.last_elapsed_time,qs.min_elapsed_time,qs.max_elapsed_time,  
SUBSTRING (qt.TEXT,qs.statement_start_offset / 2 +1,  
(CASE WHEN  qs.statement_end_offset = -1   
THEN  LEN(CONVERT (NVARCHAR(MAX ),qt.TEXT))* 2  
ELSE  qs.statement_end_offset  END  - qs.statement_start_offset)/ 2)  
AS  query_text  
FROM  sys.dm_exec_query_stats  AS  qs  WITH  (NOLOCK)  
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle)  AS  qt  
ORDER BY  qs.execution_count  DESC OPTION  (RECOMPILE);    

20. 按执行计数排名靠前的缓存存储过程(SQL Server 2012)

SELECT TOP (250)p。名称AS  [存储过程名称],qs.execution_count,    
ISNULL (qs.execution_count / DATEDIFF(second ,qs.cached_time,GETDATE()),0)  
AS  [in/ 秒],  
qs.total_worker_time / qs.execution_count  AS  [AvgWorkerTime],  
qs.total_worker_time  AS  [TotalWorkerTime],qs.total_elapsed_time,  
qs.total_elapsed_time / qs.execution_count  AS  [avg_elapsed_time],  
qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
ORDER BY  qs.execution_count  DESC OPTION  (RECOMPILE);    

21. 平均耗用时间最高的缓存SP(SQL Server 2012)

SELECT TOP (25)p。名称AS  [SP  名称],qs.total_elapsed_time / qs.execution_count    
AS  [avg_elapsed_time],qs.total_elapsed_time,qs.execution_count,  
ISNULL (qs.execution_count / DATEDIFF(其次,qs.cached_time,  
GETDATE()),0)  AS  [Calls / Second ],  
qs.total_worker_time / qs.execution_count  AS  [AvgWorkerTime],  
qs.total_worker_time  AS  [TotalWorkerTime],qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
ORDER BY  avg_elapsed_time  DESC OPTION  (RECOMPILE); 

22. 按工作总时间排列的最高缓存SP(SQL Server 2012)。

SELECT TOP (25)p。名称AS  [SP  名称],qs.total_worker_time  AS  [TotalWorkerTime],    
qs.total_worker_time / qs.execution_count  AS  [AvgWorkerTime],qs.execution_count,  
ISNULL (qs.execution_count / DATEDIFF(second ,qs.cached_time,GETDATE()),0)  
AS  [Calls / Second ],qs.total_elapsed_time,qs.total_elapsed_time / qs.execution_count  
AS  [avg_elapsed_time],qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
ORDER BY  qs.total_worker_time  DESC OPTION  (RECOMPILE);    

23.- 通过总逻辑读取的最高缓存SP(SQL Server 2012)。

SELECT TOP (25)p。名称AS  [SP  名称],qs.total_logical_reads    
AS  [TotalLogicalReads],qs.total_logical_reads / qs.execution_count  
AS  [AvgLogicalReads],qs.execution_count,  
ISNULL (qs.execution_count / DATEDIFF(second ,qs.cached_time,GETDATE()),0)  
AS  [Calls / Second ],qs.total_elapsed_time,qs.total_elapsed_time / qs.execution_count  
AS  [avg_elapsed_time],qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
ORDER BY  qs.total_logical_reads  DESC OPTION  (RECOMPILE);   

24.通过总物理读取获得最高缓存的SP(SQL Server 2012)

SELECT TOP (25)p。名称AS  [SP  名称],qs.total_physical_reads    
AS  [TotalPhysicalReads],qs.total_physical_reads / qs.execution_count  
AS  [AvgPhysicalReads],qs.execution_count,qs.total_logical_reads,  
qs.total_elapsed_time,qs.total_elapsed_time / qs.execution_count  
AS  [avg_elapsed_time],qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
AND  qs.total_physical_reads> 0  
ORDER BY  qs.total_physical_reads  DESC ,   
qs.total_logical_reads  DESC OPTION  (RECOMPILE);

25.通过总逻辑写入的最高缓存SP(SQL Server 2012)。

 
SELECT TOP (25)p。名称AS  [SP  名称],qs.total_logical_writes    
AS  [TotalLogicalWrites],qs.total_logical_writes / qs.execution_count  
AS  [AvgLogicalWrites],qs.execution_count,  
ISNULL (qs.execution_count / DATEDIFF(second ,qs.cached_time,GETDATE()),0)  
AS  [Calls / Second ],qs.total_elapsed_time,qs.total_elapsed_time / qs.execution_count  
AS  [avg_elapsed_time],qs.cached_time  
FROM  sys.procedures  AS  p  WITH  (NOLOCK)  
INNER JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  (NOLOCK)   
ON  p。[object_id] = qs。[object_id]  
WHERE  qs.database_id = DB_ID()  
ORDER BY  qs.total_logical_writes  DESC OPTION  (RECOMPILE);

26.查找当前数据库中缓存计划的缺失索引警告

SELECT TOP (25)OBJECT_NAME(objectid)  AS  [ObjectName],query_plan,   
cp.objtype,cp.usecounts  
FROM  sys.dm_exec_cached_plans  AS  cp  WITH  (NOLOCK)  
CROSS  APPLY sys.dm_exec_query_plan(cp.plan_handle)  AS  qp  
WHERE CAST (query_plan  AS  NVARCHAR(MAX ))  LIKE  N '%MissingIndex%'   
AND  dbid = DB_ID()  
ORDER BY  cp.usecounts  DESC OPTION  (RECOMPILE); 

27. 分解当前数据库使用的缓冲区

SELECT  OBJECT_NAME(p。[object_id])  AS  [ObjectName],  
p.index_id,  COUNT (*)/ 128  AS  [缓冲区  大小(MB)],  COUNT (*)  AS  [BufferCount],  
p.data_compression_desc  AS  [CompressionType]  
FROM  sys.allocation_units  AS  一个  WITH  (NOLOCK)  
INNER JOIN  sys.dm_os_buffer_descriptors  AS  b  WITH  (NOLOCK)   
ON  a.allocation_unit_id = b.allocation_unit_id  
INNER JOIN  sys.partitions  AS  p  WITH WITH  (NOLOCK)   
打开 a.container_id = p.hobt_id  
WHERE  b.database_id =  CONVERT (int ,DB_ID())  
AND  p。[object_id]> 100  
GROUP BY  p。[object_id],p.index_id,p.data_compression_desc   
ORDER BY  [BufferCount]  DESC OPTION  (RECOMPILE);    

28. 获取表名称,行数和压缩状态

 
SELECT  OBJECT_NAME(object_id)  AS  [ObjectName],  
SUM (行)  AS  [RowCount],data_compression_desc  AS  [CompressionType]  
FROM  sys.partitions  WITH  (NOLOCK)  
WHERE  index_id < 2  
and OBJECT_NAME(OBJECT_ID)  NOT LIKE  ñ 'SYS%'   
AND  OBJECT_NAME(的object_id)  NOT LIKE  ñ 'queue_%'   
AND  OBJECT_NAME(的object_id)  NOT LIKE  ñ 'filestream_tombstone%'   
AND  OBJECT_NAME(的object_id)  NOT LIKE  ñ '全文%'   
AND  OBJECT_NAME(的object_id)  NOT LIKE  ñ 'ifts_comp_fragment%'   
AND  OBJECT_NAME(的object_id)  NOT LIKE  ñ 'filetable_updates%'   
GROUP BY  object_id,data_compression_desc   
ORDER BY SUM (Rows )  DESC OPTION  (RECOMPILE);