SQL в вопросах и ответах: Скорость и размер имеют значение

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

Пол С. Рэндал

Планируйте свои запросы

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

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

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

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

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

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

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

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

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

Больше файлов, больше места

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

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

Но это также является сильным обобщением. Выигрыш зависит от подсистемы ввода/вывода, структуры файлов данных и характера рабочей нагрузки. После определенного числа большое количество файлов становится бременем и отрицательно сказывается на производительности. Подробнее см. записи в блоге по адресам http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-do-multiple-data-files-make-a-difference.aspx и http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-Multiple-data-files-on-SSDs-(plus-Fusion-ios-latest-driver).aspx.

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

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

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

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

Если вы хотите добавить больше файлов в группу, лучше всего создать новую файловую групп с большим числом файлов, после чего перенести таблицу и данные индекса в эту группу командой CREATE INDEX … WITH (DROP_EXISTING = ON), указав в качестве целевой новую файловую группу. После переноса всех данных можно удалить старую пустую файловую группу. В новую файловую группу можно переносить даже данные бизнес-приложений, используя прием, описанный Кимберли Трипп на странице.

Журнальное очищение

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

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

При сжатии файла журнала просто удаляются неактивные и неиспользуемые части журнала транзакций в конце файла журнала. Эти «части» журнала транзакций называются виртуальными файлами журнала (VLF). Есть два обстоятельства, которые не позволяют сжать VLF-файлы: невыполнение фактической операции, которая переводит VLF-файлы в неактивное состояние, и отсутствие неактивных VLF в конце журнала транзакций.

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

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

SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'MyDBName';

Используйте результаты этой команды для принятия решения, что делать дальше. Если журнал транзакций готов к очистке, но DBCC SHRINKFILE все равно не удается сжать журнал, это означает, что команде удалось сжать только текущий активный журнал (или журналы) VLF. Это может быть в середине файла журнала транзакций. В таком случае снова выполните операцию очистки журнала, а затем повторите попытку сжатия.

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

Целостность ввода/вывода

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

Ответ Это экземпляры сообщения 825. Это сообщение появилось в SQL Server 2005. Это ранее предупреждение о неполадках целостности в подсистеме ввода/вывода.

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

Если все повторные попытки терпят сбой, SQL Server инициирует ошибку 823 или 824 и подключение разрывается (потому что серьезность такой ошибки считается равной 24). Если при одной из повторных попыток операция успешно выполняется, система продолжает нормальную работу, а SQL Server записывает в журнал сообщение 825.

У сообщения 825 следующий формат:

Msg 825, Level 10, State 2, Line 1.

Это означает, что чтение файла J:\SQLskills\MyDatabase_DF1.ndf по смещению 0×000004AA188000 было успешно выполнено с одной ошибкой: неверная контрольная сумма (ожидается: 0×33d1d136, получено: 0×0a844ffd). Дополнительные сообщения в журнале ошибок SQL Server или системы могут дать дополнительную информацию.

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

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

Пол С. Рэндал (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.