如何查询当前正在执行的查询，以及各查询的CPU占用率/Disk IO?

[CodeProject-Jerry]

MSSQL服务器 CPU突然增高到100%

sp_who sp_who2 sp_lock 看后没发现什么问题

请问有何良策可以查询 当前正在执行的查询，以及各查询的CPU占用率/Disk IO?

Thx

[noteasytoregister]

用跟踪(SQL SERVER PROFILER)。

[cjzm83]

--Identify which queries are the most costly by CPU
SELECT TOP 10 
		[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
--WHERE DB_NAME(qt.dbid) = 'pnl'  -- Filter on a given database.
ORDER BY [Average CPU used] DESC;



-- Total Reads by most expensive IO query
SELECT TOP 10 
        [Total Reads] = total_logical_reads
		,[Total Writes] = total_logical_writes
		,[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 [Total Reads] DESC;

-- Total Writes by most expensive IO query
SELECT TOP 10 
		[Total Writes] = total_logical_writes
        ,[Total Reads] = total_logical_reads
		,[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 [Total Writes] DESC;

[哥子谭]

用性能工具---->SQL Server Profier可以查看你的执行情况

[yinsuxia]

可以用SQL Server Profier查看你的SqlServer正在处理什么事情