Новые возможности для разработчика приложений. Часть 2
(Продолжение, начало в №4(7) 2000-09-25
Расширенные свойства объектов
SQL Server 2000 позволяет расширять набор метаданных, жестко ассоциированных с объектами базы, к которым, в частности, относятся название поля таблицы, его тип и т. д. Создание дополнительных свойств представляет собой универсальный способ хранения комментариев к таблицам, масок ввода к полям, правил форматирования и т. д. Таким образом можно, с одной стороны, лучше документировать базу данных, а с другой, - определять клиентский интерфейс и другую специфичную для пользователя или приложения информацию на уровне сервера. При переименовании объекта свойства сохраняются. Для создания нового свойства требуется указать его имя (стандартного типа sysname длиной 128 символов) и значение, которое имеет тип SQL_VARIANT и может содержать величины размером до 7500 байт. В качестве примера создадим пользовательское свойство под названием «Описание», содержащее значение «Это адрес клиента» для поля Address таблицы dbo.Customers (см. Листинг 1).
Обращаю внимание читателей, что уровни level0, level1, level2 описывают иерархический путь к объекту. В данном случае иерархия имеет вид «Пользователь БД -> Таблица -> Поле таблицы». Иерархия может иметь не более трех уровней. В качестве объектов верхнего (нулевого) уровня могут выступать пользователь базы либо определенный пользователем тип. Очевидно, что в зависимости от объекта верхнего уровня список объектов уровня, следующего за ним по иерархии, может меняться. Так, для таблицы это могут быть поля, индексы, ограничения, триггеры; для представления - поля и триггеры instead-of; для процедуры - параметры и т. д. Полный перечень иерархий приводится в документации по SQL Server в главе «Using Extended Properties of Database Objects». Имена расширенных свойств являются уникальными в пределах реализации данной иерархии, т. е. мы можем создать точно такое же свойство для другого поля той же таблицы, для такого же поля другой таблицы, а также для другого пользователя, так как, как известно, dbo.Customers и [Другой пользователь БД]. Customers рассматриваются как две разные таблицы. Расширенные свойства объектов БД хранятся в системной таблице sysproperties. Обновление расширенных свойств производится с помощью хранимой процедуры (см. Листинг 2).
Пользовательские свойства можно создавать для объектов любого уровня, например для таблиц (см. Листинг 3).
Для одного и того же объекта можно создать сколько угодно разных свойств (см. Листинг 4).
Системная функция fn_listextendedproperty позволяет определить значения пользовательского свойства по его имени и пути к объекту:
select * from ::fn_listextendedproperty(?Описание?, ?user?, ?dbo?, ?table?, ?Customers?, ?column?, ?Phone?)
Ее результатом является множество из одной записи, полями которого служат тип объекта, имя объекта, название свойства и его значение. Если вместо имени свойства указано NULL, будет возвращен список всех пользовательских свойств данного объекта.
select * from ::fn_listextendedproperty(NULL, ?user?, ?dbo?, ?table?, ?Customers?, ?column?, ?Phone?)
Аналогично, если NULL указать вместо имени объекта, функция вернет все свойства объектов данного уровня. Например, получить список расширенных свойств для всех полей таблицы Customers можно так:
select * from ::fn_listextendedproperty(NULL, ?user?, ?dbo?, ?table?, ?Customers?, ?column?, NULL)
NULL в типе уровня означает вывод свойств объекта более высокого уровня иерархии, в данном случае - таблицы Customers:
select * from ::fn_listextendedproperty(NULL, ?user?, ?dbo?, ?table?, ?Customers?, NULL, NULL)
Расширенное свойство можно удалить с помощью хранимой процедуры sp_dropextendedproperty. Как всегда, в качестве параметров необходимо задать имя свойства и путь к объекту (см. Листинг 5).
Администратор базы данных может удалить все пользовательские свойства объектов базы, очистив таблицу sysproperties.
Кодовая страница и порядок сортировки
В предыдущей версии SQL Server была реализована поддержка Unicode, что позволило хранить в одном поле таблицы текстовую информацию на разных языках. Тем не менее кодовая страница и порядок сортировки определялись на уровне сервера. В SQL Server 2000 такого ограничения нет, Unicode collation можно задавать вплоть до отдельного поля таблицы. Понятие «коллация» (collation) объединяет в себе кодовую страницу и порядок сортировки. SQL Server 2000 поддерживает два типа коллаций. В основном это те, что устанавливаются вместе с операционной системой, плюс еще сравнительно небольшое число коллаций приходит вместе с самим SQL Server для обеспечения совместимости с предыдущими версиями. Имена последних начинаются с «SQL_». И те и другие можно посмотреть с помощью функции fn_helpcollations():
select * from ::fn_helpcollations()
Для просмотра свойств коллации используется функция CollationProperty. Свойствами коллации являются: LCID - идентификатор в наборе коллаций Windows (очевидно, равен NULL для коллаций, принадлежащих собственно SQL Server), ComparisonStyle - способ сравнения символов разных регистров для коллаций Windows (равен NULL для бинарных коллаций и коллаций SQL Server) и CodePage - соответствующая коллации кодовая страница для типов char, varchar, text, не поддерживающих Unicode (в отличие от nchar, nvarchar, ntext). Например,
CollationProperty(?Cyrillic_General_CS_AI?, ?CodePage?) = 1251: select collationproperty(?Cyrillic_General_CS_AI?, ?CodePage?), collationproperty(?Cyrillic_General_CS_AI?, ?LCID?)
Как и ранее, при установке SQL Server предлагается выбрать коллацию уровня сервера, однако теперь она может меняться от базы к базе и от поля к полю. Коллация уровня базы данных определяется при ее создании (см. Листинг 6).
Если коллация БД не была указана явно, в качестве таковой по умолчанию будет использоваться общая серверная коллация. Аналогично обстоит дело с таблицами. Коллацию можно задавать явно для отдельных полей, в противном случае они автоматически наследуют коллацию уровня базы данных, что продемонстрировано в Листинге 7.
В данном случае поле Фрукт2 будет иметь коллацию Latin1_General_CI_AI:
select column_name, collation_name from information_schema.columns where table_name = N?Фрукты? and column_name in (N?Фрукт1?, N?Фрукт2?)
Заполним нашу таблицу (см. Листинг 8).
Теперь очевидно, что, хотя поля Фрукт1 и Фрукт2 содержат, казалось бы, одинаковые данные, ORDER BY ведет себя по-разному в зависимости от коллации поля:
select [Фрукт1] from [Фрукты] order by Фрукт1 select [Фрукт1] from [Фрукты] order by Фрукт2
Поменяем коллацию поля Фрукт2 на Cyrillic_General_Bin:
alter table [Фрукты] alter column [Фрукт2] nvarchar(20) collate Cyrillic_General_Bin
Сейчас оба поля имеют одинаковые коллации, соответственно, сортировки в предыдущих запросах будут выполнены идентично. В принципе, чтобы получить результаты сравнительно небольшого числа запросов в соответствии с какой-либо коллацией, отличной от выбранной коллации поля, совсем необязательно изменять структуру таблицы. Это можно сделать динамически и только на время выполнения запроса. Приведу для сравнения результаты запросов:
select [Фрукт1] from [Фрукты] order by Фрукт1 select [Фрукт1] from [Фрукты] order by Фрукт1 collate Сyrillic_General_CI_AI
Узнать коллацию уровня сервера можно, запросив соответствующее свойство:
select serverproperty(?ServerName?), serverproperty(?Collation?)
Изменить ее можно только, применив rebuild к базе данных master.
Новое в индексах
В предыдущей версии SQL Server появилась поддержка параллелизма внутри запроса. Обработка единого запроса несколькими процессорами одновременно позволяет заметно сократить время выполнения аналитических запросов по большим объемам данных. Подробнее о построении параллельных планов см., например, «Процессор запросов Microsoft SQL Server 7.0. О некоторых стратегиях оптимизатора при построении сложных, параллельных и распределенных планов» // СУБД №3 за 1998 г. Эта возможность получила дальнейшее развитие в SQL Server 2000. В частности, операции создания индексов теперь также могут выполняться в параллельном режиме. При этом на основe построенной (возможно, не путем полного сканирования, а лишь на представительной выборке) статистики распределения ключей оптимизатор оценивает диапазоны фрагментов, на которые должны быть разбиты данные в соответствии с возможной степенью параллелизма. Привлекаются дополнительные потоки, каждый из которых строит индексную структуру с использованием фильтра своего диапазона, после чего на координирующем потоке происходит объединение результатов в единый индекс. Параллелизм внутри запроса позволяет добиться хорошей масштабируемости операций построения индексов в зависимости от числа процессоров.
При определении индексов SQL Server 2000 позволяет задать порядок возрастания или убывания, в котором должны храниться данные полей, входящих в индекс, например: create index IX_Empl on Employees(LastName asc, BirthDate desc, HireDate desc)
Подобный индекс стоит создавать, если в приложении часто встречаются запросы, требующие сортировки результата именно в таком порядке:
select * from Employees order by LastName asc, BirthDate desc, HireDate desc
В противном случае он вообще не будет задействован, в чем легко убедиться, взглянув на план выполнения запроса:
select * from Employees order by LastName asc, BirthDate asc, HireDate asc
Для одноколоночных индексов задавать порядок вообще не имеет смысла, поскольку в этом случае SQL Server одинаково эффективно осуществляет навигацию по индексу вне зависимости от направления. Пример:
drop index Employees.IX_Empl create index IX_Empl_1 on Employees(BirthDate desc)
Тогда как для запроса
select * from Employees order by BirthDate desc,
так и для
select * from Employees order by BirthDate аsc
будет взят индекс IX_Empl_1, причем порядок сортировки не влияет на скорость выполнения запроса. Функция INDEXKEY_PROPERTY() позволяет определить порядок, в соответствии с которым то или иное поле входит в индекс.
Уже существующие индексы можно задействовать при построении новых. Опять же хочу вернуться к ситуации, когда для таблицы Employees существовал только индекс IX_Empl. Включим в Query Analyzer параметр Show execution plan (меню Query, не путать с Estimated execution plan) и создадим индекс
create index IX_Empl_2 on Employees(BirthDate desc, HireDate desc).
В панели результатов на закладке Execution plan представлен план построения индекса. Видно, что, вместо сканирования таблицы целиком, SQL Server ради экономии времени ограничился просмотром только страниц индекса IX_Empl. То же произойдет и при создании индекса по полям LastName asc, BirthDate desc, с той лишь разницей, что последующая сортировка здесь, очевидно, не потребуется.
В SQL Server 7.0 появились поля, не хранящиеся в таблице, а вычисляемые на основе операторов и функций (в том числе определенных пользователем - в версии 2000) над обычными полями. В SQL Server 2000 по вычисляемым полям можно создавать индексы. Разберем ситуации, в которых это новшество может оказаться полезным. Создадим табличку и выполним запрос, который, как легко догадаться, приведет к полному сканированию Ords (см. Листинг 9). Постараемся его оптимизировать. Для этого нужно ввести в таблицу вычисляемое поле и создать по нему индекс, после чего выполним запрос, эквивалентный предыдущему (см. Листинг 9).
Теперь запрос выполняется с использованием индекса iOrdYear, что гораздо лучше. Попытаемся оценить количественно это «гораздо», объединив оба запроса (см. Листинг 10) и обратив внимание на колонку TotalSubtreeCost, из которой видно, что индекс по вычисляемому полю позволил почти на порядок сократить стоимость выполнения запроса.
Теперь, как водится, немного о грустном, а именно о требованиях, которым должно соответствовать выражение в вычисляемом поле, чтобы по нему можно было построить индекс. Выражение не должно включать полей из других таблиц, а также функций над векторными аргументами типа SUM(), AVG() и т. д. Кроме того, оно должно быть детерминированным и точным, т. е. всякий раз при одних и тех же аргументах возвращать одинаковое значение и не содержать операций над вещественными типами. Проверить соблюдение этих условий можно с помощью функции COLUMNPROPERTY(). Например, следующее вычисляемое поле не является детерминированным (см. Листинг 11), так что будет возвращено значение 0. Выражение может быть детерминированным, но неточным. Пример см. в Листинге 12.
В обоих случаях попытка создания индекса завершится с ошибкой, до чего, впрочем, можно не доводить, предварительно проверив, является ли поле вычисляемым и индексируемым (см. Листинг 13).
Данное выше определение детерминированного выражения нуждается в дополнительных комментариях, так как из него следует, что практически ни одна заслуживающая внимания функция не является строго детерминированной, за исключением вырожденных случаев типа константы. В самом деле, нельзя, например, признать детерминированным выражение x/y, поскольку оно подспудно зависит от установки SET ARITHABORT. На результат сложения двух строк влияет SETCONCAT_NULL_YIELDS_ NULL, преобразование строки в дату неявно определяется установкой SET DATEFORMAT и т. д. Поэто-му, говоря о детерминизме в SQL Server, условились считать, что ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_ PADDING, ANSI_WARNINGS выставлены в ON, a NUMERIC_ ROUNDABORT - в OFF. В противном случае ни одно выражение не будет являться детерминированным. Установки масштаба сессии корректируются так, как показано в Листинге 14.
Можно сделать то же самое на уровне сервера для всех пользовательских сессий (см. Листинг 15), хотя, когда мы открываем соединение по ODBC или OLE DB, все эти параметры уже выставлены в правильное с точки зрения детерминизма положение, за исключением ARITHABORT, так что, в принципе, в масштабах сервера подправлять имеет смысл только его: sp_configure ?user options?, 64. Кстати, что может произойти с существующими индексами по вычисляемым полям и другими объектами, зависящими от требований детерминизма, если какой-либо из вышеперечисленных параметров установить в «недозволенное» значение? Ответ на этот вопрос будет дан в следующей главе, а пока читатель вправе рассматривать его в качестве самостоятельного упражнения.
Индексированные представления
Классическое представление (view) можно рассматривать как своего рода псевдоним для конкретного оператора SELECT, позволяющий задействовать его в запросах примерно так же, как обычную таблицу. В представлении, по большому счету, хранится только определение SQL-запроса, который выполняется всякий раз при обращении к данному представлению. В этом смысле индексированные представления (indexed views), известные также как материализованные, идут дальше, сохраняя не только определение, но и результаты запроса, которые поддерживаются в актуальном состоянии при модификации данных. Таким образом, индексированные представления действительно ведут себя аналогично индексам, и SQL Server 2000 позволяет материализовать обычное представление именно путем создания индекса для него. Представление может содержать агрегатные функции, операторы связывания таблиц или их комбинацию. Очевидно, что для материализации результатов представления первый создаваемый для него индекс должен быть: а) кластерным (чтобы в листьях хранились сами данные, а не ссылки на них) и б) уникальным (чтобы однозначно идентифицировать запись при внесении изменений). Стоит иметь в виду, что каждая материализация сопряжена с издержками, в связи с выделением места под результаты и некоторым (вообще говоря, довольно незначительным) замедлением операций обновления в объектах, участвующих в определении представления. Однако при неоправданном увеличении числа материализованных представлений в базе данных эти издержки в сумме могут тем не менее оказывать заметное негативное влияние на ресурсоемкость и производительность приложения. Словом, подобно тому, как не имеет смысла создавать индексы на все случаи жизни, не следует немедленно бросаться материализовывать все и вся. Индексированные представления, как правило, оказываются наиболее эффективными в OLAP-приложениях (задачах отчетности, поддержки принятия решения), так как позволяют сократить время выполнения сложных аналитических запросов на чтение по массивным объемам данных за счет хранения промежуточных результатов связывания таблиц, результатов группирования и агрегатов. Именно поэтому поддержка индексированных представлений реализована только в корпоративной редакции (Enterprise Edition). Индексированные представления позволили SQL Server 2000 продемонстрировать наилучший по производительности результат в 1699,8 аналитических запросов в час для 100-гигабайтной базы данных по тестам ТРС-Н (информация на момент написания статьи). SQL Server работал на сервере Compaq ProLiant 8000, оснащенном восемью процессорами Intel Pentium III Xeon 700 МГц. В «номинации» 300 Гбайт SQL Server 2000 принадлежит самое экономичное решение - 200 $/QphH при общей производительности в 1402 аналитических запроса в час (результат показан на платформе НР NetServer LXr 8500). Теоретически применение индексированных представлений не даст выигрыша в OLTP-приложениях, т. е. в задачах учета и накопления данных, для которых характерны частые короткие обновляющие транзакции. Другим примером неоправданного использования индексированных представлений могут служить группировки по высокоселективным полям, так как здесь размер представления будет по порядку величины соответствовать самой таблице.
Представление должно быть жестко привязано к схеме данных, т. е. должно быть создано как СREATE VIEW … WITH SCHEMABINDING AS … В этом случае операции изменения входящих в него объектов (ALTER TABLE, ALTER COLUMN, ALTER VIEW и т. д., не говоря уже о DROP) недопустимы, если они затрагивают определение этого представления. Функции, участвующие в индексированном представлении, должны быть детерминированными, потому что иначе может возникнуть противоречие, например, между GETDATE() и результатом, сохраненным вчера. Оптимизатор принимает решение об использовании индекса для представления, если:
- семь перечисленных в конце предыдущей главы установок соответствуют требованиям детерминизма. Если после создания индексированного представления нарушить это требование, индекс на представление не будет рассматриваться оптимизатором, а любая попытка модифицировать данные в исходных таблицах, на которые опирается представление, завершится ошибкой;
- он обнаружил соответствие между подграфом дерева запроса и определением индексированного представления. Следовательно, оптимизатор может использовать индекс на представление, даже если таковое непосредственно в запросе не фигурирует;
- в запросе не используется OPTION (EXPAND VIEWS), дающая оптимизатору указание раскрыть представление в определение запроса, что не позволяет обращаться к нему как к материализованным данным;
- оптимизатор, что называется, «ткнули носом», применив в запросе подсказку: SELECT … FROM IndVw WITH (NOEXPAND, INDEX(Iх1)) WHERE … Если представление не присутствует в запросе явно и оптимизатор по своей охоте не использует индекс на представление, способа заставить его это сделать не существует;
- стоимость запроса достаточно велика. Для простых запросов и малых объемов данных привлекать индекс может оказаться нецелесообразно, дешевле обойтись простым сканированием.
Пример создания индексированного представления (обратите внимание на вычисляемое поле в виде функции COUNT_BIG(*), без него SQL Server откажется создавать индекс на представление) приведен в Листинге 16.
Проиллюстрируем влияние параметра привязки представления к схеме (см. Листинг 17).
Первое поле спокойно удаляется из таблицы Products, поскольку нашему schemabinding view оно ничего не дает. Второе поле, наоборот, участвует в определении представления, поэтому удалить его SQL Server не даст. Кстати, для того чтобы создавать представление с параметром WITH SCHEMABINDING, пользователь должен обладать правами REFERENCES на все объекты, участвующие в определении представления. Проверяется это, например, так:
select permissions(object_id(?Products?)) & 0x4
Материализуем наше представление путем создания уникального кластерного индекса (см. Листинг 18). Еще раз напомню, что эта функциональность поддерживается только в SQL Server 2000 Enterprise Edition (можно проверить select @@version или посмотреть первую запись в журнале SQL Server, которую он создает при старте).
Как указано в плане выполнения запроса, для выборки данных будет использоваться индекс ix. Изменения в исходных таблицах, естественно, отражаются в результатах, хранящихся в индексированном представлении. После того как представление материализовано путем написания уникального кластерного индекса, для него можно создавать обычные индексы.
Рассмотрим типичную аналитическую задачу. Допустим, требуется найти наиболее успешно продаваемый продукт за некоторый период времени и сумму выручки по нему. Сравним эффективность решения задачи с использованием индексированного представления и без него. Для индексированного представления стоимость выполнения запроса (см. поле TotalSubtreeCost в первой строке выдачи плана) составит приблизительно 0,0064 (см. Листинг 19).
«Дематериализуем» представление, удалив первичный индекс ix. Это приведет к автоматическому удалению всех остальных связанных с ним индексов и повлияет на стоимость предыдущего запроса (см. Листинг 20).
Округляя, получим 0.1586, или почти в 25 раз больше, чем при использовании индекса на представление.
В завершение рассказа об индексированных представлениях хочу коснуться вопроса получения метаданных. Здесь нам, как и ранее, пригодится функция OBJECTPROPERTY, имеющая ряд параметров, специфичных для предмета данной главы, в частности является ли объект представлением, привязано ли представление к схеме, отвечает ли оно и его окружение требованиям детерминизма, можно ли его проиндексировать и, наконец, проиндексировано ли оно (см. Листинг 21).
Распределенные представления
Разбиение логически единого массива данных с целью их последующей параллельной обработки является одним из наиболее распространенных методов повышения масштабируемости. На сегодня существует два основных типа параллельных архитектур, которые выдержали испытание временем и, по-видимому, способны сохранить свои позиции в ближайшем будущем - это SMP и кластеры. О некоторых возможностях SQL Server по поддержке симметричной мультипроцессорной обработки, в частности о параллелизме внутри запроса, я уже рассказывал выше. К сожалению, масштабируемость SMP-систем ограничена необходимостью использования общей памяти, дискового пространства, устройств ввода/вывода и других ресурсов. Теоретически мы можем сколь угодно наращивать систему вертикально, устанавливая все больше процессоров, заменяя их более быстрыми моделями, расширяя объем памяти, увеличивая емкость дисков и т. д. Однако все равно необходимость доступа к общим ресурсам останется слабым звеном, и в условиях возрастающей нагрузки рано или поздно наступит момент, когда это звено начнет сдерживать общую производительность системы. Такой же недостаток присущ и кластерным архитектурам, рассчитанным на использование разделяемых (shared) ресурсов, например единого дискового массива или пространства памяти. Иногда в литературе их называют shared everything, хотя это, конечно, неверно. Не существует систем типа «все общее», и даже исключительный в этом плане случай - SMP, где любой процессор может «достучаться» до любой памяти, выполнять любую операцию чтения-записи, прерывать другие процессоры и т. д. - имеет по крайней мере один несимметричный ресурс - кэш. Таким образом, правильнее охарактеризовать эту категорию параллельных систем как shared something (в нее входят и SMP, и кластеры c разделением доступа к общим дискам на основе DLM, и кластеры ccNUMA) и противопоставить ей концепцию систем с отсутствием общих ресурсов (shared nothing). В известном смысле этой концепции соответствуют хорошо известные кластеры Microsoft Cluster Server (два узла в случае Windows 2000 Advanced Server, четыре узла под Windows 2000 DataCenter). Несмотря на наличие физически общего дискового массива, каждый узел в данный момент времени владеет на нем фрагментом, не пересекающимся с остальными. Однако этот пример не вполне удачен, так как сейчас я говорю о кластерах в первую очередь с точки зрения параллельной обработки, а не отказоустойчивости. В идеале кластер типа shared nothing может состоять из обычных компьютеров, что называется «с витрины», без каких бы то ни было разделяемых ресурсов (в том числе и на физическом уровне) и специальных требований к оборудованию, инфраструктуре и т. п. Формирование из множественных экземпляров приложения, запущенного на каждом узле, единого образа системы снаружи, согласование и перераспределение данных между экземплярами - эти и другие задачи предстоит решить разработчикам, прежде чем администраторы получат возможность «размазывать» единую базу данных по нескольким SQL Server в сети. Тем не менее такой подход представляется гораздо более перспективным. Он не требует жесткой зависимости от аппаратных решений. Производительность кластера повышается простым добавлением в систему недорогого сервера потребительского уровня. Отсутствие нагрузки на общие ресурсы существенно расширяет линейный участок кривой масштабируемости, что подтверждается недавними результатами тестов ТРС-С для OLTP-приложений. На сегодня SQL Server занимает четыре из пять первых мест в таблице абсолютных рекордов производительности. Его лучший результат в 262 244 транзакции в минуту был достигнут благодаря реализации в SQL Server 2000 начального этапа концепции shared nothing - распределенных фрагментированных представлений (distributed partitioned views). В ходе испытаний использовался кластер из 12-ти серверов Compaq ProLiant 8500 по восемь процессоров Intel Pentium III Xeon 700 MГц каждый. Не имея под рукой подобной конфигурации, я постараюсь объяснить, как это работает, чисто умозрительно.
На самом деле, практически все необходимое для поддержки распределенных представлений - прилинкованные серверы и понимание процессором запросов, как фрагментированы данные, - реализовано еще в предыдущей версии. Благодаря прилинкованным серверам появилась возможность связывать в запросах локальные и удаленные данные, принадлежащие внешнему OLE DB-ресурсу (в том числе другому SQL Server). С другой стороны, предположим, мои данные по продажам за месяц хранятся в таблицах JanuarySales, …, DecemberSales, я строю представление по продажам за год
create view YearSales as select * from JanuarySales union all … union all select * from DecemberSales,
а затем запускаю запрос
select * from YearSales where [Месяц] = 8.
Чтение будет происходить не по всему представлению в целом, а только по таблице AugustSales. Правда, для этого требуется явно «дать понять», как фрагментированы данные, установив для таблицы JanuarySales ограничение check ([Месяц] = 1), FebruarySales - check([Месяц] = 2) и т. д.
SQL Server 2000 позволяет объединить эти две возможности. Разделим все SQL Server в сети на две группы. В первую войдут серверы, служащие точками входа для пользователей, именно с ними те будут устанавливать соединения. На этих серверах данные хранить необязательно. Напротив, вторая группа объединит ресурсные серверы, на которых сосредоточены корпоративные базы данных. Каждая БД принадлежит одному серверу. Пользователи могут вообще не подозревать о существовании этих серверов. В базах данных ресурсных серверов должны находиться таблицы, которые представляют собой непересекающиеся горизонтальные фрагменты общего множества данных. Условие фрагментации должно быть прописано явно в виде ограничения CHECK на таблицу. На каждом из серверов, служащих точками входа, должны быть созданы серверы, прилинкованные к серверам ресурсов. После этого на точках входа создаются представления, объединяющие данные с ресурсных серверов. При обработке клиентского запроса к представлению оптимизатор, проанализировав ограничения, уже «знает», как устроены данные и где искать тот или иной фрагмент. Исходя из этого он строит распределенный план выполнения так, что каждый ресурсный узел получает от него подзапрос, относящийся только к его собственным, физически хранящимся на нем данным. Результаты обработки подзапросов отсылаются ресурсными узлами обратно на точку входа, которой остается собрать их все воедино и вернуть клиенту. Каждый SQL Server может одновременно выполнять функции точки входа и ресурсного сервера.
Вот, собственно, и все. Как мы видим, понятие «кластер» носит в данном случае чисто виртуальный характер. Нам не требуется связывать узлы как-либо иначе, кроме как через локальную сеть, монтировать дополнительное оборудование и т. п. Теперь я попробую воспроизвести сказанное экспериментально. Для этого потребуется два экземпляра SQL Server, работающих на одной или разных машинах - не суть важно. В моем случае это будут SQL Server 2000 Enterprise Edition по имени Leshik1 и SQL Server 2000 Standard Edition по имени Leshik2 на той же машине, что и первый. Leshik1 будет одновременно ресурсным сервером и точкой входа, а Leshik2 - чисто ресурсным сервером. Создадим непересекающиеся фрагменты данных на обоих ресурсных серверах (см. Листинг 22).
Разбиение происходит по диапазонам значений первичного ключа. Теперь следует явно задать условие разбиения (см. Листинг 23).
На точке входа нужно создать прилинкованный к ресурсам сервер и распределенное представление (см. Листинг 24).
Имитируем клиента, обратившегося к точке входа (Leshik1):
select CustID, Contact, Company, Country, City from DistrCust where CustID between ?K? and ?P?
Графический план выполнения этого запроса состоит из двух соединяющихся ветвей. Одна начинается с индексного поиска по локальной таблице CustAL, а другая - с удаленного подзапроса к CustMZ, который уйдет на сервер Leshik2 (см. значение SOURCE в аргументе оператора Remote Query). Процессор запросов ожидает (Estimated row count), что с локального сервера будет получено 10 записей, а с удаленного - восемь, что соответствует действительности (select count(*) from CustAL where CustID >= ?K? и select count(*) from Leshik2.Northwind.dbo.CustMZ where CustID <= ?P?). На этапе конкатенации происходит объединение результатов из различных источников. Таким образом, обработка данного запроса действительно распараллеливается по двум серверам.
Рассмотрим другой пример:
select CustID, Contact, Company, Country, City from DistrCust where CustID between ?U? and ?Y?
В этом случае должен быть задействован только Leshik2, поскольку Leshik1 не содержит записей, отвечающих критерию. В то же время в плане выполнения опять видны две ветви и оператор Index Seek, как будто есть надежда что-то получить от локального сервера. В чем дело? Чтобы ответить на этот вопрос, нужно вспомнить про автопараметризацию. Посмотрите на условие SEEK в аргументе оператора индексного поиска. Оно выглядит приблизительно так: [CustAL].[CustID] >= Convert([@1]) AND [CustAL].[CustID] <= Convert([@2]), т. е. ?U? и ?Y? фигурируют в плане не как константы, а как параметры. В процессе компиляции процессору запросов нет дела до конкретных значений параметров, поэтому он не может показать в плане, с каких серверов понадобятся фрагменты, а с каких нет. Кажется, что он их хочет взять сразу отовсюду. Процессор запросов вставляет в каждую ветвь оператор Filter, чтобы принять решение во время выполнения запроса. Аргументом для него служит условие вида WHERE:(STARTUP EXPR(...)). Если такие операторы существуют для каждого из фрагментов, значит, запрос будет выполняться корректно и эффективно (ненужные серверы задействованы не будут). Чтобы в этом убедиться, нужно установить SET STATISTICS PROFILE ON и повторить запрос. Обратите внимание на вторую выдачу следом за результатом. Поле Executes для Clustered Index Seek будет равно 0, как и поле Rows для всей локальной ветви. Т. е. в данном случае реально работает только Leshik2, что и требовалось доказать.
Аналогично происходит обновление данных (см. Листинг 25).
Прежде чем модифицировать данные, следует убедиться, что MS DTC на точке входа стартовал.
Вторая бета-версия не выполняла обновление первичного ключа в распределенных представлениях. В последующих промежуточных рабочих версиях этот недостаток был исправлен. Запрос
update DistrCust set CustID = ?AAAAA? where CustID = ?TORTU?
приведет к автоматическому перемещению соответствующей записи из таблицы CustMZ в СustAL.
Следует заметить, что распределенные фрагментированные представления, предназначенные для увеличения производительности за счет распараллеливания процесса обработки, на отказоустойчивость никак не влияют. (На практике для решения этой задачи можно предложить защитить каждого из участников виртуальной серверной фермы с помощью MSCS.) Кроме того, данные распределяются по серверам вручную. В случае выхода из строя одного из узлов или, наоборот, при добавлении нового узла в виртуальный кластер потребуется перераспределять данные по серверам, что опять-таки невозможно без участия администратора. Однако нужно помнить о том, что это лишь один из первых шагов к созданию полнофункциональных кластеров shared nothing, и работа в этом направлении продолжает идти полным ходом.
Изменения в резервном копировании
В SQL Server 7.0 два параметра влияли непосредственно на процесс создания резервных копий. Установка truncate log on checkpoint вынуждала неактивную часть журнала транзакций очищаться при наступлении события check point (инициированном как администратором, так и сервером). Управлять периодичностью возникновения check point по инициативе сервера администратор мог только косвенно, через recovery interval - время, которое отводится SQL Server на процесс восстановления БД после перезапуска. Очевидно, что чем меньше recovery interval, тем меньше транзакций SQL Server может позволить себе накапливать в журнале и тем чаще должно происходить событие check point. Вторая установка - это select into / bulkcopy. Она разрешала использование (и частичное протоколирование) операций массированного копирования типа bcp, SELECT INTO, WRITETEXT, UPDATETEXT. Понятно, что объемные обновления могли переполнить журнал транзакций, поэтому в нем отражались только факты выделения страниц под результаты подобных операций. Соответственно, вернуть их было можно (забрав страницы назад), а повторить - нельзя (нет содержимого). В SQL Server 2000 реализована декларативная модель протоколирования. Всего существует три режима: FULL, BULK_LOGGED и SIMPLE. Для вновь созданной базы данных режим наследуется от model, в которой по умолчанию может иметь различные значения в зависимости от редакции SQL Server. Режим восстановления относится к свойствам базы данных, его можно просмотреть с помощью select databasepropertyex(?model?, ?Recovery?) и поменять через ALTER DATABASE … SET RECOVERY ... Режим SIMPLE практически эквивалентен truncate log on check point. К его достоинствам можно отнести относительно небольшой размер журнала транзакций, резервные копии которого по понятным причинам не делаются, так что в случае сбоя все изменения с момента последнего полного или дифференциального резервного копирования будут потеряны. Режим FULL, напротив, предполагает максимально полную запись изменений и сохранение в журнале зафиксированных транзакций, что дает возможность восстановления на момент времени и в том случае, если один из файлов данных оказался испорченным. BULK_LOGGED напоминает полный режим с той лишь разницей, что массивные операции в нем фиксируются по минимуму, т. е. записывается только, какие протяжения (extents) были этими операциями затронуты. При создании резервной копии журнала транзакций в него записывается содержимое этих протяжений, что требует доступа к файлам данных. Если данные при этом испорчены, окончательная резервная копия журнала транзакций не будет создана и завершенные изменения в нем будут потеряны. Восстановление на момент времени в этом режиме не поддерживается. В отличие от версии 7.0, не существует ограничений на использование каких-либо операторов Т-SQL в зависимости от режима, например SELECT … INTO может применяться в каждом из них.
Повысилась производительность процесса дифференциального резервного копирования. В SQL Server 2000 ведется битовая карта протяжений, претерпевших изменения с момента последнего полного резервного копирования, что избавляет от необходимости сканирования всей БД в поисках модифицированных данных. Кроме того, предусмотрено дифференциальное резервное копирование не только всей БД целиком, но и отдельных входящих в нее файлов. Появилась возможность частичного восстановления из полной резервной копии на уровне групп файлов. Таким образом, можно восстановить, например, отдельный индекс или таблицу, ассоциированные с определенной группой файлов, не разворачивая всю резервную копию. Была добавлена функция установки пароля на ленту или конкретную резервную копию на ней: BACKUP DATABASE | LOG … WITH MEDIAPASSWORD = …, PASSWORD = … Нужно иметь в виду, что установка пароля не шифрует сами данные и не может предотвратить их интерпретацию средствами, лежащими ниже слоя абстракции MTF, а также перезапись. Функция под названием Snapshot Backup/Restore позволяет интегрировать в процесс создания резервных копий и их восстановления программно-аппаратные решения независимых производителей (например, зеркалирование или мгновенное создание копий данных при их записи), поддерживающие интерфейсы VDI. В принципе, это эквивалентно созданию полной резервной копии файла или базы данных, однако дает преимущества в скорости (восстановление с зеркального диска происходит буквально за секунды) и в плане разгрузки сервера баз данных.
Создание сервера «теплого» резерва (log shipping) в предыдущих версиях SQL Server осуществлялось с помощью утилит, входящих в BackOffice Resource Kit. Теперь эта возможность на правах базовой функции включена в SQL Server 2000 Enterprise Edition. Если коротко, то она состоит в автоматизации следующего процесса. Имеем два сервера: «боевой» и резервный. Сначала БД на резервном сервере полностью синхронизируется с «боевым». Затем через определенные промежутки времени на нем делаются резервные копии журнала транзакций, которые восстанавливаются на резервном сервере. В случае выхода из строя «боевого» сервера рассогласование данных не будет превышать этого интервала. В отличие от «теплого» сервер «горячего» резерва содержит абсолютно актуальную копию данных. Он может быть реализован, например, с помощью MSCS.
Еще одно новшество касается процесса восстановления данных относительно конкретной именованной транзакции. Теоретически это то же самое, что и восстановление на момент времени, но гораздо удобнее, так как дает возможность пометить наиболее важный или, наоборот, подозрительный участок кода и при случае восстановить данные в состояние непосредственно до или после его выполнения. Пример см. в Листинге 26.
Вставка второй записи в MyTbl происходит внутри именованной транзакции. Оператор WITH MARK сохраняет метку в журнале транзакций. После этого можно добавить еще третью запись и создать резервную копию журнала транзакций (см. Листинг 27).
Восстанавливаем БД и «накатываем» на нее копию журнала транзакций до метки. Это соответствует моменту, когда в таблице MyTbl находилась только запись ?aaa? (см. Листинг 28).
То же самое для ситуации восстановления по метку, когда в таблице было уже две записи (см. Листинг 29).
Заключение
Естественно, всю функциональность SQL Server 2000 невозможно охватить в рамках одной статьи. За пределами данного обзора остались вопросы интеграции со службой каталогов, механизмы безопасности, в том числе делегирование на основе Kerberos и шифрование трафика по всем сетевым библиотекам, развитие служб тиражирования, включая обновление данных на подписчике при недоступном издателе на основе очередей сообщений и репликацию схемы данных, поддержка XML на уровне OLE DB-провайдера SQL Server, усовершенствования в службе полнотекстового поиска, службе преобразования данных, аналитических службах и многое другое, что заслуживает внимательного изучения. Я надеюсь, что данный материал, несмотря на его, по большому счету, достаточно вводный характер, поможет читателям лучше освоиться в мире новых возможностей SQL Server 2000 и с максимальной отдачей использовать их для решения своих конкретных задач.
АЛЕКСЕЙ ШУЛЕНИН
Системный инженер отдела бизнес-приложений российского представительства Microsoft. Имеет сертификаты MCSE, MCDBA, MSS, MCSD. С ним можно связаться по адресу: rusdev@microsoft.com.