dbcc_name 空的_DBCC INPUTBUFFER和sys.dm_exec_input_buffer DMF概述

最新推荐文章于 2022-08-02 14:35:57 发布

culuo4781

最新推荐文章于 2022-08-02 14:35:57 发布

阅读量573

点赞数

文章标签：数据库 python linux java mysql

原文链接：https://www.sqlshack.com/overview-of-dbcc-inputbuffer-and-sys-dm_exec_input_buffer-dmf/

版权

dbcc_name 空的

This article gives an overview of the DBCC INPUTBUFFER and its replacement sys.dm_exec_input_buffer system dynamic management function .

本文概述了DBCC INPUTBUFFER及其替代的sys.dm_exec_input_buffer系统动态管理功能。

介绍 (Introduction)

Suppose you connect to a SQL instance and identify current running sessions and their query texts. Usually, DBAs use a system stored procedure called sp_who2 to list all sessions along with little diagnostic information such as CPU, memory, etc. It is rare to find any DBA that does not know the sp_who2 command. We get a row corresponding to each SPID. Here SPID belongs to Server Process ID.

假设您连接到SQL实例并标识当前正在运行的会话及其查询文本。通常，DBA使用一个称为sp_who2的系统存储过程来列出所有会话以及很少的诊断信息，例如CPU，内存等。很少找到不知道sp_who2命令的DBA。我们得到对应于每个SPID的一行。 SPID在此属于服务器进程ID。

Here is the output of the sp_who2 command in an idle instance:

这是一个空闲实例中sp_who2命令的输出：

SPID values of 1 to 50 belong to system processes such as log writer, lock monitor, checkpoint, and resource monitor.

SPID值从1到50属于系统进程，例如日志编写器，锁定监视器，检查点和资源监视器。

Status: It gives the status of the particular SPID such as Sleeping, Background, Running and Runnable:

状态：给出特定SPID的状态，例如“睡眠”，“后台”，“运行”和“可运行”：

CPU time: It is the total time of CPU time for that particular process CPU时间：这是该特定进程的CPU时间的总时间
Disk IO: It is the total disk read/write time for that particular process 磁盘IO：这是该特定进程的总磁盘读/写时间
Last batch: It gives the timestamp of the last statement from the process 最后一批：它给出了流程中最后一条语句的时间戳
DB Name: The SPID is executing a query in the database name 数据库名称： SPID正在数据库名称中执行查询
Program Name: It shows the Program name such as SSMS, SQL Agent from where the connection is coming to SQL Server 程序名称：它显示程序名称，例如SSMS，SQL Agent（连接从那里到达SQL Server）

Now let’s execute a query in SQL Server instance to fetch records from a table. In the screenshot, we can see the query is executing in SPID 62:

现在，让我们在SQL Server实例中执行查询以从表中获取记录。在屏幕截图中，我们可以看到查询正在SPID 62中执行：

Let’s say we do not know the query and its source. We can use the sp_who2 procedure to find out what is running under this SPID.

假设我们不知道查询及其来源。我们可以使用sp_who2过程找出在此SPID下正在运行的内容。

We can filter the records for the SPID 62 using the following query:

我们可以使用以下查询过滤SPID 62的记录：

sp_who2 62

We can see login, database and program name for this SPID, but it does not show the query running under this SPID:

我们可以看到此SPID的登录名，数据库和程序名，但未显示在此SPID下运行的查询：

We use another DBCC INPUTBUFFER command for getting the last statement executed in a particular SPID. We need to pass the SPID number in the argument:

我们使用另一个DBCC INPUTBUFFER命令来获取在特定SPID中执行的最后一条语句。我们需要在参数中传递SPID号：

DBCC INPUTBUFFER(62)

Is it a good way to identify query text for the SPID? Let’s explore a few drawbacks of using sp_who2 and DBCC INPUTBUFFER command:

这是识别SPID的查询文本的好方法吗？让我们探讨使用sp_who2和DBCC INPUTBUFFER命令的一些缺点：

We cannot use this command to retrieve query text for all sessions
我们不能使用此命令来检索所有会话的查询文本
For multiple sessions, we need to run the DBCC INPUBUFFER command for each session. For example, if we want to check the query text for 60, 61, 62 SPID’s, we need to run the command as shown below
对于多个会话，我们需要为每个会话运行DBCC INPUBUFFER命令。例如，如果要检查60、61、62个SPID的查询文本，则需要运行如下所示的命令

We get individual output for each command along with event type and event info (query). By default, it shows only 4000 characters of the query for the language event type:

我们获得每个命令的单独输出以及事件类型和事件信息（查询）。默认情况下，它仅显示4000个字符的语言事件类型查询：

DBCC INPUTBUFFER命令所需的权限 (Permissions required for DBCC INPUTBUFFER command)

We need one of the following permissions for executing this command:

我们需要以下权限之一才能执行此命令：

sysadmin fixed server role sysadmin固定服务器角色的成员
VIEW SERVER STATE permission 查看服务器状态”权限

DBCC INPUTBUFFER命令的替代方法 (Alternatives of DBCC INPUTBUFFER command)

SQL Server provides useful dynamic management views and functions. We usually use the following acronyms for these views and functions:

SQL Server提供了有用的动态管理视图和功能。对于这些视图和功能，我们通常使用以下首字母缩写词：

DMV: Dynamic Management View
DMV：动态管理视图
DMF: Dynamic Management Function
DMF：动态管理功能

These DMV and DMF provide server state information and useful in monitoring SQL Server instance, performance issues, internal query behavior, waits, etc.

这些DMV和DMF提供服务器状态信息，可用于监视SQL Server实例，性能问题，内部查询行为，等待等。

SQL Server 2014 SP2 introduced a new DMF sys.dm_exec_input_buffer as a replacement of the DBCC INPUTBUFFER command. We can use this DMF to retrieve the last query executed similar to the DBCC command. The benefit of it is that you can retrieve information for two or more sessions at the same time. It also allows you to join it with other DMV/DMF for fetching useful information.

SQL Server 2014 SP2引入了新的DMF sys.dm_exec_input_buffer，以替代DBCC INPUTBUFFER命令。我们可以使用该DMF检索与DBCC命令类似的最后执行的查询。这样做的好处是您可以同时检索两个或多个会话的信息。它还允许您将其与其他DMV / DMF结合使用以获取有用的信息。

sys.dm_exec_input_buffer的语法 (Syntax of sys.dm_exec_input_buffer)

sys.dm_exec_input_buffer ( session_id , request_id )

It requires two arguments:

它需要两个参数：

Session_ID: We use the SPID for which we want to retrieve the information. We use the same SPID in DBCC INPUTBUFFER. We can use other DMV’s for fetching the SPID automatically for all sessions Session_ID：我们使用要为其检索信息的SPID。我们在DBCC INPUTBUFFER中使用相同的SPID。我们可以使用其他DMV自动获取所有会话的SPID
- sys.dm_exec_requests
  sys.dm_exec_requests
- sys.dm_exec_sessions
  sys.dm_exec_sessions
- sys.dm_exec_connections
  sys.dm_exec_connections
Request_ID: It is the unique ID of the session. We can retrieve it from sys.dm_exec_requests DMV. It also allows NULL value for this argument Request_ID：这是会话的唯一ID。我们可以从sys.dm_exec_requests DMV中检索它。它还为此参数允许NULL值

Previously we use the DBCC command for retrieving query text for SPID 60, 61, and 62. Let’s use the sys.dm_exec_input_buffer command for retrieving the same information:

以前，我们使用DBCC命令来检索SPID 60、61和62的查询文本。让我们使用sys.dm_exec_input_buffer命令来检索相同的信息：

SELECT *
FROM sys.dm_exec_input_buffer(60, NULL); 
GO
SELECT *
FROM sys.dm_exec_input_buffer(61, NULL); 
GO
SELECT *
FROM sys.dm_exec_input_buffer(62, NULL); 
GO

It returns a similar output using the DMF as well in comparison with the DBCC INPUTBUFFER command:

与DBCC INPUTBUFFER命令相比，它也使用DMF返回类似的输出：

The output of DMF sys.dm_exec_input_buffer

Why should we use this sys.dm_exec_input_buffer DMF if it is returning the similar output as of DBCC INPUTBUFFR command?

如果sys.dm_exec_input_buffer DMF返回与DBCC INPUTBUFFR命令类似的输出，为什么还要使用它？

The benefits of using the DMF are:

使用DMF的好处是：

You can join it with other DMV\DMF for providing input for session-id automatically
您可以将其与其他DMV \ DMF结合使用，以自动为session-id提供输入
We can retrieve extra information with the join of multiple DMV, DMF’s
我们可以通过多个DMV，DMF，
You can filter results for specific session id or exclude system session-id’s from the output
您可以过滤特定会话ID的结果，或从输出中排除系统会话ID的结果

The following query combines sys.dm_exec_input_buffer DMF and sys.dm_exec_sessions DMV. We use the CROSS APPLY join operator between these for retrieving T-SQL statements of all connected user sessions (SPID>50).

以下查询结合了sys.dm_exec_input_buffer DMF和sys.dm_exec_sessions DMV。我们在这些之间使用CROSS APPLY连接运算符来检索所有已连接用户会话（SPID> 50）的T-SQL语句。

You can notice that DMF takes the value of the argument session_id from the session id of sys.dm_exec_sessions DMV. It does not require you to specify session_id manually which is a drawback of the DBCC command.

您会注意到DMF从sys.dm_exec_sessions DMV的会话ID中获取了参数session_id的值。它不需要您手动指定session_id，这是DBCC命令的缺点。

In the output, you can see additional columns such as CPU_time, logical_reads, writes, total_elapsed time, program name in the same window. You do not need to execute separate queries and combine outputs. We also get an output of multiple session id’s in a single output:

在输出中，您可以在同一窗口中看到其他列，例如CPU_time，逻辑_读取，写入，总_经过时间，程序名称。您无需执行单独的查询并合并输出。我们还可以在一个输出中获得多个会话ID的输出：

SELECT dmes.session_id, 
    ib.event_info, 
    STATUS, 
    cpu_time, 
    memory_usage, 
    logical_reads, 
    writes, 
    row_count total_elapsed_time, 
    last_request_start_time, 
    last_request_end_time host_name, 
    program_name, 
    login_name
FROM sys.dm_exec_sessions AS dmes
  CROSS APPLY sys.dm_exec_input_buffer(dmes.session_id, NULL) AS ib
WHERE dmes.session_id > 50

the output of sys.dm_exec_input_buffer to filter results

Similarly, the following query combines DMF sys.dm_exec_requsts with the DMV sys.dm_exec_requests DMV using the CROSS APPLY join operator:

类似地，以下查询使用CROSS APPLY连接运算符将DMF sys.dm_exec_requsts与DMV sys.dm_exec_requests DMV组合在一起：

SELECT Req.session_id, 
    InBuf.event_info, 
    ses.host_name, 
    ses.client_interface_name, 
    ses.open_transaction_count,ses.is_user_process
FROM sys.dm_exec_requests AS Req
  JOIN sys.dm_exec_sessions AS Ses ON Ses.session_id = Req.session_id
  CROSS APPLY sys.dm_exec_input_buffer(Req.session_id, Req.request_id) AS InBuf
WHERE Ses.session_id > 50
   AND Ses.is_user_process = 1;
GO

sys.dm_exec_input_buffer DMF所需的权限 (Permissions required for sys.dm_exec_input_buffer DMF)

It requires a VIEW SERVER STATE permission to view all executing sessions on the instance. User can see only the current session without this permission
它需要具有“查看服务器状态”权限，才能查看实例上的所有正在执行的会话。在没有此权限的情况下，用户只能看到当前会话
If a user is a database owner, it sees all executing sessions on the database. User can see only the current session without this permission
如果用户是数据库所有者，它将看到数据库上所有正在执行的会话。在没有此权限的情况下，用户只能看到当前会话

结论 (Conclusion)

It is always better to use the latest query, commands to enhance productivity and flexibility. We can still use the old way DBCC INPUTBUFFER, but I would recommend using sys.dm_exec_input_buffer dynamic management function instead. If you have not tried it before, use it and become familiar with it. It will give more useful information and saves time for you in executing multiple statements and combine results.

最好使用最新的查询和命令来提高生产力和灵活性。我们仍然可以使用旧方法DBCC INPUTBUFFER，但是我建议改为使用sys.dm_exec_input_buffer动态管理功能。如果您以前从未尝试过，请使用它并熟悉它。它将提供更多有用的信息，并为您节省执行多个语句和合并结果的时间。