SQL Admin: Август 2007

Содержание номера за Август 2007 год

Использование хеш-ключей вместо строковых индексов в SQL Server

Артур Фуллер (Arthur Fuller)

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

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

Тонкая настройка дизайна базы данных в SQL Server 2005. Часть 2*

Обзор инструментов для настройки индексов

Санчан Сахай Саксена (Sanchan Sahai Saxena)

Database Engine Tuning Advisor (DTA)

Сценарий 2: игнорировать существующие PDS и сообщить, что требуется оптимизатору запросов

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

Чтобы провести такой анализ, проделайте приведенные выше шаги, только на шаге 5В выберите Do not keep any existing PDS.

Не бойтесь, на самом деле DTA не будет удалять существующие PDS. Это самое большое преимущество DTA, поскольку вы можете представить «что будет, если», не внося изменений в существующую схему.

Кое-что о DTA Tuning Log

По мере обработки входных данных DTA записывает детальную информацию о произошедших во время анализа ошибках в DTA Tuning log (его можно найти на вкладке Progress в нижней секции).

Обычно при возникновении ошибки вы видите сообщение вида «XX% of the consumed workload has syntax errors». В журнале настройки вы найдете более подробную информацию о таких ошибках, большинство из которых можно смело игнорировать. Например:

• Statement doesn't reference any table Эти ошибки вызваны такими выражениями, как SET или DECLARE; обычно на них можно не обращать внимания.

• Statement references only small tables DTA не будет оптимизировать запрос, если он относится к маленькой таблице (менее 10 страниц данных).

• Incorrect syntax or object related errors Такие ошибки можно игнорировать, если они ссылаются на запросы с ключевыми словами BEGIN, TRY, INSERTED.

Более подробную информацию об анализе журнала настройки вы найдете в разделах «About the Tuning Log» (http://msdn2.microsoft.com/enus/library/ms189242.aspx) и «Determining Whether Events Can Be Tuned» (http://msdn2.microsoft.com/enus/library/ms187447.aspx) в онлайновой документации.

Интерпретация результатов работы DTA

После обработки данных DTA предлагает набор рекомендаций (на вкладке Recommendations) по настройке PDS. Я обычно уделяю особое внимание следующим разделам:

• Recommendation Это действие, которое вам нужно предпринять. Возможны значения Create или Drop.

• Target of Recommendation Это предполагаемое имя создаваемой PDS. DTA следует определенному принципу при назначении имен: обычно имена начинаются с _dta*. Однако я порекомендовал бы изменить это имя в соответствии с соглашением о присвоении имен, принятым в вашей базе данных.

• Definition Это список столбцов, которые будет включать новая PDS. Если щелкнуть по гиперссылке, откроется новое окно со сценарием на TSQL, реализующим данную рекомендацию.

• Estimated Improvements Здесь приводится предполагаемый рост производительности в процентах, который вы получите, реализовав рекомендации DTA.

• Space used by recommendation (MB) В секции Tuning Summary вкладки Reports вы найдете количество дополнительного дискового пространства, необходимого для реализации рекомендаций.

Встроенные отчеты о проведенном анализе

На вкладке Reports есть 15 встроенных отчетов, из которых наиболее важны следующие три.

Примечание Информацию по этим трем, а также по 12 оставшимся отчетам вы найдете в статье MSDN «Choosing a Database Engine Tuning Advisor Report» (http://msdn2.microsoft.com/enus/library/ms189106.aspx).

Отчет об использовании текущих индексов (Index Usage Report (current))

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

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

Отчет об использовании рекомендованных индексов (Index Usage Report (recommended))

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

Отчет о «стоимости» выражений
(Statement Cost Report)

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

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

Реализация рекомендаций DTA

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

• Сохранить рекомендацииМожно сохранить рекомендации в виде SQLсценария, выбрав ACTIONS | SAVE RECOMMENDATIONS. Потом вы сможете вручную запустить этот сценарий в Management Studio, чтобы создать все рекомендованные PDS.

• Применить рекомендации с помощью DTA
Если набор рекомендаций вас полностью устраивает, просто выберите ACTIONS | APPLY RECOMMENDATIONS. Можно также запланировать эту операцию на более позднее время (например, на часы минимальной загрузки системы).

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

Проведение анализа по типу
«что будет, если...» с помощью DTA

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

Снимите флажки с рекомендаций, которые вы не хотите применять, и выберите ACTIONS | EVALUATE RECOMMENDATIONS.

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

И снова я должен повторить: DTA выполняет анализ по типу «что будет, если...», не внося реальных изменений в рабочую базу данных.

STATISTICS XML

Если вам нужно просто определить оптимальный набор индексов для некоторого запроса, вам подойдет параметр STATISTICS XML — быстрая альтернатива DTA.

Если при выполнении запроса активирован режим STATISTICS XML, SQL Server не только возвращает для запроса результирующий набор данных, но и запускает выражения TSQL, получающие план выполнения для запроса в виде XMLфайла (Showplan.xml). Например, попробуйте запустить следующее SQLвыражение:

SET STATISTICS XML ON
GO
Use Adventureworks
SELECT CustomerID, PurchaseOrderNumber, TotalDue
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND TotalDue > 200.00
AND TerritoryID = 3;
GO
SET STATISTICS XML OFF
GO

Под результатами выполнения запроса должна быть ссылка на XMLфайл. Наиболее важные элементы этого файла это:

• <StmtSimple> Значение StatementText представляет собой текст только что запущенного вами запроса. Вы можете убедиться, что оно совпадает с текстом, который вы ввели в Management Studio.

• <MissingIndexes> Здесь приведены сведения обо всех отсутствующих индексах, которые, по мнению оптимизатора запросов, могли бы положительно повлиять на производительность этого запроса.

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

Из этого листинга видно, что нам недостает одного индекса на таблице Sales.SalesOrderHeader. Обратите внимание на значение Impact в элементе <MisingIndexGroup>, которое соответствует предполагаемому улучшению времени реакции в процентах после реализации указанных в этом XML индексов.

В элементе <MissingIndex>:

• <ColumnGroup Usage="EQUALITY">Перечисляет все столбцы индекса, используемые в запросе для проверки на совпадение (в нашем случае TerritortyID). Из всех указанных в запросе условий оптимизатор запросов сначала выполняет поиск по индексу на equalityстолбце, чтобы вычленить удовлетворяющие критерию строки.

• <ColumnGroup Usage="INEQUALITY">Перечисляет столбцы индекса, используемые в запросе для проверки на «больше чем», «меньше чем» и подобные (в нашем случае ShipMethodID и TotalDue).

• <ColumnGroup Usage="INCLUDE">Перечисляет столбцы индексов, включенные для покрытия запроса (PurchaseOrderNumber и CustomerID). Вы можете указать для некластерного индекса включенные столбцы, чтобы избежать поиска по закладкам в плане выполнения

Примечание Узнать о включенных столбцах в некластерных индексах больше вы можете из статьи MSDN «Index with Included Columns» (http://msdn2.microsoft.com/enus/library/ms190806.aspx).

Чтобы создать этот недостающий индекс, воспользуйтесь следующим шаблоном:

CREATE NONCLUSTERED INDEX <<IndexName>>
ON <<Schema.ObjectName>> (equality_columns,
за которыми следуют inequality_columns)
INCLUDE (included_columns);

То есть в нашем случае:

CREATE NONCLUSTERED INDEX TestIndex
    ON Sales.SalesOrderHeader (TerritoryID, Ã
                    ShipMethodID, TotalDue)
    INCLUDE (PurchaseOrderNumber, CustomerID);

Связанные с индексами динамические представления управления (DMV)

Динамические представления управления (Dynamic Management Views) — новшество SQL 2005. Они сохраняют состояние на уровне сервера, а также конфигурационную информацию для вашей инсталляции SQL Server. В SQL 2005 есть более 50 DMV. Хранящуюся в них информацию можно использовать для мониторинга «здоровья» экземпляра сервера, диагностики, а также настройки производительности.

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

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

Перед тем как начать, перезапустите SQL Server на тестовой машине, чтобы очистить все DMV. Далее запустите в новом окне тот же запрос, что и в предыдущем разделе, но с выключенным параметром STATISTICS XML:

SELECT CustomerID, PurchaseOrderNumber, TotalDue
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND TotalDue > 200.00
AND TerritoryID = 3;

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

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

select * from sys.dm_db_missing_index_details

Этот запрос вернет id объекта, на котором следует создать индекс (в данном случае это Sales.SalesOrderHeader), а также значения equality_columns, inequality_columns и included_columns, смысл которых такой же, как и у описанных выше элементов XML. Вы увидите, что DMV рекомендует создать тот же недостающий индекс, что и параметр STATISTICS XML; предложенный для этого синтаксис также совпадает с приведенным выше.

При каждом перезапуске SQL Server содержимое этих DMV стирается. Так что, возможно, будет полезно скопировать из них нужные сведения в отдельные таблицы для проведения периодического анализа.

Выбор правильного инструмента

И параметр STATISTICS XML, и связанные с индексами DMV — прекрасные инструменты для оценки требований к индексам, выдвигаемых некоторым заданным запросом. Самое существенное ограничение в обоих случаях заключается в невозможности тонкой настройки сервера с учетом полной нагрузки, то есть всех возможных запросов. Если ваши приложения являются критически важными и посылают большое число запросов, этот подход потребует слишком больших затрат времени и усилий.

Более того, при использовании DMV вы получаете «сырую» информацию о столбцах, на которых недостает индексов. У такого подхода тоже есть несколько ограничений, и он не очень точен при работе с запросами, содержащими предикаты неравенств. Узнать об этих ограничениях больше вы можете из статьи BOL «Limitations for Using the Missing Indexes Feature» (http://msdn2.microsoft.com/enus/library/ms345485.aspx).

Database Engine Tuning Advisor — более сложный инструмент, который предоставляет больше возможностей, чем другие описанные здесь методы. Работая с DTA, вы можете пользоваться близкой к реальной моделью нагрузки на систему, а данные им рекомендации более точны. В отличие от других методов DTA можно использовать в SQL 2000. Кроме того, с его помощью можно проводить анализ типа «что будет, если...», не внося реальных изменений в вашу базу данных.

Я настоятельно рекомендую обращаться к DMV или STATISTICS XML только для быстрой оценки влияния запроса на требования к индексам. Свои догадки и оценки предполагаемых улучшений всегда следует проверять с помощью Database Engine Tuning Advisor, используя близкие к реальным данные о нагрузке на систему.

Заключение

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

Из всех этих методов я лично предпочитаю Database Engine Tuning Advisor. Чтобы получить наиболее эффективные рекомендации, вам следует собрать данные о нагрузке на ваш сервер с помощью шаблона TUNING и передать их для анализа в DTA.

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

Выявление неиспользуемых индексов

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

Проверка наличия избыточных объектов,
не учтенных DTA

Теоретически после анализа DTA не должен оставлять в вашей базе «гипотетических» PDS (индексов и т. д.). Однако в этом можно легко убедиться, запустив вот этот запрос:

select name from sys.indexes where is_hypothetical = 1

*См. Санчан Сахай Саксена. Тонкая настройка дизайна базы данных в SQL Server 2005. Часть 1 // SQL Server для администраторов. 2007. № 7.

Часто задаваемые вопросы по использованию аргументов командной строки

Дик Льюис (Dick Lewis)

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

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

Снижение риска возможной компрометации SQL Server 2000

По материалам www.securitylab.ru

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

Возможность повышения привилегий локального пользователя за счет агента SQL

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

Описание

В СУБД SQL Server поддерживаются возможности репликации данных и выполнение заданий по расписанию. Данные возможности выполняются
с помощью сервиса, называемого SQL Server Agent. Этот сервис выполняется с повышенными привилегиями — привилегиями сервиса СУБД.

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

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

SETUSER N'guest' WITH NORESET

Злоумышленник способен обойти данное ограничение при помощи вызова следующих хранимых процедур:

• xp_execresultset;

• xp_printstatements;

• xp_displayparamstmt.

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

Ниже представлен пример создания произвольного системного файла непривилегированным пользователем:

use msdb
go
execute sp_add_job @job_name = 'Test', @enabled = 1,
   @description = 'test', @delete_level = 1
go
execute sp_add_jobstep @job_name = 'Test',
   @step_name = 'test_step', @subsystem = 'TSQL',
   @command = 'select ''test output'' ',
   @output_file_name = 'c:\autoexec.bat'
go
execute sp_add_jobserver @job_name = 'Test',
   @server_name = 'SERVER_NAME'
go
execute sp_start_job @job_name = 'Test'
go

Какая версия BOL установлена на компьютере?

Александр Гладченко

После того как BOL стал обновляться через Microsoft Update (http://msmvps.com/blogs/irinanaumova/archive/2007/05/17/909765.aspx), этот вопрос, наверняка, будет волновать многих.

Самое простое — заглянуть в оснастку установки и удаления программ, которая присутствует в списке панели управления. В ней можно увидеть картину, подобную рис. 1.

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

HKEY_CLASSES_ROOT\Installer\Products

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

Каждая новая версия BOL получает новый идентификатор, и, мало того, свой идентификатор получает и каждый перевод BOL. То есть, если вы установили английскую и русскую редакции BOL, у вас будет два разных идентификатора; а после того как вы установите BOL для SQL Server 2008, добавится еще и третий. Кроме заметных различий в названиях (текст, который на рисунке выделен) и различий в идентификаторах, каждое такое издание имеет два варианта внутренних версий. Один вариант версии BOL можно увидеть, если попытаться воспользоваться кнопкой «Отправить отзыв» (Send Feedback), которая располагается в нижнем колонтитуле каждой статьи BOL. Для доставки отзыва используется обычное письмо, в теме которого можно увидеть примерно такой текст, в начале которого можно заметить внутреннюю версию:

/1:SQL/2:9.0000.7103.1552/3:1.0/4:sqlgtst9/5:ms-help:
//MS.SQLCC.v9/MS.SQLSVR.v9.ru/sqlgtst9/html/91ddee3a…

Однако совершенно другую версию BOL вы увидите в соответствующем ключе системного реестра:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
Microsoft SQL Server 2005 Redist\BOL\

Чтобы узнать версию BOL, подключившись к серверу, например в Management Studio, и выполнив сценарий на TSQL, вы можете использовать доступный по следующей ссылке код: BOL Versions (2007/06/06) (http://msmvps.com/blogs/gladchenko/pages/946284.aspx). О том, как осуществляются загрузка и обновление BOL, можно почитать тут: загрузка и обновление электронной документации (http://msdn2.microsoft.com/ruru/library/91ddee3a98d94419a19c26d06b816ac6.aspx).

SET DATE_CORRELATION_OPTIMIZATION ON?!

Оптимизация запросов, которые обращаются к коррелируемым столбцам типа datetime

Александр Гладченко

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

В SQL Server 2005 появился новый параметр базы данных DATE_CORRELATION_OPTIMIZATION инструкции SET. Этот параметр улучшает производительность запросов, выполняющих эквивалентное соединение двух таблиц, у которых столбцы datetime коррелируют друг с другом, и содержащих ограничение на дату в предикате запроса (рис. 1).

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

Например, в базе данных AdventureWorks столбец OrderDate таблицы Purchasing.PurchaseOrderHeader и столбец DueDate таблицы Purchasing.PurchaseOrderDetail коррелируют друг с другом. Значения даты в столбце PurchaseOrderDetail.DueDate слегка превышают значения в таблице PurchaseOrderHeader.OrderDate.

Если параметр базы данных DATE_CORRELATION_OPTIMIZATION включен, SQL Server собирает статистику корреляции между двумя таблицами базы данных, содержащими столбцы datetime и связанными ограничением внешнего ключа из одного столбца. По умолчанию этот параметр выключен.

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

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

USE AdventureWorks GO
-- Create a unique index to take the place of the
-- existing primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX IX_
   PurchaseOrderDetail_PurchaseOrderID_Ã
   PurchaseOrderDetailID
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,Ã
   PurchaseOrderDetailID)
-- Drop existing clustered index by dropping
-- constraint
ALTER TABLE Purchasing.PurchaseOrderDetail

DROP CONSTRAINT PK_PurchaseOrderDetail_Ã
PurchaseOrderID_PurchaseOrderDetailID
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate)
-- Enable DATE_CORRELATION_OPTIMIZATION

-- database option
ALTER DATABASE AdventureWorks
SET DATE_CORRELATION_OPTIMIZATION ON

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

SELECT * FROM Purchasing.PurchaseOrderHeader h,
Purchasing.PurchaseOrderDetail d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201'

Значения столбца PurchaseOrderDetail.DueDate, которые возвращает этот запрос, как правило, превышают значения в столбце PurchaseOrderHeader.OrderDate на величину, принадлежащую некоторому интервалу, например 14 дней. Благодаря этому SQL Server может предположить, что предыдущий запрос можно выполнить быстрее, используя запрос, подобный следующему:

SELECT * FROM Purchasing.PurchaseOrderHeader h, Ã
              Purchasing.PurchaseOrderDetail d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND   h.OrderDate
   BETWEEN '1/1/02' AND '2/1/02'
AND d.DueDate
   BETWEEN CAST ('20020101' AS datetime) + 14
AND CAST ('20020201' AS datetime) + 14

Точная форма нового условия во втором предложении AND зависит от первоначального запроса и значений в базе данных. Оптимизатор использует добавленное условие для построения плана выполнения. Например, по столбцу PurchaseOrderDetail.DueDate построен кластерный индекс, который используется для извлечения строк, удовлетворяющих условию d.DueDate BETWEEN CAST (‘20020101’ AS datetime) + 14 AND CAST (‘20020201’ AS datetime) + 14. Если столбец Purchasing.PurchaseOrderDetail содержит данные за несколько лет, производительность запроса может значительно ухудшиться (в несколько раз) по сравнению с первоначальным запросом.

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

Требования к использованию параметра базы данных DATE_CORRELATION_OPTIMIZATION

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

• Параметры базы данных инструкции SET должны быть установлены следующим образом: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL и QUOTED IDENTIFIER должны быть установлены (SET) в состояние ON. Параметр NUMERIC_ROUNDABORT должен быть установлен (SET) в состояние OFF.

• Таблицы должны быть связаны отношением внешнего ключа из одного столбца.

• Обе таблицы должны содержать столбцы datetime, объявленные как NOT NULL.

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

• Обе таблицы должны принадлежать одному пользователю.

При включении параметра базы данных DATE_CORRELATION_OPTIMIZATION учтите следующее:

• SQL Server хранит сведения о корреляции в форме статистики. SQL Server обновляет статистику при выполнении операций INSERT, UPDATE и DELETE в соответствующих таблицах; это может привести к ухудшению производительности этих операций. Параметр DATE_CORRELATION_OPTIMIZATION не следует включать в базе данных, где часто выполняются операции обновления.

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

• Включение параметра DATE_CORRELATION_OPTIMIZATION не улучшает производительность в следующих ситуациях:

• нет такой пары таблиц, которые удовлетворяют описанным условиям для сбора статистики;

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

Чтобы включить параметр базы данных DATE_CORRELATION_OPTIMIZATION, используйте ALTER DATABASE (TransactSQL).

Работа со статистикой корреляции

При включении параметра базы данных DATE_CORRELATION_OPTIMIZATION для каждой подходящей пары таблиц автоматически создается статистика корреляции в форме индексированных представлений. Когда оптимизатор запросов SQL Server может извлечь пользу из сведений о корреляции двух столбцов datetime, он использует статистику корреляции в плане запроса. Статистика корреляции также включается в логику работы инструкций INSERT, UPDATE и DELETE. Имя статистики корреляции имеет следующую форму:

_MPStats_Sys_<constraint_object_id>_<GUID>_
<FK_constraint_name>

<FK_constraint_name> — это имя внешнего ключа в представлении каталога sys.objects, на котором основано соответствие столбцов datetime. <constraint_object_id> — это восьмиразрядное представление идентификатора objectid ограничения внешнего ключа.

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

DateCorrelationOptimization="true"

Например, узел <Predicate> под влиянием статистики корреляции примет следующий вид:

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

При включении параметра DATE_CORRELATION_OPTIMIZATION базы данных SQL Server создает статистику корреляции для всех подходящих пар столбцов datetime. Кроме того, SQL Server создает дополнительную статистику корреляции в следующих случаях:

• При создании ограничения внешнего ключа при помощи инструкций CREATE TABLE или ALTER TABLE, удовлетворяющего требованиям к оптимизации корреляции столбцов datetime.

• При создании кластерного индекса по столбцу datetime, который коррелирует со столбцом datetime в другой таблице.

ПримечаниеСтатистика корреляции не формируется, если кластерный индекс создается с параметром ONLINE = ON. Тем не менее после подтверждения создания индекса статистика корреляции, которая зависит от индекса, может быть построена в ответ на событие в другой транзакции, например в результате создания ограничения внешнего ключа.

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

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

Статистика корреляции удаляется в следующих ситуациях:

• При выключении параметра DATE_CORRELATION_OPTIMIZATION вся статистика корреляции, созданная SQL Server, удаляется.

• Если статистика корреляции занимает слишком много места или не улучшает производительность, она удаляется.

• При удалении ограничения внешнего ключа инструкциями DROP TABLE или ALTER TABLE удаляется статистика корреляции, связанная с этим ограничением.

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

• При выполнении инструкции ALTER TABLE...SWITCH удаляется статистика корреляции, созданная для исходной или целевой таблицы.

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

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

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

Статистика корреляции создается в ходе той же транзакции, которая привела к ее созданию или удалению. Эта транзакция не является ни оперативной, ни асинхронной.

При простой настройке отдельного рабочего сервера помощник по настройке ядра базы данных учитывает затраты и преимущества статистики корреляции. Однако в тестовой среде помощник по настройке ядра базы данных не считает статистику корреляции внутренним объектом системы. Таким образом, статистика корреляции не используется при оптимизации запросов помощником по настройке ядра базы данных во время анализа индексов. В тестовой среде можно пропускать любые рекомендации, которые помощник по настройке ядра базы данных делает относительно индексированных представлений, содержащих статистику корреляции, поскольку он принимает во внимание затраты на ее сбор, но не учитывает преимущества. В обоих случаях помощник по настройке ядра СУБД может не порекомендовать выбор определенных индексов, например кластерных индексов по столбцам datetime, которые могут увеличить производительность при включении параметра DATE_CORRELATION_OPTIMIZATION.

Запрос метаданных о статистике корреляции

Значение параметра базы данных DATE_CORRELATION_OPTIMIZATION хранится в столбце is_date_correlation_on представления каталога sys.databases (TransactSQL).

Чтобы определить, основано ли представление на статистике корреляции, выберите столбец is_date_correlation_view представления каталога sys.views.SQL Server сокращает часть FK_constraint_ имени статистики корреляции, если имя превышает ограничение на длину идентификаторов.

(Возврат на основную страницу)