查找目前SQL Server所执行的SQL语法,并展示资源情况:
SELECT
s2.dbid,
DB_NAME ( s2.dbid ) AS [数据库名],
--s 1.sql_handle,
(
SELECT
TOP 1 SUBSTRING (
s2.text,
statement_start_offset / 2 + 1,
(
(
CASE
WHEN statement_end_offset = - 1 THEN
( LEN ( CONVERT ( NVARCHAR ( MAX ), s2.text ) ) * 2 ) ELSE statement_end_offset
END
) - statement_start_offset
) / 2 + 1
)
) AS [语句],
execution_count AS [执行次数],
last_execution_time AS [上次开始执行计划的时间],
total_worker_time AS [自编译以来执行所用的 CPU 时间总量(微秒) ],
last_worker_time AS [上次执行计划所用的 CPU 时间(微秒) ],
min_worker_time AS [单次执行期间曾占用的最小 CPU 时间(微秒) ],
max_worker_time AS [单次执行期间曾占用的最大 CPU 时间(微秒) ],
total_logical_reads AS [总逻辑读],
last_logical_reads AS [上次逻辑读],
min_logical_reads AS [最少逻辑读],
max_logical_reads AS [最大逻辑读],
total_logical_writes AS [总逻辑写],
last_logical_writes AS [上次逻辑写],
min_logical_writes AS [最小逻辑写],
max_logical_writes AS [最大逻辑写]
FROM
sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text ( sql_handle ) AS s2
WHERE
s2.objectid IS NULL
ORDER BY
last_worker_time DESC