SQL Server DBA日常查询视图_数据库性能视图

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;

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;

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;

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;

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;

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];

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

select   request_session_id   spid,
    OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks 
where resource_type='OBJECT'
;

declare @spid  int 
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

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;

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];

12.清理缓存与查询执行计划

dbcc freeproccache
select * from sys.dm_exec_query_stats
select * from sys.dm_exec_cached_plans

DBCC用来清空执行计划缓存，后面两个查询语句主要用来确定执行计划缓存是不是清空