base query.sql

DROP TABLE IF EXISTS [TestTable]
GO

CREATE TABLE [dbo].[TestTable](
	[keyColumn] [uniqueidentifier] NOT NULL,
	[name] [sysname] NOT NULL,
	[object_id] [int] NOT NULL,
	[principal_id] [int] NULL,
	[schema_id] [int] NOT NULL,
	[parent_object_id] [int] NOT NULL,
	[type] [char](2) NULL,
	[type_desc] [nvarchar](60) NULL,
	[create_date] [datetime] NOT NULL,
	[modify_date] [datetime] NOT NULL,
	[is_ms_shipped] [bit] NOT NULL,
	[is_published] [bit] NOT NULL,
	[is_schema_published] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (KeyColumn)
GO
CREATE INDEX NCIX_name ON TestTable (name)
GO
CREATE INDEX NCIX_object_id ON TestTable (object_id)
GO

DROP TABLE IF EXISTS Dummy01
CREATE TABLE Dummy01(C1 int PRIMARY KEY, C2 uniqueidentifier, timestamp_col timestamp)
GO
------
INSERT INTO TestTable SELECT NEWID(), o.* FROM sys.objects AS o CROSS JOIN sys.all_columns
ALTER INDEX PK_TestTable ON TestTable REBUILD WITH(RESUMABLE=ON, ONLINE=ON, MAXDOP=1)
GO 

HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO 検証スクリプト.psq

$sql = @"
SELECT 
    SYSDATETIMEOFFSET() AT TIME ZONE 'Tokyo Standard Time' AS collect_date
    , num_of_bytes_written
    , (SELECT waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_type = 'HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO') AS waiting_tasks_count
FROM 
    sys.dm_io_virtual_file_stats(DB_ID(), 2)
"@

$replSql = @"
select last_commit AT TIME ZONE 'Tokyo Standard Time' from v
"@

$constr = @(
    "Server=xxxxxx.database.windows.net;user=xxxxxx;password=xxxxxxx;database=xxxxxxxx",
    "Server=xxxxxx.database.windows.net;user=xxxxxx;password=xxxxxxx;database=xxxxxxxx"
)

$con = New-Object System.Data.SqlClient.SqlConnection
$con1 = New-Object System.Data.SqlClient.SqlConnection
$con2 = New-Object System.Data.SqlClient.SqlConnection

$con.ConnectionString = $con1.ConnectionString = $constr[0]
$con2.ConnectionString = $constr[1]

$oldValue = 0
$old_value_wait = 0
while ($true) {
    $con.Open()
    $cmd = $con.CreateCommand()
    $cmd.CommandText = $sql
    $ret = $cmd.ExecuteReader()
    [void]$ret.Read()
    if ($oldValue -eq 0){
        $oldValue = $ret["num_of_bytes_written"] / [MATH]::Pow(1024,2)
        $old_value_wait = $ret["waiting_tasks_count"]
    }else {
        $con1.Open()
        $cmd1 = $con1.CreateCommand()
        $cmd1.CommandText = $replSql
        $primary_last_commit = $cmd1.ExecuteScalar()
        try{
            $con2.Open()
            $cmd2 = $con2.CreateCommand()
            $cmd2.CommandText = $replSql
            $secondary_last_commit = $cmd2.ExecuteScalar()
    
        }catch{
            $secondary_last_commit = 0
        }

        "{0} | {1:##0.0} MB | {2} | Primary {3} | Secondary {4}" -f `
            $ret["collect_date"], `
            ($ret["num_of_bytes_written"] / [MATH]::Pow(1024,2) - $oldValue), `
            ($ret["waiting_tasks_count"] - $old_value_wait), `
            $primary_last_commit, `
            $secondary_last_commit | Tee-Object -Append -FilePath "D:\Work\mismatched.txt"

        $oldValue = $ret["num_of_bytes_written"] / [MATH]::Pow(1024,2)
        $old_value_wait = $ret["waiting_tasks_count"]
        $con1.Close()
        $con2.Close()
    }
    $con.Close()
    Start-Sleep -Seconds 1
}

request_checker.ps1

$sql = @"
SELECT
    GETDATE() AS CollectDate,
    r.session_id,
    r.command,
    r.last_wait_type,
    r.wait_resource,
    r.wait_time
FROM 
    sys.dm_exec_requests  AS r
    LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    s.is_user_process = 1
    AND s.host_name = 'xxxxxxxxxx'
    AND r.session_id <> @@SPID
"@
Clear-Host

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=xxxxxx.database.windows.net;user=xxxxxx;password=xxxxxxx;database=xxxxxxxx"

$da = New-Object System.Data.SqlClient.SqlDataAdapter
$ds = New-Object System.Data.DataSet

$da.SelectCommand = & {
    $cmd = $con.CreateCommand()
    $cmd.CommandText = $sql
    return $cmd
}

while ($true) {
    $con.Open()
    [void]$da.Fill($ds)
    if($ds.Tables.last_wait_type -notmatch "LOG_RATE_GOVERNOR|SOS_SCHEDULER_YIELD|MEMORY_ALLOCATION_EXT|PREEMPTIVE_OS_WRITEFILEGATHER|WAITFOR"){
        $ds.Tables | ft
    }
    # $ds.Tables | ft
    $con.Close()
    $ds.Clear()
    Start-Sleep -Milliseconds 500
}