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

Jose_Manuel_Jurado · ‎May 02 2023

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 Com...

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()

You could find here the stored procedure called InsertDataBlock

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!

Products (47)

Special Topics (23)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

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