SQL в вопросах и ответах: Повышение производительности SQL

  • Статья

Всегда найдется несколько путей повышения производительности SQL Server, и очень часто это не стоит ни копейки.

Пол С. Рэндал

Новая норма

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

Ответ Осторожно, не перестарайтесь с нормализацией. Обычная цель нормализации — третья нормальная форма, или 3NF, в которой все неключевые атрибуты помогают определить ключ. Есть полезная фраза, определяющая это правило: «Атрибуты определяют ключ, только ключ и ничего более ключа, и да поможет мне Кодд» (Е.Ф. Кодд изначально определил 3NF в 1971 году, подробнее см. ссылку. Фраза переделана из клятвы, приносимой свидетелями в американском суде.)

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

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

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

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

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

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

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

Загадки производительности

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

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

Например, при наличии нескольких тысяч (или даже сотен) параллельных подключений, выполняющих вставку в таблицу, будет происходить блокировка. Если воспользоваться динамическим административным представлением sys.dm_tran_locks, оно не покажет никаких ошибок. Все потоки держат блокировку страницы IX (в одной из нескольких страниц) и ключа X (в записях этих страниц). Все выглядит нормально, но определенно есть определенная блокировка. Блокировка не связана с обычными блокировками SQL Server. Подробнее о блокировках и блокировании в SQL Server см статью.

Если посмотреть динамическое представление sys.dm_os_waiting_tasks, вы увидите, что большинство потоков ожидают с типом ожидания PAGELATCH_EX одну из немногих страниц. Причина в том, что для изменения копии страницы файла данных в памяти одной блокировки недостаточно. Кстати sys.dm_os_waiting_tasks одно из самых полезных представлений, потому что показывает, что ожидают все потоки в SQL Server.

С точки зрения SQL Server копия страницы в памяти на самом деле является структурой данных. Изменить структуру данных одной реляционной блокировкой невозможно. Нужно защитить целостность структуры данных, которую могут нарушить множество потоков, изменяющих ее одновременно. Это базовое положение науки о вычислениях. Управлять исключительным доступом при изменении структуры данных в SQL Server можно средствами легкого механизма синхронизации, который называется кратковременной блокировкой (latch).

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

Есть несколько решений этой проблемы:

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

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

Мысли о производительности

Вопрос У нас проблем с производительностью нашего SQL Server, и разработчики говорят, что нам нужен более мощный сервер для обслуживания имеющейся нагрузки. Мы являемся классическими «администраторами БД по неволе». Не могли бы вы подсказать, возможны ли другие варианты, помимо приобретения более мощного «железа»?

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

Нам приходилось наблюдать это несколько раз, и причиной всегда было неправильное программирование на T-SQL.

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

  • Посмотритет административное представление sys.dm_os_waiting_tasks, чтобы выяснить, где больше всего тратится времени на ожидание освобождения ресурсов. Если приходится долго ждать на обработку данных в клиентских приложением, накачивание серверных «мускулов» ничего не даст. О такой ситуации свидетельствует большое число строк в представлении в типом ожидания ASYNC_NETWORK_IO.
  • Изучите задержки записи и чтения в подсистеме ввода/вывода с помощью представления sys.dm_io_virtual_file_stats, чтобы выяснить, не перегружена ли она. Это может означать, что нужна более удачная стратегия индексирования. Tempdb также может быть узким местом, что может свидетельствовать о чрезмерном использовании временных таблиц.
  • Изучите DMV-представления на предмет отсутствия очень нужны индексов.
  • Представление sys.dm_db_index_usage_stats позволит обнаружить индексы, которые поддерживаются, но никогда не используются.

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

Параллельные линии

Вопрос У нас есть несколько запросов, которые создают проблемы, когда выполняются параллельно, поэтому мы хотим ограничить их выполнение в одном потоке. Как это сделать так, чтобы не все не стало однопоточным, когда мы зададим серверному параметру MAXDOP значение «1»?

Ответ В образовательных целях заметим, что MAXDOP означает «maximum degree of parallelism», то есть «максимальный уровень параллелизма». Это по сути число одновременных параллельных потоков, которое разрешено использовать при выполнении запроса.

Это хорошо, что вы не прибегаете к крайним мерам и не уменьшаете MAXDOP до единицы, потому что параллелизм — одна из самых замечательных механизмов повышения производительности в SQL Server. Многие прибегают к такому радикальному решению, следуя плохому совету, в котором говорится, что преобладание ожиданий CXPACKET (в соответствии с результатами анализа статистики ожидания) нужно устранять путем запрещения параллелизма.

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

Есть два намного более эффективные методы ограничения параллелизма.

  • Повышение общесерверного параметра «cost threshold for parallelism», чтобы сократить число запросов, в которых используется параллелизм. Это произвольное число, генерируемое в процессе компиляции запроса. Оно используется для принятия решения, нужно ли генерировать план выполнения запроса с использованием параллелизма. Увеличив этот параметр, можно эффективно предотвратить использование параллелизма в запросах, в которых его применять не стоит. И этот параметр нельзя обойти, задав другую подсказку в запросе. Подробнее об этом параметре см. блог Джонатана Кехайяса (Jonathan Kehayias) по адресу.
  • Можно также использовать регулятор ресурсов (только в версии Enterprise Edition). Он позволяет размещать запросы в «сегментах» (который называется группой рабочей нагрузки), а затем назначать каждому свой параметр MAXDOP. Можно переопределить заданное средствами sp_configure значение MAXDOP, но с регулятором ресурсов это не пройдет. Для распределения запросов по сегментам можно использовать любые фильтры. Можно также иметь несколько сегментов с разными параметрами MAXDOP. Все больше людей используют этот метод для тонкого управления параллелизмом.

Paul S. Randal

Paul S. Пол С. Рэндал (Paul S. Randal)* носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/@PaulRandal.*