Как сделать Стресс-тест HADR_SYNC_COMMIT и PAGELATCH_UP в SQL Server: PowerShell-скрипт с мониторингом и автоочисткой

Выбор баз – подключается к указанному экземпляру SQL Server, выводит список пользовательских баз в Out-GridView для множественного выбора.
Подготовка окружения – принудительно удаляет/пересоздаёт тестовую таблицу dbo.hadr_stress_test в каждой выбранной базе и глобальную таблицу ##hadr_stress_counter в tempdb.
Генерация нагрузки
- HADR_SYNC_COMMIT: запускает параллельные потоки INSERT с пакетной вставкой. При синхронном режиме AG возникает ожидание подтверждения от вторичных реплик.
- PAGELATCH_UP: несколько десятков потоков обновляют одну строку в tempdb, вызывая конкуренцию за latch-и страниц выделения (PFS/SGAM).
Контроль роста – отдельные фоновые потоки выполняют TRUNCATE TABLE каждые 15 секунд с механизмом повторных попыток при блокировках.
Мониторинг в реальном времени – каждые 8 секунд скрипт опрашивает sys.dm_exec_requests и выводит сессии с ожиданиями HADR_SYNC_COMMIT, PAGELATCH_UP и PAGELATCH_EX.
Автоочистка – после завершения теста удаляет все временные и тестовые объекты, завершает фоновые задачи и освобождает ресурсы.

# ============================================================
# HADR_SYNC_COMMIT + TempDB PAGELATCH_UP Stress Test
# С очисткой перед стартом + контролем роста файлов
# ============================================================

if (-not (Get-Module -ListAvailable -Name SqlServer)) {
    Write-Warning "Модуль 'SqlServer' не найден. Установите: Install-Module SqlServer -Force"
    exit
}
Import-Module SqlServer -ErrorAction SilentlyContinue

# ---------- 1. Ввод сервера ----------
$Server = Read-Host -Prompt "Введите имя MSSQL сервера (например, SQLSERVER\INSTANCE или IP,Port)"
if (-not $Server) { Write-Host "Сервер не указан. Выход."; exit }

# ---------- 2. Получение списка баз ----------
Write-Host "Подключение к $Server для получения списка баз..." -ForegroundColor Yellow
try {
    $allDBs = Invoke-Sqlcmd -ServerInstance $Server -TrustServerCertificate -Query "
        SELECT name FROM sys.databases 
        WHERE state_desc = 'ONLINE' AND database_id > 4 
        ORDER BY name;" -ErrorAction Stop
} catch {
    Write-Error "❌ Ошибка подключения к серверу: $_"
    exit
}

if (-not $allDBs) {
    Write-Warning "Пользовательские базы данных не найдены или недоступны."
    exit
}

# ---------- 3. Выбор баз ----------
Write-Host "`nОткроется окно для выбора баз. Используйте Ctrl+Click для множественного выбора." -ForegroundColor Cyan
$selectedDBs = $allDBs.name | Out-GridView -Title "Выберите базы для теста" -OutputMode Multiple
if (-not $selectedDBs) {
    Write-Host "Базы не выбраны. Скрипт остановлен." -ForegroundColor Red
    exit
}
Write-Host "✅ Выбранные базы: $($selectedDBs -join ', ')" -ForegroundColor Green

# ========== НОВЫЙ БЛОК: Принудительная очистка перед стартом ==========
Write-Host "`n🧹 Принудительная очистка таблиц dbo.hadr_stress_test в выбранных базах..." -ForegroundColor Magenta
foreach ($db in $selectedDBs) {
    try {
        $drop = "IF OBJECT_ID('dbo.hadr_stress_test', 'U') IS NOT NULL DROP TABLE dbo.hadr_stress_test;"
        Invoke-Sqlcmd -ServerInstance $Server -Database $db -TrustServerCertificate -Query $drop -ErrorAction Stop
        Write-Host "   ✅ $db : таблица удалена (если существовала)" -ForegroundColor Green
    } catch {
        Write-Host "   ⚠️ $db : не удалось удалить таблицу – $_" -ForegroundColor Yellow
    }
}
# =================================================================

# ---------- 4. Параметры теста ----------
$TestDurationSec        = 400          # Длительность теста (сек)
$WriteThreadsPerDB      = 6            # Потоков записи на базу
$BatchSize              = 80           # INSERT-ов в транзакции
$TempDBThreadsTotal     = 40           # Потоков для tempdb
$TempDBUpdateIntervalMs =  1           # Задержка между обновлениями tempdb
$CleanupIntervalSec     = 15           # Как часто очищать таблицу (сек)
$MaxRetries             = 6            # Попыток при блокировке TRUNCATE

Write-Host "`n=== HADR_SYNC_COMMIT + TempDB PAGELATCH_UP Stress Test ===" -ForegroundColor Green
Write-Host "Потоков записи (HADR): $WriteThreadsPerDB на базу" -ForegroundColor Cyan
Write-Host "Потоков tempdb: $TempDBThreadsTotal" -ForegroundColor DarkCyan
Write-Host "Очистка таблиц каждые $CleanupIntervalSec секунд" -ForegroundColor Yellow

# ---------- 5. Создание глобальной таблицы в tempdb для contention ----------
$createGlobalTemp = @"
IF OBJECT_ID('tempdb..##hadr_stress_counter') IS NOT NULL 
    DROP TABLE ##hadr_stress_counter;
CREATE TABLE ##hadr_stress_counter (
    id int IDENTITY(1,1) PRIMARY KEY,
    counter bigint DEFAULT 0
);
INSERT INTO ##hadr_stress_counter DEFAULT VALUES;
"@
Invoke-Sqlcmd -ServerInstance $Server -TrustServerCertificate -Query $createGlobalTemp
Write-Host "🌡️ Глобальная таблица ##hadr_stress_counter создана в tempdb" -ForegroundColor DarkCyan

# ---------- 6. Создание тестовых таблиц в каждой выбранной базе (заново) ----------
foreach ($db in $selectedDBs) {
    $createTable = @"
    CREATE TABLE dbo.hadr_stress_test (
        id int IDENTITY(1,1) PRIMARY KEY,
        col1 uniqueidentifier DEFAULT NEWID(),
        col2 datetime2 DEFAULT GETDATE(),
        col3 bigint DEFAULT ABS(CHECKSUM(NEWID()))
    );
"@
    Invoke-Sqlcmd -ServerInstance $Server -Database $db -TrustServerCertificate -Query $createTable
    Write-Host "📦 Таблица dbo.hadr_stress_test создана в $db" -ForegroundColor Gray
}

$AllJobs = @()

# ---------- 7. Запуск потоков записи (HADR_SYNC_COMMIT) ----------
Write-Host "`n🔵 Запуск потоков записи (HADR_SYNC_COMMIT)..." -ForegroundColor Cyan
foreach ($db in $selectedDBs) {
    for ($i = 1; $i -le $WriteThreadsPerDB; $i++) {
        $job = Start-Job -ScriptBlock {
            param($Server, $Database, $Duration, $Batch)
            $connStr = "Server=$Server;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
            $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
            $conn.Open()
            $cmd = $conn.CreateCommand()
            $startTime = Get-Date
            while (((Get-Date) - $startTime).TotalSeconds -lt $Duration) {
                $cmd.CommandText = @"
                BEGIN TRAN;
                DECLARE @i int = 0;
                WHILE @i < $Batch
                BEGIN
                    INSERT INTO dbo.hadr_stress_test DEFAULT VALUES;
                    SET @i += 1;
                END
                COMMIT TRAN;
"@
                try { $cmd.ExecuteNonQuery() | Out-Null } catch { }
                Start-Sleep -Milliseconds 5
            }
            $conn.Close()
        } -ArgumentList $Server, $db, $TestDurationSec, $BatchSize
        $AllJobs += $job
        Write-Host "   ⚡ Поток записи $i для $db запущен" -ForegroundColor Gray
    }
}

# ---------- 8. Запуск потоков tempdb (PAGELATCH_UP) ----------
Write-Host "🟠 Запуск потоков tempdb (PAGELATCH_UP)..." -ForegroundColor DarkCyan
for ($i = 1; $i -le $TempDBThreadsTotal; $i++) {
    $job = Start-Job -ScriptBlock {
        param($Server, $Duration, $UpdateIntervalMs)
        $connStr = "Server=$Server;Database=tempdb;Integrated Security=True;TrustServerCertificate=True"
        $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = "UPDATE ##hadr_stress_counter SET counter = counter + 1;"
        $startTime = Get-Date
        while (((Get-Date) - $startTime).TotalSeconds -lt $Duration) {
            try { $cmd.ExecuteNonQuery() | Out-Null } catch { }
            if ($UpdateIntervalMs -gt 0) { Start-Sleep -Milliseconds $UpdateIntervalMs }
        }
        $conn.Close()
    } -ArgumentList $Server, $TestDurationSec, $TempDBUpdateIntervalMs
    $AllJobs += $job
    Write-Host "   🔥 Поток tempdb $i запущен" -ForegroundColor DarkGray
}

# ---------- 9. Запуск потоков очистки таблиц для каждой базы ----------
Write-Host "🧹 Запуск потоков очистки таблиц..." -ForegroundColor Magenta
foreach ($db in $selectedDBs) {
    $cleanupJob = Start-Job -ScriptBlock {
        param($Server, $Database, $CleanupInterval, $MaxRetries, $Duration)
        $connStr = "Server=$Server;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
        $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $endTime = (Get-Date).AddSeconds($Duration)
        while ((Get-Date) -lt $endTime) {
            Start-Sleep -Seconds $CleanupInterval
            if ((Get-Date) -ge $endTime) { break }
            for ($retry = 0; $retry -lt $MaxRetries; $retry++) {
                try {
                    $cmd.CommandText = "TRUNCATE TABLE dbo.hadr_stress_test;"
                    $cmd.ExecuteNonQuery() | Out-Null
                    Write-Host "   🧹 $Database : таблица очищена" -ForegroundColor Green
                    break
                } catch {
                    if ($retry -eq $MaxRetries - 1) {
                        Write-Host "   ⚠️ $Database : не удалось очистить таблицу после $MaxRetries попыток" -ForegroundColor Yellow
                    } else {
                        Start-Sleep -Seconds 1
                    }
                }
            }
        }
        $conn.Close()
    } -ArgumentList $Server, $db, $CleanupIntervalSec, $MaxRetries, $TestDurationSec
    $AllJobs += $cleanupJob
    Write-Host "   🧹 Поток очистки для $db запущен (интервал $CleanupIntervalSec сек)" -ForegroundColor Gray
}

Write-Host "`n🚀 Тест запущен на $($AllJobs.Count) потоках. Длительность: $TestDurationSec секунд..." -ForegroundColor Magenta

# ---------- 10. Мониторинг ожиданий ----------
$MonitorJob = Start-Job -ScriptBlock {
    param($Server)
    Import-Module SqlServer -ErrorAction SilentlyContinue
    while ($true) {
        $q = @"
        SELECT 
            session_id,
            wait_type,
            wait_time_ms,
            wait_resource,
            blocking_session_id,
            command
        FROM sys.dm_exec_requests 
        WHERE wait_type IN ('HADR_SYNC_COMMIT', 'PAGELATCH_UP', 'PAGELATCH_EX')
        ORDER BY wait_time_ms DESC
"@
        try {
            $result = Invoke-Sqlcmd -ServerInstance $Server -TrustServerCertificate -Query $q -ErrorAction Stop
            if ($result) {
                Clear-Host
                Write-Host "=== Мониторинг ожиданий (HADR_SYNC_COMMIT / PAGELATCH_UP) - обновление 8 сек ===" -ForegroundColor Yellow
                $result | Format-Table -AutoSize
            } else {
                Clear-Host
                Write-Host "=== Ожиданий указанных типов нет ===" -ForegroundColor Green
            }
        } catch { }
        Start-Sleep -Seconds 8
    }
} -ArgumentList $Server

# ---------- 11. Ожидание окончания теста ----------
Start-Sleep -Seconds $TestDurationSec

# ---------- 12. Очистка после теста ----------
Write-Host "`n⏹️  Тест завершён. Выполняем финальную очистку..." -ForegroundColor Green
$AllJobs | Stop-Job | Out-Null
$MonitorJob | Stop-Job | Out-Null

# Удаляем тестовые таблицы из каждой базы
foreach ($db in $selectedDBs) {
    $drop = "IF OBJECT_ID('dbo.hadr_stress_test', 'U') IS NOT NULL DROP TABLE dbo.hadr_stress_test;"
    Invoke-Sqlcmd -ServerInstance $Server -Database $db -TrustServerCertificate -Query $drop
    Write-Host "   🗑️ $db : таблица удалена" -ForegroundColor Green
}

# Удаляем глобальную таблицу tempdb
$dropGlobal = "IF OBJECT_ID('tempdb..##hadr_stress_counter') IS NOT NULL DROP TABLE ##hadr_stress_counter;"
Invoke-Sqlcmd -ServerInstance $Server -TrustServerCertificate -Query $dropGlobal
Write-Host "   🗑️ ##hadr_stress_counter удалена из tempdb" -ForegroundColor Green

$AllJobs | Remove-Job
$MonitorJob | Remove-Job

Write-Host "✅ Очистка завершена. Тест полностью окончен." -ForegroundColor Green напиши описание и заголовок для статьи на сайте по данному скриптуц

Как сделать Стресс-тест HADR_SYNC_COMMIT и PAGELATCH_UP в SQL Server: PowerShell-скрипт с мониторингом и автоочисткой

Similar Posts:

Добавить комментарий Отменить ответ