1.获取有关按平均CPU 时间排在最前面的五个查询的信息
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.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 total_worker_time/execution_count DESC;
1.获取有关按平均CPU 时间排在最前面的五个查询的信息
2.返回按批执行的SQL 查询的文本，并提供有关它们的统计信息。
SELECT top 20
s2.dbid,
(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 sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
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 (total_worker_time/execution_count) desc,execution_count desc;
2.返回按批执行的SQL 查询的文本，并提供有关它们的统计信息
3.为变更数据捕获日志扫描会话中遇到的每个错误返回一行
select * from sys.dm_cdc_errors
为变更数据捕获日志扫描会话中遇到的每个错误返回一行
4.返回有关在服务器上打开时间超过指定时间（小时）的游标的信息
SELECT creation_time, cursor_id, name, c.session_id, login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
4.返回有关在服务器上打开时间超过指定时间（小时）的游标的信息
5.查找连接到服务器的用户
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
查找连接到服务器的用户
6.查找长时间运行的游标
USE master;
GO
SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;
6.查找长时间运行的游标
7.查找具有已打开事务的空闲会话
SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);
查找具有已打开事务的空闲会话
8.返回前五个查询的SQL 语句文本和平均CPU 时间
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.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 total_worker_time/execution_count DESC;
8.返回前五个查询的SQL 语句文本和平均CPU 时间
9.显示锁信息
SELECT spid,
blocked,
waitresource,
lastwaittype,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1
THEN DATALENGTH(A.text)
ELSE sp.stmt_end END - sp.stmt_start) / 2
) AS [current_cmd],
DB_NAME(sp.dbid) AS DBName,
program_name,
sp.loginame,
sp.hostname
FROM
(
select *
from sys.sysprocesses b
where exists (select 1
from sys.sysprocesses s
where b.spid = s.blocked
and s.blocked > 0)
union
select *
from sys.sysprocesses s
where s.blocked > 0
) AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];
9.1查询锁与Sql语句
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
--WHERE resource_database_id = 6
9.2显示锁信息
select request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type='OBJECT'
;
9.3查询被锁对象
declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
9.4解锁：
10.显示阻塞信息
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
10.显示阻塞信息
11.查询SQL与进程
SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];
查询SQL与进程
12.清理缓存与查询执行计划
dbcc freeproccache
select * from sys.dm_exec_query_stats
select * from sys.dm_exec_cached_plans
3.清理缓存与查询执行计划
DBCC用来清空执行计划缓存，后面两个查询语句主要用来确定执行计划缓存是不是清空