Tips for DBA: Scripting jobs using Powershell (separated files)

Вашему вниманию предлагается сильно упрощённый пример сценария Powershell, который предназначен для скриптования заданий SQL Server в отдельные файлы. Тут используется папка для файлов C:\TEMP, которая должна быть предварительно создана и, желательно, пуста. Поскольку имена заданий будут использованы в качестве имён файлов, желательно, что бы в них не использовались недопустимые для имён файлов символы. Если это неудобно, попробуйте внести изменения в то место сценария, где подобные символы заменяются на пробелы.

# Scripting jobs using Powershell (separated files)
# Сценарий скриптования заданий в отдельные файлы
clear-Host
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo ") | Out-Null
$smoserver = new-object("Microsoft.SqlServer.Management.Smo.Server") "localhost"
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') "localhost"
# Цикл коррекции и скриптования заданий
foreach ($job in $smoserver.JobServer.Jobs)
{
# Замена недопустимых символов в имени файла на пробелы
  $JobName = $job.Name -replace ":",""
  $JobName = $JobName -replace "/","-"
  $scrp.Options.FileName = "C:\TEMP\" + $JobName + ".sql"
  $scrp.Script($job) | Out-Null
}
# Измененяем сценарии заданий, что бы отключить их неконтролируемый запуск
$file = Get-ChildItem "C:\TEMP\*.sql"
foreach ($str in $file)
{
  $content = Get-Content -path $str
  $content | foreach {$_ -replace "enabled=1", "enabled=0"} | Set-Content $str | Out-Null
}

Изменения в SQL Server 2012, связанные с диспетчером памяти

Новшества в DMV

sys.dm_os_memory_nodes

В этом представлении, консолидация одностраничного и многостраничного распределения отражена в колонке pages_kb. Новый счётчик foreign_committed_kb позволяет лучше управлять памятью в архитектуре NUMA и предоставляет в распоряжение SQL Server 2012 информацию о величине оперативной памяти, которая не попала в наиболее оптимальный с точки зрения задачи NUMA узел. Значение этого счётчика может быть полезным для определения причин медленного доступа к памяти. Сервер всегда будет пытаться использовать сначала ближнюю по отношению к узлу память, по крайней мере, пока не будет полностью занята локальная память этого узла.

sys.dm_os_sys_info

В этом представлении, как и в большинстве изменений в диспетчере памяти, приняты меры к стандартизации результатов измерений счётчиков производительности, относящихся к памяти.

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_entries

sys.dm_os_memory_clerks

sys.dm_os_memory_objects

Изменения в DBCC MEMORYSTATUS

Многие счётчики производительности команд DBCC, которые раньше относились к буферному пулу, теперь относятся к диспетчеру памяти, что стало следствием пересмотра архитектуры. Кроме этого, появились новые, дополнительные информационные поля, которые отражают ошибки OS и влияние топологии NUMA. В фазе роста используемой NUMA узлами памяти можно видеть рост использования памяти ещё до того, как будет достигнуто расчётное значение. Счётчик использования страниц (Pages In Use) показывает число используемых страниц, включая оборванные страницы, страницы базы данных и страницы зарезервированной памяти. Счётчик дальних распределений (Away Committed) показывает, сколько памяти узел взял у других узлов.

Счётчики производительности

Изменения в счётчиках производительности тоже связаны с консолидацией счётчиков под объектом диспетчера памяти и стандартизацией измерения счётчиков в килобайтах. Кроме того, несколько бесполезных счётчиков, относящихся к AWE, были удалены.

В следующих статьях будут затронуты некоторые дополнительные изменения информационной поддержки администрирования, и будет рассказано о новых сообщениях диспетчера памяти, которые могут появляться в журнале ошибок (errorlog).
Цель этих изменений состоит в том, чтобы сделать работу по администрированию диспетчера памяти легче, а информацию в административных представлениях сделать интуитивно понятной в большей степени, чем это было раньше. Также, необходимо было выявить и убрать те сущности, которые больше не имеют смысла, не используются при распределении страниц памяти и больше не поддерживаются, например, такие как AWE.

Дополнительные материалы:

 

• New and Modified DMVs in SQL Server 2012

Tips for DBA: Экспресс-диагностика достаточности памяти системе и экземпляру SQL Server

-- Быстрый тест проблем с памятью
-- По мотивам: http://bit.ly/LkT05M
WITH RingBufferXML
AS(SELECT CAST(Record AS XML) AS RBR FROM sys .dm_os_ring_buffers
   WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
  )
SELECT DISTINCT 'Зафиксированы проблемы' =
          CASE
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 0 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 2
                    THEN 'Недостаточно физической памяти для системы'
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 0 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 4
                    THEN 'Недостаточно виртуальной памяти для системы' 
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 0
                    THEN'Недостаточно физической памяти для запросов'
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 4 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint')  = 4
                    THEN 'Недостаточно виртуальной памяти для запросов и системы'
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 2 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 4
                    THEN 'Недостаточно виртуальной памяти для системы и физической для запросов'
                    WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
                         XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint')  = 2
                    THEN 'Недостаточно физической памяти для системы и запросов'
         END
FROM        RingBufferXML
CROSS APPLY RingBufferXML.RBR.nodes ('Record') Record (XMLRecord)
WHERE       XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') IN (0,2,4) AND
            XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') IN (0,2,4) AND
            XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') +
            XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') > 0

Оптимизации ввода-вывода для нагруженных баз данных

В докладе будут рассмотрены вопросы оборудования дисковых подсистем для задач SQL Server в приложениях хранилищ данных и OLTP нагрузки. Будут рассмотрены варианты использования сетей на основе Fibre Channel и коммутаторов SAN. Вы увидите какие ограничения могут накладывать на производительность ввода-вывода разные компоненты дисковой подсистемы. Будут даны основы современных дисковых подсистем и их важные особенности с точки зрения обслуживания нагрузки SQL Server. Будет показано, на какие параметры конфигурации СХД, операционной системы и СУБД нужно обращать внимание. В презентации будут представлены несколько примеров, демонстрирующих возможности современных дисковых подсистем.

Другие доклады Конференция "24 Hours of PASS. Russian Edition"
http://www.techdays.ru/videos/24%20HOP

Изменения в автоматическом обновлении статистики SQL Server -T2371

Изначально, в версиях SQL Server 2008 и 2008 R2 не было никаких крупных изменений в дизайне сбора и использования статистики. Появившийся ещё во времена разработки SQL Server 7.0 алгоритм, основанный на фиксированном пороговом значении, остался без существенных изменений. Превышение этого порога вызывало автоматическое обновление статистики. Пороговое значение, используемое изначально, равнялось 20%, и оно оставалось таким во всех выпусках SQL Server, которые до недавнего времени появлялись на рынке. Это означает, что до недавнего времени все выпуски SQL Server инициировали процедуру автоматического обновления статистики, если для столбца таблицы были зафиксированы изменения, которые затрагивали более 20% от числа строк таблицы. Обновлению подлежит индекс на основе B-дерева, который включает такой столбец в качестве первого столбца индекса, или если существует отдельная статистика для этого столбца среди относящихся к таблице статистик столбцов. Индексы, в которых не участвует этот столбец в качестве первого (ведущего) столбца индекса, не нуждаются в обновлении статистики. Ещё одно ограничение, это реализация в коде SQL Server 7.0 специальных защитных мер для маленьких таблиц от частого обновления статистики. Требуется, чтобы в таблице было, по меньшей мере, 500 строк (6 строк для временных таблиц), тогда автоматический пересчёт статистики у этой таблицы будет происходить после превышения двадцатипроцентного порога изменений.

Как автоматическое обновление статистики используется приложениями SAP

SAP взяло на вооружение автоматическое обновление статистики сразу же после его появления, и десятки тысяч клиентов много лет с успехом использовали эту функциональность в своих приложениях на базе SAP Netweaver. Это положение заметно отличается от других СУБД, для которых обновление статистики у таблиц необходимо планировать как постоянную задачу. Компания SAP рекомендует использовать эту функциональность SQL Server в полном объёме и для всех своих приложений. Применительно к SAP BW существовали небольшие проблемы с тем, что требовалось иметь в таблице не меньше 500 строк. Поэтому в SAP BW были разработаны и документированы специальные процессы, предназначенные осуществлять дополнительное обновление статистики там, где не работало автоматическое обновление статистики. Документ называется: SAP OSS Note №849062 - "Optimizer statistics for InfoCubes in BW". Однако, для заказчиков систем SAP сегодня характерно увеличение объёмов данных, поддержка 64-разрядности и общая тенденция использования платформы Intel для обработки большого количества информации, необходимой бизнес - приложениям. С SAP ERP сегодня работает большое количество клиентов, а некоторые таблицы могут содержать миллиарды строк. Во многих случаях приходится сталкиваться с ситуациями, когда автоматическое обновление статистики с фиксированным порогом 20% требует изменения сотен миллионов строк в таблице, и только после этого будет запущено очередное автоматическое обновление статистики. В результате пользователи жаловались, что обновление статистики выполняется крайне редко, устаревшая статистика иногда приводит к использованию неоптимальных планов исполнения запросов.

Новое решение

В SP1 для SQL Server 2008 R2 и в последующих версиях SQL Server можно использовать флаг трассировки, который изменяет фиксированный порог обновления статистики 20% на динамически изменяющуюся величину процента изменений таблицы. Чем в таблице больше строк, тем ниже порог обновления статистики. Например, если этот флаг трассировки активирован, процедура автоматического обновления статистики для таблицы с миллиардом строк будет выполнена при изменении миллиона строк. Если же флаг трассировки не активирован, та же таблица с миллиардом записей не получит автоматического обновления статистики, пока в ней не накопится 200 миллионов изменений. Для того чтобы активировать эту новую возможность, нужно включить флаг трассировки 2371. После этого, порог для триггера обновления статистики будет зависеть от числа строк в таблице. Однако, требование того, что в таблице для возможности автоматического обновления статистики должно быть не меньше 500 строк, сохраняется. Как и прежде, обновление статистики будет запускаться на основании результатов исследования пробной выборки значений, предустановленной величины, которая динамически изменяется в зависимости от числа строк в таблице.

Более подробно о том, что даёт использование флага трассировки 2371

На представленном ниже графике видно, как работает новая функциональность. Для небольших таблиц порог по-прежнему будет примерно 20%. Только когда таблица превысит 25000 строк, начнёт действовать динамическое изменение порога срабатывания, когда при увеличении количества строк, процент изменённых записей становится все ниже и ниже. Например, в таблице с 100000 строк порог для триггера обновления статистики будет снижен до 10%. В таблице с 1000000 строк потребуется изменить около 3,2%, после чего сработает триггер автоматического обновления статистики.

В таблицах с 10000000 и 50000000 строк необходимые для обновления статистики изменения будут составлять не более 1% или 0,5% от общего числа строк. Для таблицы с 100000000 строк потребуется около 0,31%. Такое поведение нового механизма автоматического обновления статистики приведёт к тому, что для наиболее важных таблиц в схемах SAP, которые подвержены большому числу изменений, статистика будет обновляться чаще. Применительно к приложениям SAP это может быть в 20-60 раз чаще, чем это было раньше, когда после перехода в новый финансовый месяц или год необходимое число изменений накапливалось только через несколько недель или несколько месяцев, с новым флагом счёт пойдёт на дни.

Последствия включения флага трассировки

Как было показано выше, автоматическое обновление статистики будет выполняться чаще для тех таблиц, у которых изменения происходят с высокой частотой. Недостатком такого подхода является то, что обновление статистики приводит к перекомпиляции запросов, относящихся к этой таблице. Это может повысить риски получения другого плана исполнения запроса при повторном его исполнении. С другой стороны, динамический порог для триггера обновления статистики необходимо рассматривать как средство, призванное решать известные проблемы выбора оптимального плана для нового месяца или нового финансового года, когда статистика ещё актуальна для месяца предыдущего отчётного периода.
Поскольку новая функциональность только появилась, ещё нет никаких рекомендаций о том, как в целом её следует использовать для приложений SAP. Тесты этого нового флага, проведённые Microsoft в течение нескольких месяцев, оказались весьма успешными, и эта функциональность будет добавлена в новую версию SQL Server 2012, и в SAP ERP, которая будет построена на основе поддерживающих флаг версий SQL Server уже в середине ноября 2011 года. Однако авторы статьи хотели бы призвать клиентов SAP испытать новую функциональность, если запросы с устаревшей статистикой создают проблемы, или используются специальные процедуры, описанные в "OSS note #1558087". Новая функциональность становится доступной после установки Service Pack 1 для SQL Server 2008 R2, и будет доступна в следующей версии SQL Server 2012. В новой версии тоже нужно будет включать соответствующий флаг трассировки. Планов включить эту функциональность в более ранние версии SQL Server, такие, как SQL Server 2008 или 2005, нет.

Повышение пропускной способности сетевых интерфейсов для SQL Server с помощью настройки параметров RSS

Рецензенты: Thomas Kejser, Curt Peterson, James Podgorski, Christian Martinez, Mike Ruthruff
Перевод: Александр Гладченко
Технические редакторы перевода: Алексей Халяко, Ирина Наумова

Функциональность Receive-Side Scaling (RSS) впервые появилась в Windows 2003. Это нововведение было призвано повысить возможности масштабируемости операционной системы Windows, и этим предоставить новые возможности по обслуживанию большого сетевого трафика. Такой трафик характерен для систем, где SQL Server обслуживает OLTP нагрузку. Подробное описание того, какие усовершенствования RSS получила операционная система Windows 2008, можно узнать из отчёта - Receive-Side Scaling Enhancements in Windows Server 2008 и в блоге - Scaling Heavy Network Traffic with Windows.

Недавно автор работал с партнёром над тестированием возможностей масштабирования сетевой нагрузки SQL Server. В тестах использовался сервер DL980 с 8 процессорными сокетами, которые представляли системе 80 физических ядер, и управлялось всё это операционной системой Windows 2008 R2 sp1. В распоряжении SQL Server было 4 сетевых карты производительностью 1Gbps, они поддерживали сетевой трафик между SQL Server и серверами приложений. К удивлению автора, максимальная загрузка во время теста наблюдалась только на 2-х из 80-ти процессорных ядер, и практически вся эта нагрузка приходилась на привилегированный режим работы процессоров (privileged/DPC % time). Известно, что Windows 2008 R2 по умолчанию использует до 4-х процессорных ядер для управляющих модулей, относящихся к RSS (см. упомянутую выше статью). Почему же в данном случае использовалось только 2 процессора? Стоит отметить, что то время, которое процессоры работали в привилегированном режиме, могло относиться к деятельности драйверов устройств, таких как сетевое оборудование и драйверы системы хранения данных. Воспользовавшись входящим в комплект Windows SDK инструментом XPerf, можно проследить работу подобных устройств на уровне драйверов и выявить основного потребителя привилегированного режима процессоров. В данном случае, большая часть этого режима использовалась для обслуживания драйвера сетевых устройств NDIS.sys.

Подозревая, что описанное выше поведение могло проявиться в Windows ошибочно, автор явным образом задал значение для ключа реестра HLKM\System\CurrentControlSet\Services\Ndis\Parameters, установив параметру MaxNumRssCpus значение 8 (подробности об этом ключе можно найти в упомянутом выше статье), в надежде, что эта установка позволит привлечь дополнительные процессоры, для обслуживания прерываний, связанных с передаваемыми по сети пакетами. Но, эта настройка никак не повлияла на число используемых процессоров. Автор обратился за консультациями к коллегам в Windows Networking Team, с просьбой помочь решить эту проблему. Пока ответ ещё не был получен, была предпринята попытка подключить серверы приложений используя все 4 сетевые платы (серверы приложений были поделены равномерно по количеству сетевых карт, каждая группа серверов подключается только к одному IP-адресу). К всеобщей радости, большой процент привилегированной нагрузки теперь наблюдался на 8ми ядрах.

Те выводы, которые были получены методом проб и ошибок, приводили к тому, что каждая сетевая плата/IP-адрес может использовать в RSS только 2 процессора. Что же стало причиной такого ограничения? Windows Networking Team ответила на этот вопрос и посоветовала проверить установку значения "RSS rings" для каждой сетевой платы. К слову, этот термин может отличаться у разных производителей сетевых плат. Иногда можно встретить термин "RSS Queues", который, по сути, обозначает то же самое. После активации данной настройки RSS, каждое обслуживающее работу сетевых плат ядро процессора будет зависеть не только от упомянутого выше ключа реестра MaxNumRssCpus, но и от ассоциированного с ним RSS кольца. Иными словами, количество CPU, участвующих в разделении принимаемой сетевой нагрузки (Receive Side Scaling) будет определяться системой, исходя из наименьшего значения одного из параметров: значения ключа реестра MaxNumRssCpus, а так же значения"RSS Rings" установленного в свойствах сетевой карты. Проверка установки сетевой платы "RSS rings" (закладка "Дополнительно" в свойства платы) показала, что значение действительно было установлено в 2, что объясняло, почему только 2 процессорных ядра были использованы для RSS, когда использовалась только одна сетевая плата. Ещё раз напоминаем, что название для установки "RSS rings" может отличаться у разных производителей аппаратных средств. В описанной выше системе каждая сетевая плата поддерживала до 4-х "RSS rings", таким образом, максимальное число RSS ядер на каждую сетевую плату может быть 4. (см. Рисунок 1 ниже). Также имейте в виду, что RSS процессоры могут быть назначены только в первой K-Group, если используется Windows 2008 R2 или предшествующие версии ОС. В блоге, там где описана настройка DL980, есть подробности о K-Group и других аспектах рассматриваемой тут темы - Customer Proof of Concept on New HP DL980.

Рисунок 1

В итоге, в качестве решения проблемы был избран вариант с масштабированием сетевой нагрузки, когда использовались 4 сетевые платы со своими IP адресами. Это позволило получить нужную пропускную способность сетевых интерфейсов, и обеспечить обслуживание RSS таким числом процессоров, которое позволило эффективно обслужить большой сетевой трафик. Конечно, можно использовать более производительные сетевые платы 10Gbps, но при этом следует обязательно задавать правильные значения для "RSS rings".

В качестве резюме можно выделить два основных момента в настройке RSS для обслуживания нагрузки SQL Server с большим сетевым трафиком:

1. Настройте в Windows максимальное число задействованных для RSS процессоров ("starting RSS CPU") руководствуясь рекомендациями в статье: Receive-Side Scaling Enhancements in Windows Server 2008
2. Для каждой используемой сетевой платы задайте значения "RSS Rings" ("RSS Queues") таким образом, чтобы сумма этих значений для всех сетевых плат соответствовала значению MaxNumRssCpu в реестре Windows. Учтите, что максимально возможное значение у разных производителей плат может отличаться. Чтобы обеспечить необходимую производительность обслуживания большого трафика по сети, возможно, придётся обновить драйвера/firmware или установить дополнительные сетевые платы.

Примечание переводчика

Для демонстрации ещё одного варианта реализации "RSS rings" и использования для него иного термина, приведём тут два скриншота окна настройки параметров сетевых плат. Оба скриншота для сетевых плат Broadcom, на втором (Рисунок 3) помимо RSS использован режим Teaming.

Рисунок 2

Рисунок 3

Счётчики производительности, позволяющие идентифицировать узкие места дисковой подсистемы SQL Server

Описание проблемы

Известны несколько статей об обнаружении проблем ввода-вывода, связанных с SQL Server. Существуют разные методы поиска «узких мест» ввода-вывода, мы же сконцентрируемся тут на вопросе: Какие счётчики производительности необходимы для того, чтобы быстро понять, являются ли диски «узким местом»?

Решение

В сети можно найти много разной информации о том, как определить возможные узкие места ввода-вывода. Доступная информация настолько обширна, что в ней легко потеряться. Быстро определить является ли узким местом дисковая подсистема достаточно трудная задача, решение которой главным образом зависит от типа хранилища, которое может быть: DAS, SAN, NAS, iSCISI, виртуальный диск и т.п. В большинстве организаций диски доступные для SQL Server - это «чёрный ящик», что делает задачу определения реального размещения данных достаточно тяжёлой.
Во время конференции PASS Summit 2010 года на встрече SQL Clinic автор общался с экспертами в области анализа загрузки дисков на тему того, как быстро идентифицировать возможные проблемы с дисками. В конце беседы Premier Field Engineer представил очень простой, но эффективный набор счётчиков производительности для такого анализа.
Основная идея сводится к тому, чтобы использовать такие счётчики производительности, которые не нуждаются в дополнительной информации, требующей интерпретации. Есть несколько подходящих счётчиков, но только некоторые из них позволяют измерить абсолютные величины в том смысле, что они не нуждаются в дополнительных измерениях показаний других счётчиков или интерпретации полученной информации.
Таким образом, можно выделить два основных счётчика, которые можно использовать для того, чтобы быстро проанализировать работу дисков:

• Avg. Disk sec/Read - показывает среднее время в секундах, потраченное на чтение данных с диска.
• Avg. Disk sec/Write - показывает среднее время в секундах, потраченное на запись данных на диск.

Эти два счётчика измеряют время ожидания непосредственно в той программной надстройке, где диски устройства хранения данных становятся доступны операционной системе. Они позволяют точно измерить, сколько времени диски и аппаратное окружение потратили на обслуживание запросов ввода-вывода независимо от того, какие были задействованы аппаратные средства.
Используя эти счётчики при исследовании загрузки нескольких дисков, можно выяснить то, как обстоят дела с каждым отдельным диском, таким образом, можно понять, является ли узким местом какой-либо из исследуемых дисков.
Подобно правилу буравчика, если мы имеем дело с OLTP системой, среднее значение должно быть меньше 15 ms со всплесками до 25 ms. Если у вас эти значения ниже указанного порога, тогда ваш ввод-вывод не испытывает затруднений, и никакой дополнительный анализ узких мест дисков больше не потребуется. Это эмпирическое правило может использоваться для любого типа системы. Чем меньше времени требуется для чтения или записи данных, тем быстрее будет ваша система.
Картинка ниже показывает Avg. Disk sec/Read для диска C:. Можно видеть, что среднее значение порядка 0.130, что составляет 130 ms, и максимальное значение 1.089, что составляет 1089 ms. Таким образом, у исследуемой системы ввод-вывод является узким местом, что видно из полученных значений счётчиков. Если бы интерпретировалось значение Avg. Disk sec/Write, оно привело бы к аналогичным выводам.

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

• Disk Transfers/sec – демонстрирует нагрузку операций чтения и записи на диск.
• Disk Reads/sec - нагрузка операций чтения с диска.
• Disk Writes/sec - нагрузка операций записи на диск.
• Avg. Disk Queue Length - показывает среднее значение числа запросов чтения и записи, которые стояли в очереди к выбранному диску во время интервала измерений.
• Current Disk Queue Length - показывает число запросов, адресованных выбранному диску в то время, когда непосредственно выполнялись измерения.

Заключение

Целью этой статьи была рекомендация счётчиков производительности, которые годятся для экспресс-анализа того, является ли дисковая подсистема узким местом в работе компьютера. Если вы наблюдаете превышающие пороговые значения этих счётчиков, понадобится потратить дополнительное время на то, чтобы «раскопать» источник проблемы.

Дополнительные материалы

• How to Identify I/O Bottlenecks in SQL Server
• Speeding up SQL by Maximizing Disk IO
• Free Microsoft Tools to Help Setup and Maintain Perfmon for SQL Server
• System Monitor (Perfmon) Counters for SQL Server 2005
• Top Perfmon Counters for Analyzing SQL Server
• Setting up Performance Monitor to always collect SQL Server
• Free Poster – SQL Server Perfmon Counters of Interest
• Collecting performance counters and using SQL Server to analyze

Стратегия управления глубиной очереди ввода-вывода для достижения пиковой производительности

Автор, наконец, нашёл время для тестирования массива твердотельных дисков (SSD), собирая в массивы от нескольких до 20 устройств, управляемых двумя контроллерами с 4x4 портами Serial Attached SCSI (SAS). Во время предварительных тестов, когда глубина очереди обращения к дискам была очень высокой, он наблюдал большую задержку обращения к дискам, которая во время проведения ряда операций для чтения превышала 100ms и достигала более 400ms для операций записи.

Таким образом, возникают следующие вопросы:

1. Так ли уж хорошо сказывается на производительности большая глубина очереди и длительные задержки?
2. Способна ли большая глубина очереди породить проблемы со временем отклика других операций?
3. Можно ли избежать этих проблем, не теряя в производительности?

Давайте начнём искать ответы на эти вопросы, исследуя принципы использования одиночного диска, после чего необходимо будет проделать все тесты с дисковым массивом под управлением кэширующего RAID контроллера, а затем, уже в конце мы поработаем с полномасштабной системой хранения, подключённой через сеть хранения данных (SAN) и адаптированной для использования SSD дисков. Если обратиться к распространённым рекомендациям по настройке систем хранения, можно увидеть, что они зачастую цитируются без ссылок на соответствующие исследования или явного указания контекста их применимости. Мы же попытаемся проникнуть в самую суть вещей, и понять, почему упрощённая модель организации глубины очереди ввода-вывода, используемая SQL Server для доступа к листовому уровню таблиц, может быть улучшена за счёт более развитых моделей работы с дисками, а также за счёт использования SSD дисков и высокопроизводительных систем хранения данных (СХД).

Теория жёстких дисков (что такое IOPS)

Стандартная теория ввода-вывода подразумевает, что при случайном типе доступа к диску среднее время доступа к данным равно сумме задержек следующих операций:

• Позиционирование головки диска над искомой дорожкой (rotational latency);
• Среднее время поиска (seek time);
• Время передачи данных (transfer time);
• Издержки на обработку команд (command overhead);
• Задержки при доставке данных инициатору запроса (propagation delays).

Для запросов ввода-вывода с маленькими величинами блоков данных существенны только два первых слагаемых. Для дисков с 15000 оборотами на шпинделе (15K) среднее значение «rotational latency» составляет 2 ms, а для типичного 3,5 дюймового диска с 15K среднее число «seek time» около 3,4 ms. Включая эти два наиболее весомых значения и другие, менее значимые составляющие, общее время доступа может достигать в среднем до 5,5 ms. В результате чего можно выделить два ключевых признака:

1. При произвольном доступе к данным, они считаются распределёнными по всему диску.
2. При глубине очереди 1, выполняется один запрос на ввод-вывод; следующий запрос выполняется после завершения предыдущего. Для 15K диска можно получить до 180 операций ввода-вывода в секунду (IOPS) с глубиной очереди 1 и при произвольном доступе к данным, которые рассредоточены по всему диску.

А очень часто просто забывают упомянуть про эти два ключевых фактора!

В случае если доступ осуществляется к данным, хранящимся на относительно небольшой группе сопредельных цилиндров диска, среднее время поиска будет меньше, а при глубине очереди равной 1 производительность в IOPS-ах окажется выше.

Случайное чтения с жёсткого диска и варьирование глубины очереди – сравнение в IOPS-сах

Давайте теперь обратимся к смыслу второго из упомянутых выше факторов, и попытаемся сопоставить в IOPS-ах случайное чтение и чтение при разной глубине очереди. При более глубокой очереди можно наблюдать множество запросов ввода-вывода к диску, которые обслуживаются вместе, или возможно обслуживание более поздних запросов ввода-вывода до завершения более ранних. Дисковый контроллер может переупорядочить ввод-вывод, стараясь сократить время между отдельными порциями ввода-вывода, что позволяет увеличить IOPS-ы и при этом получить более высокие значения задержек для каждого отдельного запроса ввода-вывода.

Ситуация становится незначительно лучше, если увеличить глубину очереди до 2-х. Можно получить до 200 IOPS, против упомянутых выше 180-ти. Ещё лучше результаты получаются при глубине очереди равной 4 - 240 IOPS. Если продолжить удваивать глубину очереди вплоть до 32-х, можно получить ещё небольшой прирост на каждое приращение, порядка 40-50 IOPS каждое. После этого, до глубины очереди равной 64 увеличение будет совсем незначительное. К слову, диски, выпущенные до 2005 года, могли обслуживать очереди до 64 задач. Современным дискам по плечу очередь глубиной 128. При каждом удвоении глубины очереди также происходит практически удвоение задержки.

На рисунке №1 показано какой эффект оказывает увеличение глубины очереди на производительность в IOPS. На рисунке видно, что при глубине очереди запросов ввода-вывода равной 64-м, когда данные распределены по всему диску, удавалось получить до 400 IOPS. Другой, граничный случай, когда данные занимали 2,8% диска, примечателен повышением производительности относительно других измерений, с меньшими глубинами очереди. Так, например, для глубины очереди 1, удалось достичь почти 300 IOPS.

Рисунок 1: Зависимость производительности в IOPS от глубины очереди при разном объёме данных не диске.

Если грамотно учесть оба фактора, можно поднять производительность до 600 IOPS на диск.

Рисунок 2: Зависимость задержки от глубины очереди при разном объёме данных не диске.

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

Если процент запрашиваемых данных невелик, то потери от задержек будут заметно меньше, даже если глубина очереди велика. Снижение глубины очереди или процента запрашиваемых с диска данных положительно сказывается на производительности жёсткого диска, но когда оба фактора задействованы одновременно, выигрыш поистине впечатляющий. Для характерных OLTP нагрузок время отклика (а, следовательно, и задержка ввода-вывода диска) имеет такое же большое значение, как и такой показатель производительности, как пропускная способность. Именно поэтому, такое большое распространения получило правило поддерживать для OLTP среднюю глубину очереди, которая не превышает двух запросов на диск (шпиндель). Это правило было популярно в то время, когда наибольшее распространение имели диски со скоростью вращение шпинделя 7200 и 5400 об/мин.

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

Стоит особо подчеркнуть важность перечисленных ключевых факторов, поскольку до сих пор принято было опираться на правило, которое гласит: «глубина очереди одного диска должна быть не выше 2».

RAID - контроллеры и дисковые массивы

В былые времена мы оперировали в основном группами дисков (JBOD). Популярные РСУБД учитывали этот подход, обеспечивая поддержку для баз данных множества файлов и файловых групп. Затем появились light-RAID (не путать с названием книги) и RAID- контроллеры. Все увидели, что RAID позволяет значительно сократить количество "дисков", которыми приходится управлять на уровнях базы данных и операционной системы. Дисковый массив представляется в последней как один логический диск, а счётчики производительности, обычно, используются те, что предоставляет она, а не система хранения данных (СХД).

Правило, по которому глубина очереди не должна для одного диска превышать 2 (и другие сопутствующие признаки) не соотносятся напрямую с глубиной очереди, определённой по счётчикам производительности операционной системы. Таким образом, получило распространение несколько иное правило, гласящие, что задержки при обращении к диску не должны превышать примерно 10 – 20 ms, что было вычислено из прежнего правила глубины очереди 2 на диск (вероятно, для дисков с 7200/10000 об/мин), причём, без учёта описанных выше дополнительных признаков.

В большинстве случаев задержка доступа к данным ниже 10 ms говорит о том, что время отклика транзакции считается очень хорошим. Задержки в диапазоне 10 – 20 ms говорят о приемлемом времени отклика транзакции. Задержки больше 20 ms говорят о большой загруженности дисковой подсистемы. Важно понимать, что появление кратковременных скачков ввода-вывода может привести к тому, что рассчитанная по задержкам глубина очереди может оказаться высокой, в то время как время отклика будет характеризоваться картиной с острыми пиками значений. Т.е. несмотря на то, что среднее время отклика транзакции можно считать приемлемым, может существовать большое число запросов с очень плохим временем отклика.

Что происходит с глубиной очереди для RAID массива при случайном чтении

Как повлияет на характеристики ввода-вывода использование нескольких дисков, собранных в один RAID - массив? Если происходит чтение с такого логического диска, то это равносильно тому, что ввод-вывод будет направлен только на один диск массива (то есть, глубина очереди = 1), и всего один из дисков обслужит ввод-вывод. Остальные диски будут практически невостребованны, пока активность из расчёта на один диск не превысит 180 IOPS, направленных на эту группу дисков в RAID - массиве. При глубине очереди 2, вероятно, что пара запросов ввода-вывода распределилась бы между разными дисками, таким образом, что два диска будут работать с глубиной очереди 1, а остальные диски останутся неактивными. Очередь к одному диску массива станет больше единицы только тогда, когда нагрузка на массив превысит возможности обслуживания без очередей запросов ко всем дискам, которые сегодня позволяют получать 180 IOPS при 15 K оборотов на шпинделе, и с небольшой корректировкой из-за вероятности неравномерного распределения нагрузки.

Глубина очереди всего массива будет поделена между несколькими очередями к его отдельным дискам и, в зависимости от производительности в IOPS-ах входящего в массив диска, будет определяться зависимость производительности от глубины очереди к RAID – массиву. Тут важным моментом является то, что при глубине очереди 1, запрос ввода-вывода попадёт на один из дисков со своими характеристиками по IOPS, которые будут отличаться от производительности в IOPS-ах всей дисковой группы RAID – массива.

Задержка записи в журнал

В те времена, когда диски подключались напрямую к серверу, распространённой рекомендацией было использовать для файла журнала отдельный массив RAID1, т.е. пара зеркальных дисков рекомендовалась для каждого журнала транзакций базы данных с высокой транзакционной нагрузкой. Об этом редко говорят, но наиболее характерным значением величины задержки для операций записи в журнал (которые имеют характер последовательного ввод-вывода и маленький размер блока запроса) была величина, сопоставимая с 0.3ms, что приблизительно соответствовало 3000-5000 IOPS.

Производители оборудования сетей SAN часто рекомендуют не беспокоиться о выделении отдельных физических дисков для каждого журнала транзакций высокотранзакционной базы. Подразумевается, что внешняя дисковая подсистема, настроенная по рекомендациям вендора, сама прекрасно справится с этой задачей. Однако, такие результаты с очень низкой задержкой записи в журнал, как у напрямую подключённых дисков (которые, к тому же, могут обслуживаться выделенным процессором) для сетей SAN всё ещё недостижимы. Поскольку системы хранения в сетях SAN получают всё большее распространение, Microsoft внёс изменения в ядро хранения SQL Server, чтобы обеспечить возможность многопоточной записи в журнал транзакций (SQL Server 2000 SP4 и 2005 RTM позволяли обслуживать 8 одновременных запросов ввода-вывода в журнал транзакций каждой базы данных). SQL Server 2005 SP1 позволяет одновременное обслуживание 8 запросов ввода-выводов для 32-разрядной редакции SQL Server и 32-х запросов ввода-вывода для 64-разрядной редакции сервера, с предельным размером запроса 480KB. В SQL Server 2008 предельный размер повышен до 3840KB.

Случайная запись на RAID маленькими блоками

В этой статье мы не станем рассматривать те издержки записи, которые происходят внутри RAID. Однако нужно отметить, что правила для RAID5 и RAID10 часто цитируются без учёта описанных выше признаков. Как правило, говорят о производительности операций записи RAID5 и только применительно к случайной записи маленькими блоками. На контроллере без кэширования можно получать производительность ввода-вывода для записи в IOPS близкую к аналогичным характеристикам для чтения. Стоит отдельно отметить, что результаты сравнения могут отличаться для разных уровней RAID, поскольку у них разные издержки, а также на это может влиять специфика выбранной модели контроллера.

Кэширующие RAID - контроллеры и запросы ввода-вывода на чтение

В других статьях блога автор уже объяснял, почему такое кэширование запросов на чтение непродуктивно. В основном это объясняется тем, что ядро базы данных само кэширует данные, и этот кэш намного ближе и менее затратен с точки зрения доступа, чем кэш контроллера СХД. Кроме того, если сервер сконфигурирован в соответствии с рекомендациями, ядро базы данных будет иметь буферный кэш существенно большего размера, чем кэш СХД. Вероятность того, что к данным в кэше СХД будет осуществляться повторный доступ, очень мала. К тому же, издержки на кэширование запросов чтения являются для них весьма существенными, и это важный фактор для тех СХД, которые настраиваются на получение высоких значений IOPS. Издержки кэширования чтения на контроллерах СХД будут потому, что блоки данных в кэше никогда не будут запрашиваться снова, это работа будет бесполезна. У систем, которые используются в эталонных тестах TPC, кэширование чтения обычно отключается, и делается это из соображений, которые были только что перечислены.

Один уважаемый источник утверждал, что использование маленького ~ 2MB (не GB!) кэша чтения для LUN, позволяющего включить упреждающее чтение, является наиболее удачной стратегией. Я же помню, когда-то говорили, что некоторая система с сервером, у которого было 48GB оперативной памяти, показала лучшую производительности ввода-вывода, когда кэш на SAN был увеличен с 80GB до 120GB. Всё это показывает, что подобные результаты можно рассматривать под разными углами.

Кэширующие RAID - контроллеры и запросы ввода-вывода на запись

А теперь давайте обратимся к показателям производительности запросов ввода-вывода для записи, носящих случайный характер и обсуживающихся кэширующим RAID - контроллером. До сих пор, мы только ходили вокруг запросов ввода-вывода на запись, но на это есть своя причина. На Рисунке 3 показана производительность в IOPS для случайных запросов на запись с маленьким блоком, которые обслуживает кэширующий RAID - контроллер. Когда SQL Server или операционная система посылают RAID - контроллеру один или более запросов ввода-вывода для записи, записываемые данные помещаются в кэш контроллера, а серверу отправляется сообщение о завершении операции записи. После этого, становится возможным отправка следующего запроса ввода-вывода. При этом каких либо заметных изменений в производительности СХД в IOPS не произойдёт, хотя глубина очереди будет расти. Будет наблюдаться очень низкая задержка операций записи, пока не будет преодолено наивысшее значение IOPS для возможностей кеширующего контроллера.

Рисунок 3: пример IOPS для случайных запросов записи с маленьким блоком и с кэширующим RAID - контроллером

После преодоления этого порога, когда кэш записи будет заполнен, величина задержки запроса станет высокой, пока сервер не вернётся в исходное состояние, с небольшим объёмом запросов ввода-вывода на запись.

Большие СХД с большими наборами RAID

На протяжении нескольких лет СХД становились всё более мощными, их вычислительные возможности увеличивались на 40% ежегодно. В то же время производительность жёстких дисков в год растёт не так заметно, в среднем меньше чем на 10% (от 7,2K к 10K и к 15K, и это ничто по сравнению с SSD). Этот разрыв в росте вызвал необходимость создания СХД с очень большими количествами дисков. Именно в те времена получили распространение сети SAN, которые придумали специально для больших СХД.

Очень быстро стало понятно, что SAN не могут обеспечить ожидаемую производительность в IOPS, которую может суммарно выдать задействованное число дисков. Первая причина этого заключается в возможностях контроллера адаптера шины Fibre Channel (FC HBA), для которого глубина очереди по умолчанию составляла 32 (на адаптер, а не на порт СХД). Такое ограничение для значения очереди по умолчанию было введено из тех соображений, что доктриной SAN считается следующее: SAN - это поставщик совместно используемого дискового пространства. Ограничение призвано препятствовать тому, чтобы один узел создавал слишком большую нагрузку ввода-вывода. Для ограничения нагрузки, на HBA для глубины очереди устанавливали маленькое значение, в результате чего все узлы могли бы гарантировано получать свою долю от максимально возможного объёма ввода-вывода.

Если измерить зависимость IOPS от установки глубины очереди на HBA для внешнего LUN, состоявшего из большого числа дисков, станет видно, что производительность в IOPS увеличивается с увеличением глубины очереди от минимальных до максимальных значений. Это поведение похоже на то, о котором мы говорили выше, когда обсуждали зависимость производительности RAID массивов в IOPS от глубины очереди.

Выбор глубины очереди для FC HBA

Важно помнить, что раньше глубина очереди устанавливалась ко всем портам HBA или для каждого оптического порт HBA в отдельности. У последних моделей Emulex FC HBA значением по умолчанию для глубины очереди устанавливается 32 на каждый LUN. Можно настроить глубину для LUN или для всего получателя (target). QLogic использует для этого термина «Execution Throttle» и глубина по умолчанию равна 256. Как то, в одном из отчётов к эталонному тесту TPC-C, в котором использовалась система с сетью хранения SAN, была дана ссылка на зависимость производительности от изменения глубины очереди HBA: глубина менялась от 32 до 254 и без каких-либо дополнительных разъяснений. В системах для TPC-C всегда используются очень большое количество дисковых массивов. Конечно же, для таких систем следует выбрать установку глубины очереди на HBA в максимальное значение. В упомянутом документе об этом было сказано в краткой форме, и рекомендовано изменить глубину очереди HBA с 32 до 254, что соответствует рекомендованным практикам, часто встречающимся в документах Microsoft. Те документы, которые я видел, не содержали в себе объяснения первопричины такого эффекта и в них отсутствовали рекомендации по поддержке данного варианта настроек.

Стоит поговорить о конфигурации SAN с небольшим числом дисковых массивов. Резонен вопрос, нужно ли менять стандартную установку глубины очереди на LUN, если каждый LUN состоит из четырёх дисков? Требуется ли в таком случае увеличивать глубину очереди до 254? Ответ будет зависеть от тех инструкций, которые были даны ранее при обсуждении зависимости производительности в IOPS и задержек от глубины очереди, с учётом поправок на число дисков, которые относятся к LUN. Необходимо взвесить все за и против, и в дальнейшем исходить из того, является ли вашей целью обеспечение высокой скорости отклика, присущей OLTP системам, или вы предпочитаете работать с чистой пакетной/DSS нагрузкой.

Последовательный ввод-вывод

Все разговоры о зависимости IOPS от глубины очереди до сих пор не касались варианта с последовательным дисковым вводом-выводом. Последовательного ввода-вывода с большими блоками и глубиной очереди 1 на каждый LUN может оказаться вполне достаточно, чтобы утилизировать максимальную пропускную способность ввода-вывода, при условии, что эта нагрузка достаточно большая, и её хватало бы для загрузки всех дисков, из которых состоит LUN. Автор склонен полагать, что, следуя теории, размер нагрузки ввода-вывода помноженный на глубину очереди должен быть больше чем число дисков в массиве, помноженное на размер блока RAID – массива (размер сегмента/чанка/блока страйпа). Эта уверенность основывается на том, что у каждого диска будет для обслуживания своя доля ввода-вывода, даже, несмотря на то, что у автора не было возможности проверить эту гипотезу практическими тестами.

Увеличение глубины очереди от минимального значения в попытке достигнуть близкой к максимуму пропускной способности, приведёт только к увеличению задержек. В том случае, когда мы имеем дело со смешанной рабочей нагрузкой, когда обслуживается смесь из запросов с маленьким и большим блоком, можно попробовать более высокую глубину очереди. Для больших блоков это, в принципе, может поднять пропускную способность, но автор это тоже не проверял. Что касательно сетей SAN, то для них существуют некоторые предположения, согласно которым увеличение глубины очереди может помочь достичь максимальной пропускной способности при последовательной нагрузке, когда на одном RAID массиве создано несколько LUN. Сколько-нибудь подробных разъяснения на этот счёт автором найдено не было.

Характеристики ввода-вывода SQL Server

Есть несколько документов Microsoft, которые подробно описывают ввод-вывод SQL Server. Этот список включает:

• Блог CSS SQL Server Engineers: How It Works: Bob Dorr's SQL Server I/O Presentation
• Презентация Microsoft SQL Server I/O Internals
• Статья базы знаний (917047) Требования к подсистеме ввода/вывода для Microsoft SQL Server для базы данных tempdb
• Техническая статья об SQL Server, написанная Emily Wilson, Mike Ruthruff и Thomas Kejser: Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
• Статься Craig Freedman: Random Prefetching
• В комплекте электронной документации SQL Server 2008 R2 Books Online в главе «Управление буферами/Считывание страниц» сказано следующее: «Механизм упреждающего чтения позволяет компоненту Database Engine считывать из одного файла до 64 последовательных страниц (512 КБ). Эта операция выполняется как единая последовательность разборки-сборки для соответствующего числа буферов в буферном кэше (возможно, расположенных не последовательно)». Автор также приводит информацию, что при сканировании таблиц ввод-вывод SQL Server организован таким образом, чтобы попытаться склеить упреждающим чтением 1024 страницы для редакции Enterprise Edition, или 128 страниц упреждающего чтения для редакции Standard Edition.

Синхронный и асинхронный ввод-вывод SQL Server

При случайном доступе с размером запроса 8KB (что характерно для таких операторов Плана исполнения запроса, как Поиск Закладок и LOOP JOIN) SQL Server может переключиться с синхронного ввода-вывода на асинхронный. В этом случае он будет использовать порции приблизительно по 25 строк.

Рассмотрите ситуацию, обычную для транзакционных систем, которые также обслуживают задачи отчётности. Транзакции состоят из нескольких последовательно обслуживаемых запросов ввода-вывода с глубиной очереди 1. Отчёт – это запрос, который может генерировать несколько сотен запросов ввода-вывода, обслуживаемых асинхронно с большой глубиной очереди. Предположим, что для транзакций, которые будут присутствовать в данном случае, средняя глубина очереди на один диск составит 1, а средняя задержка доступа составит 5ms. Допустим, что для транзакции нужно 20 синхронных запросов ввода-вывода, которые по нашим выкладкам завершаться за 100 ms, что является разумным временем отклика. Теперь выполним сценарий отчёта, который генерирует асинхронный ввод-вывод и он достигает значений глубины очереди к диску порядка 8, с задержками около 30 ms. Отчёт выполнится быстро, потому что СХД обеспечивает 350 IOPS на диск. Но зато транзакция с 20 последовательными запросами ввода-вывода теперь отработает за 600ms. Хотя со стороны может показаться, что отчёты имеют более высокий приоритет, чем обслуживание коротких транзакций.

Замечания относительно Tempdb

Часто ввод-вывод SQL Server в системную базу данных tempdb характеризуется большой глубиной очереди. Это происходит потому, что запросы к этой базе характеризуются большими операциями с хэшами или сортировками. Если бы для таких операций нужно было меньше ресурсов, то работа с ними выполнялись только в оперативной памяти. Таким образом, виновниками в нагрузке на tempdb часто становятся слишком большие для данной системы запросы, которые порождают запросы асинхронного ввода-вывода с большой глубиной очереди.

Если руководствоваться только простым правилом что задержки ввода-вывода должны быть ниже 20 ms, то можно сделать вывод о перегрузке выделенных под tempdb дисков, потому что средние значения задержек будут очень большими. На самом же деле SQL Server просто старается держаться в рамках стратегии достижения максимальной производительности, используя для этого метрики, которые ориентированы на пропускную способность. Правильной метрикой в данном случае будет возможность дисков под tempdb обслужить достаточный для поддержания высокой производительности объем запросов ввода-вывода, и не факт, что при этом объём ввода-вывода с tempdb должен быть низким.

Большая глубина очереди для SQL Server + SSD

Автор наблюдал у запроса с полным сканированием таблицы, в котором не было подсказок оптимизатору уровня изоляции блокировок, глубину очереди запросов ввода-вывода на чтение порядка 1300. Размер запроса ввода-вывода составлял 8KB, а задержки для операций чтения были больше 200 ms, и это несмотря на то, что данные хранились на SSD дисках. Когда указывалась блокировка таблицы, число запросов ввода-вывода составляло приблизительно 500K (вероятно, основная масса в 512K разбавлялась небольшим вводом-выводом с маленьким размером блока), задержка дисковых операций получалась меньше 50 ms, а глубина очереди была приблизительно 40. Для поиска по ключу мы наблюдали ввод-вывод блоками 8 КB c глубиной очереди приблизительно 160, и с задержкой порядка 7 ms. Когда использовалось СХД с приблизительно 20 HDD дисками, глубина очереди была около 160, это приблизительно 8 на один диск, что было вполне приемлемо для производительности ввода-вывода, и результирующие задержки ещё не составляли проблемы.

Marc Bevand в блоге Zorinaq's указал, что число IOPS при глубине очереди 1 являются по существу мерой задержки. Предположим, что SSD имеет задержку 100 µs, и обеспечивает 30K IOPS для 8 KB IO (30K x 8KB = 240 MB). Получается, что при глубине очереди 1 в IOPS должно получатся 10K (1000000 µs/s/100µs). Таким образом, теория говорит нам о том, что глубина очереди 3 или больше может обеспечить производительность до 30K IOPS. Удержание глубины очереди, близкое к её минимальным значениям, необходимо, чтобы не пострадала максимальная производительность в IOPS для запросов, которые порождают огромные объёмы ввода-вывода, и для того, чтобы обеспечить хорошее время отклика для других, выполняющихся параллельно запросов.

Большая задержка записи при создании кластеризованных индексов

Команда CREATE CLUSTERED INDEX, как показывали наши наблюдения, сопровождалась очень высокой задержкой для записи. Глубина очереди достигала 500, задержка превышала 600 ms, а размер запроса ввода-вывода составлял в среднем 100 KB. Поскольку такие высокие значения задержек нежелательны во время работы пользователей, нужно стараться этого не допускать. В любом случае, принуждение СХД обслуживать такой большой ввод-вывод не имеет никакого смысла. И у кэширующих RAID - контроллеров и у SSD при записи предел пропускной способности ввода-вывода может достигаться даже при значительно меньшей глубине очереди. Увеличение объёма ввода-вывода до такой степени негативно повлияет на доступность системы для других запросов ввода-вывода, отправляемых на обслуживание тем же дискам, обслуживающим большой объём операций.

Влияние на статистику ожиданий асинхронных операций

Перед тем, как подвести черту под нашим обзором всего того, что связано с глубиной очереди ввода-вывода, автор хочет кратко коснуться темы зависимости статистики ожиданий от типа операций ввода-вывода. Сегодня можно часто слышать рекомендации по использованию в качестве базовой метрики оптимизации ввода-вывода данных исключительно о статистике ожиданий. Рассмотрим следующий пример: наша СХД состоит из 100 дисков, запрос генерирует 1 миллион операций ввода-вывода – если используется синхронный ввод-вывод с глубиной очереди 1 на каждый диск, или 100 ко всем дискам системы хранения, то мы получим 200 IOPS на один диск или 20000 IOPS для всей СХД. При этом задержка обращения к диску составит 5 ms. Тогда получается, что время исполнения запроса займёт 50 sec. Суммарное время ожиданий (total wait time) составит 5 ms для одного запроса ввода-вывода или 5000 sec для миллиона операций ввода-вывода.

Теперь рассмотрим асинхронный ввод-вывод, при котором глубина очереди к диску будет 16, при 400 IOPS на диск и с задержкой на 40 ms. Запрос с миллионом операций ввода-вывода теперь завершится за 25 sec, но суммарное время ожиданий составит при этом 40000 sec. Этот пример показывает, что всегда важно опираться в своих оценках на правильные метрики и использовать для принятия решения о способах и методах оптимизации ввода-вывода не только статистику ожиданий, но и показания соответствующих счётчиков производительности.

Резюме обзора глубины очереди ввода-вывода

Мы постарались кратко рассмотреть, на какие ключевые компоненты может воздействовать стратегия выбора глубины очереди ввода-вывода. Можно выделить следующие важные моменты:

• Результаты случайного чтения с дисков, измеренные в IOPS, могут выиграть от увеличения глубины очереди за счёт задержек.
• Для последовательного ввода-вывода большая глубина очереди не нужна, ему достаточно, чтобы все диски были загружены обслуживанием запроса ввода-вывода. Использование упреждающего чтения более чем 1024 страниц, при обслуживании сканирования таблиц, кажется вполне логичным, если запрос ввода-вывода оперирует большими блоками. Но также вполне реалистичен сценарий, когда вместо этого мы будем иметь дело с массированным вводом-выводом, у которого размер блока всего 8KB и глубокая очередь. В последнем случае, нужно внести коррективы в стратегию организации ввода-вывода, учитывая реальный размер ввода-вывода, или, возможно, потребуется выяснить, почему ввод-вывод деградирует до 8KB, если таблица при этом не фрагментирована?
• При использовании RAID-контроллера с кэшированием записи, носящие случайный характер операции записи не нуждаются в увеличении глубины очереди в целях повышения производительности таких операций.
• При использовании системы хранения с SSD дисками тоже нет смысла путём повышения глубины очереди повышать производительность операций ввода-вывода.

По мнению автора статьи, SQL Server придерживается заранее установленной стратегии выбора глубины очереди запросов ввода-вывода, которая зависит только от редакции сервера (Standard или Enterprise). Число дисков, из которых собран любой LUN, стратегией не учитывается; характер нагрузки (OLTP или DW/DSS) не учитывается. Однако оптимизировать производительность операций ввода-вывода могут помочь несколько замечаний к той стратегии, которую вы будете реализовывать для своих приложений:

1. Последовательный ввод-вывод не должен использовать упреждающее чтение более 1024 страниц, если размер блока запроса ввода-вывода составляет 8 KB.
2. Примите в расчёт, что ввод-вывод операций записи, обслуживаемый контроллером с включённым кэшированием записи, должен иметь меньшие значения глубины очереди.
3. Важно скорректировать глубину очереди ввода-вывода для случайного чтения с учётом особенностей разных типов устройств хранения: HDD или SSD.
4. Полезно скорректировать ввод-вывод для случайного чтения (для обычных HDD) с учётом модели использования: OLTP или DW/DSS.
5. Полезно скорректировать ввод-вывод для случайного чтения (для обычных HDD), учитывая число дисков в LUN.

Некоторые представленные выше рекомендации вполне могут реализоваться автоматически. А другие могут потребовать внесения изменений в параметры глобальной конфигурации, которые меняются с помощью системной процедуры sp_configure. В качестве универсального совета можно выделить одно: вносите такие корректировки, которые позволят учесть особенности пользовательской нагрузки, благодаря чему значительно повысится удобство работы пользователей с SQL Server. Сегодня, существует большое количество возможностей загнать SQL Server в «ступор», заставив его обслуживать массированные дисковые операции и огромные очереди, и тогда не помогут даже хранилища с SSD дисками. Только очень большие, отлично сконфигурированные системы хранения могут быть неуязвимы для таких проблем.

Поиск узких мест ввода-вывода для MS SQL Server

По материалам статьи Tibor Nagy: How to Identify I/O Bottlenecks in MS SQL Server - 17.03.2011

Проблема

   Суть проблематики данной статьи - регулярное замедление в работе баз данных SQL Server. После статей, посвящённых анализу использования памяти и CPU, мы хотели бы продолжить исследование причины замедления путём анализа узких мест ввода-вывода.

Решение

   Подсистема ввода-вывода - ключевой фактор производительности SQL Server, поскольку страницы базы данных постоянно перемещаются с диска в буферный пул или обратно. Помимо этого существенный трафик ввода-вывода генерируют журналы транзакций и системная база данных tempDB. Учитывая эти факторы, Вы должны быть уверены, что используемая подсистема ввода-вывода работает стабильно, иначе проблемы в работе этой подсистемы приведут к увеличению времени отклика запросов и частым тайм-аутам. В этой статье я опишу несколько способов поиска узких мест ввода-вывода, используя для этого встроенные инструменты, и представлю некоторые идеи, связанные с конфигурацией дисков.

Читайте дальше в блоге Ирины Наумовой

Распараллеленный Просмотр

Перевод Ирины Наумовой и Александра Гладченко

В этой статье я собираюсь рассмотреть то, как SQL Server распараллеливает просмотр таблицы (сканирования - scans). Оператор просмотра - один из немногих операторов, которые адаптированы к параллелизму. Большинство других операторов ничего не знают о параллелизме, и не заботятся о том, выполняются ли они параллельно; оператор просмотра является в этом случае исключением...

Читайте дальше в блоге Ирины Наумовой

Tips for DBA: sys.dm_os_wait_stats и правильный способ сбора статистики ожиданий

Статистика ожидания является очень удобным и практичным инструментом для выявления узких мест в работе SQL Server. Познакомиться со списком существующих типов ожиданий и рекомендациями по реакции на отклонения ожиданий от нормы можно в описании динамического административного представления sys.dm_os_wait_stats

Для анализа статистики ожиданий необходимо собирать эту статистику в то время, в которое проявляются проблемы, либо обслуживается нагрузка, которую необходимо исследовать. Для этого обращение непосредственно к sys.dm_os_wait_stats не очень подходит, т.к. там накоплена статистика с момента последнего запуска сервера, либо с момента последней очистки результатов динамического представления командой: DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Кроме того, результаты sys.dm_os_wait_stats потребуется ещё дополнительно агрегировать и форматировать, для удобства анализа статистики ожиданий.

Вашему вниманию предлагаются две процедуры, автор которых T. Davidson. После создания в базе данных master этих процедур , можно запустить вторую из них, например так:

EXEC dbo.track_waitstats_2005 @num_samples=20
                             ,@delay_interval=30
                             ,@delay_type='s'
                             ,@truncate_history='y'
                             ,@clear_waitstats='y'
GO

Процедура приступит к накоплению текущей статистики. По окончании или в процессе её работы можно получать отчёт об ожиданиях. Сделать это можно вызвав первую процедуру:

execute dbo.get_waitstats_2005
GO

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

Другие материалы на эту тему:

• Troubleshooting Performance Problems in SQL Server 2005
• SQL Server Best Practices Article
• Wait statistics, or please tell me where it hurts
• Методики оптимизации ожидания ресурсов (SQL Server Waits) в аналитических системах

Сценарий создания процедур:

USE master
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N'[dbo].[get_waitstats_2005]') AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo].[get_waitstats_2005]
GO

CREATE PROCEDURE [dbo].[get_waitstats_2005] (
          @report_format varchar(20)='all',
          @report_order varchar(20)='resource')
AS
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- Эта процедура выдаёт список ожиданий и процент ожиданий по каждому
-- из типов
-- (1) total wait time -- сумма ожиданий ресурса и сигнала,
-- указание @report_format ='all' предписывает отчёт о ресурсах и сигналах
-- (2) Принципы работы (упрощенно)
-- a. Когда для spid в процессе работы требуется недоступный ресурс,
-- он перемещается в список ожидания ресурса, после чего
-- ресурс ожидает в списке с момента времени T0
-- b. Сигнальный режим указывает на то, что ресурс доступен, и
-- spid перемещается в очередь исполнения в момент времени время T1
-- c. spid ожидает пока не закончится обработка до T2, поскольку центральный
-- процессор выполняет предписанные ему инструкции
-- из очереди на исполнение в порядке поступления
-- (3) resource wait time -- фактическое время, кторое ожидается
-- ресурс, пока он не станет доступным, T1-T0
-- (4) signal wait time -- время, которое прошло с того момента
-- когда ресурс стал доступен (T1)
-- и до момента T2, в который процесс снова готов к работае.
-- Таким образом, signal wasignal равняется T2-T1
-- (5) Ключевой вопрос: Действительно ли время ожидания ресурса и сигнала
-- является существенным?
-- a. Самые большие ожидания указывают на узкое место, которое нужно
-- устранить средствами масштабирования
-- b. Обычно, если вы сталкиваетесь с малым процентов ожиданий сигнала,
-- процессор тратит мало времени на обработку рабочей нагрузки
-- обслуживаемого spid
-- c. Высокий процент ожиданий сигнала указывает на то, что процессор не
-- может обеспечивать высокую производительность,
-- для каждого spid проходит существенное время, пока он не переместится
-- по очереди на исполнения в самый верх и
-- перейдёт в рабочее состояние
-- (6) Эта процедура должна выполняться во время исполнения
-- процедуры track_waitstats
--
-- Revision 4/19/2005
-- (1) add computation for CPU Resource Waits = Sum(signal waits /
-- total waits)
-- (2) add @report_order parm to allow sorting by resource, signal
-- or total waits
--
set nocount on

declare @now datetime,
@totalwait numeric(20,1),
@totalsignalwait numeric(20,1),
@totalresourcewait numeric(20,1),
@endtime datetime,@begintime datetime,
@hr int,
@min int,
@sec int

if not exists (select 1
          from sysobjects
          where id = object_id ( N'[dbo].[waitstats]') and
                    OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
          raiserror('Error [dbo].[waitstats] table does not exist',
                    16, 1) with nowait
          return
end

if lower(@report_format) not in ('all','detail','simple')
          begin
                    raiserror ('@report_format must be either ''all'',
                               ''detail'', or ''simple''',16,1) with nowait
                    return
          end
if lower(@report_order) not in ('resource','signal','total')
          begin
                    raiserror ('@report_order must be either ''resource'',
                              ''signal'', or ''total''',16,1) with nowait
                    return
          end
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total'
          begin
                    raiserror ('@report_format is simple so order defaults to
''total''',
                              16,1) with nowait
                    select @report_order = 'total'
          end


select
          @now=max(now),
          @begintime=min(now),
          @endtime=max(now)
from [dbo].[waitstats]
where [wait_type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait =
sum([signal_wait_time_ms]) + 1
from waitstats
where [wait_type] not in (
                    'CLR_SEMAPHORE',
                    'LAZYWRITER_SLEEP',
                    'RESOURCE_QUEUE',
                    'SLEEP_TASK',
                     'SLEEP_SYSTEMTASK',
                    'Total' ,'WAITFOR',
                    '***total***') and
          now = @now

select @totalresourcewait = 1 + @totalwait - @totalsignalwait

-- insert adjusted totals, rank by percentage descending
delete waitstats
where [wait_type] = '***total***' and
now = @now

insert into waitstats
select
          '***total***',
          0,@totalwait,
          0,
          @totalsignalwait,
          @now

select 'start time'=@begintime,'end time'=@endtime,
          'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-
@begintime,14),
          'report format'=@report_format, 'report order'=@report_order

if lower(@report_format) in ('all','detail')
begin
----- format=detail, column order is resource, signal, total. order by resource desc
          if lower(@report_order) = 'resource'
                    select [wait_type],[waiting_tasks_count],
                              'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
                              'res_wt_%'=cast (100*([wait_time_ms] -
                                        [signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
                    'Signal wt (T2-T1)'=[signal_wait_time_ms],
                    'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1)),
                    'Total wt (T2-T0)'=[wait_time_ms],
                    'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
          from waitstats
          where [wait_type] not in (
                    'CLR_SEMAPHORE',
                    'LAZYWRITER_SLEEP',
                    'RESOURCE_QUEUE',
                    'SLEEP_TASK',
                    'SLEEP_SYSTEMTASK',
                    'Total',
                    'WAITFOR') and
                    now = @now
          order by 'res_wt_%' desc

----- format=detail, column order signal, resource, total. order by signal desc
          if lower(@report_order) = 'signal'
                    select [wait_type],
                              [waiting_tasks_count],
                              'Signal wt (T2-T1)'=[signal_wait_time_ms],
                              'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait
                              as numeric(20,1)),
                              'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
                              'res_wt_%'=cast (100*([wait_time_ms] -
                                        [signal_wait_time_ms]) /@totalresourcewait as
numeric(20,1)),
          'Total wt (T2-T0)'=[wait_time_ms],
          'wt_%'=cast (100*[wait_time_ms]/@totalwait as
numeric(20,1))
          from waitstats
          where [wait_type] not in (
                    'CLR_SEMAPHORE',
                    'LAZYWRITER_SLEEP',
                    'RESOURCE_QUEUE',
                    'SLEEP_TASK',
                    'SLEEP_SYSTEMTASK',
                    'Total',
                    'WAITFOR') and
                    now = @now
                    order by 'sig_wt_%' desc

----- format=detail, column order total, resource, signal. order by total desc
         if lower(@report_order) = 'total'
          select
                    [wait_type],
                    [waiting_tasks_count],
                    'Total wt (T2-T0)'=[wait_time_ms],
                    'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)),
                    'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
                    'res_wt_%'=cast (100*([wait_time_ms] -
                              [signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
                    'Signal wt (T2-T1)'=[signal_wait_time_ms],
                    'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as
numeric(20,1))
     from waitstats
     where [wait_type] not in (
                    'CLR_SEMAPHORE',
                    'LAZYWRITER_SLEEP',
                    'RESOURCE_QUEUE',
                    'SLEEP_TASK',
                    'SLEEP_SYSTEMTASK',
                    'Total',
                    'WAITFOR') and
                    now = @now
          order by 'wt_%' desc
end
else
---- simple format, total waits only
     select
          [wait_type],
          [wait_time_ms],
          percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
     from waitstats
     where [wait_type] not in (
               'CLR_SEMAPHORE',
               'LAZYWRITER_SLEEP',
               'RESOURCE_QUEUE',
               'SLEEP_TASK',
               'SLEEP_SYSTEMTASK',
               'Total',
               'WAITFOR') and
          now = @now
     order by percentage desc

---- compute cpu resource waits
select
     'total waits'=[wait_time_ms],
     'total signal=CPU waits'=[signal_wait_time_ms],
     'CPU resource waits % = signal waits / total waits'=
          cast (100*[signal_wait_time_ms]/[wait_time_ms] as
numeric(20,1)),
     now
from [dbo].[waitstats]
where [wait_type] = '***total***'
order by now
GO



if exists (select * from sys.objects where object_id = object_id(N'[dbo].[track_waitstats_2005]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
     drop procedure [dbo].[track_waitstats_2005]
go
CREATE proc [dbo].[track_waitstats_2005] (@num_samples int=10
                    ,@delay_interval int=1
                    ,@delay_type nvarchar(10)='minutes'
                    ,@truncate_history nvarchar(1)='N'
                    ,@clear_waitstats nvarchar(1)='Y')
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @num_samples is the number of times to capture waitstats, default is 10 times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn-t exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1 from sys.objects where object_id = object_id ( N'[dbo].[waitstats]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
     create table [dbo].[waitstats]
          ([wait_type] nvarchar(60) not null,
          [waiting_tasks_count] bigint not null,
          [wait_time_ms] bigint not null,
          [max_wait_time_ms] bigint not null,
          [signal_wait_time_ms] bigint not null,
          now datetime not null default getdate())
If lower(@truncate_history) not in (N'y',N'n')
     begin
     raiserror ('valid @truncate_history values are ''y'' or ''n''',16,1) with nowait
     end
If lower(@clear_waitstats) not in (N'y',N'n')
     begin
     raiserror ('valid @clear_waitstats values are ''y'' or ''n''',16,1) with nowait
     end
If lower(@truncate_history) = N'y'
     truncate table dbo.waitstats
If lower (@clear_waitstats) = N'y'
     dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs -- clear out waitstats

declare @i int,@delay varchar(8),@dt varchar(3), @now datetime, @totalwait numeric(20,1)
     ,@endtime datetime,@begintime datetime
     ,@hr int, @min int, @sec int
select @i = 1
select @dt = case lower(@delay_type)
     when N'minutes' then 'm'
     when N'minute' then 'm'
     when N'min' then 'm'
     when N'mi' then 'm'
     when N'n' then 'm'
     when N'm' then 'm'
     when N'seconds' then 's'
     when N'second' then 's'
     when N'sec' then 's'
     when N'ss' then 's'
     when N's' then 's'
     else @delay_type
end
if @dt not in ('s','m')
begin
     raiserror ('delay type must be either ''seconds'' or ''minutes''',16,1) with nowait
     return
end
if @dt = 's'
begin
     select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60) as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
     select @sec = 0, @min = @delay_interval % 60, @hr = cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
     + right('0'+convert(varchar(2),@min),2) + ':' +
     + right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
     select 'delay interval and type: ' + convert (varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' + @delay
     raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
     return
end
while (@i <= @num_samples)
begin
          select @now = getdate()
          insert into [dbo].[waitstats] ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], now)
          select [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], @now
               from sys.dm_os_wait_stats
          insert into [dbo].[waitstats] ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], now)
               select 'Total',sum([waiting_tasks_count]), sum([wait_time_ms]), 0, sum([signal_wait_time_ms]),@now
               from [dbo].[waitstats]
               where now = @now
          select @i = @i + 1
          waitfor delay @delay
end
GO

Платформа 2011: SQL Server 2K8R2 для многопроцессорных систем в задачах построения хранилищ

Оператор распараллеливания (Exchange)

Перевод Ирины Наумовой

Как я уже писал в статье Введение в распараллеливание исполнения запроса , итератор параллелизма (или обмена - Exchange operator) фактически привносит в процесс выполнения запроса возможность распараллеливания задачи. Оптимизатор помещает оператор обмена в том месте, где происходит разделение на несколько потоков, и оператор обмена перемещает строки между потоками.

SQL Server 2008 R2 для многопроцессорных систем

Руководство по производительности загрузки данных

Резюме: Этот документ описывает стратегию массовой загрузки больших объёмов информации в базы данных SQL Server. Статья охватывает два распространённых метода, а также методологии повышения производительности и оптимизации процесса массовой загрузки данных.

Введение

Настоящая техническая статья описывает существующие стратегии массовой загрузки данных, которые применяются для быстрого внесения масштабных изменений в базах данных Microsoft ® SQL Server ®.
Прежде, чем углубиться в подробности методов массовой загрузки, давайте освежим в памяти некоторые базовые принципы минимального протоколирования, которые будут представлены в главе: "Разъяснения по минимально протоколируемым операциям".
Следующие две главы: "Методы массовой загрузки" и "Другие минимально протоколируемые операции и операции над метаданными" содержат краткий обзор двух ключевых и взаимосвязанных концепций высокопроизводительной загрузки данных, таких как массовый импорт и экспорт данных и операции только над метаданными.
После небольшого погружения в тему, мы приступим к описанию способов использования этих методов в пользовательских сценариях. Приводимые тут примеры сценариев призваны проиллюстрировать типовые подходы, которые можно найти в главе: "Решения для типовых задач массовой загрузки". Особо будут рассмотрены такие сценарии, когда загрузка данных в таблицу должна выполняться при одновременном чтении из этой же таблицы. В главе "Массовая загрузка, запросы с NOLOCK и Read Committed Snapshot Isolation" описаны методы, которые могут использоваться для достижения параллельной загрузки и чтения данных.
Эта техническая статья заканчивается главой "Оптимизация массовой загрузки данных", в которой рассказано о поиске и устранении сопутствующих загрузке данных проблем.

Разъяснения по минимально протоколируемым операциям

Для поддержки сценариев загрузки больших объёмов данных, в SQL Server реализована возможность минимального протоколирования операций. В отличие от полного протоколирования, которое интенсивно использует журнал транзакций для отслеживания всех изменений строк, минимальное протоколирование операций отслеживает только распределение экстентов и изменения метаданных. Из-за того, что в журнале транзакций отслеживается существенно меньше информации, минимально протоколируемые операция, зачастую, исполняются быстрее, чем полностью протоколируемые, т.е. само протоколирование вносит большой вклад во время исполнения операции. Кроме того, из-за уменьшения необходимости осуществлять запись в журнал транзакций, размер журнала будет заметно меньше, и требования к поддержке дисковых операций ввода-вывода для него будут существенно ниже.
Следует помнить, что операция может являться операцией массовой загрузки, но при этом выполняться без минимального протоколирования. Например, при массовой загрузке данных в кластеризованный индекс или кучу, минимальное протоколирование может не использоваться. Минимальное протоколирование обычно обеспечивает дополнительный выигрыш по производительности, но даже без минимального протоколирования, массовая загрузка потребует меньше ресурсов, чем традиционная вставка строк данных.
Вопреки мифам о SQL Server, минимально протоколируемые операции могут являться частью транзакции. Поскольку все изменения в структурах распределения запротоколированы, становится возможным откатить минимально протоколируемые операции.
Минимально протоколируемые операции доступны, только если база данных использует простую модель восстановления или модель восстановления с неполным протоколированием (bulk-logged). Для получения более подробной информации, обратитесь к статье: "Операции, для которых возможно минимальное протоколирование". Обратите внимание, что при планировании стратегии резервного копирования базы данных нужно учитывать возможность выполнения массовых операций в базе данных с неполным протоколированием. Для получения более подробной информации, обратитесь к статье: Резервное копирование с использованием модели восстановления с неполным протоколированием.

Флаг трассировки 610

В SQL Server 2008 появился флаг трассировки 610, который управляет минимальным протоколированием вставок в проиндексированные таблицы. Этот флаг трассировки может быть включен с помощью одного из следующих методов:

1. Добавление флага к параметрам запуска SQL Server.
   

   • Для получения более подробной информации, обратитесь к статье: "Как настроить параметры запуска сервера (диспетчер конфигурации SQL Server)".

2. Выполнение команды
   

   • В случае локального включения, флаг трассировки действует только на текущий сеанс. Такое включение флага трассировки 610 бывает полезно, если он нужен только для ограниченного набора сценариев загрузки данных на экземпляре сервера, и действует только на те инструкции Transact-SQL, которые выполняются в текущем подключении.
   • В случае глобального включения, этот флаг трассировки будет действовать на все подключения к серверу, пока он не будет выключен или произойдёт рестарт сервера. Для получения более подробной информации об использовании команды DBCC для включения и выключения флагов трассировки, прочтите статьи: "DBCC TRACEON (Transact-SQL)" и "DBCC TRACEOFF (Transact-SQL)".

Прежде чем использовать этот флаг трассировки, нужно учесть ограничения, о которых упоминалось в предыдущем разделе.
Не каждая строка, вставленная в кластеризованный индекс во время действия флага трассировки 610, будет протоколироваться минимально. Когда операция массовой загрузки приводит к необходимости распределения новой страницы, вставка всех строк, которые будут последовательно заполнять новую страницу, будет подпадать под минимальное протоколирование. Вставка же строк в страницы, которые были распределены раньше, чем началась операция массовой загрузки, будет происходить с полным протоколированием, точно так же, как и строк, которые были затронуты во время разбиения страниц в процессе загрузки. Это означает, что для некоторых таблиц частично может использоваться полное протоколирование вставки.
Если включение флага трассировки 610 приводит к использованию сервером минимального протоколирования, это в общем случае может привести к повышению производительности. Но, как и в случаях с другими флагами трассировки, обязательно нужно убедиться, что подобное воздействие на производительность будет воспроизводиться и в промышленной среде под реальной рабочей нагрузкой.
Давайте рассмотрим следующие два примера:

Пример 1: Имеется таблица, кластеризованая по целочисленному ключу, содержащему четные числа от 0 до 14. Таблица имеет четыре страницы листового уровня, и эти страницы не заполнены полностью и могут содержать еще по две строки на каждой странице.
Выполняется массовая загрузка ещё восьми новых строк, со значениями ключа, представляющими собой возрастающую последовательность нечётных чисел от 1 до 15. Новые строки будут размещаться в существующих страницах. На представленной ниже иллюстрации показано, как таблица будет выглядеть до и после операции загрузки.

В этом примере не происходит распределения новых страниц, и включение флага трассировки 610 не приведёт к повышению производительности вставки за счёт минимального протоколирования.

Пример 2: Рассмотрим альтернативный сценарий: теперь таблица первоначально занимает две страницы, обе заполнены полностью и содержат записи со значениями ключа от 0 до 7. Далее выполняется массовая загрузка строк со значениями ключа от 8 до 15.

Этот пример демонстрирует, что происходит при включённом флаге трассировки 610. В данном случае, вставка записей со значениями ключа 8-15 на выделенные для этого новые страницы (на рисунке выше это светло-синие прямоугольники) будет проходить с минимальным протоколированием.
Один из способов проверки того, сколько новых страниц было распределено для размещения записей таблицы, состоит в выполнении запроса к sys.dm_db_partition_stats. Следующий сценарий Transact-SQL вернёт число страниц каждого индекса и таблицы:

SELECT OBJECT_NAME(p.object_id) AS object_name
       , i.name AS index_name
       , ps.in_row_used_page_count
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p
       ON ps.partition_id = p.partition_id
JOIN sys.indexes i
       ON p.index_id = i.index_id
       AND p.object_id = i.object_id

Сравнивая возвращаемые этим сценарием результаты до и после выполнения операции массовой загрузки, можно увидеть, сколько было распределено новых страниц.
Если во время массовой заливки в В-дерево используется флаг трассировки 610, рекомендуется указывать наибольшее из возможных значений параметра BATCHSIZE. В таком случае, SQL Server сможет распределить в заполняемой секции большое число новых страниц для каждого блока загрузки и секции. В некоторых случаях, такое распределение с включённым флагом трассировки 610, даже если страницы потом будут возвращены обратно, может привести к увеличению ввода-вывода, по сравнению с аналогичной нагрузкой без включения этого флага трассировки. Однако, при загрузке небольшого количества секций и использовании большого размера пакета, включение флага трассировки 610 может привести к существенному повышению производительности по сравнению с полным протоколированием вставок. Для получения более подробной информации о поведении минимального протоколирования при включении флага трассировки 610, ознакомьтесь со статьёй: "New update on minimal logging for SQL Server 2008" в блоге SQL Server Storage Engine.

Влияние на ввод-вывод минимального протоколирования с включённым флагом 610

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

Сводка по возможным условиям минимального протоколирования

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

Таблица 1: Сводка по возможным условиям минимального протоколирования

(1) Если используется метод INSERT | SELECT, можно не использовать подсказку ORDER, однако вставляемые строки должны быть отсортированы в том же порядке, что и кластеризованный индекс. При использовании BULK INSERT необходимо использовать подсказку ORDER.
(2) Параллельная загрузка возможна только в некоторых случаях. См. главу "Массовая загрузка при наличии индексов". Кроме того, минимальное протоколирование будет использовано только для тех строк, которые попали на вновь распределённые страницы.
(3) Вставка в таблицу с некластеризованным индексом может быть полностью или минимально протоколирована в зависимости от выбранного оптимизатором плана исполнения запроса.

Методы массовой загрузки

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

• Импорт данных средствами Integration Services
• BCP
• BULK INSERT
• INSERT…SELECT
• SELECT INTO

В этой статье, термин "массовая загрузка" относится к использованию любого из представленных в текущем разделе методов. Термин "BULK INSERT" (в верхнем регистре) относится к инструкциям Transact-SQL, которые базируются на тех методах массовой загрузки, которые описаны в статье "BULK INSERT (Transact-SQL)".
Использование этих методов требует понимания их возможностей и ограничений. В этой главе будут кратко описаны те варианты, которые возможны для массовой загрузки данных из внешних по отношению к Ядру SQL Server источников, а также из источников, обслуживаемых тем же самым Ядром.
Кроме того, доступные нам методы массовой загрузки данных имеют в SQL Server свои программные интерфейсы, в ADO.NET это класс SQLBulkCopy, в OLE DB это IRowsetFastload, а в ODBC это библиотека SQL Server Native Client. Несмотря на то, что тематика программных интерфейсов выходит за рамки данной статьи, многие рекомендации, лучшие практики, параметры настройки и т.п., которые рассматриваются в статье, пригодны и для случаев использования программных интерфейсов массовой загрузки.

Импорт данных средствами Integration Services

Служба SQL Server Integration Services предоставляет самые гибкие средства для массовой загрузки данных в SQL Server. Данные могут читаться из любого источника, который совместимым с Integration Services, с преобразованием и конвертацией данных в памяти, и массовой загрузкой напрямую в SQL Server, без промежуточной материализации данных на диске. Поскольку Integration Services является самостоятельным процессом, он потенциально может быть запущен на другом компьютере, что позволяет существенно разгрузить процессоры SQL Server, сняв с них нагрузку на преобразование данных. Это довольно простой способ масштабирования массовых операций, когда необходимо добиться повышения производительности.
Существует две возможности загрузки данных, которые обеспечивают минимальное протоколирование и доступ к функциональности массовой загрузки SQL Server: это компоненты загрузки с именами: "Назначение SQL Server" и "Назначение OLE DB" (SQL Server Destination и OLE DB Destination).

Назначение SQL Server

Загрузка в Назначение SQL Server является самым быстрым способом массовой загрузки из потока данных SQL Server Integration Services. Эта возможность поддерживает все варианты параметров массовой загрузки SQL Server, кроме ROWS_PER_BATCH.
Следует помнить, что использование этой возможности может потребовать подключения к SQL Server через Общую память, а это означает, что необходимо запускать службу Integration Services на том же компьютере, что и службы Ядра SQL Server.

Назначение OLE DB

Загрузка в Назначение OLE DB поддерживает все варианты массовой загрузки, которые доступны для загрузки в SQL Server. Однако, для поддержки упорядоченной массовой загрузки, требуется некоторая дополнительная настройка. Для получения более подробной информации, ознакомьтесь со статьями: "Управление порядком сортировки во время массового импортирования данных" и "Выполнение операций массового копирования". В этих статьях описано, как использовать параметры команд и утилит, или интерфейсы OLE DB для загрузки отсортированных данных (при использовании интерфейсов массовой загрузки, нужно включить режим "fast load").
Загрузка в OLE DB для подключения к SQL Server использует протокол TCP/IP или именованные каналы. Это означает, что Назначение OLE DB, в отличие от Назначения SQL Server, может работать на другом компьютере, не на том, на который идёт массовая загрузка. Поскольку пакеты службы Integration Services, которые используют OLE DB, не обязаны работать непосредственно на том компьютере, на котором запущен SQL Server, становится возможным масштабирование ETL потоков с помощью множества недорогих серверов.

BCP

BCP (Bulk Copy Program) - утилита командной строки, используемая для извлечения и импорта данных в SQL Server. Программа разработана с использованием интерфейсов массового копирования и позволяет быстро вставлять данные из текстовых файлов напрямую в SQL Server. Кроме того, она позволяет экспортировать данные из таблиц SQL Server или из запросов в текстовые файлы.
BCP может читать файлы данных в формате таблиц баз данных (собственный формат SQL Server), а так же и другие форматы. Использование собственного формата работает наиболее быстро, за счёт минимального синтаксического анализа входного текстового файла.

BULK INSERT

Команда BULK INSERT представляет собой внутрипроцессный метод для переноса данных из текстовых файлов в SQL Server. Поскольку всё это выполняется в процессе Sqlservr.exe, загрузка файлов данных в SQL Server происходит очень быстро.
BULK INSERT не может использоваться для задач экспорта данных, только для импорта. Кроме этого ограничения, команда наделена всеми возможностями, присущими BCP. Команда BULK INSERT вызывается в сценариях, написанных на Transact-SQL, что делает её идеальной для использования в хранимых процедурах, основанных на Transact-SQL задачах ETL, и заданиях по расписанию службы SQL Server Agent.

SELECT INTO

Инструкция SELECT INTO создаёт новую таблицу, основанную на результирующем наборе инструкции SELECT. Вставка новых строк протоколируется минимально, что делает такой способ загрузки данных очень быстрым. К сожалению, для этой инструкции не существует способов управления BATCHSIZE или ROWS_PER_BATCH.
Ещё одним ограничением инструкции SELECT INTO является то, что результирующая таблица должна находиться в файловой группе по умолчанию. Хотя можно временно изменить значение по умолчанию для файловых групп, перед тем, как выполнить инструкция SELECT INTO, широкое использование такого сценария маловероятно.

INSERT…SELECT

В SQL Server 2008 появился новый способ, который позволяет добиться минимального протоколирования для операций вставки. Речь идёт об инструкции INSERT (Transact-SQL), которая при некоторых условиях может быть минимально протоколируемой.
У этой возможности есть несколько ограничений. Прежде всего, нужно отметить, что с этим методом не могут использоваться такие параметры массовой загрузки, как указание размера фиксации (commit size), управление проверкой ограничений и отключение триггеров. Во-вторых, таблица, куда идёт заливка, должна быть заблокирована с помощью монопольной (X) блокировки, в то время как другие методы массовой загрузки (Integration Services, BCP и BULK INSERT), описанные в этой статье, используют блокировку массового обновления (BU). Поскольку монопольная (X) блокировка не совместима с другими монопольными (X) блокировками, единовременно может выполниться только одна вставка. Для получения более подробной информации о блокировках массового обновления (BU) и монопольных (X) блокировках, ознакомьтесь со статьёй в SQL Server Books Online: "Режимы блокировки".
Несмотря на то, что операция вставки будет однопоточной, инструкция INSERT…SELECT может быть распараллелена. Распараллеливаться сможет та часть инструкции INSERT…SELECT, в которой Ядро SQL Server исполняет инструкцию SELECT. Ввиду того, что этот метод может преобразовать данные во время транзита, высокая скорость преобразования данных, которое основано на инструкции Transact-SQL, достигается за счёт того, что запрос оптимизируется средствами SQL Server. Для таких операций, которые основаны на инструкции SELECT, именно это определяет скорость работы.
Будет ли минимально протоколироваться операция INSERT…SELECT в кластеризованный индекс, зависит от того, включён ли флаг трассировки 610. Операция INSERT…SELECT в кучу может минимально протоколироваться даже без включения флага трассировки 610.

Куча

Инструкция INSERT, которая получает строки от операции SELECT и вставляет их в кучу, будет протоколироваться минимально, когда для таблицы назначения используется подсказка WITH (TABLOCK).
Для того чтобы получить минимальное протоколирование операций вставки в кучу, используйте следующий синтаксис Transact-SQL:

INSERT INTO <ТаблицаНазначения> (<Колонки>) WITH (TABLOCK)
SELECT <Колонки> FROM <НекоторыеУтверждения>

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

Кластеризованные индексы

Если операция INSERT…SELECT направлена в кластеризованный индекс, эта операция будет протоколироваться минимально, при условии, что будет активизирован флаг трассировки 610. Обратите внимание, что минимальное протоколирование используется даже без подсказки TABLOCK. Это означает, что может существовать несколько параллельных инструкций INSERT…SELECT, которые вставляют в таблицу данные в одно и то же время и в режиме минимального протоколирования. У такой возможности тоже есть несколько ограничений. Для получения более подробной информации, прочтите главу: "Массовая загрузка при наличии индексов".
Кроме этого, операция массовой загрузки будет минимально протоколироваться без флага трассировки, когда истинны оба представленные ниже условия:

• Для таблицы, куда направлена вставка, определена подсказка WITH (TABLOCK)
• Таблица, куда направлена вставка, пуста

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

INSERT…SELECT с чтением из текстовых файлов или данных OLE DB источников

Команду INSERT…SELECT в SQL Server можно использовать для чтения из любого OLE DB источника данных. Для реализации такой возможности используется команда OPENROWSET в качестве исходной инструкции SELECT.
Использование OPENROWSET позволяет добиться точно такого же поведения, как у BULK INSERT и INSERT…SELECT, кроме того, становится возможным использовать соединения или фильтровать входной набор данных до их вставки.
Следует помнить, что BULK INSERT позволяет направлять несколько минимально протоколируемых потоков в кучу, а INSERT…SELECT может использовать для вставки только один поток.
OPENROWSET также позволяет настраивать несколько параметров пакетной обработки вставки. При использовании OPENROWSET в качестве источника INSERT…SELECT, доступны для настройки следующие параметры:

• ORDER
• ROWS_PER_BATCH или BATCH_SIZE
• IGNORE_CONSTRAINTS и IGNORE_TRIGGERS
• FIRSTROW и LASTROW
• KEEPDEFAULTS и KEEPIDENTITY
/p>

Для получения более подробной информации, прочтите статью SQL Server Books Online OPENROWSET (Transact-SQL).

Возможности записи для собственного ПО

"ODBC", "OLE DB", ".NET" и "DB-library for SQL Server" позволяют реализовать свои собственные методы массовой загрузки данных. Полное описание программных интерфейсов (API) массовой загрузки и их возможности выходят за рамки настоящей статьи.
Для получения информации о разработке собственных методов массовой загрузки, ознакомьтесь со следующими материалами:

• Класс SqlBulkCopy (из библиотеки классов .NET Framework)
• Выполнение операций массового копирования
• Инструкции по массовому копированию наборов строк (OLE DB)
• Выполнение операций массового копирования (ODBC)
• Инструкции по массовому копированию с помощью драйвера ODBC для SQL Server (ODBC)

Варианты выбора средств массовой загрузки

В представленной ниже таблице приведен краткий обзор разных методов массовой загрузки, доступных в Integration Services и SQL Server.

Таблица 2: Варианты выбора средств массовой загрузки

(1) Если DTEXEC выполняется не на том же сервере, на котором запущена служба SQL Server, Integration Services может работать очень быстро, за счет освобождения Ядра базы данных от операций преобразования данных.
(2) Обратите внимание, что INSERT…SELECT не позволяет выполнять вставки в одну и ту же таблицу параллельно. Если вставка идёт в одну таблицу, скорее всего наиболее быстрым вариантом будет Integration Services, потому что это позволяет выполнять несколько потоков параллельно.
(3) Размер блока чтения зависит от источника. Для текстовых файлов, используются размеры блока 128Кб.
(4) Если назначением является SQL Server, то процессорных циклов будет затрачено больше, чем у BULK INSERT, и это будет ограничением максимальной скорости. Но поскольку Ядро базы данных освобождается от операций преобразования данных, производительность однопоточной вставки будет быстрее, чем у BULK INSERT.

Немного о BATCHSIZE и ROWS_PER_BATCH

Эти два параметра массовой загрузки нуждаются в некоторых разъяснениях. Первый из них - BATCHSIZE, он предписывает, сколько строк будет передано единовременно во время массовой операции. Если оставить это значение по умолчанию, вся массовая операция будет выполнена в одной большой транзакции. Однако, если параметру задано не нулевое значение, для передачи указанного серверу количества строк будет создана и завершена новая транзакция. Обычно параметр BATCHSIZE используется для поэтапной передачи всех строк, чтобы в случае сбоя не передавать весь пакет целиком. Однако нужно понимать, что автоматическая "докачка" строк не предусмотрена. Отслеживание того, сколько строк было уже вставлено, является задачей разработчика приложения. Обратите внимание на то, что слишком маленькие значения для параметра BATCHSIZE могут привести к снижению производительности, из-за неоправданного увеличения затрат ресурсов на обслуживание транзакций. На Рисунке 3 можно увидеть, как значение этого параметра влияло на массовую загрузку 17Гб в таблицу одним потоком. Обратите внимание, что для значений больше 10000 наблюдается лишь небольшое увеличение производительности.
Ненулевые значения BATCHSIZE наиболее полезны для вставки в индексированные таблицы. Если не указывать упорядочивание данных, операция SORT будет выполнена внутренними механизмами для каждого индекса при вставке каждого пакета. Т.е. достаточно маленькие значения BATCHSIZE могут позволить выполнить сортировку в памяти, и обойтись без промежуточной материализации на диске. Однако это может стать причиной роста фрагментации индексов, которая также может привести к падению производительности запросов, которым свойственны упорядоченные просмотры диапазонов ключа. Фрагментации из-за вставки в кластеризованный индекс можно избежать, если вставлять данные в уже отсортированном виде, как это описано в главе "Вставка упорядоченных данных".
Стоит проверить несколько вариантов настройки параметра BATCHSIZE для свойственных вашим приложениям нагрузок, что позволит увидеть влияние разных значений на производительность загрузки и итоговую фрагментацию. Кроме того, ненулевое значение параметра BATCHSIZE может оказаться полезным, если не используется подсказка TABLOCK, и нужно избежать эскалации блокировок. Типичной для этого ситуацией является наличие запросов на чтение, параллельных загрузке, когда нежелательно их блокировать. Даже если для предотвращения эскалации блокировок используются другие методы (например, используется ALTER TABLE), ненулевой BATCHSIZE будет необходим для того, чтобы предотвратить утилизацию слишком большого объёма памяти, которая используется блокировками при большой нагрузке.

Вторым параметром является ROWS_PER_BATCH, он задаёт приблизительное число строк во всём пакете операции массовой загрузки данных. Этот параметр имеет смысл, когда параметр BATCHSIZE не задан. Кроме того, если в массовой загрузке используется BATCHSIZE больше нуля, параметру ROW_PER_BATCH нельзя присваивать значения, необходимо оставить его пустым или нулевым.
С помощью ROWS_PER_BATCH можно подсказать оптимизатору запросов объём загрузки, а тот, в свою очередь, постарается оптимизировать ход выполнения массовой операции. Если известно примерное число строк, оптимизатор сможет лучше распределить ресурсы и необходимый объем памяти для обслуживания пакета.
Поскольку информация о параметрах BATCHSIZE и ROWS_PER_BATCH может вас запутать, так как у этих параметров в разных инструментах массовой загрузки разные имена, мы свели эти два разных метода в одну таблицу, которая показывает способы их использования:

Таблица 3: BATCHSIZE и ROWS_PER_BATCH для разных массовых методов

(1) Если BATCH_SIZE не задан, BCP будет использовать в качестве значения по умолчанию 1000.
(2) Для INSERT…SELECT нельзя управлять параметрами BATCHSIZE и ROWS_PER_BATCH.
(3) В качестве источника нужно использовать OPENROWSET(BULK…).

Размеры пакетов в Integration Services

В Integration Services размеры пакетов используются по-другому по сравнению с методами массовой загрузки.
По умолчанию Integration Services создаёт один пакет на конвейерный буфер. После того, как буфер сбрасывается на диск, пакет (имеется в виду пакет вставки, а не пакет SSIS) фиксируется. Можно изменить такое поведение, задав значение параметру "Maximum Insert Commit Size" (Максимальный фиксируемый размер вставок) для получателя данных.
В представленной ниже таблице показаны возможные варианты использования "Maximum Insert Commit Size" (MICS).

Таблица 4: Максимальный фиксируемый размер вставок и его особенности

Обратите внимание, что единственным способом получения пакетов большего размера, чем размер буфера состоит в том, чтобы изменить размер буфера в свойствах потока данных.

Сжатие данных и массовая загрузка

Когда массовая загрузка данных выполняется в сжатую таблицу или секцию, сжатие уровня страницы и уровня строки обычно происходит прямо во время массовой загрузки.
Однако следует помнить об одном исключении: когда массовая загрузка направлена в кучу со сжатием страниц, нужно использовать подсказку TABLOCK, чтобы получилось именно сжатие страниц. Если подсказка TABLOCK не используется, то для кучи будет выполнено сжатие уровня строк.
Использование сжатия уровня страниц на получателе массовой загрузки приведёт к естественному снижению производительности загрузки, особенно, если система ввода-вывода способна набрать достаточную скорость, позволяющую полностью загрузить процессоры.
На представленной ниже иллюстрации продемонстрирован эффект от сжатия таблицы с 50 миллионом строк, при этом система не испытывает проблем с большим вводом-выводом:

Красная линия графика показывает размер таблицы, а синие полосы показывают затраченное время.

Другие минимально протоколируемые операции и операции над метаданными

В дополнение к изложенному материалу стоит упомянуть, что кроме операций вставки, существует ещё ряд операций, которые тоже протоколируются минимально. Если такие операции выполняются вместе с инструкциями вставки данных, во многих случаях загрузка в хранилища данных может полностью осуществляться как минимально протоколируемая операция.
Хотя операции массовой загрузки протоколируются минимально довольно часто, совершенно не обязательно, что так будет происходить всегда. Например, массовая загрузка данных в таблицу, которая уже содержит данные и имеет индексы, не будет протоколироваться минимально даже при том, что будет по-прежнему считаться быстрой массовой загрузкой. В этой статье мы будем рассматривать все типы массовой загрузки, и те, которые протоколируются минимально, и те, которые протоколируются полностью.
В этом разделе мы опишем операции, которые затрагивают только метаданные. Операции, подобные SWITCH и TRUNCATE, могут затронуть большие объёмы данных, хотя выполняют совсем небольшие изменения в файлах данных и журнале транзакций.

DROP TABLE

Операция уничтожения таблицы выполняется только над метаданными. Такое удаление таблицы позволяет очень быстро удалить большое количество строк, без затрат на протоколирование, как в случае инструкции DELETE.

MERGE

Команда MERGE является новой инструкцией Transact-SQL, и тоже позволяет вставлять строки с минимальным протоколированием. Это справедливо, когда вставка посредством MERGE направлена в кучу, в других случаях для минимального протоколирования вставок необходимо активировать флаг трассировки 610.

TRUNCATE TABLE

Точно так же как DROP TABLE, инструкция TRUNCATE TABLE является операцией, выполняемой только над метаданными. Следует помнить, что относительно TRUNCATE TABLE действуют некоторые ограничения. Вот они:

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

CREATE INDEX, DROP INDEX и REBUILD INDEX

Операции записи, выполняемые при создании, удалении или пересоздании индекса, являются минимально протоколируемыми. Несмотря на то, что в файлы данных записывается много информации, в журнале транзакций этому будет соответствовать совсем мало записей.
В корпоративных редакциях SQL Server, создание индексов распараллеливается. Однако нужно помнить, что параллелизм при создании индексов может стать причиной их фрагментации.

Partition SWITCH (переключение секции)

В SQL Server 2005 появилось секционирование таблиц и переключение секций. Переключение секций физически не перемещает данные; эта операция выполняется только над метаданными, что позволяет загружать параллельно много однотипных потоков данных в несколько промежуточных таблиц. После того как загрузка будет закончена, можно выполнить команду SWITCH для создания одной большой таблицы.
Изменения значений строк в таблице в режиме минимального протоколирования невозможны, как и удаления из хранимого подмножества строк. Однако, как будет показано в главе "Решения для типовых задач массовой загрузки", команда SWITCH может использоваться для эмуляции поведения минимально протоколируемых инструкций.
Создание промежуточной таблицы, которая будет переключена взамен секции главной таблицы, может оказаться довольно сложной задачей. Для того чтобы использовать SWITCH, необходимо, чтобы метаданные и распределение страниц промежуточной таблицы были точно такими же, как и у основной таблицы. Для этого нужно следовать представленному ниже алгоритму, который позволит выполнять манипуляции над метаданными согласовано:

1. Создание точной копии схемы главной таблицы.

   Наиболее очевидным способом добиться этого результата является использование инструкции CREATE TABLE, которая должна соответствовать исходной схеме таблицы.
   Также можно использовать показанный ниже простой метод, позволяющий быстро создать идентичную по схеме таблицу:

   SELECT TOP(0) *
   INTO <TargetTable>
   FROM <SourceTable>

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

   CREATE CLUSTERED INDEX IX_temp
   ON <<argetTable> () ON []
   DROP INDEX <TargetTable>.IX_Temp

2. Соответствие индексов главной и промежуточной таблиц.

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

   • Настройки сжатия
   • Привязка к файловой группе

3. Создание ограничений для промежуточной таблицы.

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

   ALTER TABLE <TargetTable>
   ADD CONSTRAINT CK_<X>
   CHECK (<PartCol> < <LeftValue>)

   Для секций между первой и последней будет следующий формат ограничения:

   ALTER TABLE <argetTable>
   ADD CONSTRAINT CK_<X>
   CHECK (<PartCol> IS NOT NULL
   AND <PartCol> >= <LeftValue>
   AND <PartCol> < <RightValue>)

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

   ALTER TABLE <TargetTable>
   ADD CONSTRAINT CK_<X>
   CHECK (<PartCol> IS NOT NULL
   AND <PartCol> >= <LeftValue>)

   Все представленные выше шаблоны предполагают, что функция секционирования использует RANGE LEFT.

4. Исполнение инструкции SWITCH.

   После всех необходимых подготовительных действий, можно исполнить команду SWITCH:

   ALTER TABLE <SourceTable>
   SWITCH PARTITION <X> TO <TargetTable>

   Для того чтобы облегчить понимание всех тонкостей управления секциями, воспользуйтесь бесплатным инструментом, который доступен для скачивания на сайте CodePlex: SQL Server Partition Management

Решения для типовых задач массовой загрузки

В этой главе будут рассмотрены некоторые наиболее типичные сценарии загрузки данных и представлены шаблоны для их реализации. В примерах будет использоваться отвлечённая таблица фактов Sales. Будут рассмотрены следующие сценарии:

• Массовая загрузка в пустую, несекционированную таблицу
• Массовая загрузка в несекционированную таблицу, в которой уже имеются данные
• Массовая загрузка в секционированную таблицу
• Удаление всех строк из секции или таблицы
• Удаление большого числа строк из секции или таблицы
• Обновление большой части данных секции или таблицы

В каждом из указанных сценариев основное внимание будет уделено тому, что нужно сделать для повышения скорости выполняемых операций.
В целях иллюстрации, в приводимых ниже примерах будем использовать метод BULK INSERT. Однако, эти инструкции применимы и для других методов массовой загрузки.

Массовая загрузка в пустую, несекционированную таблицу

Поскольку загрузка данных в несекционированную таблицу является довольно простой операцией, оптимизирована она может быть несколькими способами.
Перед тем, как начать загрузку данных в пустую таблицу, считается хорошей практикой отключать у этой таблицы все индексы. После загрузки индексы нужно пересоздать.
Если исходные данные извлекаются из таблицы в SQL Server, и для этого используются SELECT INTO или INSERT…SELECT, загрузка пройдёт быстро и просто. Если исходные данные не хранятся в SQL Server, можно использовать то, что подойдёт лучше: BCP, BULKINSERT, INSERT…SELECT или Integration Services.
Выполнить много параллельных операций вставки в кучу можно в том случае, когда выбран такой сценарий массовой загрузки, который приведёт к выполнению операции c установкой на таблице блокировки массового обновления (BU). BU - блокировки совместимы, и следовательно две массовые операции могут выполняться одновременно.
Если ваша дисковая подсистема достаточно быстра, есть смысл рассмотреть возможность параллельного исполнения нескольких операций BULK INSERT. Одиночная массовая операция будет утилизировать ресурсы только одного процессорного ядра. Как мы увидим и в других сценариях, использование нескольких параллельных потоков массовых операций является ключом к масштабированию массовой загрузки данных.
В этом сценарии будет нецелесообразно использование инструкций INSERT…SELECT и SELECT INTO. Обе они приведут к исключительной (X) блокировке на уровне всей таблицы, куда идёт загрузка. А это означает, что единовременно может выполняться только одна операция массовой загрузки, что является ограничением для масштабируемости. Однако, такие средства, как: BCP, BULK INSERT и Integration Services способны обойтись BU - блокировкой, если указать оптимизатору подсказку TABLOCK.
Исполняется несколько операций массовой загрузки, типа:

BULKINSERT Sales FROM'Sales<X>.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' , TABLOCK )

Выполнение параллельной массовой загрузки в одну таблицу-кучу масштабируется довольно хорошо. Наши тесты производительности, описанные в этой статье: "ETL World Record", показали следующую зависимость:

16 параллельных массовых операций масштабируются практически линейно, правда, всё это зависит от размера данных. После этого, проявляется особенность работы в SQL Server распределения внутренних структур данных, что ограничивает производительность вставки. Наличие подобного узкого места сигнализируется сервером в виде появления в sys.dm_os_latch_stats ожидания ALLOC_FREESPACE_CACHE. Обратите внимание, что представленные на графике значения меняются в зависимости от размера вставляемых строк.
Если необходимо добиться линейного масштабирования вышеуказанного ограничения, то более высокой производительности можно добиться с помощью секционирования таблицы. Используя секционирование можно загружать несколько таблиц одновременно и потом с помощью переключения секций объединять данные, как это показано в главе "Массовая загрузка в секционированную таблицу".

Вставка упорядоченных данных

Это особый случай, который нуждается в отдельном рассмотрении. Если у получающей данные таблицы имеется кластеризованный индекс, и исходные данные упорядочены точно так же, как задано ключом кластеризованного индекса, можно задействовать подсказку ORDER и не отключать кластеризованный индекс у таблицы на время массовой загрузки. Использование подсказки ORDER может значительно ускорить массовую загрузку, потому что это приводит к устранению в плане исполнения загрузки промежуточного шага сортировки по ключу кластеризованного индекса. Однако так поступать следует только в том случае, если данные поступают из источника уже упорядоченными; предварительная же сортировка перед вставкой не приведёт к увеличению скорости загрузки.
Если вначале таблица пуста, операция будет протоколироваться минимально. Однако если в ней уже есть данные, и, если не включен флаг трассировки 610, операция будет протоколироваться полностью.
Подсказки ORDER и TABLOCK можно указывать одновременно. Это не позволит одновременно направлять несколько потоков в одну таблицу (накладывается блокировка стабильности схемы Sch-M). Однако это будет наиболее быстрым решением для одного потока, сопоставимое по скорости с однопоточной массовой загрузкой в кучу, и при этом устранятся необходимость в дополнительном после загрузки шаге CREATE INDEX (использующего дорогую операцию сортировки).
Для одного потока, представленный ниже сценарий операции массовой вставки был близок по производительности и при вставке в кучу, и при вставке в кластеризованный индекс с ключом по OrderDate.

BULK INSERT Sales FROM 'C:\temp\Sales200401'
WITH (
FIELDTERMINATOR = ';'
, ROWTERMINATOR = '\n'
, TABLOCK
, ORDER(OrderDate)
)

Высокая производительность получалась при вставке в кластеризованный индекс и кучу. Обратите внимание, что, если не включить флаг трассировки 610, то такая операция будет минимально протоколирована только в том случае, когда получающая данные таблица будет пуста, и используется параметр BATCHSIZE = 0. Для случая с ненулевым BATCHSIZE, минимально протоколироваться будет только первый пакет (первая фиксация).
Вставка в кластеризованный индекс отсортированных входных данных приводит к снижению фрагментации. Однако сортировка данных может потребовать большой объём памяти. Если нужно снизить фрагментацию и одновременно снизить утилизацию памяти сортировкой, можно воспользоваться следующей уловкой:

1. Создайте временную таблицу с такой же схемой как у таблицы, получающей данные.
2. Убедитесь, что у временной таблицы создан такой же кластеризованный индекс, как у получающей таблицы.
3. Выполните массовую загрузку данных в промежуточную таблицу. Если это окажется полезно для производительности, включите флаг трассировки 610.
   
   • Данные массовой загрузки окажутся в промежуточной таблице, они уже будут отсортированы, но могут оказаться фрагментированными.
4. Используйте метод массовой загрузки INSERT…SELECT для перемещения данных из промежуточной таблицы в таблицу, куда изначально нужно было загрузить данные.
   
   • Поскольку промежуточная таблица уже отсортирована, данные будут вставляться в таблицу назначения в отсортированном порядке.
   • Этот подход обеспечит минимальную фрагментацию вставляемых данных в таблице назначения.

Описанная уловка потребует больше операций ввода-вывода, чем загрузка данных напрямую в таблицу назначения. Однако минимизация фрагментации может повысить скорость чтения для больших рабочих нагрузок при просмотре всей таблицы или диапазонов ключей.
Обратите внимание, что если при массовой загрузке в кластеризованный индекс используются маленькие пакеты, которые потенциально могут поместиться в памяти, SQL Server оценивает необходимый для сортировки объём памяти, предполагая, что в среднем столбцы переменной длины будут заполнены наполовину. Если для какого-либо пакета размер данных в памяти окажется большим, чем эта оценка, сортировка будет сопровождаться сбросом страниц на диск (протечкой), что серьёзно ухудшит производительность. Вы можете обнаружить события протечки сортировки (sort spill) используя SQL Server Profiler, отслеживая класс событий "Sort Warning".
Если, вместо половинной наполненности столбцы varchar имеют тенденцию к полному заполнению, можно помочь SQL Server предотвратить протечку сортировки, используя следующий обходной манёвр:

При создании временной таблицы, о которой говорилось выше при описании шага 1, увеличьте втрое размер столбцов переменной длины. При этом настройки длины столбцов таблицы постоянной длины изменять не нужно. Это увеличит оценки памяти, распределяемой на шаге сортировки до более чем достаточных значений. Протечек не будет.

Настройка Назначения OLE DB для вставки упорядоченных данных в Integration Services

Несмотря на то, что средствами графического пользовательского интерфейса настройка сортировки входного потока данных делается не совсем очевидным способом, такая возможность для Назначения OLE DB существует и настроить её в Integration Services можно в расширенных свойствах компонента.

Достаточно добавить в свойстве FastLoadOptions подсказку ORDER, как это показано на рисунке выше.

Массовая загрузка в несекционированную таблицу, в которой уже имеются данные

Если таблица, в которую выполняется загрузка, не содержит индексов, нужно руководствоваться советами из предыдущей главы. Напомним, что в уже имеющуюся таблицу не стоит загружать данные методом SELECT INTO.
Когда загрузка выполняется в таблицу, которая уже имеет индексы, это повод для принятия особых мер. Сначала, прочтите статью SQL Server Books Online: "Рекомендации по оптимизации массового импорта данных".
В этой статье представлены рекомендации по отключению индексов перед массовой загрузкой данных в таблицу. Однако стоит помнить, что рекомендации в SQL Server Books Online применимы только для однопоточных операций массовой загрузки. По мере увеличения параллелизма и числа ядер, удаление индексов, одновременная загрузка в нескольких потоках, а затем восстановление индексов может выполниться быстрее, чем вставка данных в существующие индексы. Также, стоит помнить, что массовая загрузка в кластеризованный индекс будет протоколироваться минимально, только если таблица пуста или если включён флаг трассировки 610. Это означает, что протоколирование нескольких параллельных массовых загрузок не будет минимальным.
Стоит протестировать оба сценария: удаление/создание индексов и загрузка данных в таблицу с индексами. Если выбор падёт на параллельную загрузку всей таблицы, руководствуйтесь советами предыдущей главы.

Массовая загрузка при наличии индексов

Массовая загрузка данных в таблицу, которая уже имеет индексы, сильно отличается от массовой загрузки в кучу без индексов.
Если массовая загрузка осуществляется в таблицу с одним или несколькими индексами, нужно учесть представленные ниже особенности организации такой загрузки.

Индексированные таблицы не поддерживают блокировки Bulk Update (BU)

Таблицы с индексами не поддерживают BU-блокировку, вместо неё будет исключительная блокировка (X-блокировка). X-блокировки, в отличие от BU-блокировок, не совместимы с другими блокировками такого же типа. Массовая загрузка в таблицу с индексами потребует наложения X-блокировки уровня строки. В принципе, нет запретов на использование подсказки TABLOCK для выполнения массовой загрузки в индексированную таблицу, но это приведёт к тому, что X-блокировки поднимутся до уровня таблицы, что заблокирует другие потоки массовой загрузки в ту же самую таблицу. Для обеспечения параллелизма массовой загрузки в индексированную таблицу, не стоит использовать подсказку TABLOCK.

Определите оптимальное значение для BATCHSIZE

Эмпирическим путём подберите такое значение BATCHSIZE, при котором производительность массовых операций будет наивысшей. Нулевой размер BATCHSIZE полезен для вставки в кучу без индексов, и неприменим для вставки в таблицы, у которых индексы имеются. Если вставляемые данные предварительно не отсортированы (и указана подсказка ORDER), оптимизатор выполнит операцию сортировки вставляемых данных перед тем, как вставить данные пакета. Если такую сортировку не получается выполнить в памяти, будет выполнена промежуточная материализация сортируемых данных в tempdb, операции ввода-вывода в которой приведут к замедлению скорости вставки. Небольшой размер BATCHSIZE гарантирует, что сортировка будет происходить в памяти, но недостатком такого подхода является расщепление страниц, и фрагментация индексов, если вставляемые данные не отсортированы. Также, от размера BATCHSIZE будет зависеть возможность одновременной загрузки данных множеством запросов к одной и той же таблице.

Избегайте укрупнения блокировок

Укрупнение блокировок до уровня таблицы может начаться, если число X-блокировок уровня строки превысит пороговое значение. Если такое случается, параллелизм деградирует до однопоточной вставки. Методика борьбы с подобным сценарием укрупнения блокировок зависит от версии SQL Server:

• Для SQL Server 2005, укрупнение блокировок происходит при достижении установки 5000 блокировок. Для борьбы с этим применяют такие способы:

  • Установка настолько маленького BATCHSIZE, чтобы не происходило укрупнение блокировок.
  • Другой способ радикальный, можно использовать флаг трассировки 1211, который полностью отключает укрупнение блокировок для всего сервера.

• Для SQL Server 2008, также возможно полностью отключить укрупнение блокировок до уровня таблицы.

Использование непересекающихся входных потоков

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

Использование флага трассировки 610

Если включить флаг трассировки 610, вставка в таблицу с индексами может стать минимально протоколируемой операцией. Таблицы с кластеризованными индексами поддерживают одновременную массовую загрузку в несколько потоков вставки, пока эти потоки содержат непересекающиеся данные. Если будет обнаружено пересечение данных, потоки будут блокировать друг друга (но это не будут взаимоблокировки).
Ниже показан пример, где на основе таблицы Sales в несколько шагов проиллюстрировано, как можно добиться максимума производительности при многопоточном исполнении BULK INSERT в таблицу с кластеризованным индексом:

1. Отключаем для таблицы эскалацию блокировок. ALTER TABLE Sales SET (LOCK_ESCALATION = DISABLE) В SQL Server 2005 можно отключить эскалацию блокировок, присвоив BATCHSIZE значение 5000. Иначе, этого можно было достичь включением флага трассировки 1211 (который полностью отключает эскалацию блокировок на сервере). Для получения более подробной информации о флаге трассировки 1211, см. Lock Escalation in SQL2005.

2. Запустите несколько команд BULK INSERT с непересекающимися диапазонами.

   Секционирование данных на непересекающиеся диапазоны по столбцу Date: BULKINSERT Sales FROM'200101.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' ) BULKINSERT Sales FROM'200102.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' ) … и т.д. … Не используйте подсказку TABLOCK для отдельных потоков.

   Поэкспериментируйте с разными значениями BATCHSIZE, чтобы понять, для каких значений достигается оптимальная производительность, с учётом особенностей вашей подсистемы ввода-вывода и ресурсов памяти.

3. Проверка блокировок и параллелизма.
   Вы должны наблюдать несколько незаблокированных потоков массовой загрузки. Каждый из этих потоков должен удерживать монопольную X-блокировку уровня строки. Для проверки можно использовать запрос, подобный показанному ниже:

   SELECT lck.resource_type, lck.request_mode
          , lck.request_status , lck.request_session_id
          , COUNT(*) number_locks
   FROM sys.dm_tran_locks lck
   INNER JOIN sys.partitions par
          ;ON lck.resource_associated_entity_id = par.hobt_id
   INNER JOIN sys.objects obj
          ;ON par.object_id = obj.object_id
   WHERE obj.name = 'Sales'
   GROUP BY lck.resource_type, lck.request_mode
          , lck.request_status, lck.request_session_id

   Он должен возвращать нечто подобное:

   
   Рисунок 7: Листинг блокировок, удерживаемых операциями массовой загрузки

   Вы можете видеть X-блокировки строк (представленные значением KEY в колонке resource_type) и блокировки с намерением монопольного доступа (IX) на только что распределенных страницах данных.

Массовая загрузка в секционированную таблицу

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

• Появится возможность наложить массовую BU-блокировку на изъятую из таблицы секцию, что позволит в одно и то же время осуществлять массовые операции в несколько потоков.
• Перед переключением, можно отключить и потом воссоздать индексы для изъятой из таблицы секции, что может поднять скорость массовой загрузки.
• Массовая загрузка прямиком в секционированную таблицу потребует исполнения операции сортировки, даже если у таблицы нет индексов, или загружаемые данные заранее упорядочены, например, с помощью подсказки ORDERED. Операция сортировки инициируется оптимизатором запросов для устранения издержек, вызванных частым открытием и закрытием новых секций при одновременной большой нагрузке операций вставки данных в таблицу.

После того, как секция изъята, она ведет себя точно так же, как обычная таблица, и можно применять все рекомендации из предыдущих глав. Однако на практике не следует забывать добавлять ограничения, согласующие добавляемые секции (и включить CHECK CONSTRAINTS в опциях загрузки) перед началом операции массовой загрузки. Можно добавить проверочное ограничение и после загрузки, но это потребует исполнения полного просмотра таблицы. Если же не добавить ограничение, не получится переключить секцию обратно в таблицу.
Однако есть особый случай, который нужно учитывать. Если планируется одновременная вставка данных в несколько секций, возможна дальнейшая оптимизация этого процесса. В таком случае, нужно переключить каждую секцию в отдельную таблицу. С каждой из этих таблиц можно параллельно исполнять несколько операций массовой загрузки. Этот метод проиллюстрирован ниже:

1. Создание временных таблиц для каскадирования

   Создаются временные таблицы, в которые будут переключаться секции, как это было описано выше, в главе "Partition SWITCH (переключение секции)". Переключение данных во временные таблицы: ALTER TABLE Sales_P SWITCH PARTITION<X> TO Sales_200<X>

2. Оптимизация массовой загрузки с разделением на множество независимых таблиц

   BULK INSERT Sales_200 FROM'Sales200.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' ) …и т.д.…

3. Переключите промежуточных таблиц назад в главную таблицу.

   Возврат промежуточных таблиц назад, в виде секций: ALTER TABLE Sales_200<X> SWITCH TO Sales_P PARTITION <X>

4. Очистка промежуточных таблиц.

   Удаление временных таблиц, используемых для распараллеливания загрузки:: DROP TABLE Sales_200<X>

5. Выбор окончательного сценария загрузки.

   Представленная ниже блок-схема поможет сделать правильный выбор сценария массовой загрузки.

   
   Рисунок 8: Блок-схема решений для выбора методов массовой загрузки.

Удаление всех строк из секции или таблицы

Этот сценарий, несмотря на его простоту, часто упускают из вида. Самым быстрым способом удаления всех данных из таблицы, будет выполнение инструкции:

TRUNCATE TABLE Sales

Удаление данных из секции немного сложнее:

1. Создайте пустую временную таблицу, для помещения туда данных секции.

   Создаём временную таблицу, в которую будем переключать секцию, используя для этого описанный в главе "Partition SWITCH (переключение секции)" метод.

   Если исходная таблица имеет индексы, нужно воссоздать индексы и для временной таблицы.

2. Выполняем инструкцию SWITCH для той секции, данные которой нужно перенести во временную таблицу.

   ALTERTABLE Sales_P SWITCH PARTITION 1 TO Sales_Temp

   Исходная секция после этого опустеет.

3. DROP или TRUNCATE для временной таблицы.

   Если временная таблица больше не нужна, удаляем её: DROP TABLE Sales_Temp Иначе, можно очистить таблицу, после чего её можно будет использовать повторно, например, для удаления других секций: TRUNCATE TABLE Sales_Temp

Удаление большого числа строк из секции или таблицы

До сих пор мы рассматривали только способы вставки и удаления данных с минимальным протоколированием и/или с массовой загрузкой.
Бывают ситуации, когда нежелательно использовать инструкцию TRUNCATE для всех секций, но нужно удалить большое число строк из этой таблицы. Инструкция DELETE протоколируется полностью. Однако, существует возможность получить эффект, который похож на DELETE с массовой операцией. Эта возможность представлена ниже, в виде пошагового алгоритма.

1. Создайте копию основной таблицы.

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

2. Массовая загрузка строк в промежуточное хранилище секции.

   Чтобы заработала массовая вставка, можно использовать один из двух методов. Метод INSERT…SELECT: INSERT INTO Sales_Temp WITH (TABLOCK) SELECT*FROM Sales WHERE OrderDate >='20010101' AND OrderDate <'20020101' AND <Keep Criteria> Второй способ заключается в использовании возможностей Integration Services, позволяющих создать несколько параллельных, потоков в таблицу Sales_Temp.

3. Переключение секции и усечение старых данных

   Если нужна транзакционность операции, запустите транзакцию с помощью: BEGIN TRAN Чтобы удалить ненужные строки из главной таблицы, используйте методику, описанную в предыдущей главе: "Удаление всех строк из секции или таблицы".

4. Воссоздание индексов и использование SWITCH.

   Восстановить все индексы и ограничения таблицы Sales_Temp, чтобы они соответствовали тем, что присутствуют в главной таблице. Инструкция по выполнению операций над секциями была представлена выше, в главе "Partition SWITCH (переключение секции)". Она поможет с задачей переключения таблицы Sales_Temp с пустой секцией: ALTER TABLE Sales_Temp TO Sales SWITCH PARTITION 1

5. Удалите временную таблицу.

   Удалите временную таблицу: DROP TABLE Sales_Temp Если Вы использовали транзакцию, самое время её завершить: COMMIT TRAN

Обновление большой части данных секции или таблицы

До сих пор мы рассматривали способы вставки и, до некоторой степени, удаления данных в режиме минимального протоколирования.
Ни DELETE, ни UPDATE не могут протоколироваться минимально для операций над строками таблицы или секции. Однако, иногда можно создать такие сценарии с инструкциями UPDATE и DELETE, что позволит задействовать массовые операции.
Допустим, нужно удалить и/или изменить большое число строк таблицы или секции. Объём изменений оценивается так, что общее число строк, измененных этой операцией превышает 10 - 20 % от полного объёма таблицы. В этом случае, может быть полезна представленная ниже методика.

1. Создайте во временной таблице журнал изменений.

   Создайте временную таблицу, которая будет содержать строки, представляющие собой изменённые данные в существующей секции главной таблицы. Подобным образом создавалась таблица, в главе "Partition SWITCH (переключение секции)". Выполните массовую загрузку изменяемых строк в таблицу Sales_Delta. Впишите новые значения, которые должны быть после изменений.

2. Если таблица секционирована, переключите секцию, в которую попадают подлежащие изменению данные.

   Создайте временную таблицу, в которую будут переключены данные изменяемой секции. Для этого можно воспользоваться методом, который был описан выше, в главе "Partition SWITCH (переключение секции)". Переключить секцию, данные которой будут изменяться, можно так: ALTER TABLE Sales_P SWITCH PARTITION 1 TO Sales_Old После этого у вас будут две таблицы: Sales_Delta и Sales_Old.

Если таблица не секционирована, пропустите этот шаг и используйте далее основную таблицу вместо таблицы Sales_New, в качестве одного из источников следующего шага.

3. Слияние (merge) журнала изменений и изначальной таблицы, и массовая загрузка во временную таблицу.

   Выполнение слияния:

   Создайте временную таблицу, куда будет выполнено переключение секции, как это было описано выше, в главе "Partition SWITCH (переключение секции)". Выполните слияние старых записей в Sales_Old и разницы из Sales_Delta. Это легко реализуется с помощью Integration Services, если использовать такие компоненты, как merge или lookup. Иначе, слияние можно выполнить с помощью инструкции Transact-SQL, подобной этой: SELECT o.PrimaryKey   , COALESCE(d.Col1, o.Col1)   , COALESCE(d.Col2, o.Col3) ...и т.д.... FROM Sales_Old o LEFT JOIN Sales_Delta d

   После успешного выполнения этого шага, таблица Sales_New должна быть заполнена и содержать результаты правильного слияния строк, показанных на рисунке промежуточных таблиц.
   Представленный на этом шаге сценарий позволяет добиться высокой степени параллелизма, если одновременно запускать несколько параллельных операций слияния со вставкой в таблицу Sales_New. Каждая операция должна исполняться самостоятельно, и опираться на собственное подмножество данных таблицы Sales_Delta.

4. Удаление журнала изменений и первоначальных данных.

   Если необходимо выполнить изменение в транзакции, тогда этот шаг нужно начинать с инструкции: BEGIN TRAN Удалите промежуточную таблицу журнала изменений, созданную на шаге 1: DROP TABLE Sales_Delta Удалите промежуточную таблицу с оригинальными данными, которая появилась на шаге 2: DROP TABLE Sales_Old Если таблица не секционирована, выполните усечение Sales вместо удаления таблицы Sales_Old.

5. Переключение (SWITCH) результатов слияния

   Использовать команду SWITCH, чтобы переместить новую версию данных таблицы взамен прежних данных: ALTER TABLE Sales_New SWITCH TO Sales_P PARTITION 1 Удалите таблицу Sales_New, чтобы избавиться от промежуточных результатов: DROP TABLE Sales_New Если для завершающих шагов использовалась транзакция, её нужно завершить командой: COMMIT TRAN

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

Массовая загрузка, запросы с NOLOCK и Read Committed Snapshot Isolation

Очень часто, когда нужно читать данные из таблицы, которая потенциально подвержена множественным блокировкам, запрос исполняется с указанием подсказки оптимизатору NOLOCK. Такое чтение известно как "грязное чтение", а сами запросы с NOLOCK выполняются с уровнем изоляции Read Uncommitted и чреваты риском получения неполных или противоречивых результирующих наборов. Однако, поскольку с этой подсказкой не используется совмещаемая блокировка, такие запросы не будут заблокированы операциями записи.
Read committed snapshot isolation (RCSI) появился в SQL Server 2005 в качестве новой возможности чтения данных и наложение на это время блокировок, не мешающих работать другим процессам, в то время, как существуют другие запросы, изменяющие данные в той же таблице. Это хорошая альтернатива NOLOCK, потому что при использовании такого уровня изоляции гарантируется целостное, непротиворечивое с точки зрения транзакций представление данных, для которого не нужно использовать специальные подсказки оптимизатору. Изначально RCSI предназначался для характерных OLTP нагрузок, но этот уровень изоляции может стать мощным подспорьем в нагрузках, свойственных информационным хранилищам или для сценариев, в которых велика доля операций массовой вставки.
RCSI включается для базы данных. При включении этого уровня изоляции, запросы на чтение не накладывают разделяемую блокировку на строки, страницы или таблицу, и поэтому они не блокируются эксклюзивными X-блокировками или BU-блокировками. Вместо блокировок, новые или изменённые в таблице строки получают 17-байтный идентификатор версии, и ещё до того, как образ таких строк будет изменён в рамках транзакции (изменения или удаления) они будут скопированы в системную базу данных tempdb, которая в этом случае исполняет роль хранилища версий строк для всего экземпляра SQL Server. Когда исполняется запрос на чтение, извлекаются только те строки, которые существовали с самого начала исполнения запроса, при этом, игнорируя позднейшие номера версии строк, а для изменений извлекаются ссылки в tempdb для соответствующих предыдущих версий строк.
Транзакционная семантика для RCSI идентична обычному уровню изоляции Read Committed, с той разницей, что запросы на чтение всегда возвращают данные на момент начала запроса, вместо того, чтобы блокироваться до окончания конкурирующих запросов на запись. В результате этого, те приложения, которые требуют, чтобы не было никаких изменений во время выполнения запросов с уровнем изоляции RCSI, сохраняют точно такое же поведение, как и в предыдущих версиях. Транзакции, которые устанавливают уровень изоляции REPEATABLE READ, или когда инструкция SELECT выполняется с HOLDLOCK, будут по-прежнему запрашивать блокировки и потенциально подпадать под блокировки одновременных запросов на запись. Кроме того, не стоит путать RCSI с другим новшеством SQL Server 2005, речь идёт об ещё одном уровне изоляции: Snapshot Isolation (уровень изоляции моментального снимка). Этот уровень изоляции тоже использует версионность строк.
Для тех баз данных, которые подвержены большому числу операций UPDATE или DELETE, уровень изоляции RCSI может породить дополнительную нагрузку и повысить требования к пропускной способности tempdb. Однако, при изоляции RCSI, операции INSERT (включая BULK INSERT), не будут создавать нагрузки на tempdb и не станут приводить к дополнительным затратам ресурсов кроме добавления 17 байтов к каждой строке (обратите внимание на то, что в довершение ко всему эти 17 байт несжимаемы).
Хотя при RCSI чтение не страдает от X-блокировок, существует два случая при массовой загрузке данных, при которых в SQL Server 2008 запросы с уровнем RCSI будут блокироваться (так же как запросы с NOLOCK):

 

• При загрузке кучи с подсказкой TABLOCK, когда используются операции BULK INSERT, INSERT…SELECT или другие операции массовой загрузки. Это происходит, потому что при загрузке накладывается BULK OPERATION блокировка с намерением монопольного доступа (IX-блокировка), а чтение с NOLOCK или RCSI накладывают разделяемую BULK OPERATION блокировку (S-блокировка). Для массовой загрузки в кучу с параллельными операциями чтения, несмотря на использование RCSI или NOLOCK, нужно убрать подсказку TABLOCK, что приведёт к отказу от возможности минимального протоколирования этих операций.
• В начале загрузки в пустую таблицу с кластеризованным индексом, с подсказкой TABLOCK, и с использованием операций BULK INSERT, INSERT…SELECT или других операций массовой загрузки. Это происходит, потому что загрузка выполняется с блокировкой стабильности схемы (Sch-M блокировка), а чтение выполняется с блокировкой изменения схемы (Sch-S блокировка). В качестве альтернативы можно включить флаг трассировки 610, что обеспечит минимальное протоколирование и отказ от использования подсказки TABLOCK.

Кроме этих исключений при уровне изоляции RCSI операции BULK INSERT (включая INSERT…SELECT) могут использоваться с подсказкой TABLOCK, или они могут использовать нормальные правила эскалации блокировок, и во всех этих случаях чтение не будет блокироваться. Производительность будет аналогична исполнению запросов с подсказкой NOLOCK, с тем преимуществом, что в отличие от способа с NOLOCK, чтение будет оперировать транзакционно целостным представлением данных таблицы. Кроме того, даже если одновременно будет существовать множество продолжительных запросов на чтение данных таблицы, вставка новых данных с помощью операции INSERT не будет блокироваться чтением. Чтобы включить для базы данных уровень изоляции RCSI, нужно выполнить представленную ниже команду, которая должна выполняться в отсутствие других подключений к изменяемой базе данных.

ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON

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

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'

Перед принятием решения об использовании RCSI для ваших рабочих нагрузок стоит убедиться, что у вас имеется полное понимание того, насколько часто выполняются операций UPDATE и DELETE, и нужно обеспечить такую конфигурацию tempdb, которая позволила бы без проблем справиться с той частью рабочей нагрузки, которая обеспечивает изменения и удаления данных. Также нужно учитывать, что уменьшение блокировок между операциями чтения и записи может привести к увеличению конкурентного ввода-вывода, порождаемого этими запросами, и нужно обеспечить необходимую пропускную способность для возросшего числа запросов ввода-вывода к используемой дисковой подсистеме.

Оптимизация массовой загрузки данных

В этой главе мы углубимся в те методы, которые обычно используются для оптимизации процесса массовой загрузки. SQL Server способен обеспечивать очень высокую производительность массовой загрузки. Но для этого и все остальные ресурсы должны обеспечивать должную поддержку и производительность.
Существует несколько направлений оптимизации массовой загрузки. Прежде всего, это параллелизм, который является ключевым фактором оптимизации для массовой загрузки, и который позволяет линейно масштабировать процесс по числу процессорных ядер.
Во время поиска решений по повышению производительности, статистика ожиданий и счетчики производительности являются основными источниками информации, необходимой для анализа первопричин возможных проблем. Наиболее критичные параметры производительности рассмотрены ниже, в главах: "Отслеживаемые типы ожиданий" и "Отслеживаемые счетчики производительности".
Некоторые моменты настройки производительности и устранения критических проблем требуют более глубокого вмешательства. К таким моментам относится глава "Отслеживаемые счётчики производительности" с углублённым описанием оптимизации сетевого взаимодействия, главы: "Конкуренция не планировщике", "Конкуренция PFS". Последним направлением, но не последним по значимости, мы рассмотрим небольшой набор рекомендаций в главе: "Оптимизация ввода-вывода и размещения файлов".

Параллелизм и секционирование загрузки

Основным фактором оптимизации производительности во время массовых операций является параллелизм. Лучше всего параллелизм достигается выполнением нескольких команд массовой загрузки в одно и то же время. Поскольку каждая массовая операция выполняется в собственном потоке, понадобится выполнить несколько таких операций, чтобы загрузить все процессорные ядра. Каждая команда массовой операции потенциально может утилизировать свой процессор на 100%. Следовательно, оптимальная производительность при загрузке всех ядер на 100% может быть достигнута в том случае, когда число параллельно выполняемых команд массовых операций будет таким же, как общее число процессорных ядер, выделенных в вашей системе для массовых операций.
При проектировании параллельных операций, также нужно учитывать то, какое влияние на процесс оказывает очерёдность исполнения операций, когда одна операция заканчивается перед началом другой. В идеале, получаемые данные должны секционироваться на части равного размера. Это будет гарантией того, что все параллельные задачи завершатся приблизительно в одно и то же время.
На иллюстрации ниже показано, как перекос при распределении размера секций может привести к увеличению времени исполнения.

Отслеживаемые типы ожиданий

Когда нужно повысить производительность массовых операций, вначале нужно проанализировать данные из системного административного представления sys.dm_os_wait_stats. Оно поможет определить, блокируются ли какие-либо процессорные ресурсы. Сначала внимание стоит сфокусировать на значениях с наиболее продолжительным временем ожидания и постепенно оптимизировать наиболее "узкие" места. По ссылке Вы найдёте сценарий с сайта TechNet, который может оказаться полезным для этих целей: Retrieve Waitstat Snapshots.
Также, будет полезен сбор данных из sys.dm_os_latch_stats, особенно если планируется реализовать много потоков массовой загрузки в одну таблицу.
Ниже представлена таблица, в которой перечислены некоторые наиболее распространённые при массовой загрузке типы ожиданий, указаны их возможные причины, и те действия, которое необходимо предпринять для исследования и устранения проблемы.

Таблица 5: Типичные ожидания для массовых операций

Отслеживаемые счетчики производительности

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

Если для массовой загрузки данных по сети используется Integration Services или BCP, правильная настройка производительности становится критически важным фактором.
Взаимодействуя со своим отделом локальных и глобальных сетей, выясните инфраструктуру и конфигурацию сети. Стоит также удостовериться в том, что выбранная конфигурация обеспечивает минимально возможное время ожидания. Ниже перечислены несколько параметров, которые для этого можно оптимизировать:

• Используйте высокопроизводительные сетевые платы и коммутаторы.
• Установите последние версии сертифицированных драйверов используемых сетевых плат.
• Включите полный дуплекс.
• Включите поддержку "Гигантских" фреймов (Jumbo frames).
• Используйте режим "TCP Chimney Offload".
• Используйте режим Receive Side Scaling (RSS).

Ниже перечислены ссылки на документы, где можно найти краткий обзор ключевых атрибутов сетевых интерфейсов и того, как они взаимодействуют с операционной системой Windows®:

• High Performance Network Adapters and Drivers
• Scalable Networking Pack: Frequently Asked Questions
• Introduction to Receive-Side Scaling

Оптимизировать производительность сети непосредственно для клиента массовой загрузки можно, изменяя параметр размера сетевого пакета для подключения к SQL Server. В Integration Services размер пакета настраивается в свойствах менеджера подключения, как это проиллюстрировано ниже.

Если используется BCP, размер пакета позволяет задать ключ -a. Увеличение размера сетевого пакета сокращает число операций чтения и записи, потребляемых сетевым стеком SQL Server. Поскольку подобные изменения в настройках приведут к тому, что каждая операция будет чуть больше утилизировать процессоры, сокращение ввода-вывода может поднять производительность. В таблице, приведенной ниже, представлены несколько способов оптимизации одного потока, которые применялись для достижения мирового рекорда загрузки данных в ETL системе.

Таблица 6: Эффект от размера сетевого пакета

Если для достижения требуемого уровня пропускной способности по сети используется несколько сетевых плат, можно извлечь выгоду из привязки прерываний сетевых интерфейсов Windows к указанным процессорным ядрам. Для получения подробной информации о такой привязке ознакомьтесь со статьёй: Scaling Heavy Network Traffic with Windows.
Используя гигабитную сетевую плату можно достичь пропускной способности приблизительно в 70-100 мегабайт в секунду в зависимости от передаваемых при массовой загрузке данных. Для измерения производительности сети используйте счетчики из раздела Сетевой интерфейс (Network Interface).

Конкуренция на планировщике (Scheduler Contention)

Конкуренция на планировщике возникает тогда, когда две параллельные операции массовой загрузки исполняются на одном и том же планировщике SQLOS, за который они конкурируют, т.е. конкурируют за циклы одного и того же процессорного ядра. Обычно такое может случиться на компьютерах, у которых имеется много процессорных ядер. Наличие конкуренции на планировщике можно обнаружить по ожиданию SOS_SCHEDULER_YIELD, которое может появляться даже в тех случаях, когда число процессов загрузки данных не превышает число процессорных ядер.
Одним из способов решения подобной проблемы является программная настройка поддержки экземпляром SQL Server неоднородного доступа к памяти (Soft-NUMA). Каждый программно настроенный узел Soft-NUMA должен содержать по одному процессорному ядру. После этого, каждому Soft-NUMA узлу назначается индивидуальный порт TCP/IP. Это позволит выбирать явно, на какой процессор направляется нагрузка, указав необходимый порт в качестве части строки подключения к SQL Server. Обратите внимание, что изменение параметров настройки Soft-NUMA требует рестарта службы SQL Server. На рисунке ниже проиллюстрирован этот принцип.

Для получения дополнительной информации о настройке Soft-NUMA для подобных конфигураций, см. "Как настроить сервер SQL Server на использование программной архитектуры NUMA" и "Как сопоставить порты TCP/IP узлам NUMA".
Когда настроена привязка портов к узлам, в журнале ошибок SQL Server эти порты TCP будут представлены так, как это показано на рисунке ниже.

Также можно настроить отдельный порт, который будет отображаться на все процессорные ядра, и его можно будет использовать для обычных подключений, которые не задействованы в синхронизированной рабочей нагрузке массовой загрузки данных.
Можно предотвратить конкуренцию на планировщике с помощью других методик, например, с помощью методики, получившей название: "Отключись и подключись заново". Эта методика описана в этой технической статье: "Resolving scheduler contention for concurrent BULK INSERT".

Конкуренция PFS (Page Free Space)

Несмотря на то, что массовая загрузка с минимальным протоколированием сопровождается малыми объёмами записи в журнал транзакций, возможны трудности с распределением новых страниц для целей массовой загрузки. При таких распределениях, SQL Server использует специальный тип страниц - Page Free Space (PFS), с помощью которых отслеживается использование страниц в файле данных.
Поскольку для массовых операций новые страницы должны распределяться очень быстро, обращение к страницам PFS для выделения места под загружаемые данные может стать критически важным шагом. Если такие обращения будут проблематичными, сервер об этом будет сигнализировать ожиданием PAGELATCH_UP. С помощью запроса к sys.dm_os_buffer_descriptors можно удостовериться, что ожидается доступ к страницам PFS. Избавиться от ожиданий PAGELATCH_UP можно путём увеличения числа файлов данных в оперируемой файловой группе. Обратите внимание, что наличие нескольких файлов данных в файловой группе может сделать дисковый ввод-вывод менее эффективным, как это описано в идущей далее главе "Получатель массовой загрузки SQL Server".
Для получения более подробной информации о страницах PFS и о том, как они могут влиять на производительность, обратитесь к техническому документу "How many files should a database have? -Part 1: OLAP workloads" и статье в SQL Server Books Online "Управление размещением экстента и свободным местом".

Оптимизация ввода-вывода и размещения файлов

Когда выполняется параллельная загрузка в несколько потоков, нужно тщательно продумать конфигурацию используемой подсистемы ввода-вывода. Поскольку нужно добиться увеличения скорости чтения и записи, при оптимизации следует фокусироваться на времени ожидания диска.
Процесс массовой загрузки должен одновременно читать данные из источника и выполнять их массовую загрузку средствами ядра СУБД. Это означает, что нагрузка ввода-вывода имеет преимущественно последовательный характер доступа. Существенный выигрыш в производительности могут дать такие приёмы, которые, например, использовались при установлении Мирового Рекорда в ETL, и заключались в усовершенствовании вторичных факторов производительности ввода-вывода массовой загрузки, например, таких как размещение файлов по дискам.
Часто, может оказаться выгодным размещать исходные данные на других дисках, не на тех, на которые идёт загрузка. Изоляции друг от друга нагрузок чтения и записи можно достичь путём разделения двух разных по характеру нагрузки последовательных потоков по разным шпинделям.
Если учитывать особенности операционной системы Windows, то нужно убедиться, что используемые разделы дисков отформатированы с размером кластера NTFS равным 64 Кб, и кластеры правильно выровнены по границам сектора относительно блоков дисковых массивов.
Источник и получатель данных массовой загрузки оптимизируются по-разному, что будет рассмотрено в следующих главах.

Источник данных

Для достижения максимальной производительности массовой загрузки, нужно организовать несколько параллельных потоков чтения из источника данных, по одному и более для каждой операции массовой загрузки.
Если источник секционирован, и чтение выполняется последовательно, можно оптимизировать источник, помещая каждую исходную секцию данных на собственный, выделенный для этой секции набор шпинделей. Однако если вы имеете много исходных секций, большое количество потоков может быстро стать неуправляемым.
В качестве наглядного примера сложности управления большим числом потоков можно привести гипотетический случай с использованием текстовых файлов в качестве исходных данных и выполнение 64 параллельных команд BULK INSERT для этих текстовых файлов.
Давайте рассмотрим две крайности в конфигурации дисковых массивов:

• Just a Bunch Of Disks (JBOD) - это конфигурация с отдельными дисками (или возможно RAID1 или RAID 10 LUN), которые распределены в сегментированный массив.
• Stripe And Mirror Everything (SAME) - это конфигурация с распределением дисков в единственный большой LUN.

Just a Bunch Of Disks (JBOD)

С точки зрения производительности ввода-вывода, размещение JBOD обычно самое быстрое. В этом случае, каждый текстовый файл выкладывается на свой диск. Эта конфигурация показана ниже.

Поскольку BULK INSERT будет последовательно читать файл, для диска нагрузка ввода-вывода будет последовательной с очень маленьким временем задержки на операциях дискового поиска. Современный жесткий диск способен легко превысить скорость последовательного чтения в 100 Мб/сек, что более чем достаточно для нужд BULK INSERT. Однако следует учитывать повышение сложности управления подобным размещением файлов. В данном случае, понадобилось бы организовать в операционной системе 32 LUN, и это только для поддержки файлов исходных данных. Каждый из этих LUN должен быть соответствующим образом настроен на SAN или на встроенном SCSI контроллере. Кроме этого, добавится работы по секционированию, управлению файловой системой и резервному копированию, что может существенно усложнить поддержку и обслуживание такого решения. Если операции по обслуживанию и установке не будут выполняться автоматизированной системой, в отсутствии опытного персонала, работа с подобным сложным размещением файлов и большим числом аппаратных элементов повышает риски ошибок и проблем, вызванных человеческим фактором.

Stripe And Mirror Everything (SAME)

Следующий крайний случай наоборот предоставляет очень управляемую конфигурацию. В ней используется один большой LUN, набранный из большого числа дисков в один из возможных уровней RAID, и на этом логическом диске помещаются все файлы данных.
Такой сценарий обычно используется, когда важна простота установки и обслуживания, а также необходимо обеспечить возможность быстрого размещения большого количества файлов с исходными данными на сервере.

Однако несколько параллельных последовательных потоков чтения с одних и тех же шпинделей могут породить эффект, который известен под именем "ввод-вывод внахлёст" (I/O weaving). Даже притом, что рабочая нагрузка будет носить последовательный характер (с точки зрения ядра хранилища SQL Server), для диска такая нагрузка будет носить случайный характер. Поскольку ввод-вывод в реальности случайный, возрастает влияние времени поиска диска, что вынуждает вас увеличивать число шпинделей, чтобы обеспечить заданные требования к производительности.
Некоторые системы SAN и контроллеры SCSI способны использовать собственный кэш, чтобы преобразовать случайный ввод-вывод, и собрать несколько потоков в последовательный ввод-вывод. Стоит проконсультироваться у своего поставщика систем хранения, чтобы выбрать верные характеристики дисковой подсистемы для вашего характера ввода-вывода.
Наиболее удачная конфигурация для вашей системы может оказаться где-нибудь посередине между двумя описанными тут стратегиями SAME и JBOD. Вы должны добиться баланса между производительностью JBOD и управляемостью SAME. Также, обратите внимание, что стратегия JBOD опирается на тот факт, что массовая загрузка будет носить преимущественно последовательный характер. Если нагрузка носит смешанный характер, стратегия SAME может обеспечить наилучшую производительность системы в целом.

Получатель массовой загрузки SQL Server

Точно так же, как и в описанном выше случае с источниками данных, таблица, получающая данные массовой загрузки, также может выиграть от оптимизации ввода-вывода. Когда выполняется массовая загрузка в таблицу, характер ввода-вывода операций записи носит последовательный характер. Это можно использовать в своих интересах.
Сначала, разберём, как SQL Server пишет данные в файлы. Рассмотрим файловую группу с двумя файлами.

Когда SQL Server выполняет массовую загрузку в таблицу, новые экстенты распределяются между файлами файловой группы поочерёдно. В конфигурации по умолчанию, SQL Server распределяет один экстент для файла и переходит к следующему в цепочке файлу. Можно увеличить число распределяемых за раз экстентов в 64 раза, для этого необходимо включить параметр запуска сервера -E.

Таблица 7: Эффект от использования флага -E

Поскольку каждый экстент занимает 64 Кб, это будет минимальный размер записи в файл во время массовой загрузки. На первый взгляд, можно было бы ожидать последовательный поток блоков по 64 Кб, записываемых на каждый LUN. Однако, реальное положение вещей несколько лучше этого предположения.
SQL Server использует штатную оптимизацию ввода-вывода Windows, которая склеивает (scatter/gather) блоки. Такая оптимизация используется при обслуживании нескольких запросов ввода-вывода, которые могут группироваться, чтобы сформировать один, большой блок запросов. Например, четыре запроса ввода-вывода по 64 Кб могут быть собраны в один запрос на 256 Кб. Поскольку исполнение одного большого запроса ввода-вывода происходит быстрее, чем нескольких маленьких, использование scatter/gather повышает производительность операций записи. При больших нагрузках массовых операций это способствует повышению производительности, и если такие возможности удастся задействовать, то средства мониторинга будут показывать использование запросов ввода-вывода размером до 256 Кб.
Чем больше файлов в файловой группе, тем больше времени понадобится SQL Server чтобы пробежаться по всем файлам и "возвратиться" к первому файлу для записи. Из-за этого, операция scatter/gather может решить сбросить на диск буфер ввода-вывода ещё до того, как SQL Server заполнит этот буфер до его лимита в 256 Кб. Наблюдая за счетчиком производительности "Средний размер одной записи на диск (байт)" (Logical Disk / Avg. Disk Bytes / Write), можно определить, как хорошо scatter/gather заполняет буфер записи.
Когда в файловой группе немного файлов, вероятность того, что scatter/gather соберёт большие запросы ввода-вывода, повышается. Запуск SQL Server с флагом -E также будет способствовать укрупнению размеров блока записи.
Для достижения мирового рекорда ETL, число файлов в файловой группе было небольшим, и выбиралось из соображений получения в результате работы scatter/gather и -E практически равных 256 Кб блоков ввода-вывода.

Однако не следует забывать, что слишком маленькое число файлов в файловой группе может привести к конкуренции PFS, которая была описана выше.

Оптимизация раскладки файлов по логическим дискам

Во время массовой загрузки, каждый файл базы данных получает последовательный поток запросов ввода-вывода на запись, с размерами блока между 64 Кб и 256 Кб.
Как было показано в главе "Источник данных", оптимизация подсистемы ввода-вывода для получения максимально последовательной рабочей нагрузки может дать существенный выигрыш в производительности массовой загрузки. Тут мы снова должны рассмотреть уже описанные выше две крайности: JBOD против SAME. Что лучше: иметь один большой LUN или несколько, логических дисков меньшего размера?
Как правило, если положить один файл базы данных на один LUN, это обеспечивает самую высокую производительность операций ввода-вывода. Каждый такой LUN может быть сформирован из нескольких дисков, собранных в RAID. В отличие от оптимизации чтения, где каждый файл с данными для массовой загрузки нуждается в собственном LUN, есть смысл положиться на SQL Server, который направит поток данных массовой загрузки в несколько получающих данные таблиц, преобразовав его в последовательный вид для записи в один и тот же файл. Поскольку SQL Server во время массовой загрузки, пишет в файл последовательно, нет необходимости закреплять по одному LUN на каждый поток массовой операции и оптимизировать этим её производительность. Это означает, что можно создать небольшое число больших LUN на специализированных для этого шпинделях. Каждый LUN должен обслуживать один файл базы данных - то есть нужно обеспечить соотношение LUN к файлам базы данных близкое 1:1. Конечно, если SAN или кэш контроллера могут сгладить негативные последствия пересечения ввода-вывода, можно создавать более одного файла на одном LUN. До развертывания системы стоит проверить, сколько последовательных потоков записи будет обращено к одному и тому же LUN в используемой для этих целей подсистеме ввода-вывода.
Представленные выше соображения являются аргументами в пользу конфигурации SAME - один большой LUN для каждой файловой группы базы данных. Однако, как было упомянуто ранее в главе о конкуренции PFS, слишком маленькое число файлов (а значит и число LUN, потому что нужно сохранить соотношение 1:1) может вызвать проблемы с распределением страниц PFS. Поэтому, для файловой группы можете потребоваться использовать больше одного файла на каждом LUN. Например, в упомянутом уже проекте по мировому рекорду ETL, мы использовали по 4 файла базы данных на одном LUN компьютера, куда была направлена загрузка.

Описание проекта: Мировой Рекорд ETL

В рамках проекта по Мировому Рекорду ETL, 56 задач BULK INSERT исполнялись параллельно со средней скоростью 600 Мб в секунду и с пиками до 850 Мб в секунду.
Сервер базы данных SQL Server 2008:

• Unisys ES7000/one Enterprise Server with 32 Dual core 3Ghz Intel 7140M CPUs
• Всего 64 ядра
• 8 x 4Gbit Emulex HBAs
• 8 x 1Gbit Intel Pro /1000MT Network cards
• 256 GB RAM (для операций BULK INSERT использовалось только 30Гб)

Оптимальное размещение файла SQL Server в этих условиях было таким:

• Файлы данных SQL Server были расположены на EMC Clariion CX3-80 SAN с общим числом шпинделей 165.

  • Были "нарезаны" 16 Мета-LUN. Каждый Мета-LUN был собран из 8 шпинделей как показано на рисунке 19

    
    Рисунок 18: Мета-LUN
• 133Гб/4Гбит шпиндели с числом оборотов в минуту 15К.
• 56 файлов данных, по 4 файла данных на каждом LUN.
  
  • Журнал SQL Server: single4x (1+1R1) Мета-LUN

Размещение файлов для чтения:

• 2 x EMC Clariion CX600 SAN
  
  • 10 LUN в RAID5, по 5 на каждом CX600
  • 7 x 36Гб шпинделей с числом оборотов в минуту 15К на один LUN
  • На каждом LUN лежали 5 или 6 текстовых файлов.

Для получения более подробной информации, см. следующие материалы:

• ETL World Records
• Unisys ES7000 Enterprise servers and BI solutions

Дополнительные ссылки на статьи о вводе-выводе

Для получения более подробной информации о вводе-выводе, изучите эти материалы:

• 10 рекомендаций по настройке хранилищ данных
• Страница загрузки SQLIO
• Статья KB: "How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem"
• SQL Server Storage Engine Team Blog
• Основы I/O в SQL Server 2000 и Основы ввода-вывода Microsoft SQL Server. Часть 2
• Predeployment I/O Best Practices
• Disk Subsystem Performance Analysis for Windows

Заключение

Если время исполнения задачи пакетной загрузки данных выходит за отведённые рамки, или если нужно достичь максимальной производительности загрузки большого объёма данных, эта статья предоставляет руководство по налаживанию мониторинга и оптимизации производительности подобных задач.
В статье представлена информация о том, как выбрать метод массовой загрузки, чтобы этот метод лучше всего подходил для вашей задачи. Массовая загрузка лежит в основе некоторых распространённых задач. Описанные здесь решения помогают выбрать правильный сценарий, и помочь вам подступиться к решению и поддержке задачи массовой загрузки данных.
Для рабочих нагрузок, которые характеризуются высокой нагрузкой, исключение "узких мест" является ключом для обеспечения наилучшей производительности. В главе "Оптимизация массовой загрузки данных" были представлены рекомендации по оптимизации конфигурации SQL Server для повышения производительности операций массовой загрузки. Там же можно найти соответствующие ссылки на дополнительные материалы по этой теме.
Совместное использование SQL Server и Integration Services позволяет организовать высокопроизводительную загрузку данных. Во время работ по проекту Мирового Рекорда ETL использовались руководства из настоящей статьи. После выполнения описанных здесь мер оптимизации, были втрое превышены показатели "коробочного" варианта установки. Кроме того, была достигнута близкая к линейной масштабируемости в пределах от 1 до 64 параллельных операций массовой загрузки на 64-х ядерной системе Unisys ES7000/one, которой управляла операционная система Windows Server ® 2008 Datacenter Edition.
Для получения более подробной информации:

http://www.microsoft.com/sqlserver/: SQL Server Web site
http://sqlcat.com/: SQL Customer Advisory Team Web site
http://technet.microsoft.com/ru-ru/sqlserver/: SQL Server TechCenter
http://msdn.microsoft.com/ru-ru/sqlserver/: SQL Server DevCenter

Эта статья помогла Вам? Пожалуйста, сообщите нам о ваших впечатлениях. Оцените по пятибалльной системе качество этой статьи, и поясните причины, побудившие поставить выбранную вами оценку? Например, причины могут быть такие:

Вы поставили отличную оценку из-за наличия хороших примеров, качественных снимков экрана, понятного изложения, или у вас были иные причины?
Ваша оценка оказалась низкой из-за плохих примеров, неразборчивых снимков с экрана, или путаного изложения материала?

Обратная связь с читателем поможет нам повысить качество технических статей, которые мы предлагаем для вашего ознакомления.

Обратная связь

Пример настройки Soft-NUMA

Сегодня получили широкое распространение многоядерные системы. Персональные компьютеры с четырьмя ядрами уже не редкость. Т.о. счастливые обладатели подобных многоядерных систем могут на практическом примере апробировать Soft-NUMA и как можно привязать к Soft-NUMA узлу порт сетевого протокола TCP/IP.
Для этого будем использовать компьютер х86 с четырьмя логическими процессорами. Процессоры в системе нумеруются с нуля до трёх. Воспользовавшись программой SQL Server Management Studio, в свойствах сервера, на закладке Processors, отключается привязка к первому процессору (CPU0) для обоих типов привязки: Affinity и I/O Affinity. Это делается для того, чтобы оставить один процессор операционной системе для привязки к нему других приложений (неравён час, прожорливость SQL Server помешает вам насладиться представленным тут примером). Как вы наверняка знаете, другие процессы можно привязывать к процессорам с помощью Диспетчера Задач Windows. Таким образом, можно снизить влияние на тестируемые процессоры нагрузки от активного во время тестирования программного окружения. Для двух следующих процессоров (CPU1 и CPU2) отменим I/O Affinity, оставив для них только привязку Affinity. Это необходимо, чтобы не совмещать обслуживание процессорами системных дисковых операций с обслуживанием сетевых запросов. И последний процессор (CPU3) мы наоборот замаскируем для Affinity и оставим ему привязку только для I/O Affinity. Этому процессору будет отведена роль обслуживания системного процесса отложенной записи. Нет особых причин, которые меня побудили включить эту настройку в систему, просто хотелось, чтобы вы пощупали и этот параметр глобальной конфигурации сервера.

Внимание! Этот пример не является примером хорошей практике или какой-либо рекомендацией к подражанию в промышленных системах. Целью этого примера является только очень упрощённая демонстрация возможностей Soft-NUMA, которую можно воспроизвести в домашних условиях.

Привязку процессоров и отложенной записи можно выполнить и с помощью системной хранимой процедуры sp_configure, как это показано ниже:

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'affinity mask', 6;
EXEC sp_configure 'affinity I/O mask', 8;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE;
GO

После этого, поскольку было выполнено изменение параметра глобальной конфигурации affinity I/O mask, чтобы все изменения вступили в силу, потребуется перезапуск службы SQL Server.
В данном тестовом примере создаётся два программных узла, каждый должен содержать по одному логическому процессору, первый - CPU1, а второй - CPU2. Каждому из программных узлов будет присвоен свой порт TCP, первому - 20001, а второму - 20002. Для чистоты эксперимента, отключим все используемые SQL Server протоколы, кроме TCP/IP. Это можно сделать, воспользовавшись поставляемой в дистрибутиве утилитой SQL Server Configuration Manager, в разделе SQL Server 200х Network Configuration, если перейти на узел "Protocols for MSSQLSERVER". В правой части появившегося окна будет представлен список выбранных для SQL Server протоколов, и по правой кнопке для них доступна команда отключения или подключения каждого из протоколов.

ВНИМАНИЕ! Для того чтобы привязать порты к процессорам, необходимо вручную выполнить изменения системного реестра операционной системы. Вам необходимо помнить, что ошибочные действия с реестром могут привести к возникновению проблем в работе сервера или его компонент. Прежде, чем приступать к изменениям в реестре, удостоверьтесь, что Вы имеете актуальную копию состояния системы. Для обеспечения возможности быстрого отката неверных или неудачных изменений, сохраните изменяемые ключи или целые ветви реестра в экспортируемых файлах. Все операции с системным реестром Вы делает на свой страх и риск, и не рекомендуются вносить изменения вручную, особенно на промышленных системах без глубокого предварительного тестирования. Кроме того, следует учитывать, что системы x86 и x64 используют разные местоположения веток реестра и пути размещения файлов приложений и служб.

Первым шагом для достижения этой цели является создание Soft-NUMA узлов. Для этого, нужно в системном реестре добавить следующую ветку ключей:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node0]
"CPUMask"=dword:00000002
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node1]
"CPUMask"=dword:00000004

Обратите внимание, что потребуется создать новый подраздел системного реестра NodeConfiguration, в котором описываются программные узлы. Node0 - это подраздел первого узла, в котором указывается ключ CPUMask с шестнадцатеричным значением, соответствующим маске физических процессоров, задействованных для этого узла. В нашем примере заданное значение dword:00000002 соответствует выбору второго физического процессора - CPU1. Подраздел Node1 содержит маску процессоров для второго программного узла. Эта маска соответствует выбору третьего процессора - CPU2.
Таким образом, мы определили для SQL Server два программных узла, и в каждый входит по одному процессору. Задаваемый маской программного узла набор объединяемых процессоров может включать несколько процессоров в произвольном, определяемом маской порядке (с учётом изложенных выше ограничений). Если бы нам потребовалось включить в состав первого программного узла ещё и первый физический процессор, тогда маска должна была бы получить значение dword:00000003, и нам бы ещё потребовалось добавить привязку Affinity для CPU0, от которой мы отказались на подготовительном шаге нашего примера.
Планирование обслуживания сетевой нагрузки процессорами в рамках такого программного узла осуществляется циклическим выбором входящих в программный узел процессоров.
Следующим шагом, является назначение портов программным узлам. Для этого, необходимо откорректировать значение параметров TCP Port, доступные на закладке "IP Addresses" свойств протокола TCP/IP, в узле "Protocols for MSSQLSERVER" утилиты "SQL Server Configuration Manager". В нашем примере, необходимые изменения вносятся непосредственно в системный реестр, путём редактирования тех ключей, в которых SQL Server хранит параметры сетевых протоколов. Ниже представлено содержимое REG - файла, в котором установлены все необходимые для примера параметры протокола TCP/IP.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp]
"Enabled"=dword:00000001
"ListenOnAllIPs"=dword:00000001
"NoDelay"=dword:00000000
"KeepAlive"=dword:00007530
"DisplayName"="TCP/IP"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"="20001[0x1], 20002[0x2]"
"TcpDynamicPorts"=""
"DisplayName"="Any IP Address"

Здесь представлена ветвь системного реестра для подраздела TCP сетевой библиотеки. Как видно, протокол TCP разрешён, и прослушивание будет вестись по всем IP адресам. Во вложенном подразделе IPAll заданы значения портов, используемых экземпляром SQL Server. Эти порты прописаны в строковом ключе TcpPort.
Вы, наверное, уже обратили внимание, что справа от каждого из номеров портов в квадратных скобках указана маска, которая соответствует номеру (начинающиеся не с нуля, а с единицы) Soft-NUMA узла, определённого нами на предыдущем шаге. Маску можно указывать в десятичном или шестнадцатеричном формате, в нашем примере, формат шестнадцатеричный. Таким образом, поступающие на порт 20001 сетевые запросы пользователей будут исполняться на первом процессорном узле (у нас это процессор CPU1), а запросы к порту 20002 будут обслуживаться на втором программном узле (CPU2).
Чтобы наши изменения в системном реестре были подхвачены сервером, необходимо перезапустить службу SQL Server.
Дополнительную информацию о привязке портов сетевого интерфейса к узлам Soft-NUMA можно получить в электронной документации Microsoft SQL Server Books Online: "Как сопоставить порты TCP/IP порт с узлами NUMA".
Для того чтобы убедиться в том, что всё так и будет происходить, несложно создать сравнительно большую рабочую нагрузку на оба порта. В этом примере, мы будем исполнять на каждом из портов довольно простую, но ресурсоёмкую команду по переиндексации всех таблиц тестовой базы данных AdventureWorks, которую можно взять по этой ссылке: http://msftdbprodsamples.codeplex.com. Ниже показан текст запроса, содержащего эту команду.

USE AdventureWorks;
GO
EXEC sp_MSforeachtable "DBCC DBREINDEX ('?')";
GO

Для того чтобы направить этот запрос на заданный порт, создаём два псевдонима нашего SQL Server, которые будут отличаться только портами TCP. Псевдонимы также можно создавать с помощью утилиты SQL Server Configuration Manager, перейдя к разделу псевдонимов в узле SQL Native Client Configuration. Поскольку псевдонимы также хранятся в системном реестре, давайте посмотрим, как они там могут выглядеть:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"Node1"="DBMSSOCN,SQLRU,20001"
"Node2"="DBMSSOCN,SQLRU,20002"

Из представленных выше ключей реестра видно, что созданы два псевдонима, каждый из которых подключается по имени к серверу SQLRU, но для каждого из них для протокола TCP задан свой порт. Для псевдонима Node1 задан порт TCP 20001. А для псевдонима Node2 задан порт TCP 20002.
Теперь запустим программу SQL Server Management Studio, зарегистрируем там оба наших серверных псевдонима и, создав новое окно T-SQL запроса с подключением к псевдониму сервера Node1, введём в нём сценарий представленной выше команды. Чтобы наблюдать утилизацию процессоров во время исполнения запроса, в нашем случае удобно воспользоваться системной программой из комплекта поставки операционной системы - Диспетчер Задач.
Запустив исполнение запроса на переиндексацию всех таблиц тестовой базы данных AdventureWorks, и обращаясь к серверу через псевдоним Node1, автор наблюдал следующую картину:

Рис. 1. Подача нагрузки на первый программный узел

Как видно на Рисунке 1, основная доля рабочей нагрузки попала на второй процессор, чего мы и добивались. Теперь, давайте запустим эту же рабочую нагрузку через псевдоним Node2. В этом случае, закладка Быстродействие в оснастке Диспетчер Задач во время исполнения запроса приобретёт вид, который представлен на следующем рисунке:

Рис. 2. Подача нагрузки на второй программный узел

Из рисунка 2 видно, что рабочая нагрузка переместилась на третий процессор как мы и планировали. В итоге, мы получили такую схему привязки, что в зависимости от избираемого клиентским подключением порта, создаваемая им рабочая нагрузка может посылаться на один из двух процессоров, доступных в системе для клиентских подключений.
В случае NUMA, привязка портов сетевых интерфейсов осуществляется так же, как и для SMP серверов, только может понадобиться добавить ключи реестра, определяющие протоколы сетевых интерфейсов для нескольких сетевых адаптеров. В нашем примере, в варианте использования одного адаптера, понадобились бы следующие ключи системного реестра Windows:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IP1]
"Enabled"=dword:00000001
"Active"=dword:00000001
"TcpPort"="20001[0x1]"
"TcpDynamicPorts"=""
"DisplayName"="Specific IP Address"
"IpAddress"="127.0.0.1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IP2]
"Enabled"=dword:00000001
"Active"=dword:00000001
"TcpPort"="20002[0x2]"
"TcpDynamicPorts"=""
"DisplayName"="Specific IP Address"
"IpAddress"="127.0.0.1"

На представленных выше рисунках 1 и 2, ваш интерес, возможно, привлекла некоторая активность, которая наблюдается в виде загрузки узла, на который по идее не должна была поступать никакая нагрузка. Давайте попробуем разобраться в её природе.
Первое предположение, которое приходит в голову, что процесс повторного создания индексов может быть распараллелен и нагрузка распределяется на нескольких процессоров. Другая гипотеза, это выполнение ресурсоёмких сортировок с промежуточной материализацией в tempdb, которая выполняется в другом потоке.
Системная процедура sp_MSforeachtable пересоздаёт индексы таблиц базы данных последовательно и трудно предугадать, какие из этих индексов будут пересоздаваться с использованием распараллеливания или потребуют промежуточной материализации. Поскольку мы наблюдаем активность на программном узле, который не нагружался запросом, можно предположить, что с помощью программных узлов можно указать только те группы процессоров, которые должны быть нагружены в первую очередь. Получается, что если для сервера будет существовать возможность создания такого числа потоков, которое превышает число процессоров выбранного для запроса программного узла, то нагрузка будет размещена и на процессоры, которые не входят в число процессоров выбранного программного узла. В общем случае, вывод такой, что наш тестовый сценарий не очень "чисто" демонстрирует возможности Soft-NUMA.
Из документации следует, что механизмы планирования задач операционной системы и SQL Server устроены таким образом, что планирование потоков не привязывается жёстко к схеме процессорных узлов. Если в системе есть свободные процессоры, и не наложено никаких ограничений на число потоков, обслуживающих запрос, то нет препятствий задействовать под подаваемую на программный или NUMA-узел нагрузку столько процессоров, сколько доступно для экземпляра SQL Server.
Имея в виду вышесказанное, давайте немного изменим условия нашего предыдущего примера, преследуя цель более наглядно и приближённо к реальным задачам продемонстрировать работу Soft-NUMA. Мы будем посылать определённым для примера псевдонимам сервера такую нагрузку, которая больше похожа на реальную работу пользователей. Как вы помните, используемые в примерах псевдонимы отличаются только прослушиваемыми сервером баз данных портами, которые привязаны к заданным программным узлам. Нам потребуется нагрузка, которая лучше поддаётся распараллеливанию, чем использованная нами ранее переиндексация таблиц.
В следующей демонстрации, что бы получить распараллеливаемую нагрузку, на тестовом компьютере был развернут стандартный набор инструментов и шаблонов эталонного теста TPC-H. Для простоты, размер исходной информации базы данных был выбран в один гигабайт. Спецификацию и подробное описание теста TPC-H, базы данных и запросов можно найти на сайте tpc.org. Была создана и проверена тестовая база и необходимый набор запросов. Наиболее удобным для демонстрации представляется запрос Q13, исполнение которого не занимало на сервере много времени, хорошо утилизировало процессоры и требовало распараллеливания задач. Все большие таблицы и индексы базы данных были размещены в отдельных файловых группах, каждая из которых состояла из нескольких файлов.
Поскольку, в нашем примере для каждого программного узла выделялось по одному процессору, то чтобы не было перехлёста рабочей нагрузки на другие узлы, в глобальных параметрах конфигурации SQL Server были выполнены следующие изменения:

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE;
GO<

Предложенный Вашему вниманию сценарий включает режим установки расширенного набора глобальных параметров конфигурации SQL Server и устанавливает предельное число используемых для исполнения параллельного плана запроса процессоров в единицу. Этим фактически было отменено распараллеливание любых запросов.
Выполнено это было именно для того, чтобы посылаемые на программные узлы запросы исполнялись в одном потоке и, как следствие, эксплуатировали только один процессор, на основе которого был определён программный узел.
На следующем рисунке представлена диаграмма утилизации второго процессора, который составляет по условиям нашего теста первый программный узел.

Рис. 3. Подача нагрузки на первый программный узел

Как видно из рисунка 3, уже не наблюдается активности на других процессорах, как это было в варианте запроса на переиндексацию таблиц. Используя абстракцию процессорного узла и ограничение MAXDOP = 1, мы смогли локализовать нагрузку только на заданном по условиям теста процессоре. В реальных условиях уровень максимальный уровень параллелизма, который исходит из требований бизнеса, может стать одним из определяющих факторов выбора числа ядер в одном узле.
Если запрос Q13 направить посредством соответствующего псевдонима на второй программный узел, мы увидим ожидаемую в этом случае картину, которая представлена на рисунке 4.

Рис. 4. Подача нагрузки на второй программный узел

Если же немного изменить условия теста и установить MAXDOP = 2, то при отправке запроса на порт TCP/IP:20001, мы увидим, что нагрузка распараллелится на всех процессорах, выделенных для используемого в тесте экземпляра SQL Server. Т.к. всего у нас два логических процессора, загружены будут оба Soft-NUMA узла, как это показано на рисунке 5.

Рис. 5. Влияние MAXDOP = 2

Правила, по которым нужно настраивать параметры глобальной конфигурации для SMP сервера с программными узлами фактически такие же, как для неадаптированных к NUMA приложений. Если целью является исключение передачи части рабочей нагрузки одного Soft-NUMA узла на другие узлы, то SQL Server должен иметь такую конфигурацию, которая не позволяла бы создавать больше потоков, чем количество процессоров, которое определено для одного программного узла.
Одним из полезных свойств описанного в последнем примере способа управления планированием нагрузки является то, что можно разнести на разные процессоры одного экземпляра SQL Server запросы от разных клиентов в сети. Это может дать заметный выигрыш в производительности экземпляра, если из-за негативного влияния нагрузок клиентов друг на друга нежелательно обслуживать их на одних и тех же ресурсах. Негативное влияние может проявляться в виде очень долгого использования процессора одним из потоков или конкуренцией исполняемых на одном узле потоков за локальные ресурсы узла. SQL Server предоставляет в распоряжение администратора баз данных средства управления планированием потоков. С помощью этих средств администратор может существенно снизить подобное негативное влияние потоков друг на друга.
Кроме того, продемонстрированные в последних тестах результаты позволяют рекомендовать подобное управление планированием потоков для приложений, код которых недоступен для модификации собственными силами. Этот подход применим, если требуется балансировка порождаемой приложениями нагрузки в рамках одного экземпляра SQL Server, а возможности реализовать это на стороне клиента нет. Все изменения, которые потребуется внести администратору - это определить в системном реестре Soft-NUMA узлы, привязать к ним порты сетевых интерфейсов, создать необходимые псевдонимы, а потом прописать соответствующие строки подключения в конфигурации приложения. В итоге, используя новые возможности планирования потоков, можно выделить разные группы процессоров для клиентов, которые посылают серверу "тяжёлые" аналитические запросы, и для тех клиентов, которые посылают серверу короткие транзакции или выборки. Выполнив необходимые для этого настройки, можно практически свести к минимуму возможное негативное влияние таких разнотипных запросов к одной и той же базе данных. Дополнительные возможности регулирования нагрузки может дать комбинирование средств и возможностей Soft-NUMA с возможностями регулятора ресурсов SQL Server 2008.
Сама возможность балансировки нагрузки между процессорами одного экземпляра SQL Server позволяет экономить лицензии и не приобретать дополнительные сервера только для того, чтобы на одном сервере балансировать нагрузку между процессорами, т.е. фактически выделяя и закрепляя ресурсы за приложениями или группами приложений. В предыдущих версиях SQL Server это достигалось только за счёт установки дополнительных именованных экземпляров сервера баз данных, а потом, процессоры распределялись между установленными экземплярами (что можно было делать динамически или можно было задать в глобальной конфигурации экземпляров жёсткую привязку процессоров экземплярам).
Другие сценарии привязки портов к процессорным узлам можно найти в электронной документации Microsoft SQL Server Books Online: "Сценарии NUMA".

Обновите свои новостные каналы лентами из SQL Server BLOGROLL 2010

SQL Server BLOGROLL 2010

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

ЗАКАЗАТЬ

Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700

Пример статьи из сборника:

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

Автор Александр Юрьевич Гладченко

Статья написана по мотивам технического документа Майкрософт: "SQL Server Replication: Providing High Availability using Database Mirroring" и описания в электронной документации SQL Server 2008 Books Online (далее BOL): "Репликация и зеркальное отображение базы данных".
В этой статье мы рассмотрим новые возможности обеспечения высокой доступности тиражируемых данных, используя для этого Репликацию транзакций, Доставку журналов и зеркальные копии баз данных.
Основанная на Репликации транзакций распределённая система хранения данных может обеспечить высокую устойчивость к отказам серверов баз данных. Подобные решения позволяют достичь высокой степени доступности, за счёт поддержки избыточных копий данных. Кроме Репликации, избыточность на уровне баз данных способны обеспечить несколько механизмов SQL Server 2008. Это такие возможности, как резервное копирование с последующим восстановлением, Доставка журналов и Зеркальное отображение базы данных. Причём, Зеркальное отображение является единственным механизмом, который поддерживает точную копию защищаемой базы данных практически в реальном масштабе времени, и гарантирует отсутствие потерь данных.
В этой статье на примерах мы посмотрим, как можно использовать Зеркальное отображение реплицируемой базы данных для повышения её доступности. Мы рассмотрим как Репликация и Зеркальное отображение влияют друг на друга, а также, как Зеркальное отображение совместимо с Доставкой журналов и как Доставка журналов совместима с Репликацией. Кроме того, в этой статье мы коснёмся возможностей использования для первоначальной синхронизации баз данных механизмов Доставки журналов, и вкратце рассмотрим принципы работы инициализации подписчика, основанной на логических номерах виртуальных журналов (LSN), которая позволяет сократить время восстановления после отказа при наличии зеркальной копии базы данных Подписчика.

Совместимость Доставки журналов с Зеркальным отображением и Репликацией

В настоящей статье мы не ставим целью полностью раскрыть тему совмещения Доставки журналов с Зеркальным отображением баз данных и Репликацией. Эта тема достаточно подробно раскрыта в BOL. Здесь мы рассмотрим только те аспекты Доставки журналов, которые нам пригодятся для первоначальной синхронизации основной базы данных Зеркального отображения и баз данных Подписчиков в топологии репликации. Коротко будет сказано и о том, как можно использовать Доставку журналов в целях резервного копирования участвующих в Репликации баз данных.
Целью Доставки журналов SQL Server 2008 является обеспечение автоматической синхронизации баз данных, которая осуществляется путём резервного копирования журналов транзакций в базе данных Источника, и последующей доставке и восстановление копий журнала транзакций в базе данных Получателя. Сервер, который обслуживает базу данных Получателя, выступает в роли резервного сервера или сервера отчётов. Сервер отчётов предоставляет возможность обработки запросов пользователей на чтение данных. Одна база данных Источника может синхронизироваться с одной или с несколькими базами данных Получателей. Доставка журналов применима к тем базам данных, которые используют полную модель восстановления или модель восстановления с неполным протоколированием.
Давайте договоримся, что сервер, первоначально обслуживающий базу данных Источника, будем называть сервером источника (термин "основной сервер" был бы удобнее, но он используется в Зеркальном отображении, и мы его не станем использовать, чтобы не было путаницы), а сервер, первоначально обслуживающий базу данных Получателя, называть резервным сервером. После настройки, сервер источника обслуживает базу данных Источника, но может сменить роль и обслуживать базу данных Получателя. Изменение роли приводит к одновременному изменению настроек на всех участвующих в Доставке журналов серверах, поскольку сервер источника может быть только один.
В Доставке журналов предусмотрены средства мониторинга и оповещения обо всех штатных и нештатных состояниях участников процесса Доставки журналов. Начиная с SQL Server 2008, поддерживается сжатие резервных копий, и такие копии можно использовать для Доставки журналов. Копии журналов создаются и доставляются с помощью заданий автоматизации службы SQL Server Agent, всего таких заданий четыре: задание резервного копирования Источника, задание копирования файлов Получателю, задание восстановления копий на Получателе и задание рассылки предупреждений.
Формат хранения данных на диске для SQL Server x64 и x86 одинаков, т.е. в Доставке журналов могут участвовать сервера баз данных под управлением 32-х и 64-х разрядных операционных систем. Доставка журналов поддерживается следующими редакциями SQL Server 2005: Enterprise Edition, Standard Edition и Workgroup Edition. Серверы, задействованные в доставке журналов, должны иметь одинаковые параметры сортировки, а базы данных источника и получателей могут использовать только модель полного восстановления (Full) или модель восстановления с неполным протоколированием (Bulk Logged).
Доставка журналов может быть средством тиражирования данных, однако, в этой статье мы рассмотрим возможность использования Доставки журналов для первоначальной синхронизации баз данных в Зеркальном отображении и Репликации.
Из BOL мы знаем, что Доставку журналов допустимо совмещать с Зеркальным отображением. В таком случае база данных Источника должна являться основной базой данных Зеркального отображения. Причём, Зеркальное отображение может быть настроено в любом из трёх режимов его работы. Базы данных Получателей не должны находиться на том же экземпляре сервера, что и база источника или на экземпляре Зеркального отображения базы Источника. Поскольку Зеркальное отображение позволяет делать резервные копии только базы данных основного сервера, использование в качестве базы данных Источника зеркальной копии основной базы невозможно. Настраивать Доставку журналов и Зеркальное отображение на основном сервере можно в любом порядке.
Допустимо совмещать Доставку журналов с Репликацией, и это справедливо для издаваемой базы данных, баз данных Подписок, и для базы данных Распространителя. Следует учитывать, что процесс репликации будет прерван в случае перехода на резервный сервер, поскольку агенты репликации не умеют правильно реагировать на изменение ролей серверов в Доставке журналов, и это приведёт к тому, что транзакции перестанут тиражироваться подписчикам. После того, как роль базы данных Источника вернётся в первоначальное состояние, репликация возобновляется, и все те транзакции, которые были скопированы Доставкой журналов с резервного сервера на сервер источник, реплицируются подписчикам. В случае безвозвратной потери базы данных Источника, достаточно переименовать Получателя, чтобы возобновить процесс репликации.
Первоначальная инициализация базы данных Получателя в процессе настройки Доставки журналов осуществляется путём восстановления полной резервной копии базы данных источника с параметрами NORECOVERY или STANDBY. Для организации доставки журналов необходимо создать сетевой ресурс, в котором будут создаваться резервные копии журналов транзакций и который будет доступен всем участвующим в Доставке журналов серверам. В нашем примере в качестве такого ресурса будет использоваться каталог сервера источника C:\MSSQL\LogShip\.
Существует возможность синхронизировать процесс доставки журналов с тиражированием данных в репликации транзакций. Такая синхронизация устанавливается с помощью системной хранимой процедуры sp_replicationdboption, которая позволяет для публикуемой базы данных и базы данных Распространителя установить опцию "sync with backup". Когда эта опция установлена для базы данных Распространителя, это гарантирует, что транзакции в журнале публикуемой базы данных не будут усечены до тех пор, пока не будет создана их резервная копия в базе данных Распространителя. Усечение журнала транзакций публикуемой базы данных откладывается до завершения резервного копирования усекаемых транзакций в базе данных Распространителя. Установка этой опции позволяет управлять точкой усечения базы данных Публикации. Новое значение вступает в силу после очередного запуска Агента чтения журнала или по истечении заданного параметром агента -MessageInterval интервала времени, если Агент чтения журнала работает в непрерывном режиме. За счёт того, что в базе данных распространителя не будет транзакций, которых нет в резервной копии публикуемой базы данных, можно не боятся рассогласованности издаваемой базы данных и базы данных Распространителя в случае восстановления издаваемой базы данных из резервной копии. Однако, следует помнить, что такой способ синхронизации добавляет задержку в процесс тиражирования транзакций и не гарантирует отсутствия потерь данных, в случае повреждения журнала транзакций. Кроме того, для Доставки журналов опция важна только для издаваемой базы данных.
В рамках этой статьи мы не будем рассматривать совместную работу Доставки журналов и Репликации. Доставка журналов нами будет использоваться только для целей первоначальной синхронизации баз данных подписчиков и Зеркального отображения базы данных Издателя. Поэтому сценарии включения Доставки журналов будут использоваться в примерах с демонстрацией включения Зеркального отображения и Репликации.
Дополнительную информацию о Доставке журналов можно получить в следующих статьях BOL:

• Развертывание доставки журналов
• Репликация и доставка журналов
• Зеркальное отображение баз данных и доставка журналов
• Резервное копирование и восстановление из копий реплицируемых баз данных
• Стратегии резервного копирования и восстановления из копии репликации моментальных снимков и репликации транзакций
Совместимость Зеркального отображения и Репликации

Не все базы данных в топологии Одноранговой репликации транзакций допускают Зеркальное отображение (зеркалирование). Например, это невозможно для базы данных Распространителя. В Репликации транзакций зеркалирование базы данных подписчика требует учёта некоторых ограничений, которых нет для издаваемой базы или в одноранговой топологии. Возможность использования в репликации зеркалирования зависит от того, насколько будут способны задействованные агенты репликации отрабатывать состояние отказа основной базы данных и автоматически переключаться на зеркальную копию. Так, например, ни один из соответствующих агентов репликации не может правильно отреагировать на отказ базы данных Распространителя и переключиться на работу с зеркальной копией этой базы данных. Для обеспечения высокой доступности Распространителя его следует вынести на выделенный компьютер и отдать его под управление отказоустойчивого кластера. Однако, те агенты репликации, которые соединяются с публикуемой базой данных, умеют переключаться на зеркальную копию, и в случае отказа способны подключиться автоматически, обеспечив непрерывность процесса репликации.
С подписчиками всё не так просто, как с издателем. К сожалению, ни один из работающих с базами на подписчике агентов репликации не предназначен для автоматического переключения в случае отказа. Самым естественным путём переключения потока данных репликации является удаление подписки и создание её заново.
Дополнительные сведения можно получить в следующей статье BOL: "Репликация и зеркальное отображение базы данных".

Влияние зеркалирования на работу Агента чтения журнала

Зеркалирование публикуемой базы данных влияет на поведение Агента чтения журнала, его состояние становится зависимым от состояния зеркалирования. Агент чтения журнала зеркалируемой базы данных будет копировать из журнала вначале записи тех транзакций, которые были до этого скопированы и завершены в журнале регистрации транзакций зеркальной базы данных (процесс, с помощью которого пишутся записи в журнал транзакций зеркальной базы данных, в документации называют hardening - закрепление). Т.е. реплицироваться будут только записи с таким LSN, который больше LSN последней закреплённой в журнале транзакций зеркала.
Это позволяет выставить из топологии основной сервер (зеркало доступно, но существуют такие записи в журнале транзакций, которые еще не были закреплены на зеркале) или изолировать его (когда зеркало недоступно). В обоих случаях, пока основной сервер работоспособен и его база данных доступна, любые изменения в его базе данных не будут реплицированы, пока соответствующие записи журнала транзакций не будут закреплены на зеркале.
Такое поведение добавляет задержки в поток репликации, и если произойдёт отказ зеркалирования, будет гарантировано, что записи в журнале Подписчика не обгонят фиксацию в основной базе данных.
Ели Агент чтения журнала вынужден ждать закрепления записей в журнале транзакций зеркальной базы данных, в хронологии работы Агента чтения журнала будут появляться уведомления следующего вида: "Replicated transactions are waiting for next Log backup or for mirroring partner to catch up".

Изменение поведения Агента чтения журнала при установке флага трассировки 1448

В редких случаях, эффект задержек из-за зеркалирования для логики работы использующих репликацию приложений может оказаться не приемлем. Для решения этой проблемы добавлен новый флаг трассировки 1448, который предписывает репликации продолжаться даже в тех случаях, когда основная база данных выставлена или изолирована. Обычно, Агента чтения журнала ждет, пока не будут закреплены записи в журнале регистрации транзакций зеркальной базы данных, после чего он копирует их в базу данных Распространителя. Когда сервер запущен с флагом трассировки 1448, это ожидание исключается, и Агента чтения журнала может сразу копировать изменения, независимо от состояния зеркалирования.
Этот флаг трассировки можно применять в SQL Server 2008, а для SQL Server 2005 он появился в составе Cumulative update package 2 для SQL Server 2005 Service Pack 2. Подробности о флаге трассировки №1448 можно узнать в статье базы знаний Майкрософт №937041.
Обратите внимание на потенциальную опасность исключения задержек тиражирования в репликации. Для приложений отсутствие задержек может иметь решающее значение, и это может послужить причиной установки флага трассировки №1448 при использовании зеркалирования с репликацией. Надо понимать, что существует вероятность того, что использование этого флага приведёт к проблемам в случае отказа Зеркального отображения. Эти проблемы будут рассмотрены ниже.

Влияние отказа зеркалирования на работу Агента чтения журнала

Когда происходит автоматическая или ручная отработка отказа Зеркального отображения, Агент чтения журнала должен автоматически соединиться с новым основным сервером и продолжить копировать транзакций (если параметр -PublisherFailoverPartner был установлен правильно, как это будет описано ниже). Есть два случая, когда этого может не произойти. Первый, это когда отказ не может быть отработан правильно, потому что зеркальный сервер по каким либо причинам не может стать основным сервером. Второй случай может произойти только тогда, когда включён флаг трассировки №1448, о котором только что упоминалось выше.
Когда Зеркальное отображение не работает в режиме высокой доступности или основной сервер был выставлен или изолирован, может оказаться, что на основном сервере есть завершённые транзакции, которых еще не были закреплены на зеркале. Если основная база данных станет недоступной, и произойдёт передача её роли зеркалу, может случиться потеря тех транзакций, которые были завершены на основном сервере, но еще не были закреплены на зеркале. Если к тому же включён флаг трассировки №1448, некоторые из завершённых транзакций основного сервера могут быть скопированы, но ещё не закреплены на зеркале. Это приведёт к тому, что состояние метаданных о тиражируемых транзакциях на Распространителе будет опережать реальное состояние транзакций на принявшем роль основной базы данных зеркале. Т.е. Агент чтения журнала уже передавал Распространителю некоторое количество транзакций. После обнаружения таких расхождений, Агент распространителя выдаст ошибку: "The process could not execute 'sp_repldone/sp_replcounters' on ' GLADCHENKO-TEST'". Кроме этого сообщения об ошибке, будут и детализирующие ситуацию сообщения, из которых можно узнать, какой номер виртуального журнала пытался считать Агент чтения журнала. Исправить положение можно запустив с помощью системной хранимой процедуры sp_replrestart принудительную синхронизацию метаданных Издателя и Распространителя. Однако, велика вероятность того, что из-за расхождений в метаданных часть не синхронизированных в результате отказа основного сервера транзакций уже может попасть в базы данных Подписчиков. Это приведёт к тому, что Агент распространителя тоже прекратит свою работу, выдав сообщение об ошибке синхронизации данных. Поскольку в результате сбоя синхронизации Распространитель просто повторит изменения, передаваемые не прошедшими синхронизацию транзакциями, для преодоления ошибки работы Агента распространителя достаточно сменить его профиль на стандартный профиль с именем: "Continue on data consistency errors".

Как влияет нарушение зеркалирования на работу Агента чтения журнала

Как уже отмечалось, в случае отказа Зеркального отображения Агент чтения журнала станет работать с журналом транзакций зеркала основной базы данных. Возможность переключения на сменивший свою роль новый основной сервер обеспечивается параметром запуска агента -PublisherFailoverPartner. Пока между обоими участниками Зеркального отображения действует партнёрство, т.е. они продолжают работать по схеме основная база - зеркало основной базы, в случае отказа основной базы данных Агент чтения журнала подключится к принявшей роль основной, зеркальной базе данных.
Если основной сервер будет недоступен очень долго, лучше всего удалить зеркалирование, что позволит избежать проблем с ростом журнала транзакций зеркальной базы данных. Если отказ приведёт к тому, что будет нарушено партнёрство основной и зеркальной баз данных, Агент чтения журнала будет продолжать работать с зеркалом, пока ситуация с партнёрством не восстановится или зеркальное отображение не будет удалено. Если потом агента перезапустить, он попробует соединяться с основной базой данных, которая больше не является издаваемой базой данных, что приведёт к сбою репликации. В хронологии работы Агента чтения журнала появятся сообщения такого типа: "User-specified agent parameter values: -Publisher GLADCHENKO-TEST'". Также будут более детальные сообщения об отказе подключения. Для того чтобы решить подобную проблему, и заставить Агент чтения журнала продолжить свою работу, создайте на Распространителе псевдоним действующего сервера издателя.

Настройка периода хранения Распространителя

По умолчанию на Распространителе значение периода хранения транзакций установлено в ноль. Это означает, что транзакции очищаются сразу же, как только они будут доставлены всем Подписчикам. При использовании Зеркального отображения, в момент, когда из-за отказа Агент распространителя переключается на работу с зеркальной копией базы данных, нужно чтобы в базе данных распространителя ещё оставались транзакции, которые уже доставлены всем Подписчикам. Такое случается, когда зеркалирование работает в высокопроизводительном режиме, т.е. передача транзакций зеркалу идёт асинхронно и состояние зеркала может сильно отставать от состояния основной базы данных. Тогда, при необходимости добавления нового Подписчика, которого нежелательно инициализировать, у Агента распространителя должна существовать возможность догрузить новому Подписчику те транзакции, которые на момент добавления подписчика ещё не успели закрепиться в зеркале. Другой такой случай может произойти при отказе сервера Издателя, когда данные в базе данных публикации больше не доступны, и это не позволяет корректно инициализировать новую базу данных подписчика. В таком случае, при отсутствии транзакций на Распространителе, может произойти потеря данных.
Чтобы избегать потери данных или длительного простоя в системе с зеркалированием базы данных подписчика, рекомендуется установить адекватный период задержки транзакций у распространителя.
В наших примерах мы будем использовать следующий вариант настройки параметра задержки распространения:

USE distribution
EXEC sp_changedistributiondb N'distribution', N'min_distretention', 3
EXEC sp_helpdistributiondb;
GO

Типы ручной синхронизации Подписчика

Репликация не поддерживает автоматический переход на зеркальное отображение подписанной на публикацию базы данных, т.е. хотя зеркалирование и возможно, Агент распространителя не сможет переключиться на зеркало после отказа основной базы данных Зеркального отображения. Работа Агента распространителя закончится ошибкой сразу, как только он поймёт, что не может подключиться к базе данных, являющейся основной в партнёрстве Зеркального отображения. Зеркалирование Подписчика можно настраивать как до, так и после подписки. Однако, если планируется инициализация Подписчика моментальным снимком Публикации, эффективнее будет настраивать Зеркальное отображение после инициализации базы данных Подписчика.
В случае отказа Зеркального отображения базы данных Подписчика, необходимо перенаправить Репликацию на зеркальный сервер, туда, где теперь располагается новая база данных Подписчика, на новом основном сервере. Хотя этот метод требует заново оформить подписку, полной её инициализации не потребуется, и это благодаря новому типу синхронизации SQL Server 2008.
Все методы ручной инициализации Подписчика основаны на том, что база данных приводится в согласованное с Издателем состояние, и это становится отправной точкой тиражирования последовавших за этим состоянием транзакций Издателя. После создания Подписки с ручной синхронизацией, Агент распространителя должен иметь информацию о том, какие транзакции нужно тиражировать, не потеряв при этом новые данные или изменения в имеющихся данных. В качестве такой отправной точки используется тот номер LSN, который применим к соответствующему методу инициализации. Например, в методе с указанием параметра "replication support only" определение LSN происходит автоматически. Когда же инициализация осуществляется восстановлением из резервной копии, LSN берётся из заголовка файла резервной копии. В случае инициализации по LSN, необходимый номер виртуального журнала задаётся пользователем. Этот номер нужно указывать аккуратно, т.к. относящиеся к нему транзакции должны присутствовать на этот момент в базе данных Распространителя. Вот почему так важна задержка транзакций у Распространителя, это очень помогает в случае отказа.

Примеры

Для демонстрации новых возможностей обеспечения доступности Репликации SQL Server 2008 нам потребуется сервер Распространитель, который в представленном ниже сценарии создания Распространителя называется GLADCHENKO-VHD:

-- Сценарий создания Распространителя
use master
exec sp_adddistributor @distributor = N'GLADCHENKO-VHD'
, @password = N'' -- безопасность репликации не тема этой статьи,
                  -- оставляем уязвимой
GO
exec sp_adddistributiondb
  @database = N'distribution'
, @data_folder = N'C:\MSSQL\DATA'
, @log_folder = N'C:\LOG\DATA'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties'
    and type = 'U '))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder'
    , 'user', 'dbo', 'table', 'UIProperties', null, null)))
    EXEC sp_updateextendedproperty
  N'SnapshotFolder'
    , N'C:\MSSQL\ReplData'
    , 'user'
    , dbo
    , 'table'
    , 'UIProperties'
else
    EXEC sp_addextendedproperty
  N'SnapshotFolder'
    , N'C:\MSSQL\ReplData'
    , 'user'
    , dbo
    , 'table'
    , 'UIProperties'
GO

exec sp_adddistpublisher
  @publisher = N'GLADCHENKO-VHD'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO

Зеркальное отображение издаваемой базы данных

Мы будем использовать сервер GLADCHENKO-TEST в качестве основного сервера в зеркале и сервера издателя в Репликации. Публиковаться будет база данных MIR, и она же будет иметь своё Зеркальное отображение. Публикация тоже будет называться MIR. Сервер, на котором будет размещаться зеркало, называется GLADCHENKO-VHD и этот же сервер является в нашей тестовой топологии репликации Распространителем. В качестве сервера Подписчика будет выступать экземпляр GLADCHENKO-A\SUB, на котором база данных подписки будет называться MIR. Хотя репликацию можно настроить с помощью SQL Server Management Studio, мы будем её настраивать с помощью системных процедур Transact-SQL.
Для простоты демонстрации, все учётные записи, от имени которых будут запускаться службы, и имена входа, в контексте безопасности которых будут выполняться работы на серверах, будут сведены к одной, доменной учётной записи пользователя AG@troika.ru. Этот пользователь является локальным администратором на всех серверах, и включён в серверную роль sysadmins всех указанных выше серверов баз данных.

Шаг 1. Создание Публикации

Публикуемая база данных MIR состоит из четырёх таблиц: PerformanceCounter, PerformanceSignature, PerformanceSignatureData и PerformanceSignatureHistory. На самом деле, эти таблицы тиражируются средствами репликации транзакций с сервера, на котором размещена база данных System Center Operations Manager 2007 SP1. Сделано это для того, чтобы эмулировать работу "живого" приложения, поскольку данные мониторинга инфраструктуры серверов поступают в эти таблицы постоянно и без длительных перерывов. Именно этот поток реплицируемой информации мы и будет реплицировать с сервера GLADCHENKO-TEST на сервер GLADCHENKO-A\SUB.
Для получения более подробной информации о настройке Распространителя, смотрите следующие темы в SQL Server 2008 Books Online:

• Как настроить публикацию и распространение (программирование репликации на языке Transact-SQL)
• Как настроить публикации и распространение (среда SQL Server Management Studio)

Ниже представлен сценарий создания Публикации.

Вначале, нужно разрешить на Распространителе ещё одного Издателя. Для этого на сервере GLADCHENKO-VHD выполните следующий сценарий:

exec sp_adddistpublisher
@publisher = N'GLADCHENKO-TEST'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO

После этого можно приступить к созданию Публикации, для чего на сервере GLADCHENKO-TEST нужно выполнить следующий сценарий:

use master
-- Указываем Распространителя
exec sp_adddistributor @distributor = N'GLADCHENKO-VHD'
, @password = N'*********' -- тут нужно указать заданный
                           -- на Распространителе пароль
-- В ответ должны получить подтверждение:
-- "You have updated the Publisher property 'active' successfully"
GO

use [MIR]
exec sp_replicationdboption @dbname = N'MIR'
, @optname = N'publish'
, @value = N'true'
GO

-- Добавляет Публикацию
exec sp_addpublication
  @publication = N'MIR'
, @description = N'Публикация БД MIR в репликации
транзакций, на сервере GLADCHENKO-TEST'
, @sync_method = N'native'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'false'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @ftp_login = N'anonymous'
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @autogen_sync_procs = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'true'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
, @p2p_conflictdetection = N'false'
, @p2p_originator_id = 1
-- В ответ должны получить сообщение:

-- Job 'GLADCHENKO-TEST-MIR-1' started successfully.

-- "Warning: The logreader agent job has been implicitly created
-- and will run under the SQL Server Agent Service Account"
GO

-- Создаём инициализирующий моментальный снимок
exec sp_addpublication_snapshot @publication = N'MIR'
, @frequency_type = 1
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 8
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
-- Добавляем наши таблицы в виде статей публикации
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceCounter'
, @source_owner = N'dbo'
, @source_object = N'PerformanceCounter'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceCounter'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceCounter'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceCounter'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceCounter'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignature'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignature'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignature'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignature'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignature'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignature'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignatureData'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignatureData'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignatureData'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureData'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureData'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureData'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignatureHistory'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignatureHistory'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignatureHistory'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureHistory'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureHistory'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureHistory'
GO
-- настраиваем и запускаем Агента чтения журнала
exec [MIR].sys.sp_addlogreader_agent
  @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
exec [MIR].sys.sp_addqreader_agent
@job_login = null
, @job_password = null
, @frompublisher = 1
GO
-- Формируем лист доступа к публикации (PAL)
-- Сюда нужно даобавить все задействованные агентами логины
exec sp_grant_publication_access
  @publication = N'MIR'
, @login = N'sa'
GO
exec sp_grant_publication_access
  @publication = N'MIR'
, @login = N'NT AUTHORITY\SYSTEM'
GO

Шаг 2. Настройка Доставки журналов для инициализации Подписчика

На этом шаге мы должны подготовить базу данных на сервере Подписчика, для того, чтобы она могла быть инициализирована из резервной копии. Поскольку поток транзакций в базе данных нашего Издателя не прерывается 24 часа в сутки, 7 дней в неделю и 365 дней в году, нам будет удобно задействовать для этого механизм Доставки журналов.
Для включения Доставки журналов нужно инициализировать базу данных Получателя путем восстановления полной резервной копии базы данных Источника. В нашем случае, резервным сервером, а заодно и сервером Подписчика будет компьютер GLADCHENKO-A\SUB.
Делаем полную копию издаваемой базы данных, для чего, на сервере GLADCHENKO-TEST выполняем следующий сценарий:

BACKUP DATABASE [MIR]
TO DISK = N'C:\MSSQL\BACKUP\MIR.bak'
WITH NOFORMAT, INIT
, NAME = N'Полная копия БД MIR'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Дожидаемся получения подтверждения успешности этой операции, в окне результатов появится приблизительно такой текст:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 18104 pages for database 'MIR', file 'MIR' on file 1.
100 percent processed.
Processed 3 pages for database 'MIR', file 'MIR_log' on file 1.
BACKUP DATABASE successfully processed 18107 pages in 4.394 seconds (32.193 MB/sec).

После того, как копия успешно создана, нужно её скопировать на сервер Источника. В нашем случае, нужно взять файл с компьютера GLADCHENKO-TEST из папки C:\MSSQL\BACKUP\MIR.bak и скопировать его на компьютер GLADCHENKO-A, в папку: C:\MSSQL\BACKUP\MIR.bak.
После того, как файл резервной копии окажется на сервере Получателя, нужно восстановить базу данных Получателя, что мы и сделаем на экземпляре GLADCHENKO-A\SUB, используя следующий сценарий:

RESTORE DATABASE [MIR]
FROM DISK = N'D:\MSSQL\BACKUP\MIR.bak'
WITH FILE = 1
, MOVE N'MIR' TO N'C:\MSSQL\DATA\MIR.mdf'
, MOVE N'MIR_log' TO N'D:\MSSQL\LOG\MIR_1.ldf'
, NOUNLOAD, REPLACE, STATS = 10, NORECOVERY
GO

После успешного восстановления базы данных, приступаем к настройке Доставки журналов между публикуемой базой данных на Издателе, и только что восстановленной базой на Подписчике. Подробное описание настройки Доставки журналов можно найти в статье электронной документации: "Как включить доставку журналов (Transact-SQL)". Создадим папку для обмена резервными копиями журналов транзакций: C:\MSSQL\LogShip. На эту папку у учётных записей служб должен быть полный доступ.
Далее, настраивает базу данных Источника вместе с заданием резервного копирования, а также записями локального и удаленного мониторов:

DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier

EXEC master.dbo.sp_add_log_shipping_primary_database
 @database = N'MIR'
,@backup_directory = N'C:\MSSQL\LogShip'
,@backup_share = N'\\GLADCHENKO-TEST\lsbackup'
,@backup_job_name = N'LSBackup_MIR'
,@backup_retention_period = 1440
,@backup_compression = 0
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
GO

Значения идентификаторов получились соответственно следующие:

@LS_BackupJobId = DDCD7E77-F045-4649-A9F1-CF8FE843C711
@LS_PrimaryId = 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F

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

c:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Backup 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F -server GLADCHENKO-TEST'

Создаём расписание для задания резервного копирования журналов транзакций базы данных MIR:

DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule
 @job_id=N'DDCD7E77-F045-4649-A9F1-CF8FE843C711'
,@name=N'1'
,@enabled=1
,@freq_type=4
,@freq_interval=1
,@freq_subday_type=4
,@freq_subday_interval=10
,@freq_relative_interval=0
,@freq_recurrence_factor=1
,@active_start_date=20081202
,@active_end_date=99991231
,@active_start_time=0
,@active_end_time=235959
,@schedule_id = @schedule_id OUTPUT
SELECT @schedule_id
GO

Это расписание предусматривает выполнение задания каждые 10 минут в течение суток.
Мы не планируем создавать полное решение Доставки журналов, наша задача только облегчить первоначальную инициализацию Подписчика. Однако, давайте полностью следовать упомянутой выше инструкции BOL по включению Доставки журналов.
Далее, нам нужно на сервере Источника задать базу данных Получателя:

EXEC master.dbo.sp_add_log_shipping_primary_secondary
 @primary_database = N'MIR'
,@secondary_server = N'GLADCHENKO-A\SUB'
,@secondary_database = N'MIR'
,@overwrite = 1
GO

После этого, на сервере Получателя (Подписчик, в нашем примере это компьютер GLADCHENKO-A\SUB) нужно запустить процедуру, которая создаст все необходимые задания по применению резервных копий на базе данных Получателя.

DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier

EXEC master.dbo.sp_add_log_shipping_secondary_primary
 @primary_server = N'GLADCHENKO-TEST'
,@primary_database = N'MIR'
,@backup_source_directory = N'\\GLADCHENKO-TEST\lsbackup'
,@backup_destination_directory = N'\\GLADCHENKO-A\D$\MSSQL\LogShip'
,@copy_job_name = N'LSCopy_GLADCHENKO-TEST_MIR'
,@restore_job_name = N'LSRestore_GLADCHENKO-TEST_MIR'
,@file_retention_period = 4320
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
GO

Получаем следующий набор идентификаторов:

@LS_Secondary__CopyJobId = 338E7DF6-F266-40CD-902E-86F424E12E6C
@LS_Secondary__RestoreJobId = 6C0FE890-5FC5-4BA7-BD89-EC13214514E8
@LS_Secondary__SecondaryId = DC7800CE-767C-4F2B-9A42-70DC5F807184

После этого, среди заданий экземпляра GLADCHENKO-A\SUB появятся два новых задания, для которых определены шаги, но ещё не настроены расписания их работы. Первое задание с именем: LSCopy_GLADCHENKO-TEST_MIR. У этого задания только один шаг и он выполняет следующий сценарий:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Copy DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

Эта инструкция копирует файлы резервных копий журналов транзакций с сервера Источника на сервер Получателя.
Добавить типовое расписание работы этого задания можно с помощью следующего сценария:

EXEC msdb.dbo.sp_add_schedule
 @schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20081203
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = '338E7DF6-F266-40CD-902E-86F424E12E6C'
,@schedule_id = @LS_SecondaryCopyJobScheduleID
GO

Второе задание называется LSRestore_GLADCHENKO-TEST_MIR и предназначено для восстановления скопированных с Источника резервных копий на базе данных Получателя. Единственный шаг этого задания выполняет следующую инструкцию:

С:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Restore DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

Для добавления этому заданию расписания запуска необходимо на сервере GLADCHENKO-A\SUB выполнить следующий сценарий:

EXEC msdb.dbo.sp_add_schedule
 @schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20081203
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
 @job_id = '6C0FE890-5FC5-4BA7-BD89-EC13214514E8'
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
GO

И теперь осталось только добавить в Доставку журналов базу данных Получателя, что можно сделать, выполнив следующий сценарий на сервере GLADCHENKO-A\SUB:

EXEC master.dbo.sp_add_log_shipping_secondary_database
 @secondary_database = N'MIR'
,@primary_server = N'GLADCHENKO-TEST'
,@primary_database = N'MIR'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
,@overwrite = 1
GO

Следующим действием в настройке Доставки журналов будет подготовка сервера мониторинга, в качестве которого будет выступать экземпляр GLADCHENKO-A. На этом экземпляре нужно выполнить следующий сценарий:

EXEC msdb.dbo.sp_processlogshippingmonitorprimary
 @mode = 1
,@primary_id = N'3346F94D-3DB6-4A56-BE99-DF2E60E0A25F'
,@primary_server = N' GLADCHENKO-TEST'
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@primary_database = N'MIR'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
GO

После этого, нужно включить те задания обслуживания Доставки журналов, которые были отмечены как выключенные. Это должно привести к тому, что Доставка журналов начнёт свою работу и будет поддерживать актуальность базы данных MIR на сервере GLADCHENKO-A\SUB в автоматическом режиме.
Теперь, когда придёт время создавать Подписку, достаточно будет ненадолго отключить клиентов от базы данных Издателя, дождаться или запустить вручную поочерёдно все задания Доставки журналов на Источнике и потом на Получателе, отключить все эти задания, и подготовить базу данных Получателя к настройке Подписки, выполнив этот сценарий:

RESTORE DATABASE MIR WITH RECOVERY
GO

Шаг 3. Настройка Подписки

После успешной инициализации базы данных Подписчика резервной копией, которую мы поддерживали в актуальном состоянии средствами Доставки журналов, можно приступать к подписке на Публикацию. Для этого, на сервере GLADCHENKO-TEST нужно с помощью представленного ниже сценария объявить сервер и базу данных Подписчика:

use [MIR]
exec sp_addsubscription @publication = N'MIR'
, @subscriber = N'GLADCHENKO-A\SUB'
, @destination_db = N'MIR'
, @sync_type = N'initialize with backup'
, @backupdevicetype = N'disk'
, @backupdevicename = 'LogShip\MIR_20081203195807.trn'
, @fileidhint = 1
, @subscription_type = N'pull'
, @update_mode = N'read only'
GO

Все последующие действия по оформлению Подписки нужно выполнять на сервере GLADCHENKO-A\SUB. Следующий сценарий создаёт Подписку по запросу:

use [MIR]
exec sp_addpullsubscription @publisher = N'GLADCHENKO-TEST'
, @publication = N'MIR'
, @publisher_db = N'MIR'
, @independent_agent = N'True'
, @subscription_type = N'pull'
, @description = N''
, @update_mode = N'read only'
, @immediate_sync = 1

И наконец, следующий сценарий нужно выполнить для создания задания запуска Агента распространителя, который будет доставлять изменения непосредственно Подписчику:

exec sp_addpullsubscription_agent @publisher = N'GLADCHENKO-TEST'
, @publisher_db = N'MIR'
, @publication = N'MIR'
, @distributor = N'GLADCHENKO-VHD'
, @distributor_security_mode = 1
, @distributor_login = N''
, @distributor_password = null
, @enabled_for_syncmgr = N'False'
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20081203
, @active_end_date = 99991231
, @alt_snapshot_folder = N''
, @working_directory = N''
, @use_ftp = N'False'
, @job_login = null
, @job_password = null
, @publication_type = 0
GO

Когда все необходимые действия по оформлению подписки завершены, на сервер GLADCHENKO-A\SUB можно полностью удалить Доставку журналов и позаботиться об удалении созданных там заданий Доставки журналов.

Шаг 4. Зеркалирование публикуемой базы данных

В репликации транзакций существует очень опасная точка отказа, это Издатель, об обеспечении высокой доступности которого раньше было трудно заботиться. Теперь, для повышения доступности Издателя, можно создать Зеркальное отображение его базы данных и обеспечить автоматическое переключение на зеркальную копию в случае отказа. Для примера, мы попробуем настроить Зеркальное отображение базы данных Публикации на сервере Издателя и имитируем отказ с переключением Агента чтения журнала на зеркальную копию публикуемой базы данных.
В электронной документации BOL есть несколько статей, которые подробно рассматривают вопросы, связанные с настройкой Зеркального отображения баз данных. Вот список названий некоторых из них:

• Настройка зеркального отображения базы данных.
• Как настроить сеанс зеркального отображения базы данных (среда SQL Server Management Studio).
• Подготовка зеркальной базы данных к зеркальному отображению.
• Пример. Настройка зеркального отображения базы данных с помощью проверки подлинности Windows (язык Transact-SQL)
• Зеркальное отображение базы данных ALTER DATABASE (Transact-SQL)

В последней из перечисленных выше статей говориться, что предварительно на зеркальном сервере должна быть восстановлена резервная копия основной базы данных, и все последующие резервные копии журнала транзакций, восстановленные с использованием предложения WITH NORECOVERY. Как уже отмечалось выше, лучше всего подготовку синхронной копии зеркалируемой базы данных позволяет сделать Доставка журналов. Поскольку в настоящей статье мы уже уделили много внимания настройке Доставки журналов, повторение этой процедуры для инициализации копии основной базы данных мы опустим, будем считать, что все эти процедуры уже выполнены. Копия базы данных на момент начала настройки Зеркального отображения должна находиться в актуальном по отношению к основной базе данных состоянии.
В нашем примере, основной экземпляр и зеркало настраиваются так, чтобы использовать одного и того же Распространителя. В конфигурации Агента чтения журнала мы изменим настройки, чтобы получить нужное нам поведение агента в случае отказа сервера Издателя. Для этого на сервере Распространителя GLADCHENKO-VHD нужно установить значение для параметра запуска Агент чтения журнала -PublisherFailoverPartner, например так:

exec sp_add_agent_parameter
  @profile_id = 1 -- ID Агента моментальных снимков
, @parameter_name = N'-PublisherFailoverPartner'
, @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала
GO

exec sp_add_agent_parameter
  @profile_id = 2 -- ID Агента чтения журнала
, @parameter_name = N'-PublisherFailoverPartner'
, @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала
GO

Для принятия изменений профиля, нужно перезапустить задание Агента чтения журнала.
Зеркалирование публикуемой базы данных начнём с создания конечных точек зеркалирования. В первую очередь создадим конечную точку на Издателе, это у нас компьютер GLADCHENKO-TEST:

CREATE ENDPOINT [MirroringEndpoint]
    AUTHORIZATION [AG@troika.ru]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
( ROLE = PARTNER
    , AUTHENTICATION = WINDOWS KERBEROS
    , ENCRYPTION = REQUIRED ALGORITHM RC4
)
GO

На сервере Зеркального отображения GLADCHENKO-VHD (это у нас Распространитель) тоже нужна конечная точка:

CREATE ENDPOINT [MirroringEndpoint]
    AUTHORIZATION [AG@troika.ru]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
( ROLE = ALL
    , AUTHENTICATION = WINDOWS KERBEROS
    , ENCRYPTION = REQUIRED ALGORITHM RC4
)
GO

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

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

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

GRANT CONNECT ON ENDPOINT:: MirroringEndpoint TO AG@troika.ru GO

После раздачи прав, нужно объявить участников Зеркального отображения. На сервере Зеркальной копии GLADCHENKO-VHD выполним следующую команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-TEST.troika.ru:5022'

После этого, с основного сервера GLADCHENKO-TEST запустим симметричную команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-VHD.troika.ru:5022'

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

ALTER DATABASE [MIR] SET PARTNER OFF

Современные серверные операционные системы оснащаются встроенными Брандмауэрами. Такие сетевые экраны тоже способны помешать подключению к порту конечной точки. Проверить возможность подключения с основного сервера к серверу зеркальной копии можно с помощью консольной команды операционной системы:

telnet GLADCHENKO-VHD 5022

Шаг 5. Переключение Публикации на зеркальную копию

Ну а теперь мы перейдём к самому интересному, ради чего были все эти подготовительные действия. Итак, мы имеем репликацию транзакций между двумя серверами, и Распространителя на третьем сервере. Кроме того, у нас настроено Зеркальное отображение базы данных Издателя на сервер Распространителя. Перед нами стоит задача - возобновить репликацию, которая была прервана в результате отказа Издателя. Доступность Издателя определяется Зеркальным отображением базы данных, на основе объектов которой создана наша Публикация. Отказ этой базы данных расценивается нами, как отказ Зеркального отображения и поводом для ручной или автоматической смены роли, когда зеркальная копия становится основной базой данных.
Далее, имитируем отказ основного сервера, который у нас является Издателем:

ALTER DATABASE MIR SET PARTNER FAILOVER

В ответ получаем сообщение: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%".
После этого основная база данных Зеркального отображения меняется ролями со своим зеркалом. Причём, на Распространителе появляется публикация и видно, что у неё уже существует Подписка (которую мы создавали на сервере GLADCHENKO-TEST). Изменения, которые пользователи будут делать в базе данных MIR после её переезда на сервер GLADCHENKO-VHD, будут вначале закрепляться в новом зеркале, в базе данных на сервере GLADCHENKO-TEST. Потом они будут попадать в базу данных Распространителя, считанные Агентом чтения журнала, который умеет обращаться ко второму партнёру Зеркального отображения в случае отказа основного сервера или основной базы данных.
Если на сервере GLADCHENKO-TEST запустим следующую команду:

SELECT PUBLISHINGSERVERNAME()

Эта команда возвратит имя исходного издателя для опубликованной базы данных, участвующей в Зеркальном отображении. Эта функция показывает первоначального издателя опубликованной базы данных, и поэтому у нас она вернёт, и всегда будет возвращать: "GLADCHENKO-TEST".
Если после этого выполнить на сервере GLADCHENKO-VHD ту же саму команду: "ALTER DATABASE MIR SET PARTNER FAILOVER", то всё вернётся в изначальное состояние. Публикация снова будет располагаться на сервере GLADCHENKO-TEST.
В отличие от агентов репликации, Монитор репликации не очень приспособлен для отслеживания работы с перемещённой на зеркальный сервер базой данных. Хотя его интерфейс будет показывать перемещение Публикации, информация о сеансах репликации может вводить администратора репликации в заблуждение. Трассировочные маркеры тоже не всегда помогают контролировать процесс репликации. В таком режиме работы репликации лучше всего контролировать сами данные, сверяя последние их изменения на издателе и подписчиках.
Существует опасность, что отказ основной базы данных приведёт к возникновению проблем репликации транзакций. Это особенно актуально, когда не соблюдается порядок первоначальной фиксации транзакции в зеркале базе данных. Если случиться так, что несколько некорректных транзакций будут препятствовать продолжению репликации данных, можно принудительно проигнорировать эти транзакции, что позволяет сделать системная процедура sp_setsubscriptionxactseqno.

Выводы

Зеркальное отображение можно использовать для повышения доступности Издателя в топологиях Репликации транзакций и Репликации слияния. Если важно, чтобы клиенты продолжали получать возможность изменения данных в издаваемой базе данных и автоматически переключались на её зеркальную копию в случае отказа, применение зеркалирования является оправданным.
В упомянутом в самом начале настоящей статьи техническом документе Майкрософт: " SQL Server Replication: Providing High Availability using Database Mirroring" предлагается вариант решения для зеркалирования баз данных Подписчиков. Там же вы найдёте аргументы для выбора такого решения.
Для повышения доступности базы данных Распространителя существует пока только одно решение - отказоустойчивая кластеризация.

Сравнения списка объектов SQL Server в Powershell на примере сравнения логинов на двух серверах