This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
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()
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!