sql server监控

sql server监控

http://www.mssqltips.com/sqlservertip/2522/sql-server-monitoring-checklist/

1.查看sql server 服务运行状态：

http://msdn.microsoft.com/zh-cn/library/ms189089%28v=sql.105%29.aspx

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'

 

2.查看sql server agent 工作执行情况：

use msdbgoselect '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 sjjoin sysjobsteps sjs  on sj.job_id = sjs.job_idjoin sysjobhistory sjh  on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_idwhere 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 7amunionselect '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 sjjoin sysjobhistory sjh  on sj.job_id = sjh.job_idwhere 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

 

3.查询数据库备份情况：

SELECT d.name AS "Database",       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"FROM sys.databases dLEFT 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_nameWHERE (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 dLEFT 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_nameWHERE recovery_model = 1  AND (backupdate IS NULL OR backupdate < getdate()-1)

4.查看sql server 错误日志情况：

declare @Time_Start datetime;declare @Time_End datetime;set @Time_Start=getdate()-2;set @Time_End=getdate();-- Create the temporary tableCREATE TABLE #ErrorLog (logdate datetime                      , processinfo varchar(255)                      , Message varchar(500))-- Populate the temporary tableINSERT #ErrorLog (logdate, processinfo, Message)   EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';-- Filter the temporary tableSELECT LogDate, Message FROM #ErrorLogWHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'ORDER BY logdate DESC-- Drop the temporary table DROP TABLE #ErrorLog

5.查看磁盘剩余空间：

--剩余空间exec master.dbo.xp_fixeddrives--总空间和剩余空间declare @svrName varchar(255)declare @sql varchar(400)--by default it will take the current server name, we can the set the server name as wellset @svrName = @@SERVERNAMEset @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'--creating a temporary tableCREATE TABLE #output(line varchar(255))--inserting disk name, total space and free space value in to temporary tableinsert #outputEXEC xp_cmdshell @sql--script to retrieve the values in MB from PS Script outputselect rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'from #outputwhere line like '[A-Z][:]%'order by drivename--script to retrieve the values in GB from PS Script outputselect rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'from #outputwhere line like '[A-Z][:]%'order by drivename--script to drop the temporary tabledrop table #output

6.sql server内存使用情况：

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

 

7.sql 执行消耗情况（IO vs. CPU vs. 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 qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qporder by total_elapsed_time/execution_count desc

8.数据库连接情况：

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

9.正在处理的请求数：

DECLARE @BRPS BIGINTSELECT @BRPS=cntr_value FROM sys.dm_os_performance_countersWHERE 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_countersWHERE counter_name LIKE 'Batch Requests/sec%'

 

9.