SQLServer 实用语句之慢、触发器、锁

查询过去执行的查询慢

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

查询当前正在执行的语句慢

SELECT     req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

触发器

drop trigger companyDelete_trigger; --删除触发器
select name from sysobjects where xtype='TR'; --所有触发器

create trigger companyDelete_trigger on T_ADMIN for update
as if update (FCOMPANYID)
begin
declare @cou int
select @cou=count(*) from T_ADMIN where FCOMPANYID is null;
if (@cou>0) RAISERROR('所属公司不能为空', 16, 1)
end ; -- 添加触发器

CREATE TRIGGER TRG_Delete
ON T_Emp AFTER DELETE
AS
    BEGIN
         RAISERROR('数据不能删', 16, 1)
    END ;
    
CREATE TRIGGER TRG_AAA ON t_Relation AFTER update
AS
    BEGIN
    declare @fid varchar(44);
         select @fid=(select fid from inserted)
    if('RIfIPiwwTOami8mzo3VSyikoiSk=' = @fid)  RAISERROR(' FID: %s For Update ', 16, 1,@fid)
    END ;

查询阻塞的进程

SELECT   [session_id],
  [blocking_session_id] AS '正在阻塞其他会话的会话ID',
  DB_NAME([database_id]) AS '数据库名称',
  [request_id],  [cpu_time],
  [start_time] AS '开始时间',
  [status] AS '状态',
  [command] AS '命令',
  dest.[text] AS 'sql语句',
  [reads] AS '物理读次数',
  [writes] AS '写次数',
  [logical_reads] AS '逻辑读次数',
  [row_count] AS '返回结果行数',
  [wait_type] AS '等待资源类型',
  [wait_time] AS '等待时间',
  [wait_resource] AS '等待的资源'
  FROM sys.[dm_exec_requests] AS der  CROSS APPLY
  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest

查询被锁的表

select request_session_id 锁表进程,OBJECT_NAME(resource_associated_entity_id) 被锁表名 
from sys.dm_tran_locks where resource_type='OBJECT'