SQL Server优化入门系列(一)——快速定位阻塞SQL

俊达3年前技术文章1431

引言

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

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

  • 有哪些会话在执行,当前状态是什么,在执行什么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_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


    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;


    sqlserver-queries.png


    查询返回的主要信息:

    • 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


    sqlserver-connections.png


    返回的信息:

    • BlockingSessionId

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

    • WhyBlocked:阻塞原因

    • wait_duration_ms: 阻塞时间(毫秒)

    • BlockedSessionId:被阻塞的session id

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



    相关文章

    Hue简介

    Hue简介

    1.    Hue介绍1.1.  Hue是什么l   HUE = Hadoop User Experiencel   A...

    开源大数据集群部署(十)Ranger usersync部署

    开源大数据集群部署(十)Ranger usersync部署

    ranger usersync部署Ø 解压包[root@hd1.dtstack.com ranger]# pwd /opt/ranger [root@hd1.dtstack.com ranger]...

    数据湖Iceberg

    数据湖Iceberg

    1、概述         Iceberg 是一个面向海量数据分析场景的开放表格式(Table Format)。表格式(Table  Format)可以理解为元数据以及数据文件的一种组织方式,处于计算框...

    ACK版本升级

    ACK版本升级

    需求:将ACK版本从1.14.8升级至1.16.9升级前注意事项:集群升级需要机器可以公网访问,以便下载升级所需的软件包。集群升级Kubernetes过程中,可能会有升级失败的情况,为了您的数据安全,...

    Nginx限流

    Nginx限流

    一、背景         限流的目的是通过对并发访问/请求进行限速来保护系统,一旦达到限制速率则可以拒绝服务(定向到错误页),多应用于高并发场景和安全防护场景。通过限流有效地减缓暴力密码破解攻击,也可...

    等待事件latch: cache buffers chains 的分析与优化

    等待事件latch: cache buffers chains 的分析与优化

    等待事件latch: cache buffers chains 的分析与优化要理解latch: cache buffers chains并解决这个问题,就需要深入的了解Buffer Cach...

    发表评论    

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。