查看连接数
select * from master.dbo.sysprocesses
查看前50最耗CPU的SQL语法
SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
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)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg CPU Time] DESC
查找目前SQL Server所执行的SQL语法，并展示资源情况：
SELECT s2.dbid ,
DB_NAME(s2.dbid) AS [数据库名] ,
--s1.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