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

查询线程是否锁表:

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
  • 3
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值