SQL當(dāng)前的進(jìn)程信息，當(dāng)前的執(zhí)行語(yǔ)句
[導(dǎo)讀]bb--?List?expensive?queries?
?DECLARE?@MinExecutions?int;?
?SET?@MinExecutions?=?5?
??
?SELECT?EQS.t
bb
--?List?expensive?queries? ?DECLARE?@MinExecutions?int;? ?SET?@MinExecutions?=?5? ?? ?SELECT?EQS.total_worker_time?AS?TotalWorkerTime? ???????,EQS.total_logical_reads?+?EQS.total_logical_writes?AS?TotalLogicalIO? ???????,EQS.execution_count?As?ExeCnt? ???????,EQS.last_execution_time?AS?LastUsage? ???????,EQS.total_worker_time?/?EQS.execution_count?as?AvgCPUTimeMiS? ???????,(EQS.total_logical_reads?+?EQS.total_logical_writes)?/?EQS.execution_count?? ????????AS?AvgLogicalIO? ???????,DB.name?AS?DatabaseName? ???????,SUBSTRING(EST.text? ?????????????????,1?+?EQS.statement_start_offset?/?2? ?????????????????,(CASE?WHEN?EQS.statement_end_offset?=?-1?? ????????????????????????THEN?LEN(convert(nvarchar(max),?EST.text))?*?2?? ????????????????????????ELSE?EQS.statement_end_offset?END?? ??????????????????-?EQS.statement_start_offset)?/?2? ?????????????????)?AS?SqlStatement? ???????--?Optional?with?Query?plan;?remove?comment?to?show,?but?then?the?query?takes?!!much?longer?time!!? ???????--,EQP.[query_plan]?AS?[QueryPlan]? ?FROM?sys.dm_exec_query_stats?AS?EQS? ??????CROSS?APPLY?sys.dm_exec_sql_text(EQS.sql_handle)?AS?EST? ??????CROSS?APPLY?sys.dm_exec_query_plan(EQS.plan_handle)?AS?EQP? ??????LEFT?JOIN?sys.databases?AS?DB? ??????????ON?EST.dbid?=?DB.database_id?????? ?WHERE?EQS.execution_count?>?@MinExecutions? ???????AND?EQS.last_execution_time?>?DATEDIFF(MONTH,?-1,?GETDATE())? ?ORDER?BY?AvgLogicalIo?DESC? ?????????,AvgCPUTimeMiS?DESC
a
下載文檔
