MySQL vs SQL Server

Audits row lock wait frequencies, buffer pool hit efficiency, dirty page ratios, and monitors replica replication lag.

# 1. Audit InnoDB row lock waits, buffer pool hit ratio & dirty pages
mysql -h mysql-primary -u dbre_admin -p -e "
SELECT 
  NAME, 
  COUNT 
FROM information_schema.INNODB_METRICS 
WHERE NAME IN (
  'lock_row_lock_waits', 
  'lock_row_lock_time_avg', 
  'buffer_pool_reads', 
  'buffer_pool_read_requests', 
  'buffer_pool_pages_dirty'
);"

# 2. Check active replica IO/SQL threads and seconds behind source
mysql -h mysql-replica -u dbre_admin -p -e "SHOW REPLICA STATUS\G" | 
grep -E "(Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_Errno|Retrieved_Gtid_Set)"

Identifies active SQL Server wait statistics for latch and lock bottlenecks, and inspects TempDB space allocation across user and version stores.

# 1. Inspect active SQL Server wait statistics for latch and lock bottlenecks
sqlcmd -S mssql-primary -U dbre_monitor -P "$MSSQL_PASS" -Q "
SELECT TOP 8 
  wait_type, 
  waiting_tasks_count, 
  wait_time_ms / 1000.0 AS wait_time_sec, 
  (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec 
FROM sys.dm_os_wait_stats 
WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_TASK_STOP', 'WAITFOR', 'HADR_FILESTREAM_IOMGROUTER') 
ORDER BY wait_time_ms DESC;"

# 2. Check TempDB space allocation across user objects, internal objects & version store
sqlcmd -S mssql-primary -U dbre_monitor -P "$MSSQL_PASS" -Q "
SELECT 
  SUM(user_object_reserved_page_count) * 8 / 1024 AS user_obj_mb, 
  SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_obj_mb, 
  SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb, 
  SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb 
FROM tempdb.sys.dm_db_file_space_usage;"