Select?
?????'進(jìn)程ID'??????????????=?Str(?A.Spid,?4?),
?????'進(jìn)程ID狀態(tài)'??????????=?Convert(?varChar(100),?A.Status?),
?????'阻塞進(jìn)程的進(jìn)程ID'????=?Str(?A.Blocked,?2?),
?????'工作站名稱'??????????=?Convert(?varChar(100),?A.Hostname?),
?????'執(zhí)行命令的用戶'??????=?Convert(?varChar(100),?A.loginame?),
?????'數(shù)據(jù)庫名'????????????=?Convert(?varChar(100),?Db_Name(A.Dbid?)?),???????
?????'應(yīng)用程序名'??????????=?Convert(?varChar(100),?A.Program_Name?),
?????'正在執(zhí)行的命令'??????=?Convert(?varchar(100),?A.Cmd?),
?????'累計(jì)CPU時(shí)間'?????????=?Str(?A.Cpu,?7?),
?????'IO'??????????????????=?Str(?A.Physical_Io,?7?),
??????'登錄名'??????????????=?A.Loginame,
??????'登錄時(shí)間'???=?A.login_time,
?????'執(zhí)行語句'=B.Text
?????From?Master..Sysprocesses?A
?????Cross?Apply?Sys.Dm_Exec_Sql_Text(A.Sql_Handle)?B
?????Where?Spid?In?(?Select?top?10?spid?From?Master..Sysprocesses?order?by?physical_io?DESC)
????order?by?login_time

--有查出來幾個(gè)表是沒有主鍵，也沒有聚集索引。
SELECT??OBJECT_NAME(i.[object_id])?AS?[Table?Name]?,
????????i.name
FROM????sys.indexes?AS?i
????????INNER?JOIN?sys.objects?AS?o?ON?i.[object_id]?=?o.[object_id]
WHERE???i.index_id?NOT?IN?(?SELECT??ddius.index_id
????????????????????????????FROM????sys.dm_db_index_usage_stats?AS?ddius
????????????????????????????WHERE???ddius.[object_id]?=?i.[object_id]
????????????????????????????????????AND?i.index_id?=?ddius.index_id
????????????????????????????????????AND?database_id?=?DB_ID()?)
????????AND?o.[type]?=?'U'
ORDER?BY?OBJECT_NAME(i.[object_id])?ASC;

--有查到缺失索引的情況。
SELECT??user_seeks?*?avg_total_user_cost?*?(?avg_user_impact?*?0.01?)?AS?[index_advantage]?,
????????dbmigs.last_user_seek?,
????????dbmid.[statement]?AS?[Database.Schema.Table]?,
????????dbmid.equality_columns?,
????????dbmid.inequality_columns?,
????????dbmid.included_columns?,
????????dbmigs.unique_compiles?,
????????dbmigs.user_seeks?,
????????dbmigs.avg_total_user_cost?,
????????dbmigs.avg_user_impact
FROM????sys.dm_db_missing_index_group_stats?AS?dbmigs?WITH?(?NOLOCK?)
????????INNER?JOIN?sys.dm_db_missing_index_groups?AS?dbmig?WITH?(?NOLOCK?)?ON?dbmigs.group_handle?=?dbmig.index_group_handle
????????INNER?JOIN?sys.dm_db_missing_index_details?AS?dbmid?WITH?(?NOLOCK?)?ON?dbmig.index_handle?=?dbmid.index_handle
WHERE???dbmid.[database_id]?=?DB_ID()
ORDER?BY?index_advantage?DESC;

--有查到幾個(gè)索引不合理，寫的很多，查詢用到的很少
SELECT??OBJECT_NAME(ddius.[object_id])?AS?[Table?Name]?,
????????i.name?AS?[Index?Name]?,
????????i.index_id?,
????????user_updates?AS?[Total?Writes]?,
????????user_seeks?+?user_scans?+?user_lookups?AS?[Total?Reads]?,
????????user_updates?-?(?user_seeks?+?user_scans?+?user_lookups?)?AS?[Difference]
FROM????sys.dm_db_index_usage_stats?AS?ddius?WITH?(?NOLOCK?)
????????INNER?JOIN?sys.indexes?AS?i?WITH?(?NOLOCK?)?ON?ddius.[object_id]?=?i.[object_id]
???????????????????????????????????????????????????????AND?i.index_id?=?ddius.index_id
WHERE???OBJECTPROPERTY(ddius.[object_id],?'IsUserTable')?=?1
????????AND?ddius.database_id?=?DB_ID()
????????AND?user_updates?>?(?user_seeks?+?user_scans?+?user_lookups?)
????????AND?i.index_id?>?1
ORDER?BY?[Difference]?DESC?,
????????[Total?Writes]?DESC?,
????????[Total?Reads]?ASC;

--有查到缺失索引的情況。
SELECT??'['?+?DB_NAME()?+?'].['?+?OBJECT_SCHEMA_NAME(ddips.[object_id],
?????????????????????????????????????????????????????DB_ID())?+?'].['
????????+?OBJECT_NAME(ddips.[object_id],?DB_ID())?+?']'?AS?[statement]?,
????????i.[name]?AS?[index_name]?,
????????ddips.[index_type_desc]?,
????????ddips.[partition_number]?,
????????ddips.[alloc_unit_type_desc]?,
????????ddips.[index_depth]?,
????????ddips.[index_level]?,
????????CAST(ddips.[avg_fragmentation_in_percent]?AS?SMALLINT)?AS?[avg_frag_%]?,
????????CAST(ddips.[avg_fragment_size_in_pages]?AS?SMALLINT)?AS?[avg_frag_size_in_pages]?,
????????ddips.[fragment_count]?,
????????ddips.[page_count]
FROM????sys.dm_db_index_physical_stats(DB_ID(),?NULL,?NULL,?NULL,?'limited')?ddips
????????INNER?JOIN?sys.[indexes]?i?ON?ddips.[object_id]?=?i.[object_id]
??????????????????????????????????????AND?ddips.[index_id]?=?i.[index_id]
WHERE???ddips.[avg_fragmentation_in_percent]?>?15
????????AND?ddips.[page_count]?>?500
ORDER?BY?ddips.[avg_fragmentation_in_percent]?,
????????OBJECT_NAME(ddips.[object_id],?DB_ID())?,
????????i.[name]