博客 SQL Server性能优化初探:聚焦阻塞SQL,快速定位与解决之道

SQL Server性能优化初探:聚焦阻塞SQL,快速定位与解决之道

   数栈君   发表于 2024-01-31 10:37  170  0

引言

我们在运维数据库的时候,经常会面对这样的问题:

  • 数据库现在运行得怎么样

  • 有哪些会话在执行,当前状态是什么,在执行什么SQL

  • 哪些会话被阻塞,阻塞原因是什么

  • 会话是从哪些IP连接过来的,使用了什么账号

通过这篇文章介绍的方法,我们可以快速找到SQL Server中正在运行的SQL,以及被阻塞的会话。

连接和请求

我们可以通过SQL Server提供的一系列DMV来解答上面这些问题。

要用好这些DMV,需要我们对SQL Server的基本概念和运行机制有一些基本的了解。


客户端执行一个SQL,大致可分为几个步骤:

1、建立连接

2、将请求(SQL)发送到服务端

服务端收到客户端发送的请求后,需要:

1、解析和优化SQL

2、请任务(Task)分配到Worker执行

3、将结果返回给客户端

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/bc336c2e5f60afb2fa7bf55de9b0118e..png

下面的表格介绍了一个请求执行过程中,涉及到的核心的概念和相关的DMV:

概念

描述

相关DMV

连接

(Connection)

客户端到服务端的物理通道。

一般是TCP连接。

sys.dm_exec_connections

  • 通过session_id或most_recent_session_id关联 sys.dm_exec_session和sys.dm_exec_request表。

  • 通过most_recent_sql_handleg关联sys.dm_exec_sql_text,获取sql文本

会话

(Session)

用户请求都会通过一个会话发送。

会话会绑定到一个连接上。

sys.dm_exec_sessions

  • 通过session_id关联sys.dm_exec_requests表

  • 通过session_id关联sys.dm_exec_connections表的session_id或most_recent_session_id

请求

(Request)

一个请求可以简单理解为发送给服务器的任务(一个SQL语句、一个Batch)。

SQL Server接收到用户请求后,会生成对应的Task,分配给worker执行。

sys.dm_exec_requests

当前系统中运行的requests。

请求执行完成后,从该dmv中就查不到相关信息了。

  • 通过sql_handle关联sys.dm_exec_sql_text,结合statement_start_offset,statement_end_offset获取请求的SQL语句

  • 通过plan_handle关联sys.dm_exec_text_query_plan获取执行计划

  • 通过task_address关联sys.dm_os_tasks,sys.dm_os_waiting_tasks获取task相关信息

  • 通过session_id关联sys.dm_exec_sessions、sys.dm_exec_connections表,获取发起任务的会话和连接信息

  • 通过blocking_session_id关联

sys.dm_exec_sessions获取阻塞当前会话的session。如果引起阻塞的SQL还在执行,可以关联request、task表获取SQL语句。如果引起引起阻塞的SQL已经执行完成,则只能关联到connection表,获取会话最后执行的SQL。

任务

(task)

用户发起的请求,服务端以task的方式运行。task会分配到某个worker执行,在task执行完成之前,worker不会执行其他task。

并行查询中,一个父task会产生多个子task。

task状态:

  • PENDING: Waiting for a worker thread.

  • RUNNABLE: Runnable, but waiting to receive a quantum.

  • RUNNING: Currently running on the scheduler.

  • SUSPENDED: Has a worker, but is waiting for an event.

  • DONE: Completed.

  • SPINLOOP: Stuck in a spinlock.

sys.dm_os_tasks,

  • 通过task_address字段关联sys.dm_exec_requests,获取请求相关信息(如SQL语句)

  • 通过session_id关联sys.dm_exec_sessions、sys.dm_exec_connections表,获取发起任务的会话和连接信息

  • 通过parent_task_address关联sys.dm_os_tasks表,获取父task(如果非空)

  • 通过worker_address关联sys.dm_os_workers表,获取worker信息

sys.dm_os_waiting_tasks

  • blocking_session_id:阻塞会话ID

  • blocking_task_address: 阻塞task地址(若存在)

  • wait_duration_ms

  • wait_type

  • resource_address

Worker

worker线程(thread)或fiber,用于执行task

sys.dm_os_workers

  • tasks_processed_count:处理的任务数

  • pending_io_count:完成的io请求数

  • task_address: 关联sys.dm_os_tasks表

  • last_wait_type

Scheduler

woker会分配到某个scheduler执行。scheduler相当于逻辑CPU。

sys.dm_os_schedulers

  • load_factor: scheduler负载情况。

  • current_tasks_count:当前任务数

  • runnable_tasks_count:等待CPU的任务数

  • current_workers_count:当前worker数

  • active_workers_count:活动worker数

  • pending_disk_io_count:待完成的IO请求数

会话相关查询

查询所有会话连接和执行的SQL

通过如下SQL可以查询SQL Server实例当前正在运行的SQL


SQL
select 
a.client_net_address, a.client_tcp_port, a.local_net_address, a.local_tcp_port,
b.login_time, b.login_name, b.host_name, b.program_name, b.status,
b.total_scheduled_time, b.total_elapsed_time, b.reads, b.writes, b.logical_reads,
substring(d.text, c.statement_start_offset/2 + 1,
((case when c.statement_end_offset = -1 then len(d.text) * 2
else c.statement_end_offset end) - c.statement_start_offset)/2 + 1) as current_sql,
d.text as full_sql
from sys.dm_exec_connections a, sys.dm_exec_sessions b, sys.dm_exec_requests c cross apply sys.dm_exec_sql_text(c.sql_handle) d
where a.session_id = b.session_id
and b.session_id = c.session_id
and b.session_id > 50;

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/7019e5c04892cd88bedf3ab8c89fbc01..png

查询返回的主要信息:

  • client_net_address: 客户端IP

  • status

  • login_name: 登录名

  • current_sql: 当前执行的SQL

  • full_sql: 完整的sql(存储过程或batch的完整sql)


查询阻塞的会话信息


通过如下SQL可以查询当前被阻塞的会话、SQL


SQL
SELECT
Blocking.session_id as BlockingSessionId,
Sess.login_name AS BlockingUser,
BlockingSQL.text AS BlockingSQL,
Waits.wait_type WhyBlocked,
Waits.wait_duration_ms,
Blocked.session_id AS BlockedSessionId,
USER_NAME(Blocked.user_id) AS BlockedUser,
BlockedSQL.text AS BlockedSQL,
DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/3275a61a678c544f1ddee27c54a2ad9e..png

返回的信息:

  • BlockingSessionId

  • BlockingSQL:blocking sql并不一定是真正引起阻塞的SQL,而是阻塞session执行的最后一个SQL。

  • WhyBlocked:阻塞原因

  • wait_duration_ms: 阻塞时间(毫秒)

  • BlockedSessionId:被阻塞的session id

  • Blocked SQL:被阻塞的session执行的SQL


《数据治理行业实践白皮书》下载地址:https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址:https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs

同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack  
0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群