SqlServer巡检

微信公众平台 (qq.com)

1、检查数据库最大最小内存配置

SELECT [name], [value], [value_in_use]FROM sys.configurations WHERE [name] = 'max server memory (MB)' OR
  [name] = 'min server memory (MB)';

2、内存使用情况检查脚本

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

2、检查CPU是否有压力

--根据SOS_SCHEDULER_YIELD等待类型的百分比来判断CPU是否有压力，百分比越小越好。
WITH Waits AS 
( 
SELECT 
wait_type, 
wait_time_ms / 1000. AS wait_time_s, 
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, 
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn 
FROM sys.dm_os_wait_stats 
WHERE wait_type 
NOT IN 
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT') 
) -- filter out additional irrelevant waits 
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 
CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
FROM Waits AS W1 
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn 
GROUP BY W1.rn, 
W1.wait_type, 
W1.wait_time_s, 
W1.pct 
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

3、检查buffer cache hit ratio值

DECLARE @object_name SYSNAME
SET @object_name = CASE WHEN @@servicename = 'MSSQLSERVER' THEN 'SQLServer'
ELSE 'MSSQL$' + @@serviceName
END + ':Buffer Manager'
DECLARE
@PERF_LARGE_RAW_FRACTION INT ,
@PERF_LARGE_RAW_BASE INT
SELECT @PERF_LARGE_RAW_FRACTION = 537003264 ,
@PERF_LARGE_RAW_BASE = 1073939712
SELECT dopc_fraction.object_name ,
dopc_fraction.instance_name ,
dopc_fraction.counter_name ,
--when divisor is 0, return I return NULL to indicate
--divide by 0/no values captured
CAST(dopc_fraction.cntr_value AS FLOAT)
/ CAST(CASE dopc_base.cntr_value
WHEN 0 THEN NULL
ELSE dopc_base.cntr_value
END AS FLOAT) AS cntr_value
FROM sys.dm_os_performance_counters AS dopc_base
JOIN sys.dm_os_performance_counters AS dopc_fraction
ON dopc_base.cntr_type = @PERF_LARGE_RAW_BASE
AND dopc_fraction.cntr_type = @PERF_LARGE_RAW_FRACTION
AND dopc_base.object_name = dopc_fraction.object_name
AND dopc_base.instance_name = dopc_fraction.instance_name
AND ( REPLACE(dopc_base.counter_name,
'base', '') = dopc_fraction.counter_name
--Worktables From Cache has "odd" name where
--Ratio was left off
OR REPLACE(dopc_base.counter_name,
'base', '') = ( REPLACE(dopc_fraction.counter_name,
'ratio', '') )
)
WHERE dopc_fraction.object_name = @object_name
AND dopc_fraction.instance_name = ''
AND dopc_fraction.counter_name = 'Buffer cache hit ratio'
ORDER BY dopc_fraction.object_name ,
dopc_fraction.instance_name ,
dopc_fraction.counter_name

4、检查page life expectanc

SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

5、检查lazy write/sec

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'

6、检查数据库文件基本信息

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, max_size/128.0) AS [Max_size 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);

7、检查日志文件属性和恢复模式

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.state_desc, 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], 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,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled
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'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);

8、检查1433端口监听状态

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

9、检查是否存在密码快过期的SQL账号

SELECT @@SERVERNAME AS ServerName, SL.name AS LoginName
,LOGINPROPERTY(SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
,ISNULL(CONVERT(varchar(100),LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),'Never Expire') AS DaysUntilExpiration
,ISNULL(CONVERT(varchar(100),DATEADD(dd, CONVERT(int, LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),CONVERT(int, LOGINPROPERTY(SL.name, 'PasswordLastSetTime'))),101),'Never Expire') AS PasswordExpirationDate,
CASE
WHEN is_expiration_checked = 1 THEN 'TRUE' ELSE 'FALSE'
END AS PasswordExpireChecked
FROM sys.sql_logins AS SL
WHERE SL.name NOT LIKE '##%' AND SL.name NOT LIKE 'endPointUser' and is_disabled = 0
ORDER BY (LOGINPROPERTY(SL.name, 'PasswordLastSetTime')) DESC

10、检查job执行情况

SELECT
    [job].[job_id] AS '作业唯一标示符'
   ,[job].[name] AS '作业名称'
   , CASE WHEN [jobh].[run_date] IS NULL
               OR [jobh].[run_time] IS NULL THEN NULL
          ELSE CAST ( CAST ([jobh].[run_date] AS CHAR ( 8 )) + ' '
               + STUFF(STUFF( RIGHT ( '000000'
                                   + CAST ([jobh].[run_time] AS VARCHAR ( 6 )), 6 ),
                             3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '最后执行时间'
   , CASE [jobh].[run_status]
      WHEN 0 THEN '失败'
      WHEN 1 THEN '成功'
      WHEN 2 THEN '重试'
      WHEN 3 THEN '取消'
      WHEN 4 THEN '正在运行'
     END AS '最后执行状态'
   ,STUFF(STUFF( RIGHT ( '000000' + CAST ([jobh].[run_duration] AS VARCHAR ( 6 )), 6 ),
                 3 , 0 , ':' ), 6 , 0 , ':' ) AS '最后运行持续时间'
   ,[jobh].[message] AS '最后运行状态信息'
   , CASE [jsch].[NextRunDate]
      WHEN 0 THEN NULL
       ELSE CAST ( CAST ([jsch].[NextRunDate] AS CHAR ( 8 )) + ' '
            + STUFF(STUFF( RIGHT ( '000000'
                                + CAST ([jsch].[NextRunTime] AS VARCHAR ( 6 )),
                                6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '下次运行时间'
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN (
             SELECT
                [job_id]
               , MIN ([next_run_date]) AS [NextRunDate]
               , MIN ([next_run_time]) AS [NextRunTime]
             FROM [msdb].[dbo].[sysjobschedules]
             GROUP BY [job_id]
          ) AS [jsch]
         ON [job].[job_id] = [jsch].[job_id]
LEFT JOIN (
             SELECT
                [job_id]
               ,[run_date]
               ,[run_time]
               ,[run_status]
               ,[run_duration]
               ,[message]
               ,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber
             FROM [msdb].[dbo].[sysjobhistory]
             WHERE [step_id] = 0
          ) AS [jobh]
     ON [job].[job_id] = [jobh].[job_id]
        AND [jobh].[RowNumber] = 1
ORDER BY [job].[name]

11、检查是否有备份，备份是否正常

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], 
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date]
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 
AND bs.backup_size > 0
AND bs.[type] = 'D' -- Change to L if you want Log backups
AND database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);