微软SCOM管理中最有用的SQL查询(一)

Microsoft System Center Operations Manager (SCOM)是微软推出的专业系统监控软件，可以监控部署在网络中的服务器、应用系统和客户端，提供图形化视图，使管理员可以监控目标计算机存在的故障和产生的警告。对后台的SQL Server数据库详细结构的了解，和监控产生日志的分析有利于日常运维工作的效率提升，今天给大家介绍一些后台查询报警记录，及磁盘空间，表大小分析的常用查询T-SQL.

在使用过程中由于监控服务器数量及监控项的增加经常会引起背后的数据库和数据仓库过大，这是在管理中最常遇到的问题， 以下SQL可以精确到表占用空间的大小。

SELECT TOP 1000 a2.name AS ‘Tablename’, CAST((a1.reserved + ISNULL(a4.reserved,0))* 8/1024.0 AS DECIMAL(10, 0)) AS ‘TotalSpace(MB)’, CAST(a1.data * 8/1024.0 AS DECIMAL(10, 0)) AS ‘DataSize(MB)’, CAST((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8/1024.0 AS DECIMAL(10, 0)) AS ‘IndexSize(MB)’, CAST((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8/1024.0 AS DECIMAL(10, 0)) AS ‘Unused(MB)’, a1.rows as ‘RowCount’, (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1, a3.name AS ‘Schema’ FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) --END OF QUERY

查询整个数据库和数据仓库大小。

–Database Size and used space.

SELECT convert(decimal(12,0),round(sf.size/128.000,2)) AS ‘FileSize(MB)’, convert(decimal(12,0),round(fileproperty(sf.name,‘SpaceUsed’)/128.000,2)) AS ‘SpaceUsed(MB)’, convert(decimal(12,0),round((sf.size-fileproperty(sf.name,‘SpaceUsed’))/128.000,2)) AS ‘FreeSpace(MB)’, CASE smf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),smf.growth) +’ %’ ELSE convert(VARCHAR(10),smf.growth/128) +’ MB’ END AS ‘AutoGrow’, convert(decimal(12,0),round(sf.maxsize/128.000,2)) AS ‘AutoGrowthMB(MAX)’, left(sf.NAME,15) AS ‘NAME’, left(sf.FILENAME,120) AS ‘PATH’, sf.FILEID from dbo.sysfiles sf JOIN sys.master_files smf on smf.physical_name = sf.filename

每日监控产生的报警数量和警报类型分析。

–每日报警数量统计

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay

FROM Alert WITH (NOLOCK)

WHERE TimeRaised is not NULL

GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)

ORDER BY DayAdded DESC

每日警报数量排前20的报警项目

SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName AS ‘AlertName’,

AlertStringDescription AS ‘Description’, Name, MonitoringRuleId

FROM Alertview WITH (NOLOCK)

WHERE TimeRaised is not NULL

GROUP BY AlertStringName, AlertStringDescription, Name, MonitoringRuleId

ORDER BY AlertCount DESC

警报次数最频繁的前20条项目

SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount,AlertStringName as ‘AlertName’,

AlertStringDescription as ‘Description’,Name,MonitoringRuleId

FROM Alertview WITH (NOLOCK)

WHERE Timeraised is not NULL

GROUP BY AlertStringName, AlertStringDescription, Name, MonitoringRuleId

ORDER BY RepeatCount DESC

引起报警项目最多的对象排名

SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount,MonitoringObjectPath AS ‘Path’

FROM Alertview WITH (NOLOCK)

WHERE Timeraised is not NULL

GROUP BY MonitoringObjectPath

ORDER BY RepeatCount DESC

每日自动解决的报警项目查询

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1)

THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS [Date],

CASE WHEN(GROUPING(ResolutionState) = 1)

THEN ‘All Resolution States’ ELSE CAST(ResolutionState AS VARCHAR(5))

END AS [ResolutionState], COUNT(*) AS NumAlerts

FROM Alert WITH (NOLOCK)

WHERE TimeRaised is not NULL

GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102), ResolutionState WITH ROLLUP

ORDER BY DATE DESC