sql server 监视_使用动态管理对象监视SQL Server –会话和连接

sql server 监视

A fundamental task of Database Administrators is monitoring SQL Server performance. Whilst SQL Server does give us a user interface inside management studio that enables us to view current activity (in the rather aptly named Activity Monitor) this article is going to focus on querying Dynamic Management Objects with T-SQL to return various pieces of useful process information that we can use to monitor server usage.

数据库管理员的基本任务是监视SQL Server性能。 尽管SQL Server确实为我们提供了Management Studio内部的用户界面，使我们能够查看当前活动（在相当恰当的名称中称为“活动监视器”），但本文将重点介绍使用T-SQL查询动态管理对象以返回各种有用过程的过程。我们可以用来监视服务器使用情况的信息。

To begin with we’ll perform a very simple select statement on a DMV that is used to return information on the current authenticated sessions within our instance.

首先，我们将在DMV上执行一个非常简单的select语句，该语句用于返回实例中当前经过身份验证的会话的信息。

 
SELECT * FROM sys.dm_exec_sessions s
 

Note that I have given the DMV an alias of ‘s’ which will help simplify our queries when we come to join to other objects.

请注意，我给DMV加上了“ s”的别名，当我们加入其他对象时，这将有助于简化查询。

This returns a lot of information in terms of both columns and rows so we’ll focus on reducing the result set returned to essentially ignore any rows that relate to internal system tasks.

这将返回有关列和行的大量信息，因此我们将集中精力减少返回的结果集，从而基本上忽略与内部系统任务相关的任何行。

We can achieve this by adding a very simple where clause to our query:

我们可以通过在查询中添加一个非常简单的where子句来实现此目的：

The first result set contained mainly NULL column values for the host, program and client columns which is indicative of system sessions. The second image on the other hand contains the actual values where we can see the originating machine of any active sessions (host_name) and what application is being used (program_name) as well as the unique session ID’s and login time of the session that were available for system tasks also.

第一个结果集主要包含主机，程序和客户端列的NULL列值，这些值指示系统会话。 另一方面，第二个图像包含实际值，在这里我们可以看到任何活动会话的始发计算机（host_name）和正在使用的应用程序（program_name）以及可用的唯一会话ID和会话的登录时间。用于系统任务。

At this point it is worth mentioning a common misconception about session ID’s. In my second result set we can see session IDs associated with user processes are taking up the numbers 51 to 54. The misconception is that any ID above 50 indicates a user process which actually isn’t quite the case and whilst you’ll never find user processes using an ID that is 50 or below system processes can and often will use session IDs over 50. This is why we use a where clause based on the is_user_process column being equal to 1 rather than use a session_id > 50 predicate.

在这一点上，值得一提的是关于会话ID的常见误解。 在我的第二个结果集中，我们可以看到与用户进程相关的会话ID占用了51到54之间的数字。误解是，任何大于50的ID都表示用户进程实际上并非如此，尽管您永远找不到用户进程使用的ID等于或小于50的系统进程，并且通常会使用50以上的会话ID。这就是为什么我们基于is_user_process列等于1而不使用session_id> 50谓词来使用where子句的原因。

Now that we’ve appropriately limited the result set we need to be a bit more selective on our columns rather than return every single one. At this point, if you haven’t already done so, it’s worth scrolling through the results to see what other information column we can start to use, for example, we can return information that focused on the sessions user details with the following query:

现在我们已经适当地限制了结果集，我们需要对列进行更多选择，而不是返回每个单独的列。 在这一点上，如果您还没有这样做，那么值得在结果中滚动查看我们可以开始使用的其他信息列，例如，我们可以通过以下查询返回专注于会话用户详细信息的信息：

 
SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
 

Whilst this does show who has sessions on our instance the information is still very limited. Let’s begin adding to our query by using the database ID column in the sessions view to our query; on its own it might not be very meaningful to us if it’s a system we’re not familiar with so we can utilise a function named DB_NAME that allows us to pass in the database ID and have the corresponding database name returned to us.

尽管这确实表明谁在我们的实例上进行了会话，但是信息仍然非常有限。 让我们开始使用会话视图中的数据库ID列添加到查询中； 如果它是一个我们不熟悉的系统，那么它本身对我们可能没有什么意义，因此我们可以利用一个名为DB_NAME的函数，该函数允许我们传递数据库ID并返回给我们相应的数据库名称。

 
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] -- return the ID & database name
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
 

Here’s the database related columns returned on my test box:

这是在我的测试框上返回的与数据库相关的列：

We’ll now start looking at additional session information, including current status and values that relate to their associated workloads. We will still stay with the sys.dm_exec_sessions view and return some additional columns using the query below:

现在，我们将开始查看其他会话信息，包括与它们相关的工作负载相关的当前状态和值。 我们仍将保留sys.dm_exec_sessions视图，并使用以下查询返回一些其他列：

 
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], -- return the database name
s.status,
s.reads, s.writes, s.logical_reads
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
 

I’ve added two lines; one to return the current status of each session and another that returns read/write information.

我增加了两行； 一个返回每个会话的当前状态，另一个返回读/写信息。

The status column can be one of four values, running or sleeping as in the image above that tells us if the session is active or not running. It can also be dormant, meaning the session has been reset or it can have a status of preconnect which relates to the Resource Governor.

状态栏可以是四个值之一，可以运行或Hibernate，如上图所示，它告诉我们会话是否处于活动状态。 它也可以处于Hibernate状态，这意味着会话已被重置，或者其状态可能与资源调控器有关。

We can also view the related workload of each session by noting the read/write values. The read column indicates reads that have come from disk, which is substantially slower than reads from cache indicated by the logical_reads column.

我们还可以通过记录读/写值来查看每个会话的相关工作量。 读取列指示来自磁盘的读取，这比逻辑_读取列指示的来自缓存的读取要慢得多。

The writes column indicates write operations (surprise!), it is worth pointing out that the values any of the reads and writes columns do not correspond to table rows but database pages. If we want row information we can add the row_count column from the sys.dm_exec_sessions DMV to return that particular piece of information:

writes列指示写操作（惊奇！），值得指出的是，任何read和write列的值都不对应于表行，而对应于数据库页。 如果需要行信息，可以从sys.dm_exec_sessions DMV中添加row_count列以返回该特定信息：

 
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], -- return the database name
s.status,
s.reads, s.writes, s.logical_reads, s.row_count – added row_count
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
 

In the image above I’ve highlighted the row containing the session information for the very same query that we are executing. Note the row_count of 4 which is the same result set that we are working with and also note that the row status is running meaning the session has effectively been captured mid-execution.

在上图中，我突出显示了包含与我们正在执行的查询相同的会话信息的行。 请注意row_count为4，这与我们正在使用的结果集相同，并且还注意到行状态正在运行，这意味着会话已在执行中有效捕获。

We could also modify our where clause to use a system function named @@SPID to only view information that relates to our specific query:

我们还可以修改where子句以使用名为@@ SPID的系统函数来仅查看与特定查询有关的信息：

 
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
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1 
AND s.session_id = @@SPID -- just return info for our query
 

In the opposite way, we can use the function to exclude our query from the results as we would be focusing on other processes that are running on the instance:

相反，我们可以使用该函数从结果中排除查询，因为我们将专注于实例上正在运行的其他进程：

 
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
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1 
AND s.session_id <> @@SPID -- ignore our query completely
 

At this point we will return information that is contained outside of the sys.dm_exec_sessions DMV and start to join another Management View to see what other information we can add to our query results, we’ll do so by first running this simple select statement:

此时，我们将返回sys.dm_exec_sessions DMV外部包含的信息，并开始加入另一个管理视图，以查看可以添加到查询结果中的其他信息，我们将首先运行以下简单的select语句：

 
SELECT * FROM sys.dm_exec_connections
 

As the name implies, this DMV returns information on the established physical connections to our SQL Server instance. We could use a query similar to the one below to look at a more limited result set.

顾名思义，此DMV返回有关已建立的与SQL Server实例的物理连接的信息。 我们可以使用类似于以下查询的查询来查看更有限的结果集。

 
SELECT c.session_id, c.net_transport, c.protocol_type, 
c.client_net_address, c.client_tcp_port
FROM sys.dm_exec_connections c
 

This is the result set on my test instance, as I’m querying from the same machine we don’t see information like client IP or port details and being local the net_transport column is using Shared memory rather than TCPIP or Named Pipes. Unlike the sys.dm_exec_sessions DMV the connections view does not contain rows that correspond to internal system processes.

这是测试实例上的结果集，因为我正在同一台计算机上查询，因此看不到诸如客户端IP或端口详细信息之类的信息，并且位于本地的net_transport列使用共享内存而不是TCPIP或命名管道。 与sys.dm_exec_sessions DMV不同，连接视图不包含与内部系统进程相对应的行。

Of course at this point we have two completely separate queries, one for our session related information and another for physical connections. To bring them together we can perform an inner join using the session ID column which importantly exists in both views to merge our result sets:

当然，在这一点上，我们有两个完全独立的查询，一个用于与会话相关的信息，另一个用于物理连接。 为了将它们组合在一起，我们可以使用会话ID列执行内部联接，这在两个视图中都非常重要，以合并我们的结果集：

 
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], -- return the database name
s.status,
s.reads, s.writes, s.logical_reads, s.row_count
FROM sys.dm_exec_sessions s
 
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
 
WHERE s.is_user_process = 1 
AND s.session_id
 

Our query is now joining matching session IDs between the two DMV’s, just as we would tables (remember that a view is essentially a virtualised table) and we can now add columns from the connections DMV into our select query:

现在，我们的查询正在联接两个DMV之间的匹配会话ID，就像我们要创建表一样（请记住，视图本质上是一个虚拟表），现在我们可以将来自连接DMV的列添加到我们的select查询中：

 
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
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
WHERE s.is_user_process = 1
 

Returned information from both session and connection DMVs:

从会话和连接DMV返回的信息：

Please note there is a one to one relationship between connections and sessions and the corresponding management views.

请注意，连接和会话以及相应的管理视图之间存在一对一的关系。

This is now an ideal point to highlight the aliases that have been used in the query. In our query we are using the aliases s and c for the session and connection Management Views so let’s modify our select statement and remove the alias for session ID and all the other columns so we’re left with the following:

现在，这是突出显示查询中使用的别名的理想点。 在我们的查询中，我们为会话和连接管理视图使用别名s和c，因此让我们修改select语句，并删除会话ID和所有其他列的别名，以便保留以下内容：

 
SELECT session_id
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
WHERE s.is_user_process = 1
 

If we try to run the query we’ll get the following error:

如果我们尝试运行查询，将会得到以下错误：

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

消息209，第16级，状态1，第1行
列名“ session_id”不明确。

This is because the session_id column exists in both the session and connection DMVs so the engine is telling us that it simply doesn’t know which one to return, sessions or connection session_id.

这是因为session_id列既存在于会话DMV中，也存在于连接DMV中，因此引擎告诉我们，它根本不知道返回哪个会话或连接session_id。

In this case the alias allows us to very specify which DMV the column should come from. As we are joining on exact matches on the same column the value won’t be different whether we specify the session ID from the session or the connection DMV as demonstrated below (note the column alias I’ve used to differentiate between the two DMV columns).

在这种情况下，别名使我们可以非常指定列应来自哪个DMV。 当我们在同一列上加入完全匹配项时，无论是从会话中指定会话ID还是从连接DMV中指定值，值都不会不同，如下所示（请注意，我用来区分两个DMV列的列别名） ）。

 
SELECT s.session_id AS SessionID, 
c.session_id AS ConnectionSessionID, 
s.is_user_process, s.login_time 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
WHERE s.is_user_process = 1
 

The alias has proved to be useful for session ID because the values represent the very same thing in both the sessions and connections view and it’s certainly common to find columns in different DMVs that are duplicated, that’s really for ease of querying.

事实证明，别名对于会话ID很有用，因为这些值在会话和连接视图中都表示相同的事物，并且在重复的不同DMV中查找列当然很常见，这确实是为了便于查询。

The final demonstration is to show two columns in our DMVs that although named differently could be confused to mean the same thing, when actually they are very different.

最后的演示是在我们的DMV中显示两列，尽管它们的名称不同，但实际上却大相径庭，但它们可能会混淆意思相同的事物。

Remember in the sys.dm_exec_sessions DMV we looked at reads and writes of all the sessions on our instance? Well, the sys.dm_exec_connections DMV also has read and write columns (num_reads and num_writes) and we might be tempted to think that by querying both columns we’d get exactly the same results.

还记得在sys.dm_exec_sessions DMV中我们查看了实例上所有会话的读写吗？ 好吧，sys.dm_exec_connections DMV也具有读写列（num_reads和num_writes），我们可能会想通过查询两个列来获得完全相同的结果。

 
SELECT s.session_id AS SessionID, 
c.session_id AS ConnectionSessionID, 
s.writes AS SessionWrites, c.num_writes AS ConnectionWrites 
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
WHERE s.is_user_process = 1
 

Again, using column aliases I can view writes from the sessions view (SessionWrites) and the corresponding writes from the connections view (ConnectionWrites) side by side, here’s the output:

同样，使用列别名，我可以并排查看来自会话视图（SessionWrites）的写入和来自连接视图（ConnectionWrites）的相应写入，这是输出：

They’re quite different and actually, it’s not that surprising why because when we look up the DMV information on Books Online the definition of the columns are very different:

它们是完全不同的，实际上，这并不奇怪，因为当我们在联机丛书中查找DMV信息时，各列的定义非常不同：

SYS.DM_EXEC_SESSIONS

SYS.DM_EXEC_SESSIONS

writes

bigint

Number of writes performed, by requests in this session, during this session. Is not nullable.

写

比金特

在此会话期间，由该会话中的请求执行的写入次数。 不可为空。

SYS.DM_EXEC_CONNECTIONS

SYS.DM_EXEC_CONNECTIONS

num_writes

int

Number of data packet writes that have occurred over this connection. Is nullable.

num_writes

整型

通过此连接发生的数据包写入次数。 可为空。

The sessions view is based around page writes, but the connections view isn’t based on page level information at all but packets, which makes perfect sense as we’re looking at purely connection related information.

会话视图基于页面写入，但是连接视图完全不是基于页面级别的信息，而是基于数据包，这在我们只考虑与连接相关的信息时非常有意义。

This highlights two things, the first is the depth of information that we can retrieve from DMVs but secondly, we need to ensure we understand both what information the DMV is based on and what the columns mean, like many things in SQL an assumption will often be incorrect.

这强调了两点，首先是我们可以从DMV中检索到的信息的深度，但是其次，我们需要确保我们了解DMV所基于的信息以及列的含义，就像SQL中的许多假设一样是不正确的。

So for our query, if we wanted to add both columns in our monitoring script and cover network information then there is nothing stopping us!

因此，对于我们的查询，如果我们想在监视脚本中同时添加两列并涵盖网络信息，那么没有什么可以阻止我们！

 
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], -- return the database name
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 – return packet writes
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id 
WHERE s.is_user_process = 1
 

Whilst still relatively straight forward this query has introduced the functionality that Dynamic Management Views provide, especially for monitoring purposes. We’ve utilised some fundamental T-SQL concepts such as Joins, Functions and Aliases to bring back some incredibly useful information that we can use for checking activity on our instance.

尽管此查询仍然相对简单，但它引入了动态管理视图提供的功能，尤其是出于监视目的。 我们利用了一些基本的T-SQL概念，例如联接，函数和别名，以带回一些非常有用的信息，这些信息可用于检查实例上的活动。

The previous article in this series:

本系列的上一篇文章：

• Monitoring SQL Server with Dynamic Management Objects – Requests使用动态管理对象监视SQL Server –请求

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

sql server 监视