SQLServer常用语句

查询当前数据库上执行的SQL语句

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



SELECT *
FROM   sys.[sysprocesses] AS syspro
       CROSS apply sys.Dm_exec_sql_text(syspro.sql_handle) AS t
WHERE  status != 'sleeping'
ORDER  BY physical_io DESC; 

 

--查看AlwaysON同步延迟时间
SELECT Datediff(s, last_redone_time, Getdate()) DelayTimeS
FROM   ( ( sys.availability_groups AS ag
           JOIN sys.availability_replicas AS ar
             ON ag.group_id = ar.group_id )
         JOIN sys.dm_hadr_availability_replica_states AS ar_state
           ON ar.replica_id = ar_state.replica_id )
       JOIN sys.dm_hadr_database_replica_states dr_state
         ON ag.group_id = dr_state.group_id
            AND dr_state.replica_id = ar_state.replica_id

 

--如何查询最近执行的SQL的ISOLATION LEVEL：
SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'Read Uncommitted' 
WHEN 2 THEN 'Read Committed' 
WHEN 3 THEN 'Repeatable Read' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL ,session_id, login_time,login_name,logical_reads,[status]
,[text_size]
FROM sys.dm_exec_sessions 
where session_id = 'SPID'
order by login_time desc

 

--查询当前活动事务
select dbt.database_id,
       DB_NAME(dbt.database_id) '数据库名',
       dbt.transaction_id,
       at.name,
       at.transaction_begin_time,
       
       case at.transaction_type         --事务类型   
           when 1 then N'读/写事务'  
           when 2 then N'只读事务'  
           when 3 then N'系统事务'  
           when 4 then N'分布式事务'  
       end N'transaction类型',  
         
       case at.transaction_state  
           when 0 then N'事务尚未完全初始化'  
           when 1 then N'事务已初始化但尚未启动'  
           when 2 then N'事务处于活动状态'  
           when 3 then N'事务已结束。该状态用于只读事务'  
           when 4 then N'已对分布式事务启动提交进程'  
           when 5 then N'事务处于准备就绪状态且等待解析'  
           when 6 then N'事务已提交'  
           when 7 then N'事务正在被回滚'  
           when 8 then N'事务已回滚'  
       end  'transaction状态',
       
       st.session_id,  
       tt.text as '最近执行的语句',
       
       es.program_name
       
from sys.dm_tran_database_transactions dbt
left join sys.dm_tran_active_transactions at
        on dbt.transaction_id = at.transaction_id
left join sys.dm_tran_session_transactions st
        on at.transaction_id = st.transaction_id
 
left join sys.dm_exec_sessions es
       on st.session_id = es.session_id
left join sys.dm_exec_connections ec
       on es.session_id = ec.session_id
outer apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) tt
 
where st.session_id >= 50
      and dbt.database_id <> 32767

 查询历史执行JOB

SELECT top 50 j.name AS 'JobName' ,
          run_date ,
          run_time,
          run_duration
  FROM    msdb.dbo.sysjobs j
          INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
  WHERE   j.enabled = 1  --Only Enabled Jobs
        and j.name='JOB名称'
        AND H.run_date='20211130'
  ORDER BY 
          run_date desc ,
          run_time DESC

 查询表大小

SELECT
    OBJECT_NAME(object_id) AS TableName,
    SUM(row_count) AS RowCountaa,
    SUM(reserved_page_count) * 8 AS TotalSpaceKB,
    SUM(used_page_count) * 8 AS UsedSpaceKB,
    SUM(reserved_page_count - used_page_count) * 8 AS UnusedSpaceKB
FROM
    sys.dm_db_partition_stats
GROUP BY
    object_id

ORDER BY SUM(reserved_page_count) desc