查詢目前正在執行的SQL語法
SELECT TOP 200 qs.creation_time,last_execution_time-creation_time as time,text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
SELECT r.scheduler_id as 排程器識別碼, status as 要求的狀態, r.session_id as SPID, r.blocking_session_id as BlkBy, substring( ltrim(q.text), r.statement_start_offset/2+1, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) AS [正在執行的 T-SQL 命令], r.cpu_time as [CPU Time(ms)], r.start_time as [開始時間], r.total_elapsed_time as [執行總時間], r.reads as [讀取數], r.writes as [寫入數], r.logical_reads as [邏輯讀取數], -- q.text, /* 完整的 T-SQL 指令碼 */ d.name as [資料庫名稱] FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q LEFT JOIN sys.databases d ON (r.database_id=d.database_id) WHERE r.session_id > 50 AND r.session_id <> @@SPID ORDER BY r.total_elapsed_time desc
SELECT A.SESSION_ID,DB_NAME(A.DATABASE_ID) AS DATABASE_NAME, A.START_TIME,A.COMMAND,A.CPU_TIME, A.PERCENT_COMPLETE,A.ESTIMATED_COMPLETION_TIME,B.TEXT FROM SYS.DM_EXEC_REQUESTS A CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B WHERE SESSION_ID > 50
看哪個語法最花時間
SELECT TOP 10 total_worker_time, last_worker_time, max_worker_time, min_worker_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY max_worker_time DESC
SELECT TOP 50 [Average CPU used] = total_worker_time / qs.execution_count, [Total CPU used] = total_worker_time, [Execution count] = qs.execution_count, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Average CPU used] DESC;
SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writes ,last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handle ORDER BY total_wait_time DESCWITH DB_CPU_Stats AS (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID) SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num], DatabaseName, [CPU_Time_Ms], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent] FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databases AND DatabaseID <> 32767 -- ResourceDB ORDER BY row_num OPTION (RECOMPILE);