Новые возможности MS SQL Server 2004 "Yukon"
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors}
Прежде всего стоит обратить внимание на то, что изменился синтаксис указания дополнительных настроек. Теперь рекомендуется параметры ON или OFF указывать в обязательном порядке, а старый синтаксис, без ON/OFF, поддерживается лишь из соображений обратной совместимости. В будущих версиях от его поддержки обещают отказаться. При этом новые команды поддерживают только синтаксис с ON/OFF. Так же недопустимо смешивать два различных синтаксиса в одном операторе, например попытка создания индекса с опциями WITH (DROP_EXISTING, ONLINE = ON ) – вызовет ошибку.
Ряд настроек индекса, которые ранее задавались с помощью специальной хранимой процедуры, теперь можно указать напрямую, при создании или изменении индекса. ALLOW_ROW_LOCKS разрешает или запрещает блокировку индекса на уровне строк, а ALLOW_PAGE_LOCKS – на уровне страниц данных.
MAXDOP
MAXDOP (max degree of parallelism) – максимальное количество процессоров, используемых при построении плана выполнения запроса. В предыдущих версиях задать этот параметр напрямую при работе с индексами было нельзя – использовались настройки для всей системы, задаваемые через системную хранимую процедуру sp_configure. Теперь же этот параметр можно указать отдельно для каждого индекса. Здесь имеется в виду количество процессоров, которое будет использоваться непосредственно при создании или изменении индекса, а не при последующей работе с ним. Что называется, «пустячок, а приятно» ;)
Index include
В команде создания индекса появился параметр “INCLUDE”. Он позволяет задействовать новую, достаточно полезную функциональность, но для того, чтобы показать, что это такое, лучше начать немного издалека.
В Microsoft SQL Server индекс представляет собой B+tree, узлы которого состоят из ключевых полей, а в листьях (узлах самого последнего уровня) содержатся ссылки на записи таблицы.
Индекс может быть двух типов, кластерный (clustered) и не кластерный.
Кластерный индекс отличается от некластерного тем, что в листьях этого индекса содержатся не ссылки на записи в таблице, а сами записи. Таким образом, при наличии кластерного индекса записи в таблице выстраиваются в порядке ключей такого индекса (строго говоря это не совсем так, но в первом приближении верно). По очевидным причинам кластерный индекс может быть только один на таблицу. Идентификация конкретной записи в таблице также находится в прямой зависимости от наличия кластерного индекса. Если кластерного индекса нет, то запись находится по уникальному идентификатору записи – RID, который однозначно определяет ее положение в файле данных. Если же кластерный индекс в таблице присутствует, то физическое место этой записи не постоянно, а, следовательно, использовать RID не очень практично, поскольку его пришлось бы обновлять во всех индексах при каждом изменении. Поэтому запись идентифицируется по ключу кластерного индекса.
Допустим, необходимо выбрать поле одной из записей в таблице по какому-нибудь условию. В этом случае, в зависимости от наличия индексов, сервер может поступить несколькими способами.
Поле, которое надо выбрать, совпадает с полем, по которому нужно осуществить поиск, и по этому полю построен индекс.
| SELECT LastName FROM employees WHERE LastName = 'Callahan' |
План такого запроса прост и незатейлив:
|
|--Index Seek(OBJECT:([Employees].[LastName]), SEEK:([Employees].[LastName]=Convert([@1])) ORDERED FORWARD) |
Нужное значение просто находится по индексу.
Поле, которое необходимо просмотреть, не совпадает с полем, по которому нужно искать, но при этом по полю поиска построен кластерный индекс.
| SELECT LastName FROM Employees WHERE EmployeeID = 8 |
План такого запроса также не отличается излишней сложностью:
|
|--Clustered Index Seek(OBJECT:([Employees].[PK_Employees]), SEEK:([Employees].[EmployeeID]=Convert([@1])) ORDERED FORWARD) |
Все закономерно – идет поиск по кластерному индексу, а затем извлекается нужное поле из этого индекса.
Поле, которое надо достать из таблицы, не совпадает с полем, по которому производится поиск, и по полю поиска построен самый обычный индекс.
| SELECT LastName FROM Employees WHERE PostalCode = '98105' |
Вот здесь уже серверу приходится совершать дополнительные телодвижения, и план запроса немного усложняется:
|
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Employees])) |--Index Seek(OBJECT:([Employees].[PostalCode]), SEEK:([Employees].[PostalCode]=Convert([@1])) ORDERED FORWARD) |
Сначала по индексу находится нужная запись, а точнее, не запись, а ссылка на запись. При этом значения поля, которое на самом деле надо достать из таблицы, по-прежнему нет, так как поле поиска не является нужным полем, как это было в первом случае. На данный момент у сервера, как уже говорилось, есть только ссылка, и чтобы извлечь нужное поле, надо выполнить еще одну операцию – bookmark lookup. Стоимость этой операции в некоторых случаях может быть очень высока, например, в этом запросе она составляет половину всей его стоимости (49%). В случае с кластерным индексом такого не происходит, потому что в листьях кластерного индекса содержится вся запись, а значит, ничего искать уже не надо.
|
ПРИМЕЧАНИЕ Естественно, в реальных задачах, планы выполнения запросов могут быть гораздо более сложными. Многое зависит от селективности индексов, статистики, доступной памяти и других факторов, но рассмотрение этих высоких материй выходят за рамки данной статьи. |
В силу того, что стоимость дополнительной операции по извлечению полей, не входящих в индекс, может быть довольно высока, то иногда приходится от нее избавляться. В предыдущих версиях Microsoft SQL Server был, фактически, только один способ избавится от дорогого bookmark lookup. Для этого строился составной (композитный) индекс, первым полем или полями которого являлись поля, входящие в условие поиска, а затем шли поля, которые необходимо было извлечь. Поскольку в этом случае все нужные значения уже содержатся в ключе индекса, то потребность в дополнительных операциях отпадает. Но при подобном подходе вырастает размер ключей индекса, из-за этого увеличивается размер базы и снижается эффективность индексных операций. Вдобавок, максимальный размер ключа индекса не может превышать 900 байт, и вылезти за эти границы довольно просто.
Для решения этой проблемы в Yukon была добавлена новая функциональность. Теперь индексы могут содержать добавленные поля (include). Суть этого нововведения заключается в том, что в индекс могут быть добавлены не ключевые поля, при этом поиск по ним не осуществляется, но если они понадобятся в запросе, то дополнительных усилий по их извлечению предпринимать не приходится.
Синтаксис довольно прост, при создании или изменении индекса добавляется дополнительный параметр:
| INCLUDE(field[, field...]) |
Здесь field – список неключевых полей таблицы, которые должны быть добавлены в индекс.
При этом сам индекс состоит только из значений ключевых полей, но в листьевые узлы, и только в листьевые, добавляется копия полей, указанных в INCLUDE. Таким образом, поиск остается таким же эффективным, и отсутствуют все ограничения на размер полей, указанных в качестве включаемых, вплоть до того, что там могут находиться даже LOB. При этом индекс занимает меньше места по сравнению с обычным, составным, применявшимся ранее в подобных случаях. И хотя, на первый взгляд, экономия места за счет нелистьевых узлов кажется незначительной – это довольно серьезный плюс, в силу того, что одно из свойств деревьев заключается в прямой зависимости эффективности поиска от размера ключа. Чем меньше размер ключа, тем эффективнее поиск.
Если создать еще один индекс в таблице из предыдущего примера (по полю PostalCode), но добавить туда в качестве включаемого поля LastName, а потом опять сделать выборку LastName по определенному значению PostalCode:
|
CREATE INDEX IXPostalCode_inc ON Employees(PostalCode) INCLUDE(LastName) GO SELECT LastName FROM Employees WHERE PostalCode = '98105' |
то план запроса снова станет простым и незатейливым, а стоимость – такой же низкой, как и при использовании составного индекса.
|
|--Index Seek(OBJECT:([Employees].[IXPostalCode_inc]), SEEK:([Employees].[PostalCode]=@1) ORDERED FORWARD) |
При этом для поиска используется только что созданный индекс IXPostalCode_inc. Если сейчас попробовать извлечь другое поле той же записи по тому же критерию PostalCode, то будет использоваться обычный индекс, и снова потребуется bookmark lookup.
Посмотрев индексы, построенные по полям таблицы Employees с помощью хорошо известной хранимой процедуры sp_helpindex, можно заметить, что обычный индекс (по PostalCode), и только что построенный индекс (с включаемыми полями) ничем друг от друга не отличаются.
| index_name | index_description | index_keys |
| IXPostalCode_inc | nonclustered located on PRIMARY | PostalCode |
| PostalCode | nonclustered located on PRIMARY | PostalCode |
Более того, даже с помощью устаревшей системной таблицы sysindexes (что неудивительно), и новой sys.indexes (что странно) невозможно найти отличия. И лишь вызвав специальную функцию, дающую расширенную информацию об индексах – fn_indexinfo(...), можно заметить, что индекс IXPostalCode_inc занимает больше места.
| IndexName | IndexType | Rows | MinimumRecordSize | MaximumRecordSize | AverageRecordSize |
| PostalCode | Nonclustered Index | 9 | 22 | 26 | 23.777 |
| IXPostalCode_inc | Nonclustered Index | 9 | 36 | 46 | 40 |
Как уже говорилось, в качестве включаемых полей могут выступать поля любых типов данных, но, по очевидным причинам, в кластерный индекс добавлять не ключевые поля нельзя. Это просто не имеет смысла, так как если проводить аналогию, то в кластерном индексе вся запись является не ключевым полем. Очевидно также, что вставка записей при наличии такого индекса будет дороже, поскольку необходимо делать копию включаемого поля в листьевые узлы индекса. Однако полезных свойств у данного нововведения, безусловно больше, чем недостатков и, судя по всему, оно окажется весьма полезным.
ONLINE
Если в предыдущих версиях Microsoft SQL Server DDL-операции с индексами вызывали блокировку всей таблицы, то теперь индексы могут быть созданы, изменены и удалены без блокирования других операций с данными (online). Например, если один пользователь перестраивает кластерный индекс, то другие могут продолжать изменять и читать данные, по которым этот индекс перестраивается. Эта функциональность может оказаться очень полезной приложениям, которые работают по принципу 24x7, уменьшая время недоступности системы из-за административного обслуживания.
Опция ONLINE может быть установлена для следующих команд:
CREATE INDEX
ALTER INDEX
DROP INDEX
ALTER TABLE (при удалении или изменении UNIQUE или PRIMARY KEY ограничений (constraints))
Чтобы иметь возможность работать с данными во время выполнения DDL операций с индексами, построенными по этим данным, используются следующие временные структуры:
«исходная структура» (source) – это оригинальная таблица или кластерный индекс.
«исходный индекс» (preexisting indexes) – любой индекс, построенный по данным источника. Эта структура доступна параллельным процессам при выборке, вставке, изменении и удалении данных, в том числе для пакетных операций (bulk) и проверки ограничений контроля целостности (referential integrity constraints). Исходный индекс может быть выбран оптимизатором или даже явно указан в запросе.
«конечная структура» (target) – это новый индекс или набор индексов, который создается или перестраивается. Все запросы к исходной структуре, изменяющие данные, автоматически применяются сервером и к конечной структуре. Эта структура не используется для поиска значений до тех пор, пока операция изменения или создания нового индекса не будет зафиксирована, внутри сервера она помечается «только для записи» (write only).
«временный индекс» (temporary mapping index) – эта структура создается только при выполнении online-операций с кластерными индексами. Она используется для определения записей, которые надо удалить из нового индекса, когда во время работы с ним удаляются или изменяются данные в исходной таблице. Этот некластерный индекс создается на том же шаге, что и новый кластерный индекс, и все изменения исходных данных также применяются и к временному индексу.
Например, если происходит ONLINE-операция по перестроению кластерного индекса и четырех некластерных, ассоциированных с ним, то существует одна исходная структура (оригинальный кластерный индекс), пять предварительных индексов (четыре некластерных и один кластерный) и одна конечная структура (конечный кластерный индекс). При перестроении кластерного индекса, некластерные, ассоциированные с ним, не перестраиваются.
Во время выполнения индексных операций на исходной таблице удерживается блокировка IS (Intent Share), также, на некоторых стадиях, на короткое время накладываются блокировки S (Share) и Sch-M (Schema Modification).
Процесс выполнения не блокирующих операций с индексами может быть разделен на три фазы: подготовка, модификация и завершение.
| Фаза | Описание | Блокировки исходных данных |
| Подготовка | Подготовка метаданных для новой структуры. Параллельные операции записи данных блокируются на короткое время. Создается новая структура и помечается как write-only. | S (Shared) на таблицу. IS (Intent Shared) на таблицу. INDEX_BUILD_INTERNAL_RESOURCE. |
| МодификацияОсновная фаза | Данные сканируются, сортируются, перестраиваются и вставляются в новую структуру пакетными операциями (bulk insert). Параллельные операции вставки, изменения и удаления применятся и к исходным структурам, и к создающимся. Выборка происходит с использованием исходных структур. | IS (Intent Shared) на таблицу. INDEX_BUILD_INTERNAL_RESOURCE |
| Завершение | Прежде чем начнется эта фаза, все незафиксированные изменения исходных данных должны быть завершены. Все новые транзакции, работающие с исходной таблицей, блокируются на короткое время, до завершения этой фазы. В системных метаданных исходная структура заменяется только что созданной конечной. В случае необходимости исходная структура удаляется. | IS (Intent Shared) на таблицу. INDEX_BUILD_INTERNAL_RESOURCE. S (Shared) на таблицу после добавления некластерного индекса. SCH-M (Schema Modification) на таблицу после изменения любого индекса. |
Операции с индексом ожидают завершения всех незафиксированных транзакций, прежде чем наложить коллективную (S) блокировку или блокировку изменения метаданных (Sch-m).
Блокировка INDEX_BUILD_INTERNAL_RESOURCE предотвращает параллельные DDL-операции над исходной таблицей во время работы с индексами. Обычная пользовательская активность при этом не блокируется.
В случае перестроения кластерного и некластерного индексов за одну операцию (это может происходить при создании кластерного индекса по таблице, в которой уже существуют некластерные индексы), во время основной фазы на исходную таблицу могут накладываться кратковременные коллективные (S) блокировки. Это необходимо для корректного переключения работы с кластерного индекса на некластерные. После завершения переключения блокировка опять понижается до блокировки намерения (IS).
|
ПРИМЕЧАНИЕ В доступной на данный момент версии Yukon работа с индексами не может быть Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.),
обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus.
Помогаем в публикации. Правки вносим бесплатно.
Похожие рефераты: |