This is the 20th day of my participation in the August More Text Challenge

Monitor activity using sp_WhoIsActive

The sp_WhoIsActive stored procedure can monitor the activities currently running in SQL Server.

introduce

If the Server is slow, the database administrator needs to constantly check what is running on the SQL Server instance.

System stored procedures “sp_WHO” and “sp_who2” can check all running processes on an instance, but they lack much useful information for performance monitoring and analysis processes, and show much useless information such as system processes.

To this end, Adam Machanic (Microsoft MVP since 2004) developed a more powerful stored procedure called “SP_whoisactive”, To fill the gap between the actual requirements of the DBA and the currently provided processes (SP_WHO and SP_WHO2).

Here isa brief introduction to sp_WHO and sp_who2 stored procedures, and how to use sp_whoisactive

Sp_Who and sp_Who2

Sp_who provides information about current users, sessions, and processes in a Microsoft SQL Server database engine instance. You can filter information to return only those processes that are not idle, belong to a particular user, or belong to a particular session.

Sp_who returns information such as spID-session process ID and ecID-execution Context ID ID), process status, blocking session ID(BLK), database name (DBname), loginame and hostname associated with this process, and cmD-command type.

BLK is the ID of the blocked session

Sp_Who2 is similar to sp_Who but is not documented or supported, but it returns more information and performance counters from the current process, For example, ProgramName (ProgramName), DiskIO (DiskIO- total disk read and write times), CPU time (CPUTime- total CPU running time), and LastBatch execution time (LastBatch- last time when a stored procedure was called or a query was executed).

As shown in the screenshot, the output shows all running systems and user processes that are not needed, and the results can only be filtered using loginName and sessionID. In practice, you may need to hide system processes.

In addition, the above output does not contain any information about the currently running SQL command, such as start execution time, execution duration, wait information, and so on.

Sp_Who and sp_Who2 return active, user, or SPID process information

  • Returns information about a user’s process

Information about the session process of the SA user is displayed as follows

exec sp_who 'sa';

-- exec sp_who2 'sa';
-- exec sp_who @loginame='sa';
Copy the code

  • Returns active process information
exec sp_who 'ACTIVE';

-- EXEC sp_who2 'active';  
Copy the code

  • Returns information about the specified process ID
exec sp_who2 '1';
exec sp_who2 1;

-- exec sp_who '1';
-- exec sp_who 1;
Copy the code

Save the sp_Who2 filtering result

Here is an example of saving the result of sp_Who2 to a table or table variable or temporary table. For filtering display

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE.Copy the code

Get similar information with sysProcesses:

SELECT  spid,
        sp.[status],
        loginame [Login],
        hostname, 
        blocked BlkBy,
        sd.name DBName, 
        cmd Command,
        cpu CPUTime,
        physical_io DiskIO,
        last_batch LastBatch,
        [program_name] ProgramName   
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid 
Copy the code

Download and install whoisActive stored procedure

You can download the stored procedure directly from the WhoisActive download page, or GitHub Repository.

The latest release is 2018 and applies to all versions of SQL Server2005+.

Download, unzip, and open the who_is_active. SQL file, execute in SSMS (who_IS_ACTIVE_v11_32.sql)

Using sp_Whoisactive

exec sp_Whoisactive;
Copy the code

After executing, you can see that by default only the running user processes are returned, with the following information for each process:

Compare sp_Whoisactive information with sp_WHO and sp_who2:

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(it doesn’t seem to)
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_Whoisactive Describes the output column information

Columns have been briefly described in the above comparison, but more on that below.

  • dd hh:mm:ss:mss: Displays the command duration (execution time). You can use this column to identify long-running transactions. For example, you can identify SQL Server sessions that take more than 1 hour to run.
    • It displays the query runtime for an active request.
    • During sleep sessions (sleeping session), it shows the time since the last batch was completed
  • Session_id: indicates the SP ID of a user session.
  • Sql_text: SQL text of the running session. It is a hyperlink, click on it and get the full T-SQL.
  • Login Name: is the Login name to connect to SQL Server and execute the SQL specified in the session
  • Wait_info: is a useful column to identify the current wait for the SPID, for exampleCXPACKET,ASYNC_NETWORK_IOAnd wait time. Please refer toSQL Server wait typeIn the article.
  • Tempdb_allocations and TEMPDB_current: You can use this column to get information about TempDB allocations if the query is using the TempDB database. For example, if we use a table variable or temporary table, which is created in TempDB, we can use these two columns to track information.
  • CPU: Gets the total CPU time consumed by the query.
  • Blocking_session_id: In the case of blocking, you can get the blocked session-ID from this column.
  • Reads and writes: gives the number of Reads and writes for the current query.
  • Open_tran_count: number of open transactions in the current session.
  • Percent complete: DMV can usually be usedsys.dm_exec_requestsChecks the percentage of complete status of a few commands, such as backup and restore database commands.Sp_WhoIsActiveUse the DMV information and display it as output.
  • Program Name: Indicates the name of the application that the user connects to, such as Microsoft SQL Server Management Studio — Query, Azure Data Studio, and SQL Server Agent.

For more detailed parameters, see SQL Server – Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more

Sp_whoisactive Displays system processes

Exec sp_whoisactive @show_system_spids = 1;
Copy the code

You can also use the @get_additional_info, @get_LOCKS, @get_AVg_time parameters to see more information.

The sp_Whoisactive@helpparameter

One of the more interesting features of sp_whoisactive is the @help parameter. All relevant information can be obtained by executing the following command:

Exec sp_whoisactive @help = 1;
Copy the code

The help command consists of three parts:

  1. General information: Provides the version, copyright, website, feedback email, URL and other general information
  2. Parameter Description: A list of all available parameters and their descriptions and default information is provided
  3. Output column Description: A complete list of all available output columns and their descriptions

Several optional parameters and usage of the sp_WhoIsActive procedure

@find_block_leadersView blocked and blocked sessions

You can set the @find_block_leaders parameter to 1 and sort the results of the blocks_session_count column to check for blocked and blocked session-sessions.

EXEC sp_WhoIsActive
    @find_block_leaders = 1.@sort_order = '[blocked_session_count] DESC';
Copy the code

As you can see from the output, the session with session_id 55 is blocking, followed by the two sessions it is blocking

@get_plansGets the execution plan being queried

Suppose we are investigating a performance problem in SQL Server and find a problematic query. It would be nice to have an execution plan to see the expensive operations involved in query execution.

At this point, you can specify the parameter @get_plans=1, which appends a column with an XML execution plan to the output.

EXEC sp_WhoIsActive @get_plans = 1;
Copy the code

The QUERy_PLAN column outputs the execution plan, which must be the query statement currently executing. If the SQL statement has been executed or no SQL statement is in progress, NULL is returned

Click query_Plan to see the execution plan for that query.

@get_locksGet detailed lock information

You can use this parameter to get an XML fragment that contains detailed information about the locks held in the SQL Server session. In the output, there is an additional LOCKS column. Click LOCKS, as shown below, to see detailed lock information in XML format.

EXEC sp_WhoIsActive @get_locks = 1;
Copy the code

@get_additional_infoGets additional session parameter information

We can set several session parameters that can affect query performance. The @get_additional_info parameter provided by Sp_WhoIsActive displays information about these parameters. An additional column will also be printed as additional_info.

EXEC sp_WhoIsActive @get_additional_info = 1;
Copy the code

Save sp_whoisactive historical data

To obtainsp_whoisactiveThe result table creation script

Sp_WhoIsActive provides the creation script for its result table, obtained directly with the output parameter @schema.

declare @HistTableName VARCHAR(MAX)= 'HistWhoIsActive';
declare @schema VARCHAR(MAX);
EXEC sp_WhoIsActive @get_transaction_info = 1.@get_outer_command = 1.@get_plans = 1.@return_schema = 1.@schema = @schema OUTPUT;

-- Output the script that creates the table consisting of 
      
        placeholders
      
select @schema;

Generate the script to create the table and perform the creation
EXEC (REPLACE(@schema.'<table_name>'.@destination_table));
Copy the code

Sp_whoisactive is scheduled in the agent job

If you want to periodically save information about running processes for further analysis, you can execute sp_whoisactive in the agent job and save it to a table.

Create a new job and put the following code into the job step, modifying the first three variables as needed. If it doesn’t, the code creates a logging table, and if it doesn’t, it creates a clustered index that records current activity and cleans up old data based on the @retention variable.

How often should you collect activities? The authors suggest that collecting sp_WhoIsActive data every 30-60 seconds is a good balance between recording enough activity to solve production problems and maintaining the required data storage in a very busy environment.

It can also be changed to a stored procedure to handle:

SET NOCOUNT ON;

DECLARE @retention INT = 7.@destination_table VARCHAR(500) = 'WhoIsActive'.@destination_database sysname = 'Crap'.@schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1.@get_outer_command = 1.@get_plans = 1.@return_schema = 1.@schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema.'<table_name>'.@destination_table);
        EXEC ( @schema );
    END;

--create index on collection_time
SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL.@parameters.@destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1.@get_outer_command = 1.@get_plans = 1.@destination_table = @destination_table;

--purge older data
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE()); ';
EXEC ( @SQL );
Copy the code

To collect information about sp_whoisactive, you can also use the Insert Into command, Insert Into

Exec sp_whoisactive; . The above method is still recommended.

How do I query the result of sp_whoisactive

The following is just a primer query. You can view information for a specific time period based on earlier user complaints about slow queries. For example, I (the original author) used the WhoIsActive log table to determine who was causing tempDB to become unusually large. When I found it, tempdb was 500GB in size! I switched my queries to include TEMPdb_allocations and Tempdb_current, and ORDER BY Tempdb_allocations DESC, so it was obvious that I knew everything the business user was querying.

SELECT TOP 1000 CPU, reads, collection_time, start_time, [dd hh:mm:ss.mss] AS 'run duration', [program_name], login_name, database_name, session_id, blocking_session_id, wait_info, sql_text, *
FROM WhoIsActive
WHERE collection_time BETWEEN 'the 2016-07-20 07:55:00. 000' AND 'the 2016-07-20 09:00:00. 000'
AND login_name NOT IN ('DomainName\sqlservice')
--AND CAST(sql_text AS varchar(max)) LIKE '%some query%'
ORDER BY 1 DESC
Copy the code

How to use sp_WhoIsActive to find SLOW SQL Server queries

You are advised to refer to How to Use sp_WhoIsActive to Find Slow SQL Server Queries. The script tool is available for download. , and a video tutorial (Qiang required)

Dbas need to be able to quickly find which queries and stored procedures are running slowly.

Sp_WhoIsActive has various parameters, but remember that the more parameters you pass in, the more work sp_WhoIsActive has to do to get the desired data from SQL Server’s dynamic management view (DMV), and the slower it will run.

Here are a few useful parameters:

  • @get_plans = 1– Provides an execution plan for running queries.
  • @get_locks = 1– Provides an XML fragment that you can click to see which table, row, object, and other locks each query has. This is useful when trying to figure out why one query is preventing other queries from executing.
  • @get_task_info = 2– If a query is running in parallel and you are running aCXPACKET wait (CXPACKET waits) to find out what each task in the query is waiting for.

Refer to the sp_WhoIsActive Waiting Resource and sp_WhoIsActive official documentation for the session process that finds waiting resources (for CXPACKET)

reference

Read Adam’s posts about sp_WhoIsActive for more advanced features.

Sp_WhoIsActive also has a corresponding extension in Azure Data Studio

  • How to Log Activity Using sp_whoisactive
  • Monitoring activities using sp_WhoIsActive in SQL Server
  • An overview of the sp_WhoIsActive stored procedure