Производительность SQL Server со многими параллельными, продолжительными запросами

ЦП

Каждый запрос, поступающий на сервер (т.е. каждый «пакет»), будет связан с «задачей», см. sys.dm_os_tasks . Задачи помещаются в очередь в «планировщике», который, грубо говоря, является ядром ЦП, см. sys.dm_os_schedulers . У каждого планировщика есть несколько «рабочих» (т. Е. Потоков или волокон, см. sys.dm_os_workers ), и свободный рабочий выбирает следующую задачу из очереди планировщика и «убегает» с ней, выполняя ее до тех пор, пока задача завершена (т. е. запросы выполнены). Этот механизм планирования применяется ко всему внутри SQL, включая системные задачи, исполняемый код CLR и т. Д. И т. Д.

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

Таким образом, если у вас есть большое количество параллельных, длительно выполняющихся запросов, вы будете использовать большое количество рабочих процессов, выполняющих множество длительных задач. Это уменьшает размер пула свободных рабочих, что приводит к уменьшению числа рабочих, доступных для обслуживания других, коротких задач, поступающих на сервер (например, запросов OLTP, рукопожатий при входе в систему и т. Д.). Сервер не отвечает, потому что задачи накапливаются в очередях планировщика (это можно увидеть в столбце sys.dm_os_schedulers DMV work_queue_count ). В крайних случаях вы можете эффективно истощить систему рабочих процессов, сделав сервер полностью безответным, пока некоторые из рабочих не освободятся.

Память

План запроса, содержащий параллельные операции, обычно связан с полным сканированием больших индексов (больших таблицы). Сканирование индекса выполняется путем обхода его конечных страниц, а чтение всех конечных страниц в большой таблице означает, что все эти страницы должны присутствовать в памяти в тот или иной момент во время выполнения запроса. Это, в свою очередь, создает потребность в свободных страницах из буферного пула для размещения отсканированных страниц. Требование свободных страниц приводит к нехватке памяти, в результате чего кеши получают уведомление о начале удаления старых записей, а старые страницы данных, к которым осуществляется доступ, удаляются из пула буферов. Уведомления кеша можно увидеть в sys.dm_os_memory_cache_clock_hands . Удаление страниц данных можно контролировать, проверяя счетчик производительности good ole ' Page Life Expectancy .

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

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

IO

Это связано с пунктом выше (ПАМЯТЬ). Все эти страницы, прочитанные для выполнения сканирования индекса, должны быть перенесены в память, что означает (потенциально большую) часть пропускной способности ввода-вывода, потребляемую длительными запросами. Кроме того, все страницы грязных данных, вытесненные из пула буферов, должны быть записаны на диск, что приведет к увеличению количества операций ввода-вывода. И чистые страницы, которые были выселены, вероятно, понадобятся в будущем, так что даже больше операций ввода-вывода.

Если ввод-вывод, сгенерированный сканированием, превышает пропускную способность вашей системы, операции ввода-вывода начинают выстраиваться в очередь на дисковый контроллер (-ы). это можно легко проверить с помощью счетчиков производительности Physical Disk / Avg Queue Length .

Contention

И, наконец, самая большая проблема : конфликт блокировок. Как объяснялось, параллельные запросы почти всегда подразумевают сканирование таблиц. А при сканировании таблиц осуществляется общая блокировка каждой посещаемой строки. Это правда, что они снимают блокировку, как только запись считывается в обычном режиме работы, но все же вам гарантировано, что вы запросите S-блокировку для каждой строки в таблице . Это в значительной степени гарантирует, что эти сканирования попадут в строку, заблокированную X при обновлении. Когда это происходит, сканирование должно остановиться и дождаться снятия блокировки X, что происходит, когда транзакции обновления, наконец, фиксируются. В результате даже умеренная активность OLTP в таблице блокирует длительные запросы. В идеале это все, что происходит, а в результате - просто низкая производительность. Но все может быстро стать уродливым, если длительный запрос делает что-нибудь необычное, например получает блокировку страниц вместо блокировок строк. Поскольку эти сканирования проходят через индексы от начала до конца, и они гарантированно вступают в конфликт с обновлениями, блокировки с более высокой степенью детализации, получаемые этими запросами, больше не просто конфликтуют с блокировками обновления, но фактически приводят к взаимоблокировкам. Объяснение того, как это может происходить, выходит за рамки этого ответа.

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