#029<<#030

СОВЕТ

Эффективность использования индексов
По материалам статьи Steve Jones на SWYNK.COM «SQL Server – Indexes and Performance».

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

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

ОБРАТИТЕ ВНИМАНИЕ: Автор рассматривает некоторые особенности SQL 2000, но эти методы, с незначительными модификациями, полезны и для SQL v6.5/v7.

Что случится, когда Ваши базы вырастут?

SQL сервер имеет свойство автоматического обновления статистики, обучаемый оптимизатор запросов, и т.п. Когда Вы строите индекс по таблице (давайте рассматривать кластерный индекс), SQL сервер разбивает пакеты данных на страницы и экстенты. С v7.x и выше, экстенты могут быть разделяемыми между объектами (экстенты v6.5, принадлежат только одному объекту). В результате, давайте предполагать, что Вы создаете таблицу, строки которой располагаются в нескольких страницах. К примеру, Вы имеете 20 строк, размер которых соответствует 5 страницам данных. Будут ли Ваши данные занимать 5 страниц? Только, если FILLFACTOR установлен в 100%. Fillfactor определяет, на сколько процентов заполнены ваши страницы. Давайте принимать FILLFACTOR равным 50%, тогда Вы имели бы 10 страниц данных, занимаемых этой таблицей. Через какое-то время, данные могут вырасти до 100 страниц. Они (как минимум) займут 7 экстентов, если эти экстенты не разделяются с другими объектами. Каждая страница экстента связывается со следующей страницей специальным указателем. Следующая страница в цепочке, однако, не обязательно будет в том же самом экстенте. Поэтому, при чтении страниц, мы можем быть "переключены" к другому экстенту.

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

Экстент 1	Экстент 2
Страницы n	Страница n + 1
Страница n + 2

В этом примере показаны три страницы, где страница n связана со страницей n+1, а та, в свою очередь, связана со страницей n+2 и так далее. Чтобы прочитать эти три страницы, мы читаем экстент 1, затем переключаем к экстенту 2, затем переключаем назад к экстенту 1. Эти действия не обязательно влекут за собой физический I/O, но все эти операции складываются. Они могут быть не заметны на Вашем локальном сервере или даже слегка загруженном сервере. Зато WEB приложения, которое имеют тысячи пользователей, могут столкнутся с большой загрузкой сервера из за частых сканирований этой таблицы. Почему нормальные в начале своего использования таблицы начинают так себя вести? Потому, что при их разработке не учитывались особенности их функционирования через какое-то время. SQL сервер распределит место для каждой строки в доступном в то время пространстве. В результате, в то время как кластерный индекс должен хранить данные в страницах в физическом порядке, сами страницы не будут располагаться в физическом порядке. Вместо этого, каждая страница будет расположена далеко от следующей в цепочке страницы. Эффект, аналогичный фрагментации жёсткого диска, будет наблюдаться и при распределении страниц таблицы, которые могут быть фрагментированы через какое-то время в заполненных данными экстентах.

SQL сервер не перестраивает индексы автоматически. Программисты из Redmond оставили это для DBA, который должен отслеживать фрагментацию данных и перестраивать индексы по мере необходимости. Как бороться с фрагментацией читайте ниже.

Использование DBCC SHOWCONTIG

До появления SQL 2000 Вы должны были сначала получить ID объекта, используя команду:

select object_id('<object name>')

Для пользовательской таблицы выполняется:

select object_id('user')

Возвращаемым результатом является длинный номер (из sysobjects, например 2030630277) который очень неудобен для дальнейшего использования. Видимо вследствие жалоб потребителей, в SQL 2000 Вы можете использовать имя объекта в запросе dbcc showcontig. Например:

dbcc showcontig (user)

Это выдаст следующую статистику по вашим индексам:

DBCC SHOWCONTIG scanning 'User' table...
Table:'User' (962102468); index ID: 1, database ID: 7
TABLE level scan performed.
-Pages Scanned................................: 899
-Extents Scanned..............................: 121
-Extent Switches..............................: 897
-Avg. Pages per Extent........................: 7.4
-Scan Density [Best Count:Actual Count].......: 12.58% [113:898]
-Logical Scan Fragmentation ..................: 99.89%
-Extent Scan Fragmentation ...................: 80.99%
-Avg. Bytes Free per Page.....................: 2606.5
-Avg. Page Density (full).....................: 67.80%

Давайте проанализируем этот отчёт:

Pages Scanned – Количество физических страницы базы данных, принадлежащих этому индексу. Дает Вам полный размер занятых этим индексом страниц (каждая страница - 8КБ);

Extents scanned - экстент - 8 страниц. Pages Scanned / 8. В этом примере мы имеем 121 экстентов, которые содержат 968 страниц. Так как индекс занимает только 899 страниц, мы делаем вывод, что ряд экстентов разделяемы между другими объектами. Это не всегда плохо, но это может натолкнуть Вас на мысль, что присутствует небольшая фрагментация данных. Конечно, Вы не знаете точный процент физической фрагментации, являющейся причиной увеличения времени исполнения запросов. Минимальное количество экстентов, которые могли бы быть заняты, для 899 страниц было бы 113 (899/8).

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

Average Pages/Extent – Арифметическое деление Pages Scanned / Extents Scanned. Если бы экстенты были полностью заполненные, в результате было бы 8.

Scan Density [Best Count:Actual Count].......: 12.58% [113:898]

Показывает процент идеальности размещения страниц. В идеале должно было быть 113, но мы имеем 898, что составляет 12.58% от идеального. Здесь мы имеем обратное представление фрагментации. Чем ближе результат к 100%, тем меньше фрагментация. Вы можете видеть, что эта таблица довольно сильно фрагментирована. Сканирование постоянно использует переключение вперед и назад от одного экстента к другому, вместо использования только ссылки одной страницы на следующую в пределах экстента.

Logical Scan Fragmentation ..................: 99.89 %

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

Extent Scan Fragmentation ...................: 80.99 %

Показывает процент страниц, для которых следующая страница в индексе не расположена рядом (физически) с текущей страницей. Это говорит о том, что система I/O будет загружена на 80 % времени просмотра индекса, для поиска следующей страницы.

Avg. Bytes Free per Page.....................: 2606.5

Среднее число байт, свободных на странице. Так как страница - 8096 байтов, для нашего примера заполнены приблизительно 68 % страниц. Это может быть как хорошо, так и плохо. Если это OLTP система с частыми вставками в таблицу, то большое количество свободного пространства в странице снижает вероятность разбиения страниц, что может существенно увеличить периодичность перестройки индексов. Если это OLAP система, то предпочтительнее иметь этот значение ближе к нолю, так как здесь желательно при чтении захватывать настолько большое количество данных, насколько это возможно для каждой страницы (чтобы уменьшить время сканирования индекса).

Avg. Page Density (full).....................: 67.80%

Это процентное значение основано на результате предыдущего параметра 2606.5 и разделённого на 8096 (размер страницы). Большой процент говорит о высокой фрагментации. Т.е. имеется большое количество переключений между экстентами, что вызывает большую утилизацию I/O при использовании индекса.

Дефрагментация индексов

Во всех версиях сервера Вы можете перестроить кластерный индекс, что заставит сервер заново разместить данные в экстентах и страницах, которые будут расположены в физическом порядке и уменьшать, таким образом, фрагментацию. Но в SQL 2000 есть другой путь! Добавлена новая опция DBCC, которая называется INDEXDEFRAG. С помощью её можно дефрагментировать и кластерные и не кластерные индексы, что (согласно BOOKS ONLINE) должно улучшить эффективность, поскольку физический порядок будет соответствовать логическому порядку и (теоретически) уменьшится I/O при просмотре индекса. Если Вы разместили индексы в отдельных файлах, то будет выполнена дефрагментация каждого файла в отдельности, и страницы между файлами перемещаться не будут. Это не удобно, когда Вы добавили новую filegroup и разрешили объектам размещаться в новых файлах. При выполнении дефрагментации, пользователю выводится графическая полоса продвижения процесса.

Ещё одним дополнением является возможность остановить процесс исполнения DBCC команды.

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

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

ГОТОВИМСЯ К ТЕСТУ ПО 70-028

ШПАРГАЛКА #7> Продолжение (обзор официального курса Microsoft)
Архив шпаргалок Вы найдёте на следующих сайтах:
http://mssqlhelp.com.ru
http://subscribe.ru/archive/comp.soft.winsoft.sqlhelpyouself

Каждый DBA может существенно упростить свою работу, если освоит не сложные методы автоматизации рутинных, повседневных задач администрирования, выполняемых SQL сервером. Многие задачи (резервирование, импорт/экспорт) можно заставить сервер выполнять самостоятельно по установленному Вами расписанию. Возможные неполадки также могут устранятся сервером самостоятельно, в соответствии с заданной Вами схемой. Так, сервер может превентивно реагировать на события (например ошибки) выполняя заданные операции или устанавливая более подходящие для сложившейся ситуации режимы своей работы. Например, при появлении ошибки 9002 (переполнение журнала) можно настроить так реакцию сервера, что будет выполнятся оператор, очищающий журнал. Или другой пример, при обнаружении нежелательных блокировок можно автоматически отправлять уведомление администратору.

Всё, что вам нужно из компонент сервера для облегчения своих обязанностей, это SQL Server Agent, сам MSSQLServer и EventLog от NT. Разберём, для начала, наиболее полезную компоненту сервера. Сфера её владений, это оповещения, расписания заданий и операторы. Причём задания и оповещения отделены друг от друга и выполняются независимо. Такой подход позволяет выбирать любой из этих компонентов в качестве реакции на событие, особенно, если один из них недоступен. Сервис SQL Server Agent, как и любая другая служба NT регистрируется в системном журнале операционки и ведёт там записи о событиях сервера баз данных. После запуска, SQL Server Agent начинает сопоставлять события с заданиями и оповещениями (содержащимися в кэше) и на основе этого выбирает действия в качестве реакции на заданные администратором события. Операционкой журналируются все ошибки SQL сервера с уровнями от 19 по 25, а также, могут записываться в журналы записи, порождаемые системными хранимыми процедурами sp_addmessage и sp_altermessage. Кроме того, возможность записывать в журнал приложений Windows NT имеет оператор RAISERROR WITH LOG и расширенная хранимая процедура xp_logevent.

Сервис SQL Server Agent может запускаться, как от имени LocalSystem, так и от имени учётных записей. Разумеется, это влияет на то, какой набор возможностей будет представлен для SQL Server Agent. К примеру, в SQL SEM можно настроить автоматический рестарт этой службы в случае зависания, но это возможно, только если она стартует от имени учётной записи, которая входит в локальную группу администраторов NT. Давайте рассмотрим, как будут меняться возможности SQL Server Agent при запуске её от разных учётных записей.

Для того, что бы служба SQL Server Agent могла работать с сервером баз данных необходимо, что бы учётная запись, от имени которой она стартует, была отражена в роли сервера sysadmin. LocalSystem – это фактически то же самое, что группа Administrators локального NT сервера, что автоматически делает её членом роли sysadmin. Поскольку используется локальная учётная запись, доступ к сетевым ресурсам для SQL Server Agent будет под вопросом. Если Вы посчитали, что администраторские права на локальный сервер для SQL Server Agent – это слишком много, можете запустить его от имени локальной учётной записи. Но тогда позаботьтесь о том, что бы эта учётная запись была включена в роль sysadmin, напрямую или через отображённую на эту роль локальную группу сервера Windows NT. Опять же, сетевые ресурсы, если они на доступны всем подряд, будут для службы недоступны. Для того, что бы SQL Server Agent мог использовать сетевые ресурсы, удобно использовать для его запуска учётную запись пользователя домена. Особенно часто это используется при подключении к почтовым серверам. Для такой учётной записи нужно также обеспечить её присутствие в роли sysadmin.

Если вы правильно настроили запуск SQL Server Agent, после этого она будет пытаться подключиться к серверу баз данных. При подключении будут использоваться механизмы проверки подлинности либо NT (по умолчанию), либо SQL сервера. При механизме NT, для проверки подлинности используется та учётная запись, от имени которой стартует SQL Server Agent.

Продолжение следует

ПОЛЕЗНОСТИ

Статья В.З. Шнитман, на www.osp.ru «Серверы баз данных: проблемы оценки конфигурации системы».
http://www.infocity.kiev.ua/db/content/db014.phtml

Новые технические статьи Microsoft:

Q259403 - Vcredist.exe Installs the Latest Run-Time Components for Visual C++ Applications
http://support.microsoft.com/support/kb/articles/Q259/4/03.ASP
"Introducing the UDDI Software Development Kit."
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/Progguide.htm
"XML Wrapper Template: Transform XML Documents into Visual Basic Classes." The XML Wrapper template described in this article transforms XML documents into VB classes, hiding the more complex parts of using the Microsoft XML parser. Even developers who have little knowledge of XML or the Microsoft XML parser can use classes created with the template.
http://msdn.microsoft.com/msdnmag/issues/01/01/XMLWrap/XMLWrap.asp
"Microsoft Tahoe as a Collaborative Solution Platform." Tahoe (in beta) integrates a flexible Web portal based on Microsoft Digital Dashboard technology, content indexing and search, document management, and a collaborative applications platform. This article looks at the ways you can use Microsoft Web Storage System and the Tahoe SDK to extend and enhance Tahoe.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/tahoe.htm
"Developing Applications for the Web Storage System and Outlook Web Access." This one demonstrates certain methods of using XML, XMLHTTP, and CDO.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/WSSdevaccess.htm

#029<<#030