Lesson Learned #346: Monitoring a ReadScale Replica using a PowerShell script

Some days ago, I used this script to share how to monitor a ReadScale Replica about the latency between primary and secondary. This script is part of the session delivered and published in this article Lesson Learned #345: How to get the most from Azure SQL Database - Session Delivered - Microsoft Community Hub

I created a dummy table called DataDriven

----------------------------------

-- Create Table

----------------------------------

DROP TABLE IF EXISTS DataDriven

CREATE TABLE DataDriven (

ID BIGINT PRIMARY KEY IDENTITY(1,1),

FirstName Varchar(30),

SecondName Varchar(30),

Age int)

In order to measure the latency, I developed these PowerShell scripts one for writing data and other one for reading data from secondary obtaining data about the latency using the DMV: sys.dm_database_replica_states (Azure SQL Database) - SQL Server | Microsoft Learn

• WriteData.Ps1 that simulates a workload calling a stored procedure inserting a specific number of rows in the table DataDriven passed in the parameter.
  

#region DB

$DatabaseServer = "servername.database.windows.net"

$Database = "DatabaseName"

$Username = "UserName"

$Password = "Password"

$NumberExecutions =1000

#endRegion DB

cls

$connectionString = "Server=tcp:$DatabaseServer,1433;Initial Catalog=$Database;Persist Security Info=False;User ID=$Username;Password=$Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=True"

$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

$connection.Open()

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

$command.CommandTimeout = 60

$command.Connection=$connection

$command.CommandText = "EXEC InsertDataBlock 1"

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

$commandRead.CommandTimeout = 60

$commandRead.Connection=$connection

$commandRead.CommandText = "SELECT MAX(ID) FROM DataDriven"

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

$commandLag.CommandTimeout = 60

$commandLag.Connection=$connection

$commandLag.CommandText = "SELECT TOP 1 is_primary_replica,synchronization_state_desc,

synchronization_health_desc,

database_state_desc,

recovery_lsn,

truncation_lsn,

last_hardened_lsn,

end_of_log_lsn,

last_commit_lsn,

last_commit_time, secondary_lag_seconds,quorum_commit_lsn, quorum_commit_time FROM sys.dm_database_replica_states"

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

$commandRows.CommandTimeout = 60

$commandRows.Connection=$connection

$commandRows.CommandText = "SELECT p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

WHERE t.object_id = object_id('DataDriven') and i.index_id=1"

for ($i=0; $i -lt $NumberExecutions; $i++)

{

try

{

clear

$Value = -1

$RecordCount = -1

$Null = $command.ExecuteNonQuery()

$rdr = $commandRead.ExecuteReader()

if( $rdr.HasRows )

{

$Null = $rdr.Read()

$Value = $rdr.GetInt64(0)

}

$rdr.Close()

$rdrRows = $commandRows.ExecuteReader()

if( $rdrRows.HasRows )

{

$Null = $rdrRows.Read()

$RecordCount = $rdrRows.GetValue(0)

}

$rdrRows.Close()

$rdrLag = $commandLag.ExecuteReader()

write-Output "-------------------------"

write-Output ("Last ID Inserted : " + $Value.ToString())

write-Output ("Record Count : " + $RecordCount.ToString())

write-Output ("Iteration : " +$i)

if( $rdrLag.HasRows )

{

$Null = $rdrLag.Read()

for($iCol=0;$iCol -le $rdrLag.FieldCount-1;$iCol++)

{

write-Output ($rdrLag.GetName($iCol).ToString().PadRight(30," ") + " : " + $rdrLag.GetValue($iCol).ToString())

}

}

$rdrLag.Close()

write-Output "-------------------------"

Start-Sleep -Milliseconds 200

}

catch

{

Write-Output -ForegroundColor DarkYellow "You're WRONG"

Write-Output -ForegroundColor Magenta $Error[0].Exception

}

}

$connection.Close()

CREATE OR ALTER Procedure InsertDataBlock (@Max BIGINT)

AS

DECLARE @nValue AS INT = 1

BEGIN TRANSACTION

WHILE(@nValue<=@Max)

BEGIN

SET @nValue=@nValue+1

EXEC InsertData

END

COMMIT TRANSACTION

CREATE OR ALTER Procedure InsertData

AS

DECLARE @nChar1 AS SMALLINT

DECLARE @nChar2 AS SMALLINT

DECLARE @nAge AS SMALLINT

SET @nChar1 = CEILING(RAND()*(65-90)+90)

SET @nChar2 = CEILING(RAND()*(48-57)+57)

SET @nAge = CEILING(RAND()*(1-100)+100)

insert into DataDriven (FirstName,SecondName, Age)

VALUES( replicate(CHAR(@nChar1),CEILING(RAND()*(1-30)+30)),

replicate(CHAR(@nChar2),CEILING(RAND()*(1-30)+30)),

@nAge)

• ReadData.Ps1 that reads the data in the secondary replica, to measure the latency, LSN, workload pending to redo and last ID inserted.
  

#region DB

$DatabaseServer = "servername.database.windows.net"

$Database = "DBName"

$Username = "User"

$Password = "PAssword"

$NumberExecutions =1000

#endRegion DB

cls

$connectionString = "Server=tcp:$DatabaseServer,1433;Initial Catalog=$Database;Persist Security Info=False;User ID=$Username;Password=$Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;ApplicationIntent=Readonly;Pooling=True"

$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

$connection.Open()

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

$command.CommandTimeout = 60

$command.Connection=$connection

$command.CommandText = "SELECT TOP 1 MAX(ID) FROM DataDriven"

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

$commandLag.CommandTimeout = 60

$commandLag.Connection=$connection

$commandLag.CommandText = "SELECT TOP 1 is_primary_replica,synchronization_state_desc,

synchronization_health_desc,

database_state_desc,

recovery_lsn,

truncation_lsn,

last_received_lsn,

last_received_time,

last_hardened_lsn,

last_hardened_time,

last_redone_lsn,

last_redone_time,

log_send_queue_size,

log_send_rate,

redo_queue_size,

redo_rate,

end_of_log_lsn,

last_commit_lsn,

last_commit_time, secondary_lag_seconds FROM sys.dm_database_replica_states"

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

$commandRows.CommandTimeout = 60

$commandRows.Connection=$connection

$commandRows.CommandText = "SELECT p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

WHERE t.object_id = object_id('DataDriven') and i.index_id=1"

for ($i=0; $i -lt $NumberExecutions; $i++)

{

try

{

clear

$Value = -1

$RecordCount = -1

$rdr = $command.ExecuteReader()

if( $rdr.HasRows )

{

$Null = $rdr.Read()

$Value = $rdr.GetInt64(0)

}

$rdr.Close()

$rdrRows = $commandRows.ExecuteReader()

if( $rdrRows.HasRows )

{

$Null = $rdrRows.Read()

$RecordCount = $rdrRows.GetValue(0)

}

$rdrRows.Close()

$rdrLag = $commandLag.ExecuteReader()

write-Output "-------------------------"

write-Output ("Last ID Inserted : " + $Value.ToString())

write-Output ("Record Count : " + $RecordCount.ToString())

write-Output ("Iteration : " +$i)

if( $rdrLag.HasRows )

{

$Null = $rdrLag.Read()

for($iCol=0;$iCol -le $rdrLag.FieldCount-1;$iCol++)

{

write-Output ($rdrLag.GetName($iCol).ToString().PadRight(30," ") + " : " + $rdrLag.GetValue($iCol).ToString())

}

}

$rdrLag.Close()

write-Output "-------------------------"

Start-Sleep -Milliseconds 1000

}

catch

{

Write-Output -ForegroundColor DarkYellow "You're WRONG"

Write-Output -ForegroundColor Magenta $Error[0].Exception

}

}

$connection.Close()

If you need any additional information about the process what is waiting for you could run the following TSQL to see them.

SELECT program_name, host_name, client_interface_name,client_net_address,

COUNT(*) AS TotalConnections,

MAX(Net_packet_size) as MaxPacketSize

FROM sys.dm_exec_sessions sess

INNER JOIN sys.dm_exec_connections conn on sess.session_id = conn.session_id

GROUP BY program_name, host_name, client_interface_name,client_net_address

select wait_type = (CASE WHEN wait_type IS NULL THEN req.last_wait_type ELSE wait_type END ),req.last_wait_type, count(*) as NumTask, avg(wait_time) as AvgTime, avg(cpu_time) AvgCPUTime

from sys.dm_exec_requests req

join sys.dm_os_workers work on req.task_address = work.task_address

join sys.dm_os_tasks tasks on req.session_id = tasks.session_id

join sys.dm_os_schedulers sched on sched.scheduler_id = tasks.scheduler_id

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST

where req.status <> 'background' and req.session_id<> @@spid --and wait_type is not null

group by wait_type,req.last_wait_type

order by wait_type,req.last_wait_type

select object_name(object_id) as name,

partition_id, partition_number as pnum, rows, allocation_unit_id as au_id, type_desc as age_type_desc,

total_pages as pages

from sys.partitions p JOIN sys.allocation_units a

on p.partition_id = a.container_id

where object_id = object_id(N'DataDriven')

SELECT top 5 * FROM sys.dm_db_resource_stats

SELECT * FROM sys.dm_user_db_resource_governance where database_id=DB_ID()

SELECT top 5 *

FROM sys.dm_resource_governor_resource_pools_history_ex

WHERE name LIKE 'SloSharedPool1'

ORDER BY snapshot_time DESC;

Enjoy!

Continue reading...