In this article, we will talk about sp_WhoIsActive stored procedure and how we can use it to monitor currently running activities in SQL Server.

在本文中，我们将讨论sp_WhoIsActive存储过程以及如何使用它来监视SQL Server中当前正在运行的活动。

介绍 (Introduction)

Database administrators (DBAs) are incessantly checking currently running operations over an SQL Server instance especially when the server is slowing down.

数据库管理员（DBA）不断检查SQL Server实例上当前正在运行的操作，尤其是在服务器速度变慢时。

In general, Microsoft provided two system stored procedures called “sp_who” and “sp_who2” to retrieve all currently running processes on the instance but they lack much useful information that can facilitate the performance monitoring and analysis process, also they show much useless information (system processes).

通常，Microsoft提供了两个名为“ sp_who”和“ sp_who2”的系统存储过程来检索实例上所有当前正在运行的进程，但是它们缺少许多有用的信息来促进性能监视和分析过程，并且它们还显示了很多无用的信息（系统流程）。

For this reason, (a Microsoft MVP since 2004) developed a more powerful stored procedure called “” to fill in the gap between the actual needs of DBAs and the currently provided procedures (sp_who and sp_who2).

因此， （自2004年起成为Microsoft MVP）开发了一种功能更强大的存储过程，称为“ ”，以填补DBA实际需求与当前提供的过程（sp_who和sp_who2）之间的差距。

In the following sections, we will talk briefly about sp_who and sp_who2 stored procedure, then we will illustrate how to download and use sp_whoisactive stored procedure.

在以下各节中，我们将简要讨论sp_who和sp_who2存储过程，然后将说明如何下载和使用sp_whoisactive存储过程。

sp_Who和sp_Who2 (sp_Who and sp_Who2)

As we mentioned before, Microsoft provided sp_Who and so_Who2 stored procedures for activity monitoring in SQL Server. In this section, we will explain what is the information returned by each stored procedure and what are the differences between them.

如前所述，Microsoft提供了sp_Who和so_Who2存储过程来监视SQL Server中的活动。 在本节中，我们将解释每个存储过程返回的信息是什么以及它们之间的区别是什么。

As described in the , sp_who “provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.”

如中所述，sp_who “提供有关Microsoft SQL Server数据库引擎实例中当前用户，会话和进程的信息。 可以对信息进行过滤，以仅返回那些不是空闲的，属于特定用户或属于特定会话的进程。”

sp_who returns information such as the session process ID (SPID), the execution context ID (ECID), the process status, the blocking session ID, the database name, the login and hostname associated with this process, and the command type.

sp_who返回信息，例如会话进程ID（SPID），执行上下文ID（ECID），进程状态，阻塞会话ID，数据库名称，与此进程关联的登录名和主机名以及命令类型。

Figure – sp_who output

图– sp_who输出

sp_Who2 is similar to sp_Who but it is not documented nor supported but it returns more information and performance counter from the current processes such as the program name executing the command, Disk IO, CPU Time, last batch execution time.

sp_Who2与sp_Who相似，但未提供文档或支持，但它从当前进程返回更多信息和性能计数器，例如执行命令的程序名称，磁盘IO，CPU时间，最后一批执行时间。

Figure – sp_who2 output

图– sp_who2输出

As shown in the screenshots above, the output of these procedures is showing all system and user processes running which is not required all the time and the user can only filter using the login name or session ID while he may need to hide system processes. Also, the outputs don’t contain any information about the currently running SQL Command such as start execution time, execution duration, WAIT info and more information.

如上面的屏幕快照所示，这些过程的输出显示了并非一直都在运行的所有系统和用户进程，并且用户只能使用登录名或会话ID进行过滤，而他可能需要隐藏系统进程。 另外，输出不包含有关当前正在运行SQL命令的任何信息，例如开始执行时间，执行持续时间，WAIT信息和更多信息。

下载并安装sp_whoisactive (Download and install sp_whoisactive)

To download this procedure, you should go to the website and select the relevant release or you can do this from the .

要下载此过程，您应该转到网站并选择相关版本，或者您可以从执行此操作。

Once the download is completed, you should open who_is_active.sql file using SQL Server Management Studio and execute the script.

下载完成后，应使用SQL Server Management Studio打开who_is_active.sql文件并执行脚本。

使用sp_Whoisactive (Using sp_Whoisactive)

After installing the procedure, if we execute it, we can see that it only returns running user processes by default and provides the following information for each process:

安装该过程后，如果执行该过程，则可以看到它默认情况下仅返回正在运行的用户进程，并为每个进程提供以下信息：

Column	Description	Shown by sp_who	Shown by sp_who2
dd hh:mm:ss.mss	Process elapsed time	No	No
session_id	The process session id	Yes	Yes
sql_text	The currently running SQL command	No	No
login_name	The login name associated with the process	Yes	Yes
wait_info	The process wait information (aggregated)	No	Yes
CPU	The CPU time	No	Yes
tempdb_allocations	Number of Tempdb writes done	No	No
tempdb_current	Number of Tempdb pages currently allocated	No	No
blocking_session_id	The blocking session Id	Yes	Yes
reads	number of reads done	No	Disk IO
writes	number of writes done	No	Disk IO
physical reads	number of physical reads done	No	Disk IO
used_memory	the amount of memory used	No	No
status	The process status	Yes	Yes
open_tran_count	the number of transactions used	No	No
percent_complete	the query completion percentage	No	No
host_name	The host machine name	Yes	Yes
database_name	The database name where the query is executed	Yes	Yes
program_name	The application that executed the query	No	Yes
start_time	The process start time	No	Yes
login_time	The login time	No	No
request_id	The request Id	Yes	Yes
collection_time	The time that this last select was run	No	No

柱	描述	由sp_who显示	由sp_who2显示
dd hh：mm：ss.mss	处理时间	没有	没有
session_id	进程会话ID	是	是
sql_text	当前正在运行SQL命令	没有	没有
登录名	与流程关联的登录名	是	是
wait_info	进程等待信息（汇总）	没有	是
中央处理器	CPU时间	没有	是
tempdb_allocations	Tempdb完成写入的次数	没有	没有
tempdb_current	当前分配的Tempdb页面数	没有	没有
blocking_session_id	阻止会话ID	是	是
读	读取次数	没有	磁盘IO
写	完成的写入次数	没有	磁盘IO
物理阅读	完成的物理读取次数	没有	磁盘IO
used_memory	使用的内存量	没有	没有
状态	进程状态	是	是
open_tran_count	使用的交易数量	没有	没有
percent_complete	查询完成百分比	没有	没有
主机名	主机名	是	是
数据库名称	执行查询的数据库名称	是	是
程序名	执行查询的应用程序	没有	是
开始时间	流程开始时间	没有	是
login_time	登录时间	没有	没有
request_id	要求编号	是	是
collection_time	最后选择的运行时间	没有	没有

Figure – Procedure output part 1

图–过程输出第1部分

Figure – procedure output part 2

图–程序输出第2部分

As shown below, to show the system processes you should run the following command:

如下所示，要显示系统进程，应运行以下命令：

Exec sp_whoisactive @show_system_spids = 1

Figure – Showing system processes

图–显示系统进程

You can visualize more information from this procedure by passing additional parameters such as @get_additional_info, @get_locks, @get_avg_time, and other parameters.

您可以通过传递其他参数（例如，@ get_additional_info，@ get_locks，@ get_avg_time和其他参数）来可视化此过程中的更多信息。

One of the amazing thing about this procedure is that it is well documented and all related information can be obtained by executing the following command:

关于此过程，令人惊奇的事情之一是它有充分的文档记录，并且可以通过执行以下命令来获取所有相关信息：

Exec sp_whoisactive @help = 1

Figure – Procedure embedded documentation

图–过程嵌入式文档

As shown in the image above, the help command contains three sections:

如上图所示，help命令包含三个部分：

1. General information: where general information such as version, website, creator email are provided
2. 常规信息：提供诸如版本，网站，创建者电子邮件之类的常规信息的地方
3. Parameters description: where a list of all available parameters with their description is provided 参数说明：提供所有可用参数及其说明的列表
4. Output columns description: a full list of all available output columns with their descriptions 输出列描述：所有可用输出列及其描述的完整列表

保存历史数据 (Saving Historical data)

If we need to periodically save running processes information for further analysis, and since all output columns data types and name can be found within the procedure embedded documentation, we can create an SQL agent job that periodically executes sp_whoisactive procedure within an INSERT command such as:

如果我们需要定期保存运行中的进程信息以进行进一步分析，并且由于所有输出列的数据类型和名称都可以在过程嵌入式文档中找到，则可以创建一个SQL代理作业，该作业在INSERT命令中定期执行sp_whoisactive过程，例如：

Insert Into [dbo].[HistInformation] Exec sp_whoisactive

Then we can refer to the stored data later for further analysis.

然后，我们可以稍后参考存储的数据进行进一步分析。

资源资源 (Resources)

If you are looking to learn more about this stored procedure, you can refer to the following links:

如果您想了解有关此存储过程的更多信息，可以参考以下链接：

结论 (Conclusion)

In this article, we have described briefly sp_who, sp_who2 and sp_whoisactive procedures and we have explained why sp_whoisactive is more powerful and more needed for Database Administrators.

在本文中，我们简要介绍了sp_who，sp_who2和sp_whoisactive过程，并解释了为什么sp_whoisactive更强大，数据库管理员需要更多的功能。

翻译自: