SQL Server 2016里的sys.dm_exec_input_buffer的问题

SQL Server 2016中，存在sys.dm_exec_input_buffer函数问题。

SQL Server 2016中sys.dm_exec_input_buffer动态管理视图深度解析与应用

技术内容：

概述

SQL Server 2016提供了许多新的特性和改进，其中动态管理视图（DMV）也得到了进一步的增强，sys.dm_exec_input_buffer是SQL Server 2016中引入的一个非常有用的DMV，它可以帮助我们获取正在执行的查询的输入缓冲区内容，通过这个DMV，我们可以查看正在执行的SQL语句、参数值等信息，从而对数据库性能进行更深入的分析和优化。

sys.dm_exec_input_buffer的使用

1、动态管理视图结构

要了解sys.dm_exec_input_buffer，首先我们需要了解它的结构，以下是sys.dm_exec_input_buffer的主要列：

– session_id：当前会话的ID。

– request_id：当前请求的ID，通常与session_id配合使用。

– input_buffer：包含输入缓冲区内容的XML数据。

– event_type：事件类型，如SQL:BatchStarting、SQL:BatchCompleted等。

2、查询正在执行的SQL语句

要查询正在执行的SQL语句，可以使用以下查询：

SELECT
    es.session_id,
    er.request_id,
    SUBSTRING(ib.input_buffer, 1, 4000) AS input_buffer
FROM
    sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_requests AS er ON es.session_id = er.session_id
CROSS APPLY sys.dm_exec_input_buffer(er.session_id, er.request_id) AS ib
WHERE
    es.is_user_process = 1
    AND er.session_id <> @@SPID

此查询将返回当前数据库中所有正在执行的SQL语句及其输入缓冲区内容。

3、查看参数值

sys.dm_exec_input_buffer还可以帮助我们查看查询的参数值，以下是一个示例：

DECLARE @handle INT
SELECT
    @handle = er.plan_handle
FROM
    sys.dm_exec_requests AS er
WHERE
    er.session_id = 51
SELECT
    deib.event_info
FROM
    sys.dm_exec_input_buffer(@handle, NULL) AS deib

在这个示例中，我们首先获取了指定会话的plan_handle，然后使用sys.dm_exec_input_buffer查询了输入缓冲区内容，返回的event_info列将包含参数值。

sys.dm_exec_input_buffer的实际应用

1、查找长时间运行的查询

在数据库性能调优过程中，我们常常需要查找长时间运行的查询，结合sys.dm_exec_requests和sys.dm_exec_input_buffer，我们可以编写以下查询：

SELECT
    er.session_id,
    er.request_id,
    er.start_time,
    er.status,
    SUBSTRING(ib.input_buffer, 1, 4000) AS input_buffer
FROM
    sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_input_buffer(er.session_id, er.request_id) AS ib
WHERE
    er.session_id <> @@SPID
    AND DATEDIFF(second, er.start_time, GETDATE()) > 60

此查询将返回执行时间超过60秒的查询及其输入缓冲区内容。

2、查找占用大量资源的查询

当数据库服务器出现性能问题时，我们可能需要查找占用大量资源的查询，以下是一个示例：

SELECT
    er.session_id,
    er.request_id,
    er.cpu_time,
    er.logical_reads,
    er.writes,
    SUBSTRING(ib.input_buffer, 1, 4000) AS input_buffer
FROM
    sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_input_buffer(er.session_id, er.request_id) AS ib
WHERE
    er.session_id <> @@SPID
    AND (er.cpu_time > 1000000 OR er.logical_reads > 100000 OR er.writes > 10000)
ORDER BY
    er.cpu_time DESC,
    er.logical_reads DESC,
    er.writes DESC

此查询将返回CPU时间、逻辑读取和写入次数超过指定阈值的查询。

sys.dm_exec_input_buffer是SQL Server 2016中非常有用的一个动态管理视图，通过它我们可以获取正在执行的查询的输入缓冲区内容，进而对数据库性能进行深入分析和优化，本文介绍了sys.dm_exec_input_buffer的结构、使用方法以及在实际应用中的几个场景，希望对大家有所帮助，在实际工作中，我们可以根据自己的需求，灵活运用这个DMV，提高数据库性能调优的效率。