(Возврат на основную страницу)

Содержание номера за Апрель 2008 год

SQL Server

Апрель 2008
№ 4 (88)

Editorial

Майкл Оти

Куда дальше?

Programming

Эрланд Зоммарског

Предоставление разрешений посредством хранимых процедур. Часть 3

Чад Миллер

Windows утилиты для администраторов баз данных SQL Server4

DB Design & Warehousing

Ицик Бен-Ган

Разоблачение мифов о временных объектах. Часть 1

Куда дальше?

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

Совместимость с Windows 2008 в режиме «базовой функцио­нальности» (Server Core)

С выпуском Windows Server 2008 будущие версии SQL Server должны уметь работать под Windows, установленной в режиме Server Core. Одна из новых опций установки Windows Server 2008  Server Core представляет собой «голую» операционную систему, работающую без графического интерфейса. Иными словами, весь хлам, вроде Microsoft Internet Explorer (IE), Microsoft Office Outlook Express и .NET CLR был пропущен при инсталляции, и пользователь получает операционную систему с меньшими требованиями к ресурсам, а также более эффективную и защищенную. Очень похоже на идеальную платформу для запуска sql server, верно? А вот и нет. К сожалению, Server Core не поддерживает .NET CLR и, следовательно, SQL Server не может работать на ОС, установленной в такой конфигурации. Эту проблему можно решить добавлением .NET­функциональности в режим установки Server Core (скорее всего, что­то похожее произойдет, чтобы будущие версии SQL Server поддерживали Windows PowerShell) или удалением .NET­компонентов из SQL Server. Независимо от того, как будет решена эта проблема, будущие версии SQL Server смогут работать на Server Core.

Поддержка LINQ в SQLCLR

С выходом Visual Studio 2008 появилась возможность использовать Language­Integrated Query (LINQ) — важную функциональность, полезную разработчику. Использование LINQ позволяет писать запросы с использованием стандартного .NET­синтаксиса без необходимости вкраплять в основной массив кода команды на другом языке (т. е. TSQL). Это дает преимущество по сравнению с традиционным программированием доступа к БД средствами .NET, когда основной объем кода доступа к данным пишется с использованием объектного подхода ADO.NET , но реальный доступ к данным выполняется средствами кода на T­SQL. Использование LINQ также дает разработчикам возможность получать немедленную информацию об объекте БД и синтаксическую поддержку при написании кода. Однако LINQ пока не может использоваться для разработки кода в среде SQLCLR. Включение поддержки LINQ в SQLCLR  естественное решение и оно только простимулирует использование SQLCLR.

Клиентская часть для SQL Server Service Broker

SQL Server Service Broker предоставляет массу возможностей для разработки приложений с использованием асинхронной доставки сообщений. Но в реальном мире разработчики нечасто используют эту функциональность, так как разработка приложений с использованием Service Broker слишком сложна. В настоящее время не существует клиентской части средства разработки, помогающей создавать такого рода приложения. А разработка приложений, использующих функциональность service broker, требует применения смеси из manual XML, T­SQL и кода на .NET, что делает ее невыполнимой для большинства разработчиков. Добавление графической среды разработки под Service Broker упростило бы процесс.

Вот только несколько моих пожеланий относительно расширений возможностей следующей версии SQL Server.

Предоставление разрешений посредством хранимых процедур. Часть 3*

* См. Эрланд Зоммарског. Предоставление разрешений посредством хранимых процедур. Части 1, 2 // SQL Server для профессионалов. 2008. № 2, 3.

А надо ли вообще использовать условие EXECUTE AS?

Как вы должны были понять, мой энтузиазм в отношении способа работы условия EXECUTE AS не слишком велик. Но я вовсе не хочу сказать «не используйте это условие!», напротив, мне хочется добиться от вас понимания того, как способны повлиять на ваше приложение эти четыре коротких слова WITH EXECUTE AS OWNER. Как я говорил в начале этой статьи, если вы являетесь DBA­администратором, вам необходимо знать о том, что может привнести в хранимую процедуру разработчик. А если вы разработчик, то должны знать, что влияние условия EXECUTE AS не ограничивается простым предоставлением пользователям разрешений на время работы хранимой процедуры.

Хотя сертификаты работают всегда (за исключением того случая, когда необходимо явно отказать пользователям в доступе), нельзя отрицать, что условие EXECUTE AS — это значительно меньшее препятствие. Если у вас есть хранимая процедура, которая не инициирует никакие другие хранимые процедуры, не выполняет никаких действий по аудиту, отсутствует защита на уровне записи и вас, наконец, не слишком волнует приложение Profiler, тогда трудно найти какие­либо возражения по поводу использования условия EXECUTE AS. Случай нового приложения, которое везде и всюду использует функции original_login() или context_info() для аудита и защиты на уровне записи, предполагает еще меньше ситуаций, в которых есть вероятность причинить какой­либо ущерб, воспользовавшись условием EXECUTE AS.

И все­таки, если вы являетесь разработчиком и подумываете об использовании условия EXECUTE AS, пожалуйста, обсудите прежде этот вопрос с DBA­администратором и убедитесь в том, что он осознает последствия. Особый случай, если вы работаете для независимого поставщика программных продуктов (ISV). В этом случае, у вас есть несколько сторонних DBA­администратров, которые собираются управлять вашим приложением. Рассчитывайте на то, что из них как минимум несколько человек окажутся любителями взглянуть на имя входа в приложении Profiler. Поэтому, если вы являетесь таким ISV­поставщиком, я рекомендовал бы вам использовать условие EXECUTE AS очень осторожно.

Должен также добавить, у меня сложилось впечатление, что заимствование прав часто используется в контексте компонента Service Broker. Какой в этом смысл я, вообще говоря, сказать не могу, поскольку компонент Service Broker — это нечто такое, что мне еще предстоит осмыслить.

Но что бы вы ни делали, будьте очень, очень сдержанны в отношении условия EXECUTE AS OWNER! Вместо этого работайте с пользователями без записи входа, которые обладают точно таким же набором необходимых разрешений.

Использование ролей приложения

Роли приложения появились в версии SQL 7. Идея сводится к тому, что вы создаете роль, которую наделяете привилегиями, необходимыми для выполнения приложения. Пользователи вообще не имеют никаких прав, кроме права обращаться к базе данных. Чтобы активизировать роль, приложение обращается к системной процедуре sp_setapprole. При этом приложение должно передать пароль, который при пересылке по физическим линиям связи можно замаскировать.

Едва появившись, роли приложения обещали многое, но с их использованием связаны две проблемы:

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

•    В версиях SQL 7 и SQL 2000 нет способа, позволяющего отказаться от однажды заданной роли приложения. По этой причине использование ролей приложения не позволяет использовать пул подключений. Сегодня большинство приложений устанавливают подключение только для того, чтобы выдать запрос, и затем отключаются, полагаясь на то, что пул подключений поддерживается клиентским API­интерфейсом, что удешевляет повторное подключение.

Хорошая новость заключается в том, что последняя проблема решена в версии SQL 2005. Теперь вы можете возвратить из процедуры sp_setapprole cookie­данные, а затем, перед тем как отключиться, передать их в процедуру sp_unsetapprole. (Синтаксис см. в разделе sp_setapprole документации Books Online.)

Эта возможность не только позволяет использовать пул подключений наряду с ролями приложения, но также открывает возможности для наличия нескольких ролей приложения с пользовательскими разрешениями для различных задач, аналогично тому, что мы обсуждали для сертификатов и условия EXECUTE AS. То есть, вы указали бы роль приложения, вызвали хранимую процедуру, которой необходимы особые права, а затем вернули эту роль в исходное состояние. (Обратите внимание, вы не можете обратиться к процедуре sp_setapprole из хранимой процедуры; ее обязательно надо вызывать из области видимости верхнего уровня.) Но из­за проблем с паролем, я не рекомендовал бы такое решение.

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

Когда вы используете роли приложений, функции, которые возвращают имена учетных записей — SYSTEM_USER, suser_sname() и т. д. — по­прежнему возвращают имя учетной записи реального пользователя. Однако, функции, которые возвращают пользовательское имя уровня базы данных — USER, user_name() — возвращают имя роли приложения.

«Приложения-посредники»

Я уже неоднократно упоминал в этой статье о приложениях­посредниках, главным образом, в разделе о предложении EXECUTE AS и в разделе, посвященном команде SET CONTEXT_INFO. Здесь мне хотелось бы сделать только несколько дополнительных заме­чаний.

Чтобы «приложение­посредник» имело смысл, приложение должно иметь, по крайней мере, три уровня. Средний уровень обеспечивает аутентификацию пользователя, а затем выполняет подключение к серверу SQL Server. То же самое можно организовать с помощью ролей приложений, но с одним отличием: приложение­посредник может быть учетной записью ОС Windows, следовательно, в этом случае отсутствует передача пароля по физическим линиям связи.

Интересное наблюдение по поводу команды SET CONTEXT_INFO: эта команда могла бы полностью заменить предложение EXECUTE AS, чтобы заимствовать права реального пользователя. Ее преимущество заключается в том, что приложению­посреднику не нужны были бы никакие дополнительные привилегии вообще, но только разрешение выполнять хранимые процедуры приложения. Однако, учитывая проблему «SQL inject», о которой я упоминал в предыдущем параграфе, предложение EXECUTE AS с его усло­виями NO REVERT и WITH COOKIE представляется более безопасным.

Windows-утилиты для администраторов баз данных SQL Server

DOWNLOAD

В настоящей статье кратко изложено описание Windows-утилит и для GUI, и для командной строки, предназначенных для администраторов баз данных SQL Server и всем заинтересованным в изучении новых полезных приемов. Во всех примерах используются встроенные Windows-утилиты/команды, доступные в Resource Kits, Administration Tools и Support Tools.

Разоблачение мифа о временных объектах. Часть 1

DOWNLOAD

Программисты, пишущие на T­SQL, обычно спрашивают: «Какая разница между временными таблицами и переменными типа таблица? Как насчет табличных выражений, таких как производные (derived) таблицы, общие табличные выражения (common table expressions, CTEs), виды и встроенные (inline) функции? Где следует воспользоваться ими, а не временными таблицами или переменными типа таблица?» К сожалению, много неясного в исполь­зо­вании данных временных объектов и кон­струкций, как, впрочем, и много ложных мифов.
Во второй половине этой статьи, чтобы прояснить смысл сказанного, я углублюсь в обсуждение темы путем рассмотрения конкретных примеров.

Немного предварительной теории перед конкретными примерами

Я слышал, как люди говорят, что у переменных типа таблица нет физического представления, что они находятся только в памяти, в то время как временные таблицы имеют физическое представление в базе данных tempdb — это ложный миф номер один. Некоторые люди убеждены, что табличные выражения (например, извлекаемые таблицы, CTE) всегда реализуются физически — еще один ложный миф. К сожалению, эти мифы так распространены, что постоянно слыша такие советы, программисты неизбежно прислушиваются к ним и пишут плохо работающий код в своих программных
продуктах.

Итак, давайте откроем правду о временных объектах и разоблачим эти мифы. Я начну с общего обзора каждого типа временных объектов (т. е. локальные временные таблицы, переменные типа таблица, табличные выражения), затем дам несколько общих рекомендаций по их использованию. В следующем месяце я приведу более конкретные и реальные примеры — с анализом специфических случаев, запросов и планов выполнения, чтобы сделать все более конкретным.

Локальные временные таблицы

Локальные временные таблицы (имена которых начинаются с префикса #) создаются в базе tempdb — а именно, имеют физическое представление:

CREATE TABLE #T(col1 INT, …);

Также как и в случае с постоянными таблицами, если страницы данной временной таблицы становятся периодически доступны для SQL Server, существует потенциальная возможность того, что они останутся в кэше и там к ним будет разрешен доступ. Локальная временная таблица принадлежит сеансу, который ее создал, и видима только данному сеансу. Если ваш код создает ее в рамках произвольного пакета команд (ad­hoc batch), временная таблица автоматически уничтожается при окончании сеанса. Область видимости от точки создания и на протяжении всего сеанса — а именно, таблица видна всем хранимым процедурам и триггерам в сеансе. Если ваш код создает локальную временную таблицу с помощью хранимой процедуры или триггера, она автоматически уничтожается при окончании создавшей ее процедуры, а не когда разрывается сеанс. В таком случае, нижние уровни в стеке вызовов будут иметь доступ к временной таблице, а верхние нет.

SQL Server создает и хранит статистику (гистограммы), распределение данных, хранящихся во временных таблицах. Оптимизатор запросов SQL Server обращается к статистике, когда, например, необходимо оценить избирательность фильтров для определения необходимости использования индекса и каким именно образом.

После создания таблицы, вы можете средствами языка определения данных (data definition lan­guage,DDL) вносить изменения во временную таблицу (например, создать индекс). Учтите, что некоторые действия с временными таблицами могут вызвать перекомпиляцию хранимых процедур — например, изменение данных, обновление статистических данных, DDL изменения. SQL Server считает любые изменения, вносимые вами во временную таблицу (включая и DDL изменения) частью явной транзакции, если таковая существует. И в случае отката транзакции, все изменения во временной таблице также будут отменены.

Для создания и заполнения временной таблицы из результирующего запроса, вы можете использовать выражение SELECT INTO. SELECT INTO — объемная операция, которую SQL Server по минимуму заносит в журнал, при выборе модели восстановления базы данных FULL. Потому что моделью восстановления для tempdb является SIMPLE и это нельзя изменить, операция SELECT INTO, которая создает и заполняет временную таблицу, будет занесена в журнал по минимуму.

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

Переменная типа таблица

Переменные табличного типа объявляются аналогично другим локальным переменным с помощью оператора DECLARE:

DECLARE @T TABLE(col1 INT, …);

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

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

Также как и с локальными временными таблицами, если страницы табличной переменной становятся периодически доступны SQL Server, существует потенциальная возможность того, что они останутся в кэше и там к ним будет разрешен доступ. Также как и с локальными временными таблицами, табличная переменная принадлежит создавшему ее сеансу, и видима только в этом сеансе. Но его область более ограничена: он виден только локальным командам, это относится ко всем локальным переменным. Когда отрабатывает создавшая ее команда, табличная переменная автоматически уничтожается. Например, если вы выполните следующий код, пытаясь обратиться к табличной переменной из команды, отличной от создавшей ее, вы получите ошибку:

DECLARE @T TABLE(col1 INT);
GO
SELECT * FROM @T;

На рис. 2 показан результат выполнения этого кода. Табличная переменная не видна ни на внешнем, ни на внутреннем уровне стека вызовов. Если в своем коде вы объявите табличную переменную в подпрограмме (хранимой процедуре, триггере, пользовательской функции), она будет невидима подпрограмме, являющейся частью подпрограммы, вызвавшей объявление переменной — в отличие от локальных временных таблиц.

SQL Server не создает и не хранит статистику по табличным переменным. Следовательно, оптимизатор запросов SQL Server не может произвести оценку выборки, так как она основывается на статистике. Вы не можете вносить DDL изменения в табличные переменные после объявления их вашим кодом. Вы не можете явно создавать индексы для табличных переменных, но выбор первичного ключа и ограничения на уникальность после определения таблицы подразумевает создание индексов для табличных переменных, и эти индексы могут использоваться SQL Server в целях повышения производительности, а не только для контроля уникальности.

Факт того, что SQL Server хранит данные статистики для временных таблиц, а для табличных переменных нет, это один из важнейших фактов, который вы должны учитывать, решая, какую из них выбрать. Без статистики SQL Server в некоторых случаях не может произвести оценку выборки; следовательно, учитывая этот факт, у временных таблиц есть преимущество. Но помните о том, что в некоторых случаях оценка выборки не так важна — например, при работе с небольшим объемом данных (например, несколько дюжин строк на паре страниц). Также оптимизатору запросов не всегда нужны данные статистики для нахождения оптимального плана (например, тривиальные планы, такие как получение диапазона от кластеризованного или покрывающего индекса). Тот факт, что у табличных переменных нет данных статистики, дает им преимущество, а именно: вы избавляетесь от рекомпиляций, связанных с изменениями данных и обновлением статистических данных. Это плюс использования табличных переменных.

При существовании явной транзакции изменения, вносимые в табличную переменную, не являются частью транзакции; точнее, они являются только частью одной единичной элементарной операции. Так что, если отдельное выражение прерывается посередине, изменения, внесенные этим выражением, отменяются. Однако если явная транзакция откатывается назад, изменения, внесенные в табличную переменную, не отменяются. В некоторых случаях вы можете положиться на это свойство — например, когда вы хотите провести аудит попыток изменения данных в коде триггера, и также хотите отменить изменения. Вы можете скопировать содержимое удаленных и вставленных триггером таблиц в табличные переменные, а затем — в новой транзакции в триггере — провести аудит попыток изменения, путем копирования данных из вставленных и удаленных таблиц в таблицу, для которой проводится аудит.

У вас не получится провести аудит неудавшихся событий, для временных таблиц это невозможно, потому что при откате изменений, вставленные и удаленныe таблицы опустошаются и изменения, внесенные во временные таблицы, отменяются. А для занесения в журнал, в случае прерывания выражения до полного выполнения, необходима поддержка только отката отдельных выражений для доступа к табличной переменной; этот факт, с учетом ограничения области видимости табличной переменной в сравнении с временной таблицей, дает вам преимущество в производительности — меньше блокировок и записей в журнал, чем в случае с временными таблицами. В вопросах занесения в журнал существует исключение: при заполнении табличной переменной, вы не можете использовать выражение SELECT INTO. Следовательно, у вас будет больше записей в журнал при добавлении данных в табличную переменную, чем при использовании SELECT INTO для временной таблицы.

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

Табличные выражения

Программисты часто задумываются, есть ли преимущество в использовании табличных выражений, таких как производные (derived) таблицы или общие табличные выражения, вместо временных таблиц (или табличных переменных). Важно понимать, что временные таблицы и табличные переменные имеют физическое представление в базе tempdb, тогда как все четыре типа табличных выражений (т. e. «порождаемые» таблицы, CTE, виды, линейные UDF) являются виртуальными конструкциями. При выполнении запроса, обращающегося к табличным выражениям, SQL Server раскрывает его определение (expands its definition), затем оптимизирует и выполняет запрос по отношению к указанным объектам, как если бы все начиналось не с табличного выражения. SQL Server не материализует табличные выражения в физические. Оптимизатор в некоторых случаях может выборочно физически материализовать некоторые промежуточные наборы значений (буферные операторы), но буферизация не имеет никакой связи с табличными выражениями. Вообщем, на самом деле табличные средства не являются средством повышения производительности; это скорее средства упрощения/абстрагирования (в отличие от временных таблиц, которые вы можете использовать как средство, влияющее на производительность). Единственное исключение это индексированные виды, которые физически реализуют результирующий набор вида в B­Дереве.

Предоставленной мною информации может быть недостаточно для разрешения дилеммы — использовать ли табличные выражения или временные таблицы/табличные переменные. Возможно, с помощью примера можно прояснить ситуацию. Допустим, у вас есть большая таблица с 20 миллионами строк. Вам необходимо обработать данные и в результате обработки получить 50000 обработанных строк. Вам понадобится обращаться к обрабатываемым данным с помощью многочисленных запросов и запросов, таких, для выполнения которых оптимизатору необходимо произвести оценку селективности для выбора одного плана действий из нескольких возможных. В этом случае нет смысла использовать табличные выражения, потому что каждый запрос к табличному выражению стоит вам просмотра всех 20 миллионов строк и их обработки. В данном примере имеет смысл использовать временную таблицу. Вы материализуете 50000 строк, индексируете таблицу для поддержки этих запросов, а потом выполняете набор запросов для временной таблицы. Еще один вариант — использовать индексированный вид, который также материализует обрабатываемые данные. Но, однако, помните, что индексированный вид отрицательно скажется на производительности при работе с базовой таблицей.

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

Сначала теория, потом практика

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

(Возврат на основную страницу)