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

Опубликовано автором

В настоящей статье описывается процесс параллельного выполнения операций над базой данных 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 или другой ваш любимый редактор.

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


ссылка на оригинал статьи https://habr.com/ru/post/681166/

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *