sqlserver的日常检查脚本归结

print '                             '

print '                             '

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

print '24. 所有数据库高开销的缺失索引'

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

SELECT  TOP 100 

        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 

        , avg_user_impact

        , TableName = statement

        , [EqualityUsage] = equality_columns 

        , [InequalityUsage] = inequality_columns

        , [Include Cloumns] = included_columns

FROM        sys.dm_db_missing_index_groups g 

INNER JOIN    sys.dm_db_missing_index_group_stats s 

       ON s.group_handle = g.index_group_handle 

INNER JOIN    sys.dm_db_missing_index_details d 

       ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC;

print '                             '

print '                             '

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

print '25. 查看buffer cache命中率'

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

 SELECT  

(CAST(SUM(CASE LTRIM(RTRIM(counter_name))    

WHEN 'Buffer cache hit ratio'    

THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) / 　

CAST(SUM(CASE LTRIM(RTRIM(counter_name))    

WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER)ELSE NULL END) AS FLOAT)) * 100 　

AS BufferCacheHitRatio 　

FROM sys.dm_os_performance_counters    

WHERE LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND    

 [counter_name] LIKE 'Buffer Cache Hit Ratio%'  

go

print '                             '

print '                             '

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

print '26. 查看job执行情况'

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