| ПРИМЕЧАНИЕВ доступной на данный момент версии 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, AvgRegionsFragmentedFROM sys.fn_indexinfo (NULL, '*', DEFAULT, 'DETAILED')WHERE AvgFragmentation > 30 |
fn_virtualindexstats позволяет получить полную статистику по операциям ввода/вывода (I/O), по таблицам и индексам. Она позволяет отслеживать время, проводимое пользовательскими транзакциями в ожидании доступа к данным для чтения или записи, и отследить объекты, которые вызывают наибольшую активность. Статистическая информация хранится до тех пор, пока данные находятся в кеше. Следует помнить, что любой DDL-оператор кеш очищает. Функция эта будет очень полезна при поиске узких мест в БД, и оптимизации нагрузки.
На самом деле не только добавились новые типы, но и немного изменились свойства старых типов данных. Например, как и прежде, длина одной записи ограничена восемью килобайтами (размером страницы данных), и объявить поле длиннее этих 8k невозможно (если это, конечно, не LOB). Но вполне возможно объявление двух полей, суммарным размером превосходящих это ограничение. Но если в предыдущих версиях поместить в эти поля данные, по размеру превосходящие 8k, то все, что выходит за этот размер, будет утеряно. Теперь же данные не потеряются. Как только суммарная длина полей выходит за размер страницы данных, резервируется новая страница, в которую помещается остаток, не влезший в основную страницу. А в старой странице данных резервируется небольшой кусочек размером 24 байта, в котором размещается ссылка на только что зарезервированную страницу.
Таким образом, скорость работы, само собой, страдает, но данные при этом не теряются. То есть появилась еще одна возможность неряшливым разработчикам обвинить MSSQL в замедлении работы на ровном месте. :)
В новой версии серьезно переделана работа с 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» типа хранится отдельно, образуя цепочку страниц.
Добавились также новые типы данных, представляющие отдельно дату, отдельно время и дату со временем в виде, независимом от временной зоны. Причем в отличие от старого типа 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, что, очевидно, является внутренним представлением типа – в общем, малоинформативная конструкция.