Nowadays, within the SQL Server DBA community, it is extremely likely that we use, or at the very least have heard of, the famous stored procedure sp_WhoIsActive developed by Adam Machanic.
During my time as a DBA, I used the SP to immediately check what’s going on inside a particular SQL Server instance when it is getting all the “finger-pointing” that a particular application is running slow.
However, there are occasions where such issues become recurring and require a way to capture what’s going on to find a potential culprit. There are also scenarios where you have several instances serving as the backend for 3rd party applications. The Stored Procedure could work potentially well to find our culprits.
In this article, I will present a PowerShell tool that can help any SQL Server DBA to collect queries detected by sp_WhoIsActive inside a particular SQL Server instance. That SP would match them to a certain Search String and store them in an output file for post-analysis.
What is sp_whoisactive?
sp_whoisactive is a popular SQL Server stored procedure that provides a real-time view of the current SQL Server activity. It’s often used for troubleshooting and performance tuning purposes.
When executed, sp_whoisactive retrieves information about the currently executing queries, blocking processes, and other relevant activity on the SQL Server instance. The information is then returned as a table of results, which can be filtered and sorted to help you identify and diagnose issues.
Here are some of the key columns that are included in the sp_whoisactive result set:
- session_id: The ID of the SQL Server session that’s associated with the activity.
- request_id: The ID of the currently executing request within the session.
- start_time: The time when the activity began.
- status: The current status of the request (e.g. running, sleeping, suspended, etc.).
- command: The type of SQL command that’s being executed (e.g. SELECT, INSERT, UPDATE, etc.).
- wait_type: The type of wait that the request is currently experiencing (if any).
- blocking_session_id: The ID of any blocking session that’s preventing the request from completing.
You can customize the sp_whoisactive output by passing various input parameters, such as @filter_type, @filter, and @show_sleeping_spids, among others. These allow you to filter the output based on various criteria, such as session ID, login name, database name, and more.
Overall, sp_whoisactive is a powerful tool for monitoring and diagnosing SQL Server activity in real-time. It’s widely used by database administrators and developers, and it can be a valuable addition to any SQL Server developer’s toolkit.
Prerequisites to Keep in Mind Before Using sp_whoisactive
Here are some assumptions before diving into the details of the script:
- The script receives the name of the instance as a parameter. If none is passed, localhost will be assumed by the script.
- The script will ask you for a particular search string to compare it to the texts of queries executed in the SQL Server instance. If there is a match with any of them, it will be stored in a .txt file that you can analyze later.
- The output file with all the information related to your instance is generated for the exact path where the PowerShell is located and triggered. Make sure that you have the write permissions there.
- If you execute the PowerShell script multiple times for the same instance, any previously existing output files will be overwritten. Only the most recent one will be kept. Therefore, if you need to keep a very specific file, save it somewhere else manually.
- The bundle includes a .sql file with the code to deploy the WhoIsActive Stored Procedure to the master database of the instance that you specify. The script checks if the stored procedure already exists in the instance and creates it if it doesn’t.
Download sp_whoisactive
You can choose to deploy it to another database. Just ensure the necessary modifications within the script.
Download this .sql file from safe hosting.
The script will attempt to fetch the information from the SQL Server instance every 10 seconds by default. But if you want to use a different value, adjust it accordingly.
Make sure that the user applied to connect to the SQL Server instance has permissions to create and execute the Stored Procedures. Otherwise, it will fail to accomplish its purpose.
How to use sp_whoisactive in SQL Server sing the PowerShell Script
Here’s what you can expect from the script:
Go to the location where you have put the PowerShell script file and run it like this:
PS C:\temp> .\WhoIsActive-Runner.ps1 SERVER\INSTANCEI’m using C:\temp as an example
The only thing the script will ask you is the type of login you want to use to connect to the instance.
Note: If you use PowerShell ISE, then the prompts will look like screenshots. If you run it directly from the PowerShell console, then the options will be prompted as text within the same window.
Trusted – the connection to the SQL Server instance will be made with the same user as for the execution of the PowerShell script. You don’t have to specify any credentials, it will assume them based on the context.
Windows Login – you must provide a Windows login for the correct authentication.
SQL Login – you must provide an SQL login for the correct authentication.
No matter which option you choose, make sure that it has enough privileges in the instance to perform checks.
If you choose the login type that requires you to enter credentials, the script will notify you in case of failure:
With the correct information specified, the script will check if the SP exists in the master database and proceeds to create it if it doesn’t.
Make sure that the .sql file with the T-SQL code to create the SP is located at the same path where the script is located. The .sql file name must be sp_WhoIsActive.sql.
If you want to use a different .sql filename and a different target database, ensure the necessary modifications inside the PowerShell script:
The next step will be the Search String prompt. You have to enter it to collect any matches returned by each execution iteration of the Stored Procedure inside the SQL Server instance.
After that, you must pick how much time you want to allow for the script execution.
For demonstration purposes, I’m going to choose option #1 (5 mins). I will leave a dummy query running in my instance. The query is WAITFOR DELAY ’00:10′. I’m going to specify the Search String WAITFOR so that you can get a sense of what the script will do for you.
After the script completes its execution, you will see a .txt file that contains the name of your instance and WhoIsActive as a suffix.
Here’s a sample of what the script captured and saved in that .txt file:
Complete Code of the PowerShell Script to run sp_whoisactive
If you want to try this script, please use the below code:
param(
$instance = "localhost"
)
if (!(Get-Module -ListAvailable -Name "SQLPS")) {
Write-Host -BackgroundColor Red -ForegroundColor White "Module Invoke-Sqlcmd is not loaded"
exit
}
#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted,[string]$username,[string]$password){
if($trusted -eq 1){
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0
}
catch{
Write-Host -BackgroundColor Red -ForegroundColor White $_
exit
}
}
else{
try{
Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop -ConnectionTimeout 5 -QueryTimeout 0
}
catch{
Write-Host -BackgroundColor Red -ForegroundColor White $_
exit
}
}
}
function Get-Property([string]$property,[string]$instance){
Write-Host -NoNewline "$($property) "
Write-Host @greenCheck
Write-Host ""
switch($loginChoice){
0 {$output = Execute-Query "SELECT SERVERPROPERTY('$($property)')" "master" $instance 1 "" ""}
default {$output = Execute-Query "SELECT SERVERPROPERTY('$($property)')" "master" $instance 0 $login $password}
}
switch($property){
"EngineEdition"{
switch($output[0]){
1 {"$($property): Personal or Desktop Engine" | Out-File -FilePath $filePath -Append}
2 {"$($property): Standard" | Out-File -FilePath $filePath -Append}
3 {"$($property): Enterprise" | Out-File -FilePath $filePath -Append}
4 {"$($property): Express" | Out-File -FilePath $filePath -Append}
5 {"$($property): SQL Database" | Out-File -FilePath $filePath -Append}
6 {"$($property): Microsoft Azure Synapse Analytics" | Out-File -FilePath $filePath -Append}
8 {"$($property): Azure SQL Managed Instance" | Out-File -FilePath $filePath -Append}
9 {"$($property): Azure SQL Edge" | Out-File -FilePath $filePath -Append}
11{"$($property): Azure Synapse serverless SQL pool" | Out-File -FilePath $filePath -Append}
}
}
"HadrManagerStatus"{
switch($output[0]){
0 {"$($property): Not started, pending communication." | Out-File -FilePath $filePath -Append}
1 {"$($property): Started and running." | Out-File -FilePath $filePath -Append}
2 {"$($property): Not started and failed." | Out-File -FilePath $filePath -Append}
default {"$($property): Input is not valid, an error, or not applicable." | Out-File -FilePath $filePath -Append}
}
}
"IsIntegratedSecurityOnly"{
switch($output[0]){
1{"$($property): Integrated security (Windows Authentication)" | Out-File -FilePath $filePath -Append}
0{"$($property): Not integrated security. (Both Windows Authentication and SQL Server Authentication.)" | Out-File -FilePath $filePath -Append}
}
}
default{
if($output[0] -isnot [DBNull]){
"$($property): $($output[0])" | Out-File -FilePath $filePath -Append
}else{
"$($property): N/A" | Out-File -FilePath $filePath -Append
}
}
}
return
}
$filePath = ".\$($instance.replace('\','_'))_WhoIsActive.txt"
Remove-Item $filePath -ErrorAction Ignore
$loginChoices = [System.Management.Automation.Host.ChoiceDescription[]] @("&Trusted", "&Windows Login", "&SQL Login")
$loginChoice = $host.UI.PromptForChoice('', 'Choose login type for instance', $loginChoices, 0)
switch($loginChoice)
{
1 {
$login = Read-Host -Prompt "Enter Windows Login"
$securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
$password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
}
2 {
$login = Read-Host -Prompt "Enter SQL Login"
$securePassword = Read-Host -Prompt "Enter Password" -AsSecureString
$password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
}
}
#Attempt to connect to the SQL Server instance using the information provided by the user
try{
switch($loginChoice){
0{
$spExists = Execute-Query "SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = 'sp_WhoIsActive'" "master" $instance 1 "" ""
if($spExists[0] -eq 0){
Write-Host "The Stored Procedure doesn't exist in the master database."
Write-Host "Attempting its creation..."
try{
Invoke-Sqlcmd -ServerInstance $instance -Database "master" -InputFile .\sp_WhoIsActive.sql
Write-Host -BackgroundColor Green -ForegroundColor White "Success!"
}
catch{
Write-Host -BackgroundColor Red -ForegroundColor White $_
exit
}
}
}
default{
$spExists = Execute-Query "SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = 'sp_WhoIsActive'" "master" $instance 0 $login $password
if($spExists[0] -eq 0){
Write-Host "The Stored Procedure doesn't exist in the master database."
Write-Host "Attempting its creation..."
try{
Invoke-Sqlcmd -ServerInstance $instance -Database "master" -Username $login -Password $password -InputFile .\sp_WhoIsActive.sql
Write-Host -BackgroundColor Green -ForegroundColor White "Success!"
}
catch{
Write-Host -BackgroundColor Red -ForegroundColor White $_
exit
}
}
}
}
}
catch{
Write-Host -BackgroundColor Red -ForegroundColor White $_
exit
}
#If the connection succeeds, then proceed with the retrieval of the configuration for the instance
Write-Host " _______ _______ _______ _________ _______ _______ _______ __________________ _______ "
Write-Host "( ____ \( ____ ) |\ /||\ /|( ___ )\__ __/( ____ \( ___ )( ____ \\__ __/\__ __/|\ /|( ____ \"
Write-Host "| ( \/| ( )| | ) ( || ) ( || ( ) | ) ( | ( \/| ( ) || ( \/ ) ( ) ( | ) ( || ( \/"
Write-Host "| (_____ | (____)| _____ | | _ | || (___) || | | | | | | (_____ | (___) || | | | | | | | | || (__ "
Write-Host "(_____ )| _____)(_____)| |( )| || ___ || | | | | | (_____ )| ___ || | | | | | ( ( ) )| __) "
Write-Host " ) || ( | || || || ( ) || | | | | | ) || ( ) || | | | | | \ \_/ / | ( "
Write-Host "/\____) || ) | () () || ) ( || (___) |___) (___/\____) || ) ( || (____/\ | | ___) (___ \ / | (____/\"
Write-Host "\_______)|/ (_______)|/ \|(_______)\_______/\_______)|/ \|(_______/ )_( \_______/ \_/ (_______/"
Write-Host ""
$searchString = Read-Host "Enter string to lookup"
$timerChoices = [System.Management.Automation.Host.ChoiceDescription[]] @("&1)5m", "&2)10m", "&3)15m","&4)30m","&5)Indefinitely")
$timerChoice = $host.UI.PromptForChoice('', 'How long should the script run?', $timerChoices, 0)
Write-Host -NoNewline "Script will run "
switch($timerChoice){
0{
Write-Host "for 5 minutes."
$limit = 5
}
1{
Write-Host "for 10 minutes."
$limit = 10
}
2{
Write-Host "for 15 minutes."
$limit = 15
}
3{
Write-Host "for 30 minutes."
$limit = 30
}
4{
Write-Host "indefinitely (press ctrl-c to exit)."
$limit = 2000000
}
}
Write-Host "Start TimeStamp: $(Get-Date)"
$StopWatch = [system.diagnostics.stopwatch]::StartNew()
while($StopWatch.Elapsed.TotalMinutes -lt $limit){
$results = Execute-Query "EXEC sp_WhoIsActive" "master" $instance 1 "" ""
Get-Date | Out-File -FilePath $filePath -Append
"####################################################################" | Out-File -FilePath $filePath -Append
foreach($result in $results){
if($result.sql_text -match $searchString){
$result | Out-File -FilePath $filePath -Append
}
"####################################################################" | Out-File -FilePath $filePath -Append
}
Start-Sleep -s 10
}
Get-Date | Out-File -FilePath $filePath -Append
"####################################################################" | Out-File -FilePath $filePath -Append
Write-Host "End TimeStamp : $(Get-Date)"
Conclusion
Let’s keep in mind that WhoIsActive won’t capture queries that are executed very fast by the DB Engine. However, the spirit of this tool is to detect those problematic queries that are slow and could benefit from an optimization round (or rounds).
You might argue that a Profiler trace or an Extended Event session could accomplish the same thing. However, I find it very convenient that you can simply fire up several PowerShell windows and execute each against different instances at the same time. It is something that could turn out to be a bit tedious for multiple instances.
By using this as a stepping stone, you could go a bit further and configure an alerting mechanism to get notified about any occurrence detected by the script for any query that has been running for more than X amount of minutes.
Tags: sql server, stored procedure Last modified: April 10, 2023
