Главная » Microsoft SQL Server, Базы данных » Параметры конфигурации – ЧАСТЬ 5

0

ЕХЕС sp_configure ’two digit year cutoff’, 2041 RECONFIGURE

Параметры конфигурации индекса

Параметры статистики и заполнения индекса (табл. 34.7) устанавливают значения по умолчанию для новых индексов, создаваемых в сервере баз данных.

Таблица 34.7. Параметры конфигурации индексов

Параметр

Уровень

Графический интерфейс установки

Программная установка

Автоматическое соз

База

Management Studio

ALTER DATABASE <имя_базы> SET

дание статистики

данных

auto_create statistics {ON | OFF}

Автоматическое об

База

Management Studio

ALTER DATABASE <имя_базы> SET

новление статистики

данных

auto_update_statistics {ON | OFF}

Множитель заполне

Сервер

Management Studio

EXEC sp_configure ‘fill factor’

ния индекса

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

Будет получен следующий результат:

Global statistics settings for [Northwind]:

Automatic update statistics: ON Automatic create statistics: ON

settings for table [Categories]

Index Name   AUTOSTATS Last Updated

[PK_Categories] ON                2006-02-27 19:29:11.873

[CategoryName] ON               NULL

Изменить параметры можно с помощью добавления флага ON или OFF после имени таблицы. К тому же существует возможность изменения параметров и конкретного индекса — для этого следует дополнительно передать хранимой процедуре его имя.

Дополнительная Подробнее о процедуре создания индексов см. в главе 17. Особенности управ- информация ления индексами и их настройки мы обсудим в главе 50.

Конфигурирование автоматических настроек баз данных

Режим работы базы данных, принятый по умолчанию, конфигурируется с помощью четырех параметров (табл. 34.8). Все эти параметры можно установить в графическом интерфейсе Management Studio во вкладке Options диалогового окна Database Properties.

Таблица 34.8. Параметры конфигурации индексов

Параметр

Уровень

Графический интерфейс установки

Программная установка

Автоматическое за

База

Management Studio

ALTER DATABASE <имя_базы> SET

крытие

данных

auto_close {ON | OFF}

Автоматическое сжа

База

Management Studio

ALTER DATABASE <имя_базы> SET

тие

данных

auto_shrink {ON | OFF}

Автоматическое соз

База

Management Studio

ALTER DATABASE <имя_базы> SET

дание статистики

данных

auto_create statistics {ON | OFF}

Автоматическое об

База

Management Studio

ALTER DATABASE <имя_базы> SET

новление статистики

данных

auto_update_statistics {ON | OFF}

Автоматическое закрытие

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

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

Многие клиентские приложения постоянно открывают и закрывают подключе- Бнишние! ния к серверу баз данных. В такой ситуации включение параметра автоматического закрытия способно свести к нулю производительность SQL Server.

Чтобы управлять параметром автоматического закрытия программным путем, используйте следующую команду:

ALTER DATABASE база_данных SET AUTO_CLOSE ON | OFF

Автоматическое сжатие

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

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

Дополнительная Процедура сжатия файлов данных и журнала транзакций подробно описана в

информация главе 36.

Чтобы установить параметр автоматического сжатия, следует выполнить в программе следующую инструкцию:

ALTER DATABASE база_данных SET AUTO_SHRlNK ON | OFF

Автоматическое создание статистики

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

Чтобы установить параметр автоматического формирования статистики, следует выполнить в программе следующую инструкцию:

ALTER DATABASE база_данных SET AUTO_CREATE_STATISTICS ON | OFF

Автоматическое обновление статистики

Устаревшая статистика распределения данных вряд ли принесет какую-либо пользу. Установка данного параметра гарантирует, что статистика будет обновляться автоматически. По умолчанию автоматическое обновление статистики включено во всех редакциях SQL Server.

Для управления этим параметром в программном коде следует использовать следующую инструкцию:

ALTER DATABASE база_данных SET AUTO_UPDATE_STATISTICS ON | OFF

I               Настройки запросов и индексов в большой мере зависят от статистики распре- S VS деления данных. Стратегии, учитывающие статистику, подробно рассмотрены в

*                 I * главе 49.

Параметры конфигурации курсора

Для управления режимом работы курсора в SQL Server используются параметры, перечисленные в табл. 34.9.

! I Для просмотра статистики открытых курсоров в различных базах данных можно S VS выбрать sysdm_exec_cursors в динамическом представлении управления.

Таблица 34.9. Параметры конфигурации индексов

Параметр

Уровень

Графический интерфейс установки

Программная установка

Порог курсора

Сервер

ЕХЕС sp_configure ‘cursor threshold’

Закрытие курсора при подтверждении

Сервер, база данных, подключение

"

ALTER DATABASE <база_данных> cursor_close_on_commit {ON |

SET

OFF

Значения курсора по умолчанию

База данных

ALTER DATABASE <база данных> SET cursor_default {LOCAL | GLOBAL}

Порог курсора

Свойство порога регулирует количество строк, составляющих набор ключевых данных курсора, генерируемый асинхронно. Синхронные ключевые наборы данных работают быстрее, чем остальные типы, однако они потребляют больше ресурсов. Если параметр cursor threshold установить в значение 0, то все ключевые наборы данных будут генерироваться асинхронно. Установка этого параметра в значение -1 приведет к синхронной генерации ключевых наборов. Этот вариант может подойти для небольших ключевых наборов; в больших же наборах это может стать источником проблем. В следующем примере устанавливается максимальный размер синхронного ключевого набора данных курсора в 10000 строк:

ЕХЕС sp_configure ‘cursor threshold’, 100 00 RECONFIGURE WITH OVERRIDE

Закрытие курсора при подтверждении

Если этот параметр включен (т.е. имеет значение on), то после подтверждения транзакции курсор будет автоматически закрываться. Если же этот параметр отключен (т.е. имеет значение off), то курсор остается открытым до тех пор, пока не будет явно закрыт с помощью команды close cursor.

Для включения режима автоматического закрытия курсора используйте следующую команду: SET CURSOR_CLOSE_ON_COMMIT ON

Также вы можете воспользоваться и альтернативной командой:

ALTER DATABASE база_данных SET CURSOR_CLOSE_ON_COMMIT ON | OFF

Значения курсора, принятые по умолчанию

Установка этого параметра в значение local определяет курсор локальным по отношению к объекту, объявившему его. Его установка в значение global задает область определения курсора, выходящую за пределы объекта, создавшего его.

Для присвоения значения этому параметру в программе следует использовать следующую инструкцию:

ALTER DATABASE база_данных SET CURSOR_DEFAULT LOCAL | GLOBAL

Параметры конфигурации SQL ANSI

Параметры конфигурации SQL ANSI (табл. 34.10) определяют меру поддержки сервером стандарта ANSI.

Таблица 34.10. Параметры поддержки стандарта ANSI

Параметр

Уровень

Графический Программная установка

интерфейс

установки

Значения ANSI по умолчанию

Подключение

SET ANSI_DEFAULTS {ON | OFF}

Режим работы с пустыми значениями по умолчанию

Сервер, база данных, подключение

Management

Studio

ALTER DATABASE <база данных> ansi_null_Default {ON | OFF}

SET

Порядок участия пустых значений в операциях сравнения

Сервер, база данных, подключение

Management

Studio

ALTER DATABASE <база_данных> ansi_nulls {ON | OFF}

SET

Дополнение нулями и пробелами

Сервер, подключение

ALTER DATABASE <база_данных> ansi_jpadding {ON | OFF}

SET

Предупреждения ANSI

Сервер, база данных, подключение

ALTER DATABASE <база_данных> ansi_warnings {ON | OFF}

SET

Реакция на арифметические ошибки

Сервер, подключение

ALTER DATABASE <база данных> arithabort {ON | OFF}

SET

Игнорирование арифметических операций

Сервер, подключение

SET arithignore {ON | OFF}

Реакция на ошибки округления

База данных

ALTER DATABASE <база_данных> numeric_roundabort {ON | OFF]

SET

Конкатенация с пустым значением

База данных, подключение

ALTER DATABASE <база_данных> concat_null_yields_null {ON

SET | OFF}

Использование идентификаторов в кавычках

База данных

ALTER DATABASE <база_данных> quoted_identifier {ON | OFF}

SET

Совместимость со стандартом ANSI SQL-92

Подключение

SET fips flagger {ENTRY | FULL | INTERMEDIATE | OFF}

Параметры, установленные по умолчанию для подключения, оказывают влияние на пакеты, выполняемые в текущей сессии соединения с сервером. Большая часть перечисленных выше параметров обеспечивает совместимость SQL Server со стандартом ANSI. Так как данные параметры редко устанавливаются в серверах баз данных, рекомендуется присваивать им значения явно, в начале программного кода. Это позволит обеспечить работоспособность программ в среде, отличной от разработок компании Microsoft.

Например, язык Т-SQL требует наличия инструкции явного открытия транзакции begin transaction. В то же время в сервере баз данных Oracle эта инструкция подразумевается в начале каждого пакета. Если вы предпочитаете работать с неявными транзакциями, то проще установить параметр обязательного объявления начала транзакции в начале каждого пакета, чем полагаться на значения сервера, принятые по умолчанию. Параметры уровня сервера будут оказывать влияние на все выполняемые пакеты и могут привести к ошибкам выполнения некоторых расширений стандарта ANSI SQL, введенных в продуктах компании Microsoft. По этой причине рекомендуется оставлять параметры подключения без изменений, выполняя настройку уже в программном коде.

Параметры конфигурации SQL ANSI устанавливаются с помощью инструкции ALTER DATABASE. Из соображений совместимости с предыдущими версиями SQL Server также доступна хранимая процедура sp_dboption.

Режим работы с пустыми значениями, заданный по умолчанию

Параметр ansi_null_def ault управляет порядком работы с пустыми значениями, используемым по умолчанию. Значения этого параметра применяются, если при создании таблицы не был указан явно параметр NULL или NOT_NULL.

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

ALTER DATABASE база_данных SET ANSI_NULL_DEFAULT ON | OFF

Порядок участия пустых значений в операциях сравнения

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

Установить этот параметр в программном коде можно следующим образом:

ALTER DATABASE база_данных SET ANSI_NULLS ON | OFF

Дополнение нулями и пробелами

Параметр базы данных ansi_padding оказывает влияние только на новые создаваемые столбцы. Если он установлен в значение on, то в числовых значениях остаются лидирующие нули, а в символьных значениях— лидирующие и замыкающие пробелы. Если значением этого параметра является of f, то лидирующие и замыкающие нули и пробелы обрезаются.

Установить этот параметр в программном коде можно таким образом:

ALTER DATABASE база_данных SET ANSI_PADDING ON | OFF

Предупреждения ANSI

Параметр ansi_warnings управляет отображением предупреждений и сообщений об ошибках, связанных с соблюдением стандарта ANSI. Если этот параметр установлен в значение of f, то все ошибки, такие как деление на нуль или участие пустых значений в итоговых функциях, пропускаются; в противном случае отображаются все ошибки.

Установить этот параметр в программном коде можно следующим образом:

ALTER DATABASE база_данных SET ANSI_WARNINGS ON | OFF

Реакция на арифметические ошибки

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

Установить данный параметр можно следующим образом:

ALTER DATABASE база_данных SET ARITHABORT ON | OFF

Реакция на ошибки округления

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

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

ALTER DATABASE база_данных SET NUMERIC_ROUNDABORT ON | OFF

Конкатенация с пустым значением

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

Установить этот параметр в программном коде можно следующим образом:

ALTER DATABASE база_данных SET CONCAT_NULL_YIELDS_NULL ON | OFF

Использование идентификаторов в кавычках

Параметр quoted_identif ier, будучи установленным в значение on, позволяет ссылаться на идентификаторы, заключенные в двойные кавычки. Если же этот параметр имеет значение off, то заключение идентификаторов в кавычки не допускается; к тому же идентификатором не может быть ключевое слово:

ALTER DATABASE база_данных SET QUOTED_IDENTIFIER ON | OFF

По умолчанию этот параметр имеет значение off. Для создания или изменения индексированных представлений или индексированных вычисляемых столбцов этот параметр должен быть установлен в значение on.

Параметры конфигурации триггеров

Параметры конфигурации триггеров перечислены в табл. 34.11. Они управляют порядком работы сервера баз данных с триггерами.

Таблица 34.11. Параметры поддержки стандарта ANSI

Параметр

Уровень

Графический интерфейс установки

Программная установка

Разрешение вложения триггеров

Разрешение рекурсии триггеров

Сервер

База данных

Management Studio Management Studio

EXEC sp_configure ‘nested triggers’

ALTER DATABASE <база_данных> SET recursive_triggers {ON | OFF}

Режим работы триггеров может быть установлен как на уровне сервера, так и на уровне базы данных.

Вложение триггеров

Триггеры могут быть вложены друг в друга, при этом число уровней может достигать тридцати двух. Этот параметр конфигурируется на уровне сервера.

В программном коде разрешить вложение триггеров можно следующим образом:

EXEC sp_configure ‘nested triggers’, 1 RECONFIGURE

Рекурсия триггеров

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

Дополнительная На практике довольно часто путают понятия вложенных и рекурсивных тригге- рформация \ ров. Подробнее о триггерах, а также о порядке вызова одних триггеров из дру- ;                            гих см. в главе 23.

По умолчанию этот параметр имеет значение off. Связанный параметр допустимости вложения триггеров устанавливается на уровне сервера. В программном коде Т-SQL параметр допустимости рекурсии триггеров можно установить следующим образом:

ALTER DATABASE база_данных SET RECURSIVE_TRIGGERS ON | OFF

Параметры конфигурации состояния базы данных

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

Таблица 34.12. Параметры конфигурации состояния базы данных

Параметр

Уровень

Графический интерфейс установки

Программная установка

Автономный режим работы базы данных

База

данных

ALTER DATABASE <имя SET offline

_базы>

Доступ только для чтения

База

данных

Management Studio

ALTER DATABASE <имя_ SET read_only

разы>

Ограничение доступа только членами ролей db_owner, dbcreator И sysadmin

База

данных

Management Studio

ALTER DATABASE <имя_ SET restricted_user

_базы>

Монопольный режим доступа

База

данных

Management Studio

ALTER DATABASE <имя_ SET single_user

_базы>

Отключение монопольного режима

База

данных

Management Studio

ALTER DATABASE <имя_ SET multi_user

разы>

Уровень совместимости

База

данных

Management Studio

ALTER DATABASE <имя_ SET read_only

базы>

Состояние базы данных также может быть установлено и с помощью команды ALTER DATABASE. Из соображений совместимости с предыдущими версиями SQL Server также была оставлена хранимая процедура sp_dboption.

Уровень доступа к базе данных

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

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

ALTER DATABASE база_данных SET OFFLINE

Параметр read_only используется для ограничения состава операций, выполняемых в базе данных, только чтением. Если в момент установки этого параметра кто-то из пользователей был подключен к базе, на него действие данного параметра не распространяется. Для восстановления стандартного режима доступа к базе данных, когда разрешены операции чтения и записи, используется параметр read_write.

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

ALTER DATABASE база_данных SET READ_ONLY

Существуют еще три режима доступа к базе данных: single_user, restricted_user и multi_user. Эти параметры определяют, кто из пользователей имеет право доступа к базе данных. Однопользовательский режим доступа уместен при выполнении операций обслуживания базы данных. Параметр restricted_user ограничивает круг лиц, имеющих доступ к базе данных, только членами ролей db_owner, dbcreator и sysadmin. Параметр multi_user позволяет восстановить обычный режим функционирования базы данных.

Для установки ограниченного режима доступа к базе данных используется следующая команда:

ALTER DATABASE база_данных SET SINGLE_USER

Уровень совместимости

В SQL Server уровень совместимости может быть установлен в значения от 60 (соответствующее версии SQL Server 6.0) до 80 (соответствующее SQL Server 2005). Установка уровня совместимости ниже восьмидесятого может понадобиться, если было выполнено обновление ядра базы данных, но необходимо продолжать поддерживать режим работы старых версий сервера.

Программным путем уровень совместимости можно установить следующим образом: ЕХЕС sp_dbcmptlevel база_данных, 80

Параметры конфигурации восстановления

Параметры конфигурации восстановления перечислены в табл. 34.13.

Таблица 34.13. Параметры конфигурации состояния базы данных

Параметр

Уровень

Графический интерфейс установки

Программная установка

Модель восстановле

База

Management Studio

ALTER DATABASE <имя_базы>

SET

ния

данных

RECOVERY {FULL | BULK LOGGED |

simple}

Определение разры

База

Management Studio

ALTER DATABASE <имя базы>

SET

вов страниц

данных

TORN_PAGE_DETECTION {ON |

OFF}

Параметр

Уровень

Графический интерфейс установки

Программная установка

Время ожидания резервного копирования

Сервер

Задержка носителя

Сервер

ЕХЕС sp_configure ’media retention1

Интервал восстановления

Сервер

EXEC sp configure ‘recovery interval’

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

Модель восстановления

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

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

?               Модель с неполным протоколированием. В журнале протоколируются все инструкции DML. Массовые операции вставки не протоколируются, а только маркируются.

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

Дополнительная Более подробно различные модели восстановления будут описаны в главе 36.

информация’

Для установки модели восстановления программным путем используется инструкция SET RECOVERY.

Определение разрывов страниц

Несмотря на то что SQL Server работает со страницами данных объемом в 8 Кбайт, операционная система выполняет физическую запись на диск секторами объемом в 512 байт. Если в ходе операции записи страницы данных произошла ошибка, то на диск могут быть записаны не все ее секторы.

Для поддержки свойств ACID базы данных в каждый сектор записывается контрольный бит. Если все секторы были успешно обновлены, то контрольные биты должны в них совпадать. Если в процессе восстановления какой-либо из контрольных битов будет отличаться, то SQL Server может определить условие разрыва страницы и пометить базу данных как сомнительную.

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

Дополнительная Все дополнительные параметры восстановления (такие как время ожидания информация резервирования, удержание носителей и интервал восстановления) подробно описаны в главе 36.

Резюме

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии