[導(dǎo)讀]Select?
?????'進(jìn)程ID'??????????????=?Str(?A.Spid,?4?),
?????'進(jìn)程ID狀態(tài)'??????????=?Convert(?varChar(100),
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]
下載文檔
