sqlserver的日常检查脚本归结

核心提示：GROUP BY DB_NAME(VFS.database_id),smf.type)SELECT ltrim(ROW_NUMBER() OVER(ORDER BY io_stall DESC)) AS RowNumber,substring(DatabaseName,1,30) as DatabaseName,D

GROUP BY 

 DB_NAME(VFS.database_id)

,smf.type

)

SELECT 

 ltrim(ROW_NUMBER() OVER(ORDER BY io_stall DESC)) AS RowNumber

,substring(DatabaseName,1,30) as DatabaseName

,DatabaseFile_Type

,ltrim(CAST(1.0 * IO_Read/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Read_MB

,ltrim(CAST(1.0 * IO_Write/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Write_MB

,ltrim(CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12, 2))) AS IO_TOTAL_MB

,ltrim(CAST(IO_STALL / 1000. AS DECIMAL(12, 2))) AS IO_STALL_Seconds

,ltrim(CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10, 2))) AS IO_STALL_Pct

FROM IOFORDATABASE

ORDER BY IO_STALL_Seconds DESC;

go

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

print '                             '

print '                             '

print '                             '

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

print '6.查看数据库是否有死锁         '

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

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

use master

go

select ltrim(request_session_id) "会话ID",

substring(resource_type,1,30) "被锁定的资源",

resource_database_id "数据库",

object_name(resource_associated_entity_id) "对象",

request_mode "资源模式",

request_status  "锁状态"

from sys.dm_tran_locks

go

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

print '                             '

print '                             '

print '                             '

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

print '7.查看性能统计信息             '

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

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

use master

go

dbcc freeproccache

go

select t.text as "执行的文本", st.total_logical_reads  as "逻辑读取总次数",

st.total_physical_reads  as "物理读取总次数",

st.total_elapsed_time/1000000 as "占用的总时间",

st.total_logical_writes  as "逻辑写入总次数"

from sys.dm_exec_query_stats st

cross apply sys.dm_exec_sql_text(st.sql_handle) t