SQLServer负载用查询（查询连接数、CPU占用、锁表、等待worker）

同学！认真听

于 2024-06-20 20:15:00 发布

阅读量213

点赞数 3

分类专栏： SQL server 文章标签： sqlserver 数据库

本文链接：https://blog.csdn.net/qq_40690077/article/details/139833895

版权

SQL server 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

查询线程是否锁表：

SELECT
	request_session_id spid,
	OBJECT_NAME( resource_associated_entity_id ) DtableName 
FROM
	sys.dm_tran_locks 
WHERE
	resource_type = 'OBJECT'

查询各主机占用数据库的连接数

SELECT hostname 主机名, COUNT ( * ) 连接数量 FROM master.dbo.sysprocesses 
GROUP BY hostname 
ORDER BY COUNT ( * ) DESC

选取前10个最耗CPU时间的会话

SELECT TOP 10 [session_id], [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_time] 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 AND DB_NAME( der.[database_id] ) = 'gposdb' 
ORDER BY [cpu_time] DESC

查询CPU占用高的语句

SELECT TOP
	10 total_worker_time / execution_count AS avg_cpu_cost,
	plan_handle,
	execution_count,
	(
		SELECT SUBSTRING
			( 
				text, statement_start_offset / 2 + 1,
				(CASE WHEN statement_end_offset = - 1 THEN LEN( CONVERT ( nvarchar ( MAX ), text )) * 2 ELSE statement_end_offset END - statement_start_offset )/2) 
				FROM sys.dm_exec_sql_text ( sql_handle )
	) AS query_text 
FROM
	sys.dm_exec_query_stats 
ORDER BY
	[avg_cpu_cost] DESC

查询会话中有多少个worker在等待

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