获取SQLSERVER中产生锁的SQL语句SELECTSUBSTRING(st.text,(qs
获取SQLSERVER中产生锁的SQL语句
SELECT SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_textFROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st where qs.sql_handle in (select distinct sql_handle from sys.dm_exec_requests where session_id in (SELECT request_session_id as SpidFROM sys.dm_tran_locks lLEFT JOIN sys.partitions pON l.resource_associated_entity_id = p.hobt_idLEFT JOIN sys.indexes iON p.object_id = i.object_idAND p.index_id = i.index_idLEFT JOIN sys.objects oON p.object_id = o.object_idLEFT JOIN sys.schemas sON o.schema_id = s.schema_idLEFT JOIN sys.objects o2ON l.resource_associated_entity_id = o2.object_idLEFT JOIN sys.schemas s2ON o2.schema_id = s2.schema_idLEFT JOIN sys.databases dbON l.resource_database_id = db.database_idWHERE resource_database_id = DB_ID() and request_mode in ('X')))
查询 某个时间点的，所有执行中的sql语句的CPU占用时间（倒排序）
use master;SELECT [session_id], [cpu_time], [start_time], dest.[text] AS 'sql', DB_NAME([database_id]) AS 'dbname', [row_count] 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
查询 不同sql语句产生阻塞的情况（主要是由于锁产生的等待）
use master;SELECT top 50 dest.[text] AS 'sql', wtt.blocking_session_id, wtt.wait_duration_ms, wtt.session_id FROM sys.dm_os_waiting_tasks wtt LEFT JOIN sys.dm_exec_requests req ON wtt.blocking_session_id = req.session_id CROSS APPLY sys.[dm_exec_sql_text](req.[sql_handle]) AS dest where wtt.blocking_session_id is not null and wtt.wait_duration_ms>2000 order by wait_duration_ms desc
到此这篇关于Sql Server中通过sql命令获取cpu占用及产生锁的sql的文章就介绍到这了,更多相关cpu占用及产生锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
标签: sql Sql nbsp Server 通过 命令 获取 cpu 占用
声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！
