Lesson Learned #259: Capturing all blocking TSQL in Azure SQL DB and Managed Instance

Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one.

First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.

Based on this article in our blog Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub I developed the following script that you could capture this information.

Param($DatabaseServer = "servername.database.windows.net",

$Database = "DatabaseName",

$Username = "UserName",

$password = "Password" ,

$NumberExecutions ="30",

$WaitTimeEveryInteraction = 5,

$LogFile="")

#-------------------------------

#Delete the file

#-------------------------------

Function DeleteFile{

Param( [Parameter(Mandatory)]$FileName )

try

{

logMsg("Checking if the file..." + $FileName + " exists.") -SaveFile $false

if( FileExist($FileName))

{

logMsg("Removing the file..." + $FileName) -SaveFile $false

$Null = Remove-Item -Path $FileName -Force

logMsg("Removed the file..." + $FileName) -SaveFile $false

}

return $true

}

catch

{

logMsg("Remove the file..." + $FileName + " - " + $Error[0].Exception) (2)

return $false

}

}

#-----------------------------------------------------------

# Identify if the value is empty or not

#-----------------------------------------------------------

function TestEmpty($s)

{

if ([string]::IsNullOrWhitespace($s))

{

return $true;

}

else

{

return $false;

}

}

#-------------------------------

#File Exists

#-------------------------------

Function FileExist{

Param( [Parameter(Mandatory)]$FileName )

try

{

$return=$false

$FileExists = Test-Path $FileName

if($FileExists -eq $True)

{

$return=$true

}

return $return

}

catch

{

return $false

}

}

#----------------------------------------------------------------

#Function to connect to the database using a retry-logic

#----------------------------------------------------------------

Function GiveMeConnectionSource()

{

$lNumRetries=10

for ($i=1; $i -le $lNumRetries; $i++)

{

try

{

logMsg( "---------------------------------------------------------------------------------------------------------------")

logMsg( "Connecting to the database: " + $DatabaseServer + " - DB: " + $Database + "...Attempt #" + $i + " of " + $lNumRetries.ToString() )

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection

$SQLConnection.ConnectionString = "data source=tcp:"+$DatabaseServer +",1433"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Initial Catalog="+$Database

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Connection Timeout=30"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";User ID="+ $Username

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Password="+ $password

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Application Name=Test SQLCLIENT Connection"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Persist Security Info=False"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";ConnectRetryInterval=3"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";ConnectRetryInterval=10"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Max Pool Size=100"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Min Pool Size=1"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";MultipleActiveResultSets=False"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Pooling=True"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";Encrypt=True"

$SQLConnection.ConnectionString = $SQLConnection.ConnectionString + ";TrustServerCertificate=False"

$SQLConnection.StatisticsEnabled = 1

$start = get-date

$SQLConnection.Open()

$end = get-date

$sAdditionalInformation = " - ID:" + $SQLConnection.ClientConnectionId.ToString() + " -- HostName: " + $SQLConnection.WorkstationId + " Server Version:" + $SQLConnection.ServerVersion

logMsg("Connected to the database in (ms):" +(New-TimeSpan -Start $start -End $end).TotalMilliseconds + " " + $sAdditionalInformation ) (3)

return $SQLConnection

break;

}

catch

{

logMsg("Not able to connect - Retrying the connection..." + $Error[0].Exception.ErrorRecord + "-" + $Error[0].Exception.ToString().Replace("\t"," ").Replace("\n"," ").Replace("\r"," ").Replace("\r\n","").Trim()) (2)

$WaitTime = (5*($i+1))

logMsg("Waiting for next retry in " + $WaitTime.ToString() + " seconds ..") -SaveFile $false

Start-Sleep -s $WaitTime

}

}

}

#--------------------------------

#Verify if the value is able to convert to integer

#--------------------------------

Function IsInteger([string]$vInteger)

{

Try

{

$null = [convert]::ToInt32($vInteger)

return $True

}

Catch

{

return $False

}

}

#--------------------------------

#Log the operations

#--------------------------------

function logMsg

{

Param

(

[Parameter(Mandatory=$false, Position=0)]

[string] $msg,

[Parameter(Mandatory=$false, Position=1)]

[int] $Color,

[Parameter(Mandatory=$false, Position=2)]

[boolean] $Show=$true,

[Parameter(Mandatory=$false, Position=3)]

[boolean] $ShowDate=$true,

[Parameter(Mandatory=$false, Position=4)]

[boolean] $SaveFile=$true,

[Parameter(Mandatory=$false, Position=5)]

[boolean] $NewLine=$true

)

try

{

If(TestEmpty($msg))

{

$msg = " "

}

if($ShowDate -eq $true)

{

$Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"

}

$msg = $Fecha + " " + $msg

If($SaveFile -eq $true)

{

Write-Output $msg | Out-File -FilePath $LogFile -Append

}

$Colores="White"

If($Color -eq 1 )

{

$Colores ="Cyan"

}

If($Color -eq 3 )

{

$Colores ="Yellow"

}

If($Color -eq 4 )

{

$Colores ="Green"

}

If($Color -eq 5 )

{

$Colores ="Magenta"

}

if($Color -eq 2 -And $Show -eq $true)

{

if($NewLine)

{

Write-Host -ForegroundColor White -BackgroundColor Red $msg

}

else

{

Write-Host -ForegroundColor White -BackgroundColor Red $msg -NoNewline

}

}

else

{

if($Show -eq $true)

{

if($NewLine)

{

Write-Host -ForegroundColor $Colores $msg

}

else

{

Write-Host -ForegroundColor $Colores $msg -NoNewline

}

}

}

}

catch

{

Write-Host $msg

}

}

cls

if (TestEmpty($DatabaseServer))

{ $DatabaseServer = read-host -Prompt "Please enter a Server Name" }

if (TestEmpty($DatabaseServer))

{

logMsg ("Server Name is empty. Closing the application.")

exit;

}

if (TestEmpty($Database))

{ $Database = read-host -Prompt "Please enter a Database Name" }

if (TestEmpty($Database))

{

logMsg ("DatabaseName Name is empty. Closing the application.")

exit;

}

if (TestEmpty($Username))

{ $Username = read-host -Prompt "Please enter a User Name" }

if (TestEmpty($Username))

{

logMsg ("User Name is empty. Closing the application.")

exit;

}

if (TestEmpty($Password))

{

$Password = read-host -Prompt "Please enter a password" -MaskInput

}

if (TestEmpty($Password))

{

logMsg ("Password is empty. Closing the application.")

exit;

}

if (TestEmpty($NumberExecutions))

{

$NumberExecutions = read-host -Prompt "Please enter the number of test to run (1-2000) - Leave this field empty and press enter for default value 10."

}

if (TestEmpty($NumberExecutions))

{

$NumberExecutions="10"

}

if( -not (IsInteger([string]$NumberExecutions)))

{

logMsg("Please, specify a correct number of process to run, the value is not integer") (2)

exit;

}

if($integerNumberExecutions -lt 1 -or $integerNumberExecutions -gt 2000)

{

logMsg("Please, specify a correct number of process to run, it is a value between 1 and 2000") (2)

exit;

}

if (TestEmpty($WaitTimeEveryInteraction))

{

$WaitTimeEveryInteraction = read-host -Prompt "Please enter the number of seconds to wait for every interaction (1-30) - Leave this field empty and press enter for default value 5."

}

if (TestEmpty($WaitTimeEveryInteraction))

{

$WaitTimeEveryInteraction="5"

}

if( -not (IsInteger([string]$WaitTimeEveryInteraction)))

{

logMsg("Please, specify a correct number of seconds to wait, the value is not integer") (2)

exit;

}

$IntegerWaitTimeEveryInteraction = [int]::Parse($WaitTimeEveryInteraction)

if($integerWaitTimeEveryInteraction -lt 1 -or $integerWaitTimeEveryInteraction -gt 30)

{

logMsg("Please, specify a correct number of seconds to wait, it is a value between 1 and 30") (2)

exit;

}

if(TestEmpty($LogFile))

{

$LogFile = $HOME + "/defaulttest.Log"

logMsg ("Using the default one for log file")

}

$Null = DeleteFile($LogFile)

$Conn = $(GiveMeConnectionSource) #Connecting to the database.

if($Conn -eq $null)

{

LogMsg("Not able to connect. Closing the application...") (2)

exit;

}

$command = New-Object -TypeName System.Data.SqlClient.SqlCommand

$command.CommandTimeout = 60

$command.Connection=$Conn

$command.CommandText = "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"

$sw = [diagnostics.stopwatch]::StartNew() ##Take the time.

for ($i=1; $i -le $IntegerNumberExecutions; $i++) ##For every interaction.

{

Try

{

LogMsg( "Iteration: " + $i.ToString() )

$Reader = $command.ExecuteReader();

while($Reader.Read())

{

logMsg("BlockerSession:" + ($Reader.GetValue(0).ToString() + " Query:" + $Reader.GetValue(5).ToString())) (2)

logMsg("BlockedSession:" + ($Reader.GetValue(1).ToString() + " Query:" + $Reader.GetValue(6).ToString())) (2)

logMsg("Waiting_For:" + $Reader.GetValue(7).ToString() + " Waiting_Time_ms:" + ($Reader.GetValue(2).ToString() + " Waiting_Time_secs:" + $Reader.GetValue(3).ToString() + " Waiting_Time_Mins:" + $Reader.GetValue(4).ToString())) (2)

}

$Reader.Close()

logMsg("Waiting for next retry in " + $IntegerWaitTimeEveryInteraction.ToString() + " seconds ..") -SaveFile $false

Start-Sleep -s $IntegerWaitTimeEveryInteraction

}

catch

{

LogMsg( "Error: " + $Error[0].Exception) (2)

}

}

logMsg("-------------------------------------- SUMMARY -----------------------------------------------------------------")

Remove-Variable password

Remove-Variable username

Basically, this script needs the following input parameters:

• $DatabaseServer = "servername.database.windows.net" : Will be the name of your server Azure SQL Database or Managed Instance.
  
• $Database = "DatabaseName" : Name of your database that you want to monitor.
  
• $Username = "UserName"
  
• $password = "Password"
  
• $NumberExecutions ="30" : Number of interactions to capture the blocking issues. It is a value between 1-2000.
  
• $WaitTimeEveryInteraction = 5 : Number of seconds to wait for every interaction. It is a value between 1-30.
  
• $LogFile="" : Log file where we are going to save the information obtained from the execution of the query mentioned in this article: Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub
  

How works this script:

• In case that any parameter is empty, this script will ask this.
  
• If the $LogFile parameter is empty the script will use the default value as: $Home folder plus defaulttest.log.
  
• For every interaction this script:
  
  • Will run a query to obtain the blocking report and will save the information in the log file.
    
  • Will wait the number of seconds
    

Enjoy!

Continue reading...