Параметри конфігурації – ЧАСТИНА 5

Ехес sp_configure two digit year cutoff, 2041 RECONFIGURE

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

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

Таблиця 347 Параметри конфігурації індексів

Параметр

Рівень

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

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

Автоматичне ство

База

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:11873

[CategoryName] ON               NULL

Змінити параметри можна за допомогою додавання прапора ON або OFF після імені таблиці До того ж існує можливість зміни параметрів і конкретного індексу – для цього слід додатково передати збереженій процедурі його імя

Додаткова Детальніше про процедуру створення індексів см в главі 17 Особливості управ-інформація лення індексами та їх налаштування ми обговоримо в главі 50

Конфігурування автоматичних налаштувань баз даних

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

Таблиця 348 Параметри конфігурації індексів

Параметр

Рівень

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

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

Автоматичне за

База

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 використовуються параметри, перераховані в табл 349

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

Таблиця 349 Параметри конфігурації індексів

Параметр

Рівень

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

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

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

Сервер

Ехес sp_configure cursor threshold

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

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

&quot

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 RECONFIGUR E 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 (табл 3410) визначають міру підтримки сервером стандарту ANSI

Таблиця 3410 Параметри підтримки стандарту 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

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

Параметри конфігурації тригерів перераховані в табл 3411 Вони управляють порядком роботи сервера баз даних з тригерами

Таблиця 3411 Параметри підтримки стандарту 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

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

Параметри конфігурації стану бази даних перераховані в табл 3412 Як правило, зміни в ці параметри вносяться адміністратором при виконанні операцій обслуговування бази даних

Таблиця 3412 Параметри конфігурації стану бази даних

Параметр

Рівень

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

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

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

База

даних

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 60) до 80 (відповідне SQL Server 2005) Установка рівня сумісності нижче вісімдесятого може знадобитися, якщо було виконано оновлення ядра бази даних, але необхідно продовжувати підтримувати режим роботи старих версій сервера

Програмним шляхом рівень сумісності можна встановити таким чином: Ехес sp_dbcmptlevel база_данних, 80

Параметри конфігурації відновлення

Параметри конфігурації відновлення перераховані в табл 3413

Таблиця 3413 Параметри конфігурації стану бази даних

Параметр

Рівень

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

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

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

База

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 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*