SQL Server性能优化初探：聚焦阻塞SQL，快速定位与解决之道 我们在运维数据库的时候，经常会面对这样的问题：
数据库现在运行得怎么样
有哪些会话在执行，当前状态是什么，在执行什么SQL
哪些会话被阻塞，阻塞原因是什么
会话是从哪些IP连接过来的，使用了什么账号
通过这篇文章介绍的方法，我们可以快速找到SQL Server中正在运行的SQL，以及被阻塞的会话。
我们可以通过SQL Server提供的一系列DMV来解答上面这些问题。
要用好这些DMV，需要我们对SQL Server的基本概念和运行机制有一些基本的了解。
客户端执行一个SQL，大致可分为几个步骤：
1、建立连接
2、将请求（SQL）发送到服务端
服务端收到客户端发送的请求后，需要：
1、解析和优化SQL
2、请任务(Task)分配到Worker执行
3、将结果返回给客户端
下面的表格介绍了一个请求执行过程中，涉及到的核心的概念和相关的DMV：
概念 | 描述 | 相关DMV |
连接 （Connection） | 客户端到服务端的物理通道。 一般是TCP连接。 | sys.dm_exec_connections
|
会话 （Session） | 用户请求都会通过一个会话发送。 会话会绑定到一个连接上。 | sys.dm_exec_sessions
|
请求 （Request） | 一个请求可以简单理解为发送给服务器的任务（一个SQL语句、一个Batch）。 SQL Server接收到用户请求后，会生成对应的Task，分配给worker执行。 | sys.dm_exec_requests 当前系统中运行的requests。 请求执行完成后，从该dmv中就查不到相关信息了。
sys.dm_exec_sessions获取阻塞当前会话的session。如果引起阻塞的SQL还在执行，可以关联request、task表获取SQL语句。如果引起引起阻塞的SQL已经执行完成，则只能关联到connection表，获取会话最后执行的SQL。 |
任务 （task） | 用户发起的请求，服务端以task的方式运行。task会分配到某个worker执行，在task执行完成之前，worker不会执行其他task。 并行查询中，一个父task会产生多个子task。 task状态：
| sys.dm_os_tasks,
sys.dm_os_waiting_tasks
|
Worker | worker线程(thread)或fiber，用于执行task | sys.dm_os_workers
|
Scheduler | woker会分配到某个scheduler执行。scheduler相当于逻辑CPU。 | sys.dm_os_schedulers
|
通过如下SQL可以查询SQL Server实例当前正在运行的SQL
106
0select
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;
查询返回的主要信息：
client_net_address: 客户端IP
status
login_name: 登录名
current_sql: 当前执行的SQL
full_sql: 完整的sql（存储过程或batch的完整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
返回的信息：
BlockingSessionId
BlockingSQL：blocking sql并不一定是真正引起阻塞的SQL，而是阻塞session执行的最后一个SQL。
WhyBlocked：阻塞原因
wait_duration_ms: 阻塞时间（毫秒）
BlockedSessionId：被阻塞的session id
Blocked SQL：被阻塞的session执行的SQL
@Copyrights 2016-2023 杭州玳数科技有限公司 浙ICP备15044486号-1 浙公网安备33011002011932号
