Новые возможности MS SQL Server 2004 "Yukon"

Новые возможности MS SQL Server 2004 "Yukon"

Иван Бодягин

Введение

Описать более-менее подробно все возможности новой версии Microsoft SQL Server задача не тривиальная, поэтому в данной статье предложен лишь небольшой обзор некоторых нововведений. А именно представления метаданных, схем, немного о безопасности, новые возможности при работе с индексами и новые встроенные типы данных. Я не ставил перед собой цели раскопать все в подробностях, поскольку на данный момент доступна лишь первая предварительная версия сервера и многое может измениться, но основная функциональность, очевидно, останется, поэтому ее и имеет смысл рассмотреть.

Метаданные и безопасность

Одно из достаточно серьезных изменений в сервере касается метаданных. Ранее эта информация хранилась в нескольких системных табличках, и была довольно незатейливо структурирована, теперь же все стало несколько сложнее, но в то же время строже и логичнее. Вообще сейчас об этой части сервера сложно говорить что-либо наверняка, так как, судя по всему, часть заявленной функциональности еще не реализована, а часть ожидают довольно серьезные изменения. Главы в Books On-Line, относящиеся к безопасности, на данный момент попросту отсутствуют, но уже можно разглядеть направление дальнейшего развития и даже кое-что потрогать руками.

В предыдущих версиях сервера для обслуживания и тонкой настройки, как правило, использовались специальные системные хранимые процедуры. Теперь же вся эта функциональность вносятся в T-SQL, посредством создания новых DDL операторов или небольшого изменения старых. От системных же процедур в будущих версиях, судя по всему, откажутся. Функции диагностики, сбора статистики и просмотра прочей административной информации, также переходят от системных процедур и DBCC команд к специальным системным функциям.

Безопасность

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

Row level security

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

Работа с логинами и пользователями

Хранимые процедуры для создания пользователя и логина объявлены устаревшими и оставлены исключительно для обратной совместимости, на смену им пришли новые DDL операторы – CREATE USER, CREATE LOGIN, ALTER USER и ALTER LOGIN, которые предоставляют больше возможностей.

В Yukon можно применить политику логинов операционной системы к логинам SQL-сервера. При этом для контроля согласованности политик безопасности ОС и SQL-сервера используется специальное API, появившееся в Windows 2003 Server. При создании или изменении логина может быть выставлено два флага, CHECK_EXPIRATION и CHECK_POLICY, которые и определяют вмешательство ОС в политику логинов сервера.

Значение флага CHECK_EXPIRATION (по умолчанию ON) определяет, будет ли происходить проверка устаревания пароля. Установка этого флага в “OFF” означает, что проверка не производится, и пароль не устаревает.

Значение флага CHECK_POLICY (по умолчанию ON) определяет, будет ли производиться проверка стойкости пароля с использованием локальной политики ОС. Установка этого флага в “OFF” означает, что локальная политика ОС не используется, и действует внутренняя политика СУБД.

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

Если флаг CHECK_POLICY установлен в «OFF», то и CHECK_EXPIRATION так же должен быть установлен в «OFF». Если указан параметр MUST_CHANGE, то флаг CHECK_EXPIRATION должен быть установлен в «ON», а, следовательно, и CHECK_POLICY также должен быть «ON».

Естественно, все эти настройки применимы только к локальным пользователям SQL-сервера.

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

CREATE LOGIN Vasya WITH PASSWORD = 'password'

то ничего не получится, поскольку по умолчанию флаг CHECK_POLICY установлен в «ON», ОС проверяет пароль на стойкость и можно наблюдать ошибку 15118, примерно следующего содержания:

Password validation failed. The password does not meet policy requirements because it is not complex enough.

Однако если проверку политики безопасности ОС отключить, то создание логина Vasya с простым и незатейливым паролем пройдет вполне успешно:

CREATE LOGIN Vasya WITH PASSWORD = 'password', CHECK_POLICY = OFF

Схемы

По стандарту ANSI SQL под понятием схема (schema) понимается набор объектов БД, принадлежащий одному владельцу (principal) и образующий одно пространство имен (namespace). Иными словами схема – это набор объектов БД, которые не могут иметь одинаковые имена.

В предыдущих версиях SQL-сервера схема была непосредственно связана с владельцем объекта. Фактически между этими двумя понятиями для пользователя не было разницы. Каждый пользователь был владельцем схемы, и имя этой схемы совпадало с именем пользователя. Специальная команда создания схемы – CREATE SCHEMA, строго говоря, схему не создавала, а позволяла создать объект и раздать на него права одним оператором, облегчая тем самым жизнь администраторам.

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

avalon.employee.vasya.account

Таким образом, если в какой-то трагичный момент пользователя Vasya уволят, то для его удаления из базы надо либо удалить все объекты, принадлежащие ему, либо передать их во владение другому пользователю. Если передать эти объекты во владение кому-то другому, например пользователю Masha, то изменится и полное имя объекта:

avalon.employee.masha.account

Это потребует внесения изменений в клиентское приложение и дальнейшего тестирования – приятного в этом мало.

В новой версии Microsoft SQL Server эти два понятия (схема и ее владелец) отделены друг от друга, и поменять владельца схемы можно без изменения полного имени объекта. Очевидно, что пример с Васей и Машей несколько надуман, но, тем не менее, подобное разделение позволит более свободно и логично группировать объекты в БД по пространствам имен, серьезно повышая удобство разработки.

Более того, для этой же цели введено новое понятие синонима. Синоним создается с помощью нового оператора CREATE SYNONYM и является альтернативным именем объекта БД. Объект, на который ссылается синоним, называется «базовым объектом» (base object), и с этим базовым объектом синоним связан только по имени. Таким образом, клиентское приложение, использующее синонимы, защищено от изменения имен объектов. Кроме того, синоним, состоящий из одного слова, удобнее использовать, чем полное имя объекта, состоящего из двух, трех или четырех частей. Например, создание синонима для Employee в базе AdventureWorks для использования из Northwind выглядит примерно так:

USE Northwind GO CREATE SYNONYM MyEmployee FOR AdventureWorks.dbo.Employee

Сам синоним принадлежит схеме, таким образом, нельзя создать два одинаковых синонима в одной схеме.

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

Введено также понятие «схемы по умолчанию» (default schema). Эта схема указывается при создании пользователя или логина, и если пользователь ищет объект без указания определенной схемы, то в первую очередь объект ищется в схеме по умолчанию. Если же при создании пользователя схема по умолчанию не была указана, то используется схема DBO. При создании пользователя можно также указать несуществующую схему и создать ее позднее.

Метаданные

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

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

Например, все объекты ранее были доступны через системную таблицу sysobjects, а теперь эта информация переехала в представление sys.objects. Sysobject теперь – тоже представление, которое делает выборку из sys.objects. Но поскольку часть информации в формате sysobjects отобразить невозможно, то даже выборка всех данных из sys.object и sysobjects вернет разное количество записей.

Системные процедуры также переписаны с использованием новых системных представлений.

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

Например, если создать простенькую процедуру в тестовой базе:

CREATE PROCEDURE tst_sel AS SELECT * FROM employee GO

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

SELECT * FROM sys.procedures WHERE name='tst_sel'

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

GRANT EXECUTE ON tst_sel TO vasya

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

SELECT definition FROM sys.sql_modules WHERE name = 'tst_sel'

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

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

Чтобы избежать этих проблем, а также для большей гибкости при настройке прав просмотра метаданных, в Yukon добавлено новое право – VIEW DEFINITION. Это право перекрывает правила, описанные выше. Если предоставить пользователю Vasya право VIEW DEFINITION на объект, то ему будут доступны для просмотра все метаданные этого объекта, не взирая на остальные права, если же это право явно запретить, то никакие метаданные посмотреть уже будет нельзя, опять-таки не взирая на остальные права.

Права VIEW DEFINITION могут быть применены к объектам, расположенным на разных уровнях иерархии сервера.

-- На уровне базы данных GRANT VIEW DEFINITION TO <principal> [WITH GRANT OPTION]; -- На уровне схемы GRANT VIEW DEFINITION ON SCHEMA :: <schema> TO <principal> [WITH GRANT OPTION]; -- На уровне определенного объекта схемы GRANT VIEW DEFINITION ON <object> TO <principal> [WITH GRANT OPTION]; -- Здесь <principal> ::= <user> | <role> | PUBLIC <object> ::= <table name> | <view name> | <function name> | <procedure name> | ...

Таким образом, если сейчас разрешить пользователю Vasya VIEW DEFINITION на уровне базы:

GRANT VIEW DEFINITION TO Vasya

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

DENY VIEW DEFINITION TO Vasya

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

SELECT * FROM sysobjects -- или SELECT * FROM sys.objects

Ни один из этих запросов не вернет ни одной записи.

Вернуть первоначальное положение вещей можно, удалив это правило.

REVOKE VIEW DEFINITION TO Vasya

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

Индексы

Индексы – это внутренний механизм сервера, позволяющий кардинально повысить скорость выполнения запросов, и без них производительность реляционных БД была бы удручающе низка. В новой версии Mcrosoft SQL Server разработчики не обошли вниманием столь ответственный участок, и в механику индексирования были внесены, некоторые усовершенствования. Естественно, изменился немного и синтаксис команды создания индекса, теперь он выглядит так:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON [ { database_name . [ schema_name ] . | schema_name . } ] {table_or_view_name} ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE (column_name [ ,...n ] ) ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON {partition_scheme_name ( column_name [,...n]) | filegroup_name |default } ]

А дополнительные настройки таковы:

<relational_index_option> ::= { PAD_INDEX = {ON | OFF} | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = {ON | OFF} | IGNORE_DUP_KEY = {ON | OFF} | STATISTICS_NORECOMPUTE = {ON | OFF} | DROP_EXISTING = {ON | OFF} | ONLINE = {ON | OFF} | ALLOW_ROW_LOCKS = {ON | OFF} | 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 работа с индексами не может быть произведена в не блокирующем режиме, если в исходной таблице содержатся поля типа больших объектов (Large Objects - LOB) – text, ntext, image, varchar(max), nvarchar(max), varbinary(max) и xml. Однако в конечной версии это может измениться.

Работе с индексами в не блокирующем режиме присущи следующие особенности:

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

Указание опции ONLNE ON или OFF при изменении кластерного индекса, само собой, распространяется, и на все некластерные индексы, если их также понадобится перестроить в ходе выполнения операции. Например, пересоздание кластерного индекса с опциями CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, вызовет пересоздание всех ассоциированных некластерных индексов в не блокирующем режиме.

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

Работа с индексом ONLINE при параллельном выполнении обычных пользовательских операций может привести к взаимоблокировке (deadlock). Несмотря на то, что система обычно выбирает пользовательскую транзакцию в качестве «жертвы», в некоторых случаях может быть отменена операция индексирования.

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

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

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

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

Обслуживание

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

Перестройка индексов

Перестройку индексов теперь рекомендуется делать не с помощью системной команды DBCC DBREINDEX, а посредством указания соответствующей опции в команде ALTER INDEX. Например, перестройка всех индексов в таблице Product теперь выглядит так:

ALTER INDEX ALL ON Product REBUILD

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

Дефрагментация также внесена отдельной опцией в команду ALTER INDEX, вместо DBCC INDEXDEFRAG. Суть команды от этого не изменилась, это по-прежнему дефрагментация листьевых узлов индексов, практически не нагружающая систему и не мешающая другим операциям. Команда дефрагментации всех индексов на той же Product, как и следовало ожидать по синтаксису, мало чем отличается от команды перестройки:

ALTER INDEX ALL ON Product REORGANIZE

Информация и статистика

Служебную информацию об индексе теперь можно собрать не через sp_helpindex, DBCC SHOWCONTIG или непосредственно служебные таблицы, а через две функции fn_indexinfo и fn_virtualindexstats, которые предоставляют гораздо больше информации.

fn_indexinfo, помимо названия, типа индекса, имен ключевых полей и таблицы, к которой он относится, в зависимости от указанных опций, показывает также:

количество уровней индекса

степень фрагментации индекса

количество страниц, занимаемых индексом

процент заполнения страниц

количество листьевых узлов индекса

количество узлов устаревших версий, готовых к удалению

количество версионных узлов, удерживаемых заинтересованными транзакциями

максимальный, минимальный и средний размер узлов в индексе

Например, запрос, возвращающий все индексы в базе с фрагментацией больше 30%, выглядит примерно так:

SELECT TableName, IndexName, AvgFragmentation, RegionsFragmented, AvgRegionsFragmented FROM sys.fn_indexinfo (NULL, '*', DEFAULT, 'DETAILED') WHERE AvgFragmentation > 30

fn_virtualindexstats позволяет получить полную статистику по операциям ввода/вывода (I/O), по таблицам и индексам. Она позволяет отслеживать время, проводимое пользовательскими транзакциями в ожидании доступа к данным для чтения или записи, и отследить объекты, которые вызывают наибольшую активность. Статистическая информация хранится до тех пор, пока данные находятся в кеше. Следует помнить, что любой DDL-оператор кеш очищает. Функция эта будет очень полезна при поиске узких мест в БД, и оптимизации нагрузки.

Новые встроенные типы данных

На самом деле не только добавились новые типы, но и немного изменились свойства старых типов данных. Например, как и прежде, длина одной записи ограничена восемью килобайтами (размером страницы данных), и объявить поле длиннее этих 8k невозможно (если это, конечно, не LOB). Но вполне возможно объявление двух полей, суммарным размером превосходящих это ограничение. Но если в предыдущих версиях поместить в эти поля данные, по размеру превосходящие 8k, то все, что выходит за этот размер, будет утеряно. Теперь же данные не потеряются. Как только суммарная длина полей выходит за размер страницы данных, резервируется новая страница, в которую помещается остаток, не влезший в основную страницу. А в старой странице данных резервируется небольшой кусочек размером 24 байта, в котором размещается ссылка на только что зарезервированную страницу.

Таким образом, скорость работы, само собой, страдает, но данные при этом не теряются. То есть появилась еще одна возможность неряшливым разработчикам обвинить MSSQL в замедлении работы на ровном месте. :)

«max»-типы

В новой версии серьезно переделана работа с LOB (Large Objects) – объектами большого объема. Раньше для работы с большими объектами использовались типы данных text, ntext и image. Теперь же эти типы объявлены устаревшими, и оставлены только для обратной совместимости. На смену им пришли типы данных varchar(max), nvarchar(max) и varbinary(max) соответственно, в которых может быть размещено до 2ГБ данных.

По способу работы эти типы ничем не отличаются от их младших аналогов varchar, nvarchar и varbinary. Их можно использовать в качестве локальных переменных в хранимых процедурах, триггерах и курсорах. К ним можно применять обычные функции работы со строками, например, charindex, pathindex, len, sub>string и так далее., таким образом отпала необходимость использовать указатели и прибегать к шаманству с updatetext и writetext, если необходимо внести небольшое изменение в текстовое поле. Возможно, что в финальной версии появится возможность строить индексы по этим полям.

Конвертирование новых «max» типов также не отличается от конвертирования более коротких аналогов. Конвертирование между varbinary(max) и image выполняется неявно, как и между varchar(max) и text, и nvarchar(max) и ntext. Приведение к аналогичному типу, но меньшего размера, также производится неявно, но если при этом размер нового типа окажется меньше, чем реальный размер данных в предыдущем типе, часть данных будет утеряна.

Естественно, что «max»-типы физически хранятся немного по-другому. Ранее все LOB-типы в таблице хранились в одной общей свалке из страниц данных, организованной как B-Tree. Теперь же каждое поле «max» типа хранится отдельно, образуя цепочку страниц.

date, time, utcdatetime

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

Главное же отличие этих типов данных от всех остальных заключается в том, что это .Net-типы, что приводит к ряду особенностей работы с ними. Например, в эти типы нельзя явно конвертировать из обычного datetime, что было бы логично. Зато можно использовать ряд методов, что может быть довольно удобно.

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

-- объявляем новый тип данных DECLARE @U UTCDATETIME -- попытка получить текущую дату «в лоб» SET @U = GetDate() -- упс: Operand type clash: datetime is incompatible with utcdatetime -- Попытка номер два, теперь со строкой SET @U = cast(GetDate() as varchar(50)) -- Опять неудачка: Implicit conversion from data type varchar -- to utcdatetime is not allowed. -- Use the CONVERT function to run this query. -- И только воспользовавшись явным преобразованием строки в utcdatetime -- можно добиться успеха. SET @U = Convert(UTCDATETIME, cast(GetDate() as varchar(50)))

А теперь можно попробовать выжать из новой переменной какую-то полезную информацию:

SELECT @U

Такой запрос вернет что-то вроде 0x0188C5A4DDF9BC0800, что, очевидно, является внутренним представлением типа – в общем, малоинформативная конструкция.

А вот такой запрос вернет вполне удобоваримое время по Гринвичу на момент вызова функции GetDate.

SELECT @U::ToString()

А такой – количество наносекунд прошедших с 12 часов первого января первого года нашей эры.

SELECT @U::Ticks

Эти типы вызывают опять-таки несколько противоречивые чувства. С одной стороны, это довольно приятная возможность, относящаяся к разряду «syntactic sugar» - то есть одно из удобств, без которого вполне можно было бы обойтись. Но с другой, все это было бы логичнее сделать обычными, а не .Net-типами.

Промежуточный итог

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

Список литературы

Для подготовки данной применялись материалы сети Интернет из общего доступа