Tag Archives: SQL Server

Как посмотреть процент отката транзакции в SQL Server

Предположим такую ситуацию: у вас происходит откат (rollback) какого-либо запроса или транзакции, а вы хотите посмотреть, когда он закончится. Одним из самых простых способов для этого является, как ни странно, команда kill с опцией statusonly. Выглядеть это будет примерно следующим образом.

kill 64 with statusonly

На выходе вы получите примерно следующий результат.

SPID 64: transaction rollback in progress. Estimated rollback completion: 60%. Estimated time remaining: 13508 seconds.

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

MAXDOP и Resource Governor в MS SQL Server

В этой небольшой заметке я бы хотел немного рассказать о тонкостях в настройках параллелизма в Microsoft SQL Server. Очень многим из вас давно известна опция Max Degree od Parallelism, которая присутствует в SQL Server уже очень давно. По умолчанию она выставлена в 0, что значит, что SQL Server будет сам выбирать оптимальную степень параллелизма, то есть количество процессоров\потоков, задействованных для выполнения одной инструкции. Я сейчас не буду останавливаться и рассуждать, в какое же именно значении лучше выставлять эту опцию – это тема для отдельное заметки. Я лишь рассмотрю, как значение этой опции влияет на выполнение запросов. Например, ниже на рисунке, эта опция выставлена в 1, что означет, что параллельные планы для всех запросов по умолчанию отключены.

Также данная опция доступна для просмотра с помощью следующей команды T-SQL:

И действительно, любой план запроса по умолчанию будет последовательным. Например:

Однако, у разработчика и любого пользователя по-прежнему остается возможность повлиять на это путем использования подсказок (hints). Для этого всего лишь нужно указать нужную степень параллелизма, и генерируется нужный план запроса, например:

И если мы понаблюдаем на этот запрос через представление sys.dm_exec_query_profiles, то увидим, что он действительно выполняется в 10 потоков.

Таким образом, в системе остается потайной лаз, который могут использовать разработчики и пользователи, чтобы «ускорить» (тут я специально поставил в кавычки, т.к. не всегда большая степень параллелизма ведет к уменьшению времени выполнения запроса) свои запросы путем увеличения степени параллелизма. Но, таким образом, они могут просто «убить» сервер, запуская множество неконтролируемых параллельных запросов одновременно. Что же мы можем с этим сделать? Вот тут нам на помощь приходит Resource Governor, очень мощная и совершенно недооцененная система, которая позволяет очень гибко распределить ресурсы между разными группами пользователей. Опять же, я сейчас не буду останавливаться на том, как он устроен, и какими возможностями обладает. Я лишь остановлюсь подробно, как влияют его настройки ограничения параллелизма. Давайте для начала взглянем в настройки по умолчанию:

Опять мы видим, что по умолчанию опция выставлена в 0 и решение о выборе максимальной степени отдано на откуп SQL Server. Теперь посмотрим, что будет, если я поменяю это значение в 5. Внимание, ни в коем случае не делайте такие настройки на реальной системе, т.к. я даже не определил функцию классификации для Resource Governor и меняю default группу. Но для теста и понимания, как все работает конкретно сейчас на моем примере, этого хватит. Таким образом, я ограничиваю для всех максимальную степень параллелизма 5 потоками. Напомню, что опция Max Degree of Parallelism, которую мы рассматривали ранее выставлена по-прежнему в значение 1. Если мы теперь посмотрим на план выполнения нашего изначального запроса, то по умолчанию он будет последовательный, а с опцией maxdop 10 – параллельный. Но, если мы запустим параллельный план, то увидим кое-что интересное.

Теперь наш запрос выполняется только в 5 потоков, несмотря на то, что опция maxdop для него имеет значение 10. И, если вы укажете для запроса опцию maxdop 4, он будет выполняться в 4 потока (опция в Resource Governor установлена в 5). В этом случае подсказка maxdop меньше настройки Resource Governor, поэтому дополнительного ограничения не накладывается. Пример этого я уже не привожу.

Таким образом, Resource Governor является более мощным средством, который уже реально ограничивает максимальную степень параллелизма для запросов, и эту степень можно задать разную для разных групп пользователей. При этом опция Max Degree of Parallelism по-прежнему продолжает работать и вносит свою лепту (или слегка запутывает администраторов, разработчиков и пользователей, когда работает в купе с Resource Governor). Далее, лишь только вашей фантазией ограничены варианты выставления значений этих 2х параметров, но важно помнить лишь две вещи: Max Degree of Parallelism и подсказка (hint) maxdop для запроса влияет на то, какой план будет сгенерирован, сколько максимальное количество потоков будет возможно для этого запроса, а Resource Governor еще дополнительно сверху ограничивает запрос уже во время выполнения.

Выступление на IT Campus 26 июля 2014 г.

В последнюю пятницу июля традиционно отмечают День системного администратора. И именно в этот день у многих IT специалистов Москвы и всей России есть шанс посетить конференцию IT Campus. 3 дня участники будут проживать в палаточном городке, который они возведут собственными силами примерно в 30 километрах от г. Калуга. Организаторам удалось привлечь большое количество спонсоров и составить, на мой взгляд, очень интересную программу, а также большое количество дополнительных мероприятий, включая различные спортивные соревнования, турниры по Quake 3, настольные игры, выступления музыкальных коллективов. Цена за участие составляет всего 200 рублей, а все подробности вы можете узнать на сайте конференции. По-моему это шанс отлично провести выходные да еще и с пользой.

Одно из центральных мест на конференции будет занимать Дом Microsoft, где также пройдет большое количество активностей, например, виртуальные соревнования по скоростному спуску на коньках Red Bull Crashed Ice Kinect, где все желающие смогут попытаться пройти на Xbox одну из пяти сложнейших трасс, на которых в разное время бились за победу спортсмены Red Bull Crashed Ice. Также вы сможете пообщаться с представителями компании и узнать больше о продуктах и сервисах компании.

Я очень рад, что меня пригласили с докладом. Я буду рассказывать о практиках и методах построения отказоустойчивых решений для MS SQL Server 26 июля 2014 г. в субботу в 12:30. Буду делиться своим практических опытом, с чего начать, какие технологии доступны. Поэтому приглашаю всех желающих послушать. Также в этот день практически с самого утра и до позднего вечера вы сможете найти меня в Доме Microsoft, где я буду дежурить как эксперт и готов ответить на практически любые ваши вопросы об MS SQL Server.

Буду рад встречи со всеми и, надеюсь, погода не подведет и всех ждут отличные выходные!

Повреждение данных в SQL Server 2012 и 2014 при перестроении индексов в режиме online

Всегда, когда заходит речь о причинах повреждения данных в SQL Server, я называю программные ошибки в операционной системе и самом продукте. К счастью, это крайне редкий случай, но всем людям свойственно ошибаться, а SQL Server тоже пишут люди. Один их таких случаев произошел совсем недавно и затрагивает новые версии продуктов: SQL Server 2012 и 2014. Если кратко, то при онлайн перестроении индексов в вышеуказанных продуктов может возникнуть повреждение индексов или потеря данных, если при этом параллельно выполняются запросы на изменение большого количества строк и в определенном порядке возникает ошибка взаимоблокировки и фатальная ошибка, такая как «lock timeout». Проблема довольно серьезная, поэтому стоит обратить на нее очень пристальное внимание и установить вышедшие обновления. Дополнительно описание и ссылку на скачивание исправлений можно получить по нижеуказанной ссылке. Исправление доступно только для SQL Server 2012 SP1 и SP2, а также SQL Server 2014. Для SQL Server 2012 RTM его нет и не предвидится. FIX: Data corruption occurs in clustered index when you run online index rebuild in SQL Server 2012 or SQL Server 2014 Стоит обратить особое внимание на то, что недавно вышедший SP2 для SQL Server 2012 не содержит указанного исправления. Поэтому, если вы придерживаетесь политики ставить только сервис паки и игнорируете кумулятивные обновления или установку отдельных исправлений, то у вас могут возникнуть большие проблемы. На мой взгляд, ситуация, когда организации переходят на новые версии продукта тогда, когда становится доступен только первый или второй сервис пак не оправдана. Да, я согласен с тем, что не стоит бросаться сломя голову и обновлять ваш сервер, как только вышла новая версия продукта, но и ждать так долго тоже не имеет смысла. Сейчас мир меняется очень быстро, и релиз циклы начинают уменьшаться, что мы все отчетливо можем наблюдать эту тенденцию на примере версий 2012 и 2014. Второй сервис пак для SQL Server 2012 вышел уже после официального выхода SQL Server 2014, а третий, я боюсь, уже не будет выпущен. Но немного вернемся к основной проблеме. Как проверить, что на ваш SQL Server установлены все необходимые исправления? Для этого выполните команду select @@version на вашем сервере и результат сверьте с нижеприведенной таблицей:

SQL Server 2012 RTM Для указанной версии обновлений недоступно. Рекомендуется установить первый или второй сервис пак, а потом исправление KB #2969896.
SQL Server 2012 SP1 В случае, если версия ниже 11.0.3437, то установите исправление KB #2969896.
SQL Server 2012 SP2 В случае, если версия ниже 11.0.5522, то установите исправление KB #2969896.
SQL Server 2014 RTM В случае, если версия ниже 12.0.2370, то установите исправление KB #2969896или второй накопительный пакет исправлений KB # 2967546.

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

  • Вы можете временно отключить перестроение индексов совсем.
  • Вы можете установить опцию max degree of parallelism на уровне сервера в значение 1, но учтите, что это может негативно сказаться на производительности остальных запросов.
  • Вы можете добавить опцию WITH (MAXDOP = 1) ко всем командам перестроения индексов. В случае, если вы используете стандартные Maintenance Plans, то в них нет возможности указать, что перестроение индексов необходимо делать в однопоточном режиме. Если вы используете какие-либо еще утилиты для перестроения индексов, то уточняйте возможность перестроения в однопоточном режиме в их документации.

Про конференции SQLSaturday

Наверное, многие уже знают про сообщество PASS (The Professional Association for SQL Server) и какую роль оно играет в жизни специалистов по MS SQL Server. PASS оказывает широкую поддержку сообществ по всему миру, предоставляет инструменты для организации сообществ, организовывает такие замечательные мероприятия как PASS Summit, SQL Rally и PASS BA Conference, не говоря уже о 24 Hours of PASS, виртуальных группах и т.п. И большинство из этого предоставляется бесплатно.

Но сегодня я бы хотел поподробнее рассказать о таком феномене как SQLSaturday. Идея возникла в мае 2007 года, когда началось планирование первого мероприятия, которое прошло 11 ноября 2007 года в городе Орландо, штат Флорида, США. У истоков стояли Andy Warren, Brian Knight и Steve Jones. Следующее мероприятие состоялось спустя 3 месяца 16 февраля 2008 года в городе Тампа, штат Флорида, США. За первые 2 года прошло 23 мероприятия в США. За 2010 год состоялось уже 32 конференции. 2011 год становится поворотным в истории SQL Saturday: 26 февраля состоялся первый «субботник» за пределами США в Канаде, а уже 30 апреля выходит за рамки американского континента и проходит в Лиссабоне, Португалия. Дальше все происходит почти в геометрической прогрессии. Все больше и больше городов проводят SQLSaturday на базе локальных сообществ. Больше всего событий одновременно состоялось 14 сентября 2013 года – шесть.

Организация PASS владеет логотипом и веб сайтом мероприятия и лицензирует их организаторам на местах, кто непосредственно занимается организацией и проведением «субботников». Конференции всегда проводятся бесплатно для всех участников и финансируются за счет спонсоров. Единственное, с чем вы можете столкнуться – это небольшой взнос за обед, который обычно не превышает 10 долларов, но который позволяет организовать более-менее приличное питание во время мероприятия.

Почему именно суббота? Дело в том, что многим, в том числе докладчикам, тяжело вырваться на конференцию в рабочее время на неделе. Многие приезжают из других городов или стран, и это позволяет им также провести выходные в новом для себя месте. Но, в некоторых культурах проведение чего-либо в субботу невозможно, поэтому уже есть много прецедентов, когда SQLSaturday проходил и во вторник, и в четверг и даже в воскресенье. Единственный день, когда не проходил SQL Saturday – понедельник.

Впервые SQLSaturday в России и Украине прошли 17 ноября 2012 г. и 24 ноября 2012 г. соответственно.

Также хочу поделиться картой мира, где отмечены города, в которых прошли мероприятия. Устроим SQLSaturday в Антарктиде в понедельник? J

Отдельно хочу поблагодарить Андрея Коршикова за помощь в анализе данных по событиям SQL Saturday и создании карты с помощью Power Query и Power View.

Начала работу российская виртуальная группа по SQL Server

С радостью готов сообщить, что все формальности решены и при поддержке PASS в России начала работу виртуальная группа по SQL Server. Теперь все даже в отдаленных уголках нашей большой страны смогут в режиме онлайн послушать интересные презентации, посвященные SQL Server и задать свои вопросы докладчикам. Время все встреч запомнить очень просто: каждую третью неделю месяца по средам в 15:00 по Московскому времени. 3-3-3 (третья неделя, третий день, 3 часа для).

Начинаем 21 августа. Вы услышите первую часть доклада “Индексы и все, все, все”. Докладчиком будет Дмитрий Короткевич (Microsoft SQL Server MVP, Microsoft Certified Master). Он работает с SQL Server более 11 лет, специализируясь в архитектуре, разработке и оптимизации OLTP систем. Дмитрий ответственен за внедрение нескольких решений обрабатывающих тысячи транзакций в секунду в режиме 24×7.

Ссылка на официальный сайт группы, где вы можете посмотреть список докладов и зарегистрироваться: http://russianvc.sqlpass.org/

Генерация скриптов для создания и удаления объектов SQL Server и с помощью Powershell

В свой предыдущей статье я показал, как можно использовать мастер SQL Server Management Studio для генерации скриптов для различных объектов SQL Server. Мастер обладает большим набором возможностей, но также и рядом недостатков. Например, мы видели, что нельзя легко сгенерировать скрипты для всех объектов из определенной схемы, кроме как вручную указать все эти объекты. В этой статье мы рассмотрим другой подход к генерации скриптов – программный. Для этого мы будем использовать Powershell, мощное и гибкое средство для управления любыми Windows машинами или сервисами, в том числе и SQL Server.

Самый простой способ – запустить Powershell прямо из SSMS.

У вас откроется консоль Powershell, в которой уже будет загружен модуль SQLPS и открыт путь до нашей базы данных. Здесь можно просматривать и работать с объектами также, как и с файлами и папками. Например, с помощью следующего набора команд мы перейдем к таблицам в нашей базе данных, получим список всех таблиц в переменную и для первой таблицы в списке сгенерируем скрипт.

# Переходим к списку таблиц
cd Tables

# Считываем все таблицы в массив
$tables = Get-ChildItem

# Выводим информацию о первом элементе
$tables[1]

# Для первой таблицы генерируем скрипт
$tables[1].Script() 

У вас должно получиться что-то подобное, как на скриншоте снизу.

Но, если мы хотим, например, сгенерировать не скрипт создания, а скрипт удаления указанной таблицы. Для этого потребуется создать объект класса Microsoft.SqlServer.Management.Smo.ScriptingOptions, указать у него определенные свойства (а их у объекта большое множество) и снова вызвать метод Script у таблицы, в который в качестве параметра передать объект со свойствами.

# Создаем новый объект класса Microsoft.SqlServer.Management.Smo.ScriptingOptions
$script_options = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

# У этого объекта выставляем свойство ScriptDrops в значение true
$script_options.ScriptDrops = $true

# Снова запускаем метод Script с указанными опциями
$tables[1].Script($script_options) 

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

# Указываем файл, куда будет сохранен скрипт
$file_name = "C:\Temp\MyScript.sql"

# Перенаправляем вывод с консоли в указанный файл
$tables[1].Script($script_options) > $file_name 

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

# Для всех таблиц, имя которых начинается на Pro, сгенерировать скрипты с опциями, указанными в $script_oprions и выложить скрипт для каждого для каждого отдельного объекта в папку C:\Temp в формате <Имя схемы>.<Имя таблицы>.sql
foreach ($t in $tables | Where-Object { $_.Name.StartsWith("Pro") }) { $t.Script($script_options) > "C:\Temp\$($t.Schema).$($t.Name).sql" } 

Как мы видим, набор возможностей практически не ограничен за одним исключением. Набивать эти команды в консоли крайне неудобно. Гораздо удобнее написать отдельный скрипт и вызывать его по мере необходимости. Хочу привести пример отдельного скрипта на Powershell для генерации скриптов создания всех таблиц в базе с ограничениями, внешними ключами и т.п. в один большой скрипт с разделителями.

# Загружаем модуль для работы с SMO объектами.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Объявляем переменные и указываем имя сервера, имя базы данных и путь до файла, куда будет сохранен скрипт создания всех таблиц.
$srv_name = "(local)"
$db_name = "AdventureWorks2012"
$file_name = "C:\Temp\CreateAllTables.sql"

# Объявляем объект класса Microsoft.SqlServer.Management.Smo.Server.
$srv = New-Object('Microsoft.SqlServer.Management.Smo.Server') $srv_name

# Получаем объект нужной нам базы данных.
$db = $srv.Databases[$db_name]

# Создаем объект класса Microsoft.SqlServer.Management.Smo.Scripter, который будет выполнять всю работу по созданию скриптов.
$scripter = New-Object('Microsoft.SqlServer.Management.Smo.Scripter') $srv

# Указываем, что в скрипт нужно включать все DRI объекты (Declarative Referential Integrity: ограничения, внешние ключи и т.п.).
$scripter.Options.DriAll = $true
# Включать в скрипт создание индексов.
$scripter.Options.Indexes = $true
# Включать в скрипт добавление расширенных свойств.
$scripter.Options.ExtendedProperties = $true

# Указывать в скрипте разделитель GO между командами создания объектов.
$scripter.Options.ScriptBatchTerminator = $true
$scripter.Options.NoCommandTerminator = $false

# Указываем, что сохранять скрипт необходимо в файл.
$scripter.Options.FileName = $file_name
$scripter.Options.ToFileOnly = $true
$scripter.Options.AppendToFile=$true

# Для всех таблиц, имя которых начинается на Pro, сгенерировать скрипты и добавить их в указанный файл.
foreach ( $t in $db.Tables | Where-Object { $_.Name.StartsWith("Pro") } ) {
    $scripter.Script($t)
}

На этом все. Я постарался привести максимально полезные примеры, от которых вы сможете отталкиваться при написании своих скриптов на Powershell.

Генерация скриптов для создания и удаления объектов SQL Server из SSMS

Довольно часто возникает потребность генерации скриптов для создания или удаления различных объектов MS SQL Server. Например, для создания идентичной по структуре базы данных на другом сервере или для сохранения перед внесением каких-либо серьезных изменений. В SQL Server Management Studio есть мощный строенный механизм генерации скриптов. Для этого достаточно щелкнуть правой клавишей мыши на нужной базе данных и выбрать пункт Tasks -> Generate Scripts для запуска мастера генерации скриптов.

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

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

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

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

Здесь мы можем выбрать путь, куда будет выложен один большой скрипт для всего, либо для каждого отдельного объекта. Также формат файла: Unicode или ANSI. И, несмотря на то, что для сгенерированного скрипта есть возможность не сохранять его в файл, а либо скопировать в буфер обмена, либо открыть его сразу в новом окне SSMS, я не рекомендую использовать эту опцию, т.к. для достаточно больших скриптов это не будет работать, а каким получится скрипт зачастую нельзя предугадать. Однако, если вы заранее знаете, что скрипт получится маленьким, то это вполне оправданно.

Также на этой странице находится самая важная кнопка, Advanced, при нажатии на которую откроется окно различных настроек генерации.

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

  1. По умолчанию генерируются только скрипты создания. Есть опция генерации команд на удаления, либо и то и другое одновременно.
  2. Версия SQL Server для которой создается скрипт. Эта опция может быть полезна, если вы хотите создать объекты на более ранней версии сервера, где какие-то возможности не поддерживаются или немного отличаются. В этом случае скрипты будут сгенерированы таким образом, чтобы не вызывать ошибку при их запуске.
  3. Также есть возможность при генерации включить в скрипты команды создания пользователей и их прав на указанные объекты.
  4. Вы можете контролировать, включить ли в скрипт только описание указанных объектов, либо добавить команды вставки данных, либо и то и другое одновременно. Очень полезно, когда есть необходимость для какой-либо таблицы или таблиц сформировать скрипт на вставку данных.
  5. По умолчанию при генерации скриптов таблиц не генерируются команды создания индексов, что также может быть полезно в некоторых случаях.

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

Материалы с конференции DevCon 2013

2 недели назад в подмосковных Яхонтах прошла очередная конференция DevCon 2013. Выкладываю материалы моих докладов.

Как читать план запроса в SQL Server и на что обращать внимание

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

Презентация: https://olontsev.ru/download/DevCon2013_Query_Plans.pdf

Скрипты с демонстрациями: https://olontsev.ru/download/Query_Plans_Demo_Scripts.zip

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

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

Презентация: https://olontsev.ru/download/DevCon_2013_UDFs_and_SPs.pdf

Скрипты с демонстрациями: https://olontsev.ru/download/UDFs_and_SPs_Demo_Scripts.zip

Записи докладов можно посмотреть по указанной ссылке (в скором времени должны выложить на http://www.techdays.ru/): http://www.msdevcon.ru/online

Ну и напоследок ответ на самый главный вопрос конференции: в чем секрет белки? J

SQL Server Enterprise Edition Advanced Scanning

Мы знаем, что Enterprise редакция SQL Server содержит некоторые улучшения, которые при определенных условиях позволяют выполнять операции более оптимально, чем в Standard редакции. Одной из таких вещей является Advanced Scanning, которая позволяет нескольким запросам на сканирование делить одну операцию физического чтения с диска. Также можно встретить другое название этой особенности: Merry-go-Round Scan.

Например, у нас есть большая таблица Table1 состоящая из 1 000 000 страниц. Пользователь UserA начинает выполнять инструкцию T-SQL, которая требует сканирования таблицы. В тот момент, когда выполнилось сканирование 200 000 страниц пользователь UserB подключается и начинает другой запрос, который также требует сканирование Table1. Для сканирования после 200 001-ой страницы компонент Database Engine запланирует всего одно физическое чтение и будет возвращать полученные строки обоим планам выполнения. После сканирования 500 000 страниц пользователь UserC подключается и также запрашивает все данные из таблицы Table1. Теперь операция физического сканирования уже делится между 3мя запросами. Как только сканирование закончится для пользователя UserA, оно продолжится для пользователей UserB и UserC, но уже с 1-ой страницы и соответственно до 200 000 для UserB и до 500 000 для UserC.

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

Для демонстрации давайте создадим достаточно большую таблицу с кластеризованным индексом в БД AdventureWorks2012.

use
[AdventureWorks2012];
go

select
    [DatabaseLogID],
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
into [dbo].[BigDatabaseLog]
from [dbo].[DatabaseLog];
go

insert into [dbo].[BigDatabaseLog]
(
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
)
select
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
from [dbo].[DatabaseLog];
go 200

create clustered index [IX_CL_DatabaseLogID]
on [dbo].[BigDatabaseLog]
(
    [DatabaseLogID] asc
) on [PRIMARY];
go

Также стоит отметить, что Advanced Scanning работает только в том случае, если нет требования, чтобы поток данных был отсортирован. Например, как в следующем плане запроса:

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

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

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