WhoIsActive Runner

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.

CodingSight - sp_WhoIsActive

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.

Initial Considerations

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.
    • You can choose to deploy it to another database. Just ensure the necessary modifications within the script.
  • 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.

Using 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\INSTANCE

I’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.

PowerShell ISE Script

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:

login type that requires to enter credentials
SP exists in the master database

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:

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.

Search String prompt

After that, you must pick how much time you want to allow for the script execution.

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.

WAITFOR DELAY
String WAITFOR

After the script completes its execution, you will see a .txt file that contains the name of your instance and WhoIsActive as a suffix.

.txt file that contains the name of your instance

Here’s a sample of what the script captured and saved in that .txt file:

 sample of what the script captured and saved in that .txt file

Complete Code of the PowerShell Script

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.

Alejandro Cobar
Latest posts by Alejandro Cobar (see all)

Alejandro Cobar

Alejandro Cobar is a multi-platform DBA (with a stronger focus in SQL Server), with a passion to automate stuff as much as possible to make things work for the greater good. He began his professional journey as a developer (a bit more than 10 years ago) and has been going back and forth with DBA roles as well, being the latter the one he has mostly developed throughout the years. He also has several Microsoft SQL Server Certifications that have helped him go even further on his skills as a SQL Server Database Administrator.

Leave a Reply

Your email address will not be published. Required fields are marked *