SQL Server & Powershell: быстро, просто, параллельно. И больше не нужно выбирать 2 из 3-х +4


В настоящей статье описывается процесс параллельного выполнения операций над базой данных Microsoft SQL Server с использованием инструментария Powershell. Наиболее часто используемый сценарий - обслуживание таблиц в базе, но возможно исполнение любых запросов в параллельном режиме. Если для вас это интересно, то: Добро пожаловать.

Вступление

Я системный администратор "общего профиля", и помимо всего прочего - занимаюсь поддержкой Microsoft SQL Server и других систем, использующих его в своей работе. Периодически возникает необходимость в выполнении регламентных процедур на базах данных SQL Server - дефрагментация, перестроение индексов, другие операции на таблицах и индексах. Обычно эти операции выполняются через штатный функционал "Maintenance Plans", "Jobs" для SQL Server Agent, и так далее. Эти способы вполне хороши и достаточно просты для использования, но обладают общим недостатком, или особенностью: все запланированные операции в рамках одной базы данных выполняются на объектах в ней строго последовательно.

Чаще всего это не критично, и можно спланировать такие операции во время регламентного окна, низкой нагрузки на сервер (ночью), и т.п. Но в зависимости от типа операций и размера обрабатываемых данных возможна ситуация, когда время выполнения таких "последовательных" операций превышает допустимое, и нужно как-то выходить из ситуации.

Решение: выполнить операции параллельно сразу для нескольких таблиц/индексов в рамках одной базы. Это кратно сократит время выполнения ценой увеличения загрузки сервера.

При этом далее предполагается, что у нас есть короткое регламентное окно, когда мы можем использовать всю (или большую часть) производительности сервера баз данных для своих целей, без учёта потребностей пользователей. Если у вас сервер SQL значительно нагружен в режиме 24/7 - вам нужно другое решение.

Я не нашёл доступного простого решения по распараллеливанию массива запросов к базе данных, и как сисадмин - соорудил свой велосипед на базе Powershell. Если кто-то подскажет другой вариант - ссылки и ваш опыт в комментариях приветствуется.

Общее описание предлагаемой схемы:

  1. Первый запрос к БД получает список таблиц/индексов/<что_вам_нужно> в ней по нужным вам критериям.

  2. Из полученного списка формируется массив запросов на T-SQL (один элемент массива для каждой таблицы/индекса), и этот массив запросов прогоняется через БД параллельно. При этом можно задать количество потоков выполнения: например массив из 100 элементов (запросы для 100 таблиц) выполнять в 10 потоков. Оптимальное количество потоков определяется экспериментально, исходя из создаваемой ими нагрузки на сервер.

Подготовка

Здесь и далее предполагается, что читатель имеет базовые навыки администрирования ОС Windows, SQL Server и знает, что такое Powershell. Поехали...
Для работы предлагаемого скрипта нам понадобится Powershell версии 7 или выше. В этой версии появилась поддержка нужногой функционала: параметр -Parallel для командлета ForEach-Object. Обратите внимание, что эта версия PoSh уже не поставляется в составе ОС Windows, и вам нужно скачать и установить её отдельно. Так же эта версия PoSh имеет свой шелл и бинарник для запуска: учитывайте это в работе.
Для отладки скриптов PoSh 7.x вам возможно понадобится VS Code, но рассмотрение его установки и настройки - уже за рамками этой статьи.

Распараллеливаем: функция

Параллельное выполнение запросов реализовано через вызов универсальной функции Invoke-SQLRequest за авторством вашего покорного слуги, код функции - под спойлером.

function_Invoke-SQLRequest.ps1
function Invoke-SQLRequest { 
  <#
  .SYNOPSIS
  Выполнение запроса к серверу SQL
  .DESCRIPTION
  Выполнение запроса к заданному серверу (инстансу) SQL.
  Возможен выбор метода авторизации "Integrated Security" или "Login/Password",
  параметров выполнения запроса - параллельно или последовательно, и т.п.
  .EXAMPLE
  Invoke-SQLRequest -SqlServer 'Server' -SqlDB 'DB'
  .EXAMPLE
  Invoke-SQLRequest -SqlServer 'Server' -SqlDB 'DB' -Login 'UserLogin' -Password 'P@ssVVord' -ParallelLimit 12
  .PARAMETER SqlServer
  Имя сервера SQL или именнованного инстанса SQL для подключения
  .PARAMETER SqlDB
  Имя базы данных на сервере, к которой выполняется подключение
  .PARAMETER SQLRequest
  Текст запроса для выполнения на сервере SQL. Может быть указан массив элементов типа "string"
  .PARAMETER Login
  Логин для подключения к серверу SQL. Если отсутствует - будет выполнено подключение под
  текущей учётной записью скрипта - режим "Integrated Security"
  .PARAMETER Password
  Пароль для подключения к серверу SQL. Если отсутствует - будет выполнено подключение под
  текущей учётной записью скрипта - режим "Integrated Security"
  .PARAMETER CommandTimeout
  Время ожидания в секундах результата выполнения запроса перед прерыванием и возвратом ошибки.
  Следует изменить для длительно выполняющихся запросов.
  .PARAMETER ParallelLimit
  Количество потоков, которое будет использовано при параллельном выполнении составного запроса.
  Если не указано - будет использован 1 поток, т.е. отсутствие параллельности.
  #>
  [CmdletBinding()]
  param (
    [Parameter(Mandatory=$True)][string]$SqlServer,
    [Parameter(Mandatory=$True)][string]$SqlDB,
    [Parameter(Mandatory=$True)][string[]]$SQLRequest,
    [string]$Login,
    [string]$Password,
    [int]$CommandTimeout = 15,
    [int]$ParallelLimit = 1
  )
  Process {
    # Создаём объект для размещения вывода параллельных запросов к серверу SQL
    $ParallelOut = [System.Collections.Concurrent.ConcurrentDictionary[int,System.Object]]::new()
    # Выполняем запросы параллельно в $ParallelLimit потоков
    $SQLRequest | ForEach-Object -Parallel {

      # Функция для получения информационных сообщений "InfoMessage" при выполнении запросов
      function Get-ConnectionEvents($EventID) {
        Get-Event | % {
          if ($_.SourceIdentifier -eq $EventID) {
            $CurrentEventIdentifier = $_.EventIdentifier;   
            $InfoMessage = $_.SourceEventArgs   
            Remove-Event -EventIdentifier $CurrentEventIdentifier
            $InfoMessage.Message
          }
        }
      }

      # Создаём объект подключения к инстансу на сервере SQL
      if (!$using:Login -or !$using:Password) {
        # Если не указан логин или пароль - используем Integrated Security
        $ConnectionString = "Server = $using:SqlServer; Database = $using:SqlDB; Integrated Security = true;"
      } else {
        $ConnectionString = "Server = $using:SqlServer; Database = $using:SqlDB; Integrated Security = false; User ID = $using:Login; Password = $using:Password"
      }
      $Connection = New-Object System.Data.SqlClient.SqlConnection
      $Connection.ConnectionString = $ConnectionString
      # Подписываемся на "InfoMessage"
      $EventID = "Connection.Messages."+(Get-Random)
      Register-ObjectEvent -InputObject $Connection -EventName InfoMessage -SourceIdentifier $EventID
      # Пробуем открыть подключение, при ошибке - никаких запросов не выполняется
      try {$Connection.Open()}
      catch {$DataSet = $null; $Connection = $null}
      # Выполняем запросы только если подключение открыто успешно
      try {
        if ($Connection) {
        # Создаём новый запрос к серверу SQL
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        # Задаём текст запроса, выполняем, результат помещаем в $ReqwDataSet
        $SqlCmd.CommandText = $_
        $SqlCmd.Connection = $Connection
        $SqlCmd.CommandTimeout = $using:CommandTimeout
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet) > $null
        $Connection.Close()
        }
        # Создаём объект $Result из $DataSet.Tables[0] с дополнительным свойством InfoMessage
        $Result = $DataSet.Tables[0]
        try {
          $InfoMessage = Get-ConnectionEvents($EventID)
          $Result | Add-Member -NotePropertyName 'InfoMessage' -NotePropertyValue $InfoMessage -ErrorAction SilentlyContinue
        }
        catch {}
        # Размещаем вывод запроса $Result в объект $ParallelOut
        $Out = $using:ParallelOut
        $RndInt = Get-Random
        $Out.TryAdd($RndINT, $Result) > $null
      }
      catch {
        $DataSet = $null
      }
    } -ThrottleLimit $ParallelLimit
    $FunctionOutput = $null
    foreach ($item in $ParallelOut.Values) {$FunctionOutput += $item}
    return $FunctionOutput
  }
}

Пояснения по работе с функцией:

  • сервер SQL и имя базы данных задаются через параметры -SqlServer и -SqlDB соответственно;

  • поддерживается авторизация для доступа к серверу SQL как Integrated Security (от имени учётной записи, под которой выполняется скрипт), так и с указанием логина и пароля. Если -Login или -Password не заданы (пустые значения), то скрипт делает попытку авторизации на сервере SQL с использованием Integrated Security;

  • количество потоков выполнения запросов задаётся ключом -ParallelLimit, по умолчанию - 1 поток;

  • запрос или массив запросов к БД задаётся ключом -SQLRequest. Поддерживается (и рекомендуется) использовать массив PoSh @(), где каждый элемент - отдельный запрос;

  • помимо собственно результата выполнения запроса, полученного от SQL Server, функция возвращает в свойстве (Property) InfoMessage дополнительную информацию: сообщение об ошибке, служебную информацию и т.д.

Примеры и пояснения есть в синапсисе функции, поддерживается справочная подсистема PoSh, все важные замечания приведены в комментариях на русском языке.

Распараллеливаем: пример использования

Т.к. выше у нас только функция, то нам понадобится дополнительный функционал для её вызова с нужными параметрами и обработки возвращаемых функцией результатов.
Далее пример рабочего скрипта, реализующего полезный функционал: перестроение индексов со степенью фрагментации более заданного порога. Можно использовать его как заготовку для ваших целей.

Внимание! Многопоточное перестроение индексов может загрузить ваш сервер баз данных на 100% и вызвать другие неожиданные последствия, я предупредил! Всегда тестируйте незнакомый код в тестовой среде.

Rebuild-SQLDBIndex.ps1
# Скрипт выполняет перестроение (rebuild) индексов в базах данных $SqlDBs на сервере SQL $SqlServer
# Операции могут выполняться параллельно в зависимости от параметра $ParallelLimit

# -----------------------------------------------------------------------------
# Задаём параметры и их дефолтные значения
[CmdletBinding()]
Param (
  # Параметры подключения к БД
  [Parameter(Mandatory=$True)][string]$SqlServer,  # Имя сервера (именованного инстанса) SQL
  [Parameter(Mandatory=$True)][string[]]$SqlDBs,   # Имя базы данных. Можно указать несколько значений (массив) имён баз данных
                                                   # на сервере SQL. В этом случае базы будут обработаны последовательно.
  [string]$Login,                                  # Логин для подключения к БД. Оставьте пустым для Integrated Security
  [string]$Password,                               # Пароль для подключения к БД. Оставьте пустым для Integrated Security
  [int]$RebuildIdxPercentThreshold = 15,           # Степерь фрагментации индекса, в %% для перестроения
  [int]$ParallelLimit = 4                          # Количество параллельно перестраиваемых индексов
)
# -----------------------------------------------------------------------------
# Подключаем внешнюю функцию Invoke-SQLRequest выполняющиую запрос к серверу SQL
# Предполагается что файл функции расположен в одной папке с этим скриптом
$ScriptFolder = $MyInvocation.MyCommand.Path | Split-Path -Parent
."$ScriptFolder\function_Invoke-SQLRequest.ps1"
# -----------------------------------------------------------------------------
# Обрабатываем последовательно все имена баз данных, указанные в $SqlDBs
$SqlDBs | ForEach-Object {
  $SqlDB = $_
  Write-Host "`n ┌───── База '$SqlDB' обработка начата ─────────────────┐"
  $ScriptStart = Get-Date
  # -----------------------------------------------------------------------------
  # Выполянем перестроение (rebuild) индексов в базе данных
  # -----------------------------------------------
  # Получаем текущую модель восстановления базы, если это Full - меняем на Bulk Logged
  $SQLRequest = "SELECT recovery_model_desc FROM sys.databases WHERE name = '$SqlDB'"
  $SQLOutput = Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $SQLRequest
  $SqlDBRecoveryModel = $SQLOutput.recovery_model_desc
  if ($SqlDBRecoveryModel -eq 'FULL') {
    $SQLRequest = "USE [master]; ALTER DATABASE [$SqlDB] SET RECOVERY BULK_LOGGED WITH NO_WAIT;"
    Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $SQLRequest
  }
  # -----------------------------------------------
  # Получаем список индексов для перестроения
  $StepStart = Get-Date; Write-Host " ├─┬── Получаем список индексов для перестроения, начало:" $StepStart.ToShortTimeString()
  $SQLRequest = "
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER ON
      IF OBJECT_ID('tempdb..#NotOnlineIndex') IS NOT NULL 
      DROP TABLE #NotOnlineIndex;
    CREATE TABLE #NotOnlineIndex (
       SchName nvarchar(64)
      ,TblName nvarchar(128)
      ,IdxName nvarchar(254)
      )
    CREATE CLUSTERED INDEX IXC_NotOnlineIndex ON #NotOnlineIndex
      (SchName, TblName, IdxName)
    INSERT INTO #NotOnlineIndex (SchName, TblName, IdxName)
    SELECT  SCHEMA_NAME(t.schema_id),
      t.name,
      i.name
      FROM sys.tables t WITH (nolock) 
      JOIN sys.indexes i WITH (nolock) 
        ON i.object_id = t.object_id
    WHERE t.is_ms_shipped = 0 
      AND i.name IS NOT NULL 
      AND (EXISTS (
        SELECT 1 
          FROM sys.index_columns ic
          JOIN sys.columns c
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
          JOIN sys.types ty
            ON c.user_type_id = ty.user_type_id
        WHERE ic.index_id = i.index_id
          AND ic.object_id = i.object_id
          AND (ty.name IN ('text','ntext','xml','image','geometry','geography')
          OR  (ty.name IN ('varchar','nvarchar','varbinary')
            AND c.max_length = -1)
        )
      )
  	OR EXISTS (
        SELECT 1 
        FROM sys.columns c
        JOIN sys.types ty
          ON c.user_type_id = ty.user_type_id
        WHERE i.index_id = 1 
  		AND c.object_id = t.object_id
  		AND ty.name IN ('text','ntext','image')
      )
    )
    SELECT QUOTENAME(idx.name) idxname, QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) tblname, p.rows, st.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats( DB_ID(),NULL,NULL,NULL,NULL) st
    JOIN sys.tables t ON (st.object_id=t.object_id)
    JOIN sys.schemas sc ON (sc.schema_id=t.schema_id)
    JOIN sys.indexes idx ON (st.object_id=idx.object_id and st.index_id=idx.index_id)
    JOIN sys.partitions p ON (p.index_id=idx.index_id and p.object_id=idx.object_id)
    WHERE st.page_count > 100
      AND st.alloc_unit_type_desc = 'IN_ROW_DATA'
      AND idx.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
      AND st.avg_fragmentation_in_percent > $RebuildIdxPercentThreshold
      AND st.index_id > 0
      AND NOT EXISTS (SELECT 1 FROM #NotOnlineIndex
        WHERE SchName = sc.name
          AND TblName = t.name
          AND IdxName = idx.name
      )
    ORDER BY st.page_count ;
    DROP TABLE #NotOnlineIndex;   
  "
  $SQLOutput = Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $SQLRequest -CommandTimeout 10800
  $RebuildIndexReq = @()
  foreach ($item in $SQLOutput) {
    $RebuildIndexReq += 'ALTER INDEX ' + $item.idxname + ' ON ' + $item.tblname + ' REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF, MAXDOP = 8);'
  }
  $StepEnd = Get-Date; Write-Host " │ └── Конец:" $StepEnd.ToShortTimeString() "Длительность:" ($StepEnd-$StepStart)
  # -----------------------------------------------
  # Выполняем перестроение (rebuild) индексов параллельно
  $StepStart = Get-Date; Write-Host " ├─┬── Выполняем перестроение (rebuild) индексов, начало:" $StepStart.ToShortTimeString()
  if ($RebuildIndexReq) {
    Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $RebuildIndexReq -CommandTimeout 345600 -ParallelLimit $ParallelLimit
  } else {Write-Host " │ ├───  " -NoNewline; Write-Host "Индексы не фрагментированы: rebuild  не требуется" -ForegroundColor DarkGreen}
  $StepEnd = Get-Date; Write-Host " │ └── Конец:" $StepEnd.ToShortTimeString() "Длительность:" ($StepEnd-$StepStart)
  # -----------------------------------------------
  # Если модель восстановления базы - FULL, то меняем её обратно с Bulk Logged на FULL
  if ($SqlDBRecoveryModel -eq 'FULL') {
    $SQLRequest = "USE [master]; ALTER DATABASE [$SqlDB] SET RECOVERY FULL WITH NO_WAIT;"
    Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $SQLRequest
  }
  # Конец перестроения (rebuild) индексов
  # -----------------------------------------------------------------------------
  $ScriptEnd = Get-Date; Write-Host " ├──── Полное время выполнения скрипта:" ($ScriptEnd-$ScriptStart)
  Write-Host " └───── База $SqlDB обработка завершена ──────────────────┘"
}
# Конец скрипта

Пояснения по работе со скриптом:

  • сервер SQL и имя базы данных задаются через параметры -SqlServer и -SqlDBs соответственно. Можно указать несколько баз данных в виде массива PoSh @(), например: -SqlDBs 'DBName1','DBName2','DBName3' ;

  • поддерживается авторизация для доступа к серверу SQL как Integrated Security (от имени учётной записи, под которой выполняется скрипт), так и с указанием логина и пароля. Если -Login или -Password не заданы (пустые значения), то скрипт делает попытку авторизации на сервере SQL с использованием Integrated Security;

  • количество потоков выполнения запросов задаётся ключом -ParallelLimit, по умолчанию - 4 потока. Т.е. в данном случае у вас будет выполняется перестроение 4 индексов одновременно;

  • в случае необходимости получить дополнительную служебную информацию (ошибки, статус и т.п.), возвращаемую SQL Server вместе с результатом, можно через свойство (Property) InfoMessage возвращаемого значения.

Например так:
$SQLOutput = Invoke-SQLRequest -SqlServer $SqlServer -SqlDB $SqlDB -SQLRequest $SQLRequest -CommandTimeout 345600 -ParallelLimit 12
Write-Host $SQLOutput.InfoMessage

  • для корректной работы скрипта в одной папке с ним предполагается наличие функции function_Invoke-SQLRequest.ps1, описанной выше.

Таким образом мы получили гибкую систему для параллельного выполнения операций на объектах в базе данных с возможностью максимальной утилизации ресурсов сервера SQL.

Задачи можно выполнять с любого доступного сервера, через планировщик задач или другие средства автоматизации, доработать под свои нужды и так далее: всё в ваших руках.

Конкретно в моём случае применение этих скриптов позволило ускорить выполнение нужных операций над базой данных в 6(!) раз и с запасом уложиться в отведённое окно. В процессе разработки и отладки скрипта ни один сервер SQL не пострадал, но под конец я точно видел лёгкий дымок от процессора.

Или не от процессора, но туман точно был :-)

Спасибо за внимание, конструктивные комментарии приветствуются.

Парсер на Хабре для PoSh далёк от идеала, по этому для комфортного восприятия с нормальной подсветкой копируйте код в ISE, VS Code или другой ваш любимый редактор.

С прошедшим днём сисадмина, коллеги!




Комментарии (2):

  1. mssqlhelp
    /#24603310

    С дефрагментацией и перестроением индексов пример не удачный. Во-первых, у ALTER INDEX есть параметр MAXDOP = max_degree_of_parallelism, во вторых, кроме распараллеливания есть ещё и выделение памяти. В последовательном сценарии риски чрезмерного вытеснения памяти пользовательских запросов меньше, а когда вы запускаете ребилд без оглядки на все возможные нюансы, можно здорово обжечься.

    У ребилда очень много нюансом и большинство нужно учитывать обязательно. Параллелизм из них отнюдь не первый.

    • nick-for-habr
      /#24603390

      В статье вопросы производительности отдельного оговорены.
      Общий «дисклеймер»:

      При этом далее предполагается, что у нас есть короткое регламентное окно, когда мы можем использовать всю (или большую часть) производительности сервера баз данных для своих целей, без учёта потребностей пользователей. Если у вас сервер SQL значительно нагружен в режиме 24/7 — вам нужно другое решение.
      и конкретно по параллельному перестроению индексов:
      Внимание! Многопоточное перестроение индексов может загрузить ваш сервер баз данных на 100% и вызвать другие неожиданные последствия, я предупредил! Всегда тестируйте незнакомый код в тестовой среде.

      Касательно параметра MAXDOP для ALTER INDEX — то:
      • главное: он работает только в редакции Enterprise, в Standard и ниже — молча игнорируется;
      • он в теории распараллеливает процесс в рамках одного индекса, что при наличии избыточной процессорной «мощи» может быть недостаточно быстро;
      • ну и когда я тестировал влияние этого параметра на тестовой копии рабочей базы в Developer Edition — разница была в рамках статистической погрешности.

      Собственно крах надежд, возлагаемых на MAXDOP — в том числе и сподвиг меня на написание нужной функции самому.
      И хотя основная задача и не была связана с индексами, но функция получилась универсальная и позволяет параллельно выполнять любые атомарные (не связанные друг с другом) запросы.
      В том числе использую её и для перестроения индексов, как показано в примере, с учётом указанных выше оговорок по производительности.