Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save MasayukiOzawa/e3a2ff81628d074eacf4664e36d5ecfb to your computer and use it in GitHub Desktop.
Save MasayukiOzawa/e3a2ff81628d074eacf4664e36d5ecfb to your computer and use it in GitHub Desktop.
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
$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
}
$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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment