你的SQL Server监控清单上该有哪些内容

作为一个有着良好习惯的SQL Server DBA在每天早晨一上班都要通过一系列的列表检查确保他们的系统在顺利进行，这篇技巧文章将介绍一些更重要的项目，每一个DBA都应该手动监视或者使用某种形式的计划的脚本。

第一项：你的所有的SQL Server服务都正在运行吗？

显然一旦你连接到您的实例，你就知道数据库服务已启动并运行，但您可以使用扩展存储过程xp_servicecontrol检查是否有任何服务启动并运行。下面是您可以检查什么的几个例子。

exec master.dbo.xp_servicecontrol ‘QUERYSTATE’, ‘MSSQLServer’

exec master.dbo.xp_servicecontrol ‘QUERYSTATE’, ‘SQLServerAgent’

exec master.dbo.xp_servicecontrol ‘QUERYSTATE’, ‘SQLBrowser’

第二项：你的SQL Agent Jobs成功运行了吗？

对MSDB数据库的一个相当简单的查询可以检查这个项目。查询的第一部分是检查任何失败的作业步骤，第二部分仅仅是关注整体的工作状态，一步失败可能会导致步步失败，最好是在造成，如果你是用了SQL SErver Agent备份你的数据库，这也就是检查你的备份是否失败的好的方法。

use msdb

select ‘FAILED’ as Status, cast(sj.name as varchar(100)) as “Job Name”,
cast(sjs.step_id as varchar(5)) as “Step ID”,
cast(sjs.step_name as varchar(30)) as “Step Name”,
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),’.’,’-’)+’ ‘+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),1,2)+’:’+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),3,2)+’:’+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) ‘Start Date Time’,sjh.message as “Message”
from sysjobs sj
join sysjobsteps sjs
on sj.job_id = sjs.job_id
join sysjobhistory sjh
on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id
where sjh.run_status <> 1
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 –yesterday at 7am
union
select ‘FAILED’,cast(sj.name as varchar(100)) as “Job Name”,
‘MAIN’ as “Step ID”,
‘MAIN’ as “Step Name”,
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),’.’,’-’)+’ ‘+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),1,2)+’:’+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),3,2)+’:’+SUBSTRING(RIGHT(’000000′+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) ‘Start Date Time’,sjh.message as “Message”
from sysjobs sj
join sysjobhistory sjh
on sj.job_id = sjh.job_id
where sjh.run_status <> 1 and sjh.step_id=0
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 –yesterday at 7am

第三项：你有你的所有的SQL Server数据库的最近的备份吗？

下面两个查询将列出任何数据库，要么没有任何备份或在过去24小时尚未备份。第一个查询检查您的完整备份，第二个查询检查事务日志备份(只在完整恢复模式的数据库)。

SELECT d.name AS “Database”,
ISNULL(CONVERT(VARCHAR,b.backupdate,120),’NEVER’) AS “Last Full Backup”
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate
FROM backupset
WHERE type LIKE ‘D’
GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
SELECT d.name AS “Database”,
ISNULL(CONVERT(VARCHAR,b.backupdate,120),’NEVER’) AS “Last Log Backup”
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate
FROM backupset
WHERE type LIKE ‘L’
GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
AND (backupdate IS NULL OR backupdate < getdate()-1)

第四项：你的SQL Server Error Log有什么错误吗？

为了检查SQL Server Error Log我们将使用未经证实的扩展存储过程xp_readerorlog，这个查询会检查当前的日志至最多两天前的这段时间的任何错误。

declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();

– Create the temporary table

CREATE TABLE #ErrorLog
(
logdate datetime,
processinfo varchar(255),
Message varchar(500))

– Populate the temporary table

INSERT #ErrorLog
(logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N’desc’;

– Filter the temporary table

SELECT LogDate, Message FROM #ErrorLog
WHERE (Message LIKE ‘%error%’ OR Message LIKE ‘%failed%’) AND processinfo NOT LIKE ’logon’
ORDER BY logdate DESC

– Drop the temporary table

DROP TABLE #ErrorLog

第五项：你的SQL Server耗尽了磁盘空间了吗？

您可以使用扩展存储过程xp_fixeddrives看一眼你的硬盘上留下的空间。.

exec master.dbo.xp_fixeddrives

第六项：你正在运行的SQL Server服务器内存低吗？

在你的服务器检查内存我们可以使用动态管理视图dm_os_sys_memory.

SELECT available_physical_memory_kb/1024 as “Total Memory MB”,
available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS “% Memory Free”
FROM sys.dm_os_sys_memory

第七项：在缓存中有需要调优SQL Server语句吗？

下列查询将会为我们识别任何性能差的SQL Server语句，你可以根据你的需要修改Order by排序项（IO CPU 和Elapsed Time）.

SELECT top 10 text as “SQL Statement”,
last_execution_time as “Last Execution Time”,
(total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO],
(total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)],
(total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)],
execution_count as “Execution Count”,
qp.query_plan as “Query Plan”
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
order by total_elapsed_time/execution_count desc

第八项：你有多少个连接连接到了你的SQL Server实例？

SELECT spid, kpid, blocked, d.name, open_tran, status, hostname,cmd, login_time, loginame, net_library
FROM sys.sysprocesses p INNER JOIN sys.databases d on p.dbid=d.database_id

第九项：你的SQL Server正在处理多少个请求？

正如前面的查询中，检查您的SQL Server的请求数量不会告诉你太多。如果你在正常运行期间捕获这个数字，稍后你可以使用它作为一个进行比较的基线。一般来说大约1000/秒是一个忙碌的SQL Server，但这个数字很大程度上取决于所正在使用的硬件。另外100/秒也许确实超出了他们的实例处理能力。此外，使用下面的查询模板，您可以查询sql server相关的其他操作系统的性能计数器。

DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE ‘Batch Requests/sec%’

WAITFOR DELAY ’000:00:10′

SELECT (cntr_value-@BRPS)/10.0 AS “Batch Requests/sec”
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE ‘Batch Requests/sec%’