sqlserver的日常检查脚本归结

核心提示： ltrim(wait_time_ms) as wait_time_ms, ltrim(max_wait_time_ms) as max_wait_time_ms, ltrim(signal_wait_time_ms) as signal_wait_time_msfrom sys.dm_os_wait_stats

 ltrim(wait_time_ms) as  wait_time_ms, 　

 ltrim(max_wait_time_ms) as  max_wait_time_ms,

 ltrim(signal_wait_time_ms) as signal_wait_time_ms

from sys.dm_os_wait_stats

--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')

order by wait_time_ms desc

go

print '                             '

print '                             '

print '-------------------------------------------------------'

print '19. 查看各个数据库性能负载'

print '-------------------------------------------------------'

SELECT

substring (a.name,0,12) as [数据库名],

[连接数] = (SELECT COUNT(*)

FROM master..sysprocesses b

WHERE

a.dbid = b.dbid),

[阻塞进程] = (SELECT COUNT(*)

FROM master..sysprocesses b

WHERE

a.dbid = b.dbid AND

blocked <> 0),

[总内存] = ISNULL((SELECT SUM(memusage)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid),0),

[总IO] = ISNULL((SELECT SUM(physical_io)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid),0),

[总CPU] = ISNULL((SELECT SUM(cpu)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid),0),

[总等待时间] = ISNULL((SELECT SUM(waittime)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid),0),

[SELECTs] = (SELECT COUNT(*) 

FROM master..sysprocesses b

WHERE 

a.dbid = b.dbid AND

b.cmd LIKE '%SELECT%'),

[DELETEs] = (SELECT COUNT(*) 

FROM master..sysprocesses b

WHERE 

a.dbid = b.dbid AND

b.cmd LIKE '%DELETE%'),

[DBCC Commands] = ISNULL((SELECT COUNT(*)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid and

b.cmd like '%DBCC%'),0),

[BCP Running] = ISNULL((SELECT COUNT(*)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid and

b.cmd like '%BCP%'),0),

[Backups Running] = ISNULL((SELECT COUNT(*)

FROM

master..sysprocesses b

WHERE

a.dbid = b.dbid and

b.cmd LIKE '%BACKUP%'),0)

FROM master.dbo.sysdatabases a WITH (nolock)

WHERE 

DatabasePropertyEx(a.name,'Status') = 'ONLINE'

ORDER BY [数据库名]

go

print '                             '