sql server 监视_使用动态管理对象监视SQL Server –请求

最新推荐文章于 2022-03-08 14:29:07 发布

culuo4781

最新推荐文章于 2022-03-08 14:29:07 发布

阅读量450

点赞数

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

原文链接：https://www.sqlshack.com/monitoring-sql-server-with-dynamic-management-objects-requests/

版权

sql server 监视

In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic Management Views. The article demonstrated how we can use two views, namely sys.dm_exec_sessions and sys.dm_exec_connections to view activity on our instance of SQL Server and we achieved this by utilising some of the functionality available to us within T-SQL like joins, aliases and functions, all of which helped us develop a query to return monitoring information to us.

在上一篇文章“ 使用动态管理对象监视SQL Server –会话和连接”中，我介绍了使用T-SQL查询通过动态管理视图监视SQL Server的概念。本文演示了如何使用sys.dm_exec_sessions和sys.dm_exec_connections这两个视图来查看SQL Server实例上的活动，并且我们通过利用T-SQL中可用的一些功能（如联接，别名和函数）实现了这一点。，所有这些都帮助我们开发了一个查询，以将监控信息返回给我们。

As the sessions and connections views contain the same column, session_id, we utilised an inner join to return matching information based on this column value (there is a one to one relationship between connections and sessions). In this article we are going to put another Dynamic Management View into our query that will allow us to see information at a lower level than the session or connection objects.

由于会话和连接视图包含同一列session_id，我们利用内部联接基于此列值返回匹配信息（连接和会话之间存在一对一的关系）。在本文中，我们将在查询中放入另一个动态管理视图，该视图将使我们能够查看比会话或连接对象低的信息。

I use the term lower level because it helps to visualise what the Dynamic Management Objects represent. We have sys.dm_exec_connections which is the physical connection between a server and our SQL instance and once connected a session is authenticated by login and then we can use sys.dm_exec_sessions to see the corresponding information.

我使用较低的术语是因为它有助于可视化动态管理对象代表的内容。我们有sys.dm_exec_connections，它是服务器与SQL实例之间的物理连接，一旦连接，会话便会通过登录进行身份验证，然后我们可以使用sys.dm_exec_sessions查看相应的信息。

It stands to reason that sessions aren’t created and then nothing happens; if that was the case we’d never have performance issues! Instead work is created within SQL Server in the form of requests and we can use the sys.dm_exec_requests DMV to query this information directly:

可以说没有创建会话，然后什么也没有发生，这是有原因的。如果是这样的话，我们永远不会有性能问题！而是以请求的形式在SQL Server中创建工作，我们可以使用sys.dm_exec_requests DMV直接查询此信息：

On its own the DMV returns a lot of information and this is again, like the sessions view, because we are retrieving rows for both system and user related requests. However, unlike the sessions view we do not have a column such as is_user_process to filter out any system related row that we might not be interested in.

DMV本身会返回很多信息，这又像会话视图一样，因为我们正在检索与系统和用户相关的请求的行。但是，与会话视图不同，我们没有像is_user_process这样的列来过滤掉我们可能不感兴趣的任何与系统相关的行。

To filter these rows out we can use the matching column (session_id) to the sys.dm_exec_sessions view. We can then use an inner join with a where clause based on the sessions is_user_process column being equal to 1 that will remove any system related information:

要过滤掉这些行，我们可以使用sys.dm_exec_sessions视图的匹配列（session_id）。然后，我们可以基于会话is_user_process列等于1的where子句使用内部联接，这将删除任何与系统相关的信息：

 
SELECT * FROM sys.dm_exec_requests r 
INNER JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id 
WHERE s.is_user_process = 1

On my test instance the following result set is returned:

在我的测试实例上，返回以下结果集：

Whilst we are using two DMVs we can alter the select statement to only return information from one DMV or the other using the alias and * in the select statement:

当我们使用两个DMV时，我们可以将select语句更改为仅使用select语句中的别名和*从一个DMV或另一个DMV返回信息：

 
--return columns only from the requests view
SELECT r.* FROM sys.dm_exec_requests r 
INNER JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id 
WHERE s.is_user_process = 1

 
--return columns only from the sessions view
SELECT s.* FROM sys.dm_exec_requests r 
INNER JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id 
WHERE s.is_user_process = 1

Which show the corresponding result sets:

其中显示了相应的结果集：

Requests

要求

Sessions

届会

Of course the whole concept of joins is not to have separate results but results using columns from all sources. I say all sources because we’re not limited to one join and we can use the query we created in my last post and add in a join to the sys.dm_exec_requests DMV there:

当然，连接的整个概念不是要有单独的结果，而是要使用来自所有来源的列的结果。我说所有消息源是因为我们不仅限于一个联接，而且可以使用我们在上一篇文章中创建的查询，并在其中添加对sys.dm_exec_requests DMV的联接：

 
SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
database_id, DB_NAME(s.database_id) AS [database], 
s.status,
s.reads, s.writes, s.logical_reads, s.row_count,
c.session_id, c.net_transport, c.protocol_type, 
c.client_net_address, c.client_tcp_port, 
c.num_writes AS DataPacketWrites 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r -- added join to sys.dm_exec_requests
ON s.session_id = r.session_id
WHERE s.is_user_process = 1

Running this query will result in an error being returned:

运行此查询将导致返回错误：

Msg 209, Level 16, State 1, Line 3
Ambiguous column name ‘database_id’.

消息209，第16级，州1，第3行
列名称不明确'database_id'。

This is because our newly added DMV contains the database_id column so we’ll prefix the column in the select statement with our alias for the requests DMV:

这是因为我们新添加的DMV包含database_id列，因此我们将在select语句中为该列添加前缀以请求DMV：

 
SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
r.database_id, -- added r.alias
DB_NAME(s.database_id) AS [database], 
s.status,
s.reads, s.writes, s.logical_reads, s.row_count,
c.session_id, c.net_transport, c.protocol_type, 
c.client_net_address, c.client_tcp_port, 
c.num_writes AS DataPacketWrites 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r -- added join to sys.dm_exec_requests
ON s.session_id = r.session_id
WHERE s.is_user_process = 1

Now we have a query that is looking at connection, session and request information and we can essentially pick and mix our columns to return information that we can use or act on. The sys.dm_exec_requests DMV is very popular to use in both monitoring and troubleshooting activities because of the wealth of useful information that is available.

现在，我们有了一个查询，该查询正在查看连接，会话和请求信息，并且我们基本上可以选择并混合我们的列以返回可以使用或操作的信息。 sys.dm_exec_requests DMV在监视和故障排除活动中非常流行，因为可以使用大量有用的信息。

Before we explore the requests DMV in more detail there is one final note on the query and the joins that it is using. You may notice that our query based on session/connection information returns more rows than what the session/connection/requests one returns. This is because of the way the inner join works, by design it only returns matching rows so we’re essentially using this join mapping through all three views.

在更详细地探讨请求DMV之前，有关于该查询及其正在使用的联接的最后说明。您可能会注意到，基于会话/连接信息的查询返回的行比会话/连接/请求返回的行多。这是因为内部联接的工作方式，根据设计，它仅返回匹配的行，因此我们实质上在所有三个视图中都使用此联接映射。

The sys.dm_exec_requests view only contains information on requests that are currently executing so although we may have additional sessions that inactive shown in our sessions/connections query if there is no matching request that is currently “active” that session information will not be shown.

sys.dm_exec_requests视图仅包含有关当前正在执行的请求的信息，因此，即使没有匹配的请求当前处于“活动”状态，我们也可能在会话/连接查询中显示其他处于非活动状态的会话，因此不会显示该会话信息。

Session/Connection

会话/连接

Session/Connection/Request

会话/连接/请求

In the two screenshots above we can see that the query that does not contain the join to the requests DMV contains information for session ID’s 51 and 52. The second image where the join to sys.dm_exec_requests is present shows that we only have one row. This is all down to the matching rows that have been forced by the inner join, as we only have one executing request (session 52), that is all that is returned.

在上面的两个屏幕截图中，我们可以看到不包含对请求DMV的联接的查询包含有关会话ID 51和52的信息。第二个显示sys.dm_exec_requests联接的图像显示我们只有一行。这完全取决于内部联接所强制执行的匹配行，因为我们只有一个执行请求（会话52），即返回的全部内容。

We can see why this is happening if we look at the status column from sys.dm_exec_sessions for the two session_ids with the following query:

如果使用以下查询查看两个session_id的sys.dm_exec_sessions的status列，我们可以看到为什么发生这种情况：

 
SELECT session_id, status FROM sys.dm_exec_sessions 
WHERE session_id IN (51,52)

This shows that session 51 is inactive (no match in requests) whereas 52 is running and so would have a corresponding executing request record (with the matching session ID).

这表明会话51是非活动的（请求中没有匹配项），而会话52正在运行，因此将具有相应的执行请求记录（具有匹配的会话ID）。

This is an important concept to understand because as you work with queries contain multiple joins we can end up with smaller result sets than expected and we often have to work backwards by removing join statements to find where rows aren’t being joined correctly.

这是一个重要的概念，因为当您使用查询包含多个联接时，我们最终会得到比预期小的结果集，并且我们经常不得不通过删除联接语句来查找未正确联接行的位置来进行反向工作。

There are other join types that we can use to change this behaviour but they are out of scope for this article.

我们可以使用其他联接类型来更改此行为，但它们不在本文讨论范围之内。

Whist mentioning session IDs it’s also worth noting that the tabs in Management Studio will show the corresponding session ID of any queries you are working on:

Whist提到了会话ID，还值得注意的是，Management Studio中的选项卡将显示您正在处理的任何查询的相应会话ID：

We’ll now remove some columns from our query, including all the connection view related information so we can look at executing requests in more detail:

现在，我们将从查询中删除一些列，包括所有与连接视图有关的信息，以便我们可以更详细地查看执行请求：

 
SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
s.database_id, 
DB_NAME(s.database_id) AS [database], 
s.status, r.status 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1

Note that I have used two status columns; s.status and r.status, one from sessions and one from requests and we can see that both have a value of “running”.

请注意，我使用了两个状态列。 s.status和r.status，一个来自会话，一个来自请求，我们可以看到两者的值均为“运行”。

To demonstrate how these are actually very different I’m going to create a very simple blocking scenario using the AdventureWorks2014 sample database. To achieve this I will perform an update within a transaction that does not have a corresponding commit or rollback and then whilst that transaction is open attempt a select from the same table. I’ll then check my activity query for the results.

为了演示这些实际上有何不同，我将使用AdventureWorks2014示例数据库创建一个非常简单的阻止方案。为此，我将在没有相应提交或回滚的事务中执行更新，然后在该事务处于打开状态时尝试从同一表中进行选择。然后，我将检查活动查询的结果。

 
USE AdventureWorks2014
GO
 
BEGIN TRAN
UPDATE 
[AdventureWorks2014].[Person].[Address] SET AddressLine1 = '1971 Napa Ct.'
WHERE AddressID = 1

In a new window:

在新窗口中：

 
SELECT TOP 1000 [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2014].[Person].[Address]

The select statement won’t return any rows and actually will be stuck on executing query. This is because the update transaction is still in limbo, it hasn’t committed or rolled back and because the default behaviour for SQL is not to read uncommitted data we are left waiting for the transaction to complete one way or the other.

select语句将不返回任何行，并且实际上将卡在执行查询中。这是因为更新事务仍处于不确定状态，尚未提交或回滚，并且由于SQL的默认行为是不读取未提交的数据，我们只能等待事务以另一种方式完成。

When I look at my activity script I can see the following under the status columns:

查看活动脚本时，可以在状态列下看到以下内容：

The second row is what we need to focus on; the first status (sessions) has a value of “running” but the second status column has a value of “suspended”. This tells us that although the session is active the request is unable to run.

第二行是我们需要重点关注的内容；第一个状态（会话）的值为“正在运行”，而第二个状态列的值为“已暂停”。这告诉我们，尽管会话处于活动状态，但请求无法运行。

If I then run a ROLLBACK TRAN command in the same window under the update command this will undo any changes and finish that statement. The select statement will have returned its results and if we run the activity query again we won’t have a corresponding row for the AdventureWorks2014 database.

如果然后在更新命令下的同一窗口中运行ROLLBACK TRAN命令，这将撤消所有更改并完成该语句。 select语句将返回其结果，如果再次运行活动查询，将没有AdventureWorks2014数据库的相应行。

In fact, I can add AND s.session_id <> @@SPID to the query and my result set will not contain any rows proving that this row relates to the activity query itself.

实际上，我可以将AND s.session_id <> @@ SPID添加到查询中，并且我的结果集将不包含任何证明该行与活动查询本身相关的行。

The requests status can immediately tell us if a request is processing (running) or if there’s a potential problem (suspended) however the status column can actually contain one of five values and each gives a good indicator into the current state of a request:

请求状态可以立即告诉我们请求是否正在处理（正在运行）或是否存在潜在问题（已暂停），但是状态列实际上可以包含五个值之一，并且每个值都可以很好地指示请求的当前状态：

Background背景
Running跑步
Sleeping睡眠
Suspended已暂停
Runnable可运行

A status of background means that the request is running behind the scenes so to speak and only system requests can have this particular status, as we’ve excluded them from our result set we’ll not see any of these.

处于背景状态意味着该请求在后台运行，可以这么说，只有系统请求可以具有此特定状态，因为我们已将它们从结果集中排除，因此我们看不到任何这些。

Running as the value suggests means that the request is being processed and is consuming CPU.

如该值所示运行表示该请求正在处理中，并且正在消耗CPU。

Sleeping means that the request has finished and is awaiting command.

Hibernate表示请求已完成，正在等待命令。

Suspended as we’ve just demonstrated is that request is awaiting resource, this could be resource such as memory or disk or that it is waiting for a lock on a data page.

正如我们刚刚演示的那样，该请求正在等待资源的暂停，该请求可能是内存或磁盘之类的资源，或者正在等待数据页上的锁。

Runnable indicates that the request is waiting for CPU resource. Note that this is not the same as a suspended status or wait but is in a queue to be placed on the CPU.

Runnable表示请求正在等待CPU资源。请注意，这与挂起状态或等待状态不同，而是处于要放入CPU的队列中。

This status column is quite useful and although a very small part of the jigsaw it does enable us to see what requests are running and what requests potentially require more investigation. I use the word “potentially” quite often, particularly when demonstrating these types of DBA activities and that is because we can never make an assumption based on one small piece of information.

这个状态栏非常有用，尽管它只是拼图的一小部分，但它确实使我们能够查看正在运行的请求以及可能需要进一步调查的请求。我经常使用“潜在”一词，尤其是在演示这些类型的DBA活动时，这是因为我们永远无法基于一小部分信息做出假设。

If we go back to the blocking scenario that I created earlier we could see a suspended request but even though we knew at the time that doesn’t necessarily mean we have an urgent issue. It could be very normal behaviour, what if the request had just started running that second and was awaiting some pages? That could actually be quite acceptable.

如果返回到我之前创建的阻塞方案，则可以看到一个暂停的请求，但是即使我们当时知道这并不一定意味着我们有紧急问题。这可能是非常正常的行为，如果请求刚刚开始运行第二秒并正在等待某些页面，该怎么办？这实际上是可以接受的。

Here’s a query that focuses on some time based columns from the requests DMV (I’ve excluding the activity query session ID/SPID).

这是一个查询，着重于请求DMV中一些基于时间的列（我不包括活动查询会话ID / SPID）。

 
SELECT s.session_id, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1 
AND s.session_id <> @@SPID

In this case I’ve left the select statement running for a while and then checked the activity query which returns the following:

在这种情况下，我让select语句运行了一段时间，然后检查了活动查询，该查询返回以下内容：

I can see here that the request has had no CPU time whatsoever and has been waiting for a total of 104501ms, whilst it’s always relative to the usual demand on a server this would indicate to me that this particular request is worth investigating. Conversely should the total_elapsed_time be extremely low I would run the query a few more times to assess if the wait_time values are increasing.

我在这里可以看到，该请求没有任何CPU时间，并且一直在等待104501ms，而这始终与服务器上的通常需求有关，这向我表明该特定请求值得研究。相反，如果total_elapsed_time极低，则我将运行查询几次以评估wait_time值是否正在增加。

I’ll now add two additional columns from sys.dm_exec_requests to our query, command and percent_complete:

现在，我将sys.dm_exec_requests中的其他两列添加到我们的查询，命令和percent_complete中：

 
SELECT s.session_id, r.command, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time,
r.percent_complete
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1 
AND s.session_id <> @@SPID

Here’s a result set from the blocking scenario that we have been working with:

这是我们一直在使用的阻止方案的结果集：

The command column tells us the type of statement that is running (SELECT in this example) but the percent_complete value is 0. The percent_complete column is very useful but it will only contain a meaningful value when certain commands are being executed, for example if I start a backup and then check I receive the following:

命令列告诉我们正在运行的语句的类型（在此示例中为SELECT），但是percent_complete值为0。percent_complete列非常有用，但仅在执行某些命令时才会包含有意义的值，例如，如果我开始备份，然后检查是否收到以下消息：

I can see the command column has a value of BACKUP DATABASE and I know it’s nearly finished as the percent_complete is at 100. Note the wait time is at 0 so I haven’t encountered any waits for resource (suspended status) and the total_elapsed time is just under 5 seconds. The difference between total_elapsed_time and cpu_time is time spent in the queue awaiting CPU (runnable status).

我可以看到命令列的值是BACKUP DATABASE，并且我知道它几乎已完成，因为percent_complete为100。请注意，等待时间为0，所以我没有遇到任何资源等待（挂起状态）和total_elapsed时间不到5秒。 total_elapsed_time和cpu_time之间的差异是等待队列在CPU中花费的时间（可运行状态）。

This next image shows a different story. Here we have a very similar backup command but this time the request has a status of suspended, we know this relates to the time awaiting resource and as such we can see a wait time of 1778ms. The actual backup operation is around 43% complete.

下一张图片显示了一个不同的故事。在这里，我们有一个非常相似的备份命令，但是这次请求的状态为暂停，我们知道这与资源等待时间有关，因此我们可以看到1778ms的等待时间。实际的备份操作已完成约43％。

As mentioned the percent_complete column will only contain data for certain tasks, the full list being:

如前所述，percent_complete列将仅包含某些任务的数据，完整列表为：

ALTER INDEX REORGANIZE
更改索引重新组织
AUTO_SHRINK option with ALTER DATABASE
具有ALTER DATABASE的AUTO_SHRINK选项
BACKUP DATABASE
备份资料库
DBCC CHECKDB
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC检查表
DBCC INDEXDEFRAG
DBCC索引分解
DBCC SHRINKDATABASE
DBCC缩进数据库
DBCC SHRINKFILE
DBCC缩略文件
RECOVERY
复苏
RESTORE DATABASE
恢复数据库
ROLLBACK
回滚
TDE ENCRYPTION
TDE加密

This means for queries such as our earlier select statement we will never see a percentage complete value but clearly it is very useful for monitoring the progress of certain administrative tasks.

这意味着对于诸如我们之前的select语句之类的查询，我们永远不会看到完成百分比值，但是显然，它对于监视某些管理任务的进度非常有用。

The final part of this article is to show what our requests are waiting for. This in itself is an extremely deep subject so this post is limited to showing that we can view wait information via T-SQL rather than explain all the different possibilities.

本文的最后一部分是展示我们的请求正在等待什么。这本身是一个非常深入的主题，因此本文仅限于表明我们可以通过T-SQL查看等待信息，而不是解释所有不同的可能性。

Let’s modify our query and add the wait_type column from sys.dm_exec_requests:

让我们修改查询并从sys.dm_exec_requests中添加wait_type列：

 
SELECT s.session_id, r.command, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time,
r.percent_complete, r.wait_type 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1 
AND s.session_id <> @@SPID

The wait_type column is one of the most important pieces of diagnostic information that we can retrieve from SQL Server as it tells us exactly (at the moment of capture) what resource our requests are waiting for.

wait_type列是我们可以从SQL Server检索到的最重要的诊断信息之一，因为它准确地（在捕获时）告诉我们请求正在等待什么资源。

I’ll use two examples for this, first the backup task:

为此，我将使用两个示例，第一个是备份任务：

We can see here a wait_time of 1919ms and it has a corresponding wait_type of ASYNC_IO_COMPLETION. This is telling us exactly what resource our request is waiting for, in this example I know that the ASYNC_IO_COMPLETION relates to disk IO is typically associated with backup operations. Is this necessarily a problem? Well, to use the DBAs favourite line “it depends” and that would be answered by checking other ongoing activity and referencing previous backup timings.

我们在这里可以看到一个1919ms的wait_time，它具有相应的wait_type ASYNC_IO_COMPLETION。这正好告诉我们请求正在等待什么资源，在此示例中，我知道与磁盘IO相关的ASYNC_IO_COMPLETION通常与备份操作相关联。这一定有问题吗？好吧，使用DBA最喜欢的行“这取决于”，这可以通过检查其他正在进行的活动并参考以前的备份时间来解决。

Let’s use the blocking scenario from earlier to see what wait information we receive for that example:

让我们使用前面的阻塞场景来查看该示例接收的等待信息：

As we might have expected we now have a very different wait type. This time it’s LCK_M_S indicating that our select statement is being blocked. Fortunately the sys.dm_exec_requests DMV also contains a column where we can see which process is blocking our query, let’s add that to our script and run again:

正如我们可能期望的那样，我们现在有一个非常不同的等待类型。这次是LCK_M_S，表明我们的选择语句已被阻止。幸运的是，sys.dm_exec_requests DMV还包含一列，我们可以在其中查看哪个进程正在阻止查询，让我们将其添加到脚本中并再次运行：

Here we can see that our select statement (session 55) is being blocked by session 52. Session 52 isn’t present in our query results but we can use the built-in function sp_who to find out it’s details.

在这里，我们可以看到我们的select语句（会话55）被会话52阻塞。查询结果中不存在会话52，但是我们可以使用内置函数sp_who来查找其详细信息。

 
sp_who 52

This returns very similar information to what we have been looking at so far in the DMV queries and from this we can see session 52 is sleeping and awaiting command. We could either kill off or investigate a little further (never kill off a process without understanding its origin).

这返回的信息与到目前为止我们在DMV查询中一直看到的信息非常相似，从中我们可以看到会话52正在Hibernate并正在等待命令。我们可以终止或进一步调查（切勿在不了解进程起源的情况下终止该进程）。

One question you may ask is why we don’t just use sp_who or even its older brother sp_who2 to return activity related information?

您可能会问的一个问题是，为什么我们不仅仅使用sp_who甚至更老的兄弟sp_who2来返回与活动相关的信息？

By using Dynamic Management Objects we have much more information to use and because we are creating the queries ourselves we can create completely customised scripts to return exactly the information we need, like in the query below:

通过使用动态管理对象，我们可以使用更多信息，并且由于我们自己创建查询，因此我们可以创建完全自定义的脚本以准确返回我们所需的信息，如下面的查询所示：

 
SELECT 
s.session_id, 
s.login_name, s.host_name, c.client_net_address, 
s.program_name, db_name(r.database_id) AS [Database],
r.command, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time,
r.percent_complete, 
r.wait_type, r.blocking_session_id
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1

Of course we may want to include other bits of information and that’s the real beauty of using Dynamic Management Objects, they are extremely flexible. By adding just three more columns I can also see the read/write activity of any executing request, like in the query below:

当然，我们可能希望包含其他信息，这就是使用动态管理对象的真正好处，它们非常灵活。通过仅添加三列，我还可以看到任何执行请求的读/写活动，如下面的查询所示：

 
SELECT 
s.session_id, 
s.login_name, s.host_name, c.client_net_address, 
s.program_name, db_name(r.database_id) AS [Database],
r.command, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time,
r.percent_complete, 
r.wait_type, r.blocking_session_id,
r.reads, r.logical_reads, r.writes
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1

Having an understanding of Dynamic Management Objects is critical for DBAs and I always encourage people to read up on the information that they expose and how they relate to one another.

对动态管理对象的理解对于DBA至关重要，我总是鼓励人们阅读他们公开的信息以及它们之间的关系。

The previous article in this series

本系列的上一篇文章

Monitoring SQL Server with dynamic management objects – Sessions and connections 使用动态管理对象监视SQL Server –会话和连接

翻译自: https://www.sqlshack.com/monitoring-sql-server-with-dynamic-management-objects-requests/

sql server 监视

culuo4781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql server 监视_使用动态管理对象监视SQL Server –请求

sql server 监视 In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic ...
复制链接

扫一扫