This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Time ago, we wrote an article Lesson Learned #22: How to identify blocking issues? today,
I would like to enhance this topic by introducing a monitoring system that expands on that guide. This PowerShell script not only identifies blocking issues but also calculates the total, maximum, average, and minimum blocking times.
My idea is to run this PowerShell script, which executes T-SQL queries to identify blocking issues, showing the impact of the blocking and the blocking chains every 5 seconds. The script will save the details in a file for further review.
# Configure the connection string and folder for log file
$connectionString = "Server=tcp:servername.database.windows.net,1433;Database=dbname;User ID=username;Password=pwd!;Encrypt=true;Connection Timeout=30;"
$Folder = "c:\SQLDAta"
# Function to get and display blocking statistics
function Get-BlockingStatistics {
$query = "
select conn.session_id as blockerSession,
conn2.session_id as BlockedSession,
req.wait_time as Waiting_Time_ms,
cast((req.wait_time/1000.) as decimal(18,2)) as Waiting_Time_secs,
cast((req.wait_time/1000./60.) as decimal(18,2)) as Waiting_Time_mins,
t.text as BlockerQuery,
t2.text as BlockedQuery,
req.wait_type from sys.dm_exec_requests as req
inner join sys.dm_exec_connections as conn on req.blocking_session_id=conn.session_id
inner join sys.dm_exec_connections as conn2 on req.session_id=conn2.session_id
cross apply sys.dm_exec_sql_text(conn.most_recent_sql_handle) as t
cross apply sys.dm_exec_sql_text(conn2.most_recent_sql_handle) as t2
"
$connection = Connect-WithRetry -connectionString $connectionString -maxRetries 5 -initialDelaySeconds 2
if ($connection -ne $null)
{
$blockings = Execute-SqlQueryWithRetry -connection $connection -query $query -maxRetries 5 -initialDelaySeconds 2
$connection.Close()
}
if ($blockings.Count -gt 0) {
$totalBlockings = $blockings.Count
$maxWaitTime = $blockings | Measure-Object -Property WaitTimeSeconds -Maximum | Select-Object -ExpandProperty Maximum
$minWaitTime = $blockings | Measure-Object -Property WaitTimeSeconds -Minimum | Select-Object -ExpandProperty Minimum
$avgWaitTime = $blockings | Measure-Object -Property WaitTimeSeconds -Average | Select-Object -ExpandProperty Average
logMsg "Total blockings: $totalBlockings" (1)
logMsg "Maximum blocking time (seconds): $maxWaitTime" (2)
logMsg "Minimum blocking time (seconds): $minWaitTime" (2)
logMsg "Average blocking time (seconds): $avgWaitTime" (2)
logMsg "-- -- -- -- Blocking chain details: -- -- " (1)
foreach ($blocking in $blockings)
{
logMsg "Blocked Session ID: $($blocking.SessionId)"
logMsg "Wait Time (seconds): $($blocking.WaitTimeSeconds)"
logMsg "Blocker Session ID: $($blocking.BlockingSessionId)"
logMsg "Blocked SQL Text: $($blocking.SqlText)"
logMsg "Blocker SQL Text: $($blocking.BlockingSqlText)"
logMsg "---------------------------------------------"
}
} else {
logMsg "No blockings found at this time."
}
}
# Function to execute a SQL query with retry logic
function Execute-SqlQueryWithRetry {
param (
[System.Data.SqlClient.SqlConnection]$connection,
[string]$query,
[int]$maxRetries = 5,
[int]$initialDelaySeconds = 2
)
$attempt = 0
$success = $false
$blockings = @()
while (-not $success -and $attempt -lt $maxRetries) {
try {
$command = $connection.CreateCommand()
$command.CommandText = $query
$reader = $command.ExecuteReader()
while ($reader.Read()) {
$blockingE = New-Object PSObject -Property @{
SessionId = $reader["BlockedSession"]
WaitTimeSeconds = $reader["Waiting_Time_secs"]
BlockingSessionId = $reader["BlockerSession"]
SqlText = $reader["BlockedQuery"]
BlockingSqlText = $reader["BlockerQuery"]
}
$blockings+=$blockingE
}
$success = $true
} catch {
$attempt++
if ($attempt -lt $maxRetries) {
logMsg "Query execution attempt $attempt failed. Retrying in $initialDelaySeconds seconds..." 2
Start-Sleep -Seconds $initialDelaySeconds
$initialDelaySeconds *= 2 # Exponential backoff
} else {
logMsg "Query execution attempt $attempt failed. No more retries." 2
throw $_
}
}
}
return ,($blockings)
}
#--------------------------------
#Log the operations
#--------------------------------
function logMsg
{
Param
(
[Parameter(Mandatory=$true, Position=0)]
[string] $msg,
[Parameter(Mandatory=$false, Position=1)]
[int] $Color,
[Parameter(Mandatory=$false, Position=2)]
[boolean] $Show=$true,
[Parameter(Mandatory=$false, Position=3)]
[string] $sFileName,
[Parameter(Mandatory=$false, Position=4)]
[boolean] $bShowDate=$true,
[Parameter(Mandatory=$false, Position=5)]
[boolean] $bSaveOnLogFile=$true
)
try
{
if($bShowDate -eq $true)
{
$Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
$msg = $Fecha + " " + $msg
}
If( TestEmpty($SFileName) )
{
Write-Output $msg | Out-File -FilePath $LogFile -Append
}
else
{
Write-Output $msg | Out-File -FilePath $sFileName -Append
}
$Colores="White"
$BackGround =
If($Color -eq 1 )
{
$Colores ="Cyan"
}
If($Color -eq 3 )
{
$Colores ="Yellow"
}
if($Color -eq 2 -And $Show -eq $true)
{
Write-Host -ForegroundColor White -BackgroundColor Red $msg
}
else
{
if($Show -eq $true)
{
Write-Host -ForegroundColor $Colores $msg
}
}
}
catch
{
Write-Host $msg
}
}
#--------------------------------
#Validate Param
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
{
return $true;
}
else
{
return $false;
}
}
#--------------------------------------------------------------
#Create a folder
#--------------------------------------------------------------
Function CreateFolder
{
Param( [Parameter(Mandatory)]$Folder )
try
{
$FileExists = Test-Path $Folder
if($FileExists -eq $False)
{
$result = New-Item $Folder -type directory
if($result -eq $null)
{
logMsg("Imposible to create the folder " + $Folder) (2)
return $false
}
}
return $true
}
catch
{
return $false
}
}
function GiveMeFolderName([Parameter(Mandatory)]$FolderSalida)
{
try
{
$Pos = $FolderSalida.Substring($FolderSalida.Length-1,1)
If( $Pos -ne "\" )
{return $FolderSalida + "\"}
else
{return $FolderSalida}
}
catch
{
return $FolderSalida
}
}
#-------------------------------
#Create a folder
#-------------------------------
Function DeleteFile{
Param( [Parameter(Mandatory)]$FileName )
try
{
$FileExists = Test-Path $FileNAme
if($FileExists -eq $True)
{
Remove-Item -Path $FileName -Force
}
return $true
}
catch
{
return $false
}
}
# Function to connect to the database with retry logic
function Connect-WithRetry {
param (
[string]$connectionString,
[int]$maxRetries = 5,
[int]$initialDelaySeconds = 2
)
$attempt = 0
$connection = $null
while (-not $connection -and $attempt -lt $maxRetries) {
try {
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
} catch {
$attempt++
if ($attempt -lt $maxRetries) {
logMsg "Connection attempt $attempt failed. Retrying in $initialDelaySeconds seconds..." 2
Start-Sleep -Seconds $initialDelaySeconds
$initialDelaySeconds *= 2 # Exponential backoff
} else {
logMsg "Connection attempt $attempt failed. No more retries." 2
throw $_
}
}
}
return $connection
}
clear
$result = CreateFolder($Folder) #Creating the folder that we are going to have the results, log and zip.
If( $result -eq $false)
{
write-host "Was not possible to create the folder"
exit;
}
$sFolderV = GiveMeFolderName($Folder) #Creating a correct folder adding at the end \.
$LogFile = $sFolderV + "Blockings.Log" #Logging the operations.
logMsg("Deleting Operation Log file") (1)
$result = DeleteFile($LogFile) #Delete Log file
logMsg("Deleted Operation Log file") (1)
# Loop to run the monitoring every 5 seconds
while ($true) {
Clear-Host
Get-BlockingStatistics
Start-Sleep -Seconds 5
}
Please note that this script is provided as-is and without any warranty. Use it at your own risk. Always test scripts in a development environment before deploying them to production.
