45798 (Новые возможности MS SQL Server 2004 Yukon), страница 3

«временный индекс» (temporary mapping index) – эта структура создается только при выполнении online-операций с кластерными индексами. Она используется для определения записей, которые надо удалить из нового индекса, когда во время работы с ним удаляются или изменяются данные в исходной таблице. Этот некластерный индекс создается на том же шаге, что и новый кластерный индекс, и все изменения исходных данных также применяются и к временному индексу.

Например, если происходит ONLINE-операция по перестроению кластерного индекса и четырех некластерных, ассоциированных с ним, то существует одна исходная структура (оригинальный кластерный индекс), пять предварительных индексов (четыре некластерных и один кластерный) и одна конечная структура (конечный кластерный индекс). При перестроении кластерного индекса, некластерные, ассоциированные с ним, не перестраиваются.

Во время выполнения индексных операций на исходной таблице удерживается блокировка IS (Intent Share), также, на некоторых стадиях, на короткое время накладываются блокировки S (Share) и Sch-M (Schema Modification).

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

Операции с индексом ожидают завершения всех незафиксированных транзакций, прежде чем наложить коллективную (S) блокировку или блокировку изменения метаданных (Sch-m).

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

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

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

Исходная таблица не может быть изменена, удалена или очищена (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, вместо DBCC INDEXDEFRAG. Суть команды от этого не изменилась, это по-прежнему дефрагментация листьевых узлов индексов, практически не нагружающая систему и не мешающая другим операциям. Команда дефрагментации всех индексов на той же Product, как и следовало ожидать по синтаксису, мало чем отличается от команды перестройки:

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

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

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

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

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

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

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

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

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

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

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

Например, запрос, возвращающий все индексы в базе с фрагментацией больше 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, substring и так далее., таким образом отпала необходимость использовать указатели и прибегать к шаманству с 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, что было бы логично. Зато можно использовать ряд методов, что может быть довольно удобно.

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

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

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