sqlserver的日常检查脚本归结

核心提示：ORDER BY user_updates DESC;'-- Select records.SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC-- Tidy up.DROP TABLE #TempUnusedIndexespr

ORDER BY user_updates DESC

;

'

-- Select records.

SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

-- Tidy up.

DROP TABLE #TempUnusedIndexes

print '                             '

print '                             '

print '                             '

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

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

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

print '*********************************'

SELECT  TOP 10 

        [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 '----------------------------'

print '5.查询数据库IO                 '

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

print '*********************************'

go

WITH IOFORDATABASE AS

(

SELECT

 DB_NAME(VFS.database_id) AS DatabaseName

,CASE WHEN smf.type = 1 THEN 'LOG_FILE' ELSE 'DATA_FILE' END AS DatabaseFile_Type

,SUM(VFS.num_of_bytes_written) AS IO_Write

,SUM(VFS.num_of_bytes_read) AS IO_Read

,SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) AS Total_IO

,SUM(VFS.io_stall) AS IO_STALL

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS

JOIN sys.master_files AS smf

  ON VFS.database_id = smf.database_id

 AND VFS.file_id = smf.file_id