Продолжение Администрирование БД

Администрирование. Лекция 8. Продолжение Администрирование БД.

Гарантией того, что ваша система будет работать эффективно и правильно является грамотное администрирование и регулярное выполнение задач обслуживания баз данных. SQL Server содержит множество средств для автоматического конфигурирования, такие как динамическое управление памятью, пул памяти, использование дополнительное памяти, различные параметры. С помощью многочисленных параметров системной хранимой процедуры sp_configure можно активизировать/останавливать различные свойства:

sp_configure [ [ @configname = ] 'option_name'  [ , [ @configvalue = ] 'value' ] ]

[ @configname= ] 'option_name'

Имя параметра конфигурации. Аргумент option_name имеет типvarchar(35) и значение по умолчанию NULL.

[ @configvalue= ] 'value'

Новое значение параметра конфигурации. Аргумент value имеет тип int и значение по умолчанию NULL. Максимальное значение зависит от конкретного параметра.

USE master;

GO

Рекомендуемые материалы

EXEC sp_configure 'recovery interval', '3';

RECONFIGURE WITH OVERRIDE;

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

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

2.1 Динамическое управление памятью

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

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

2.2 Пул памяти

Объектный пул (англ. object pool) — порождающий шаблон проектирования, набор инициализированных и готовых к использованию объектов. Когда системе требуется объект, он не создаётся, а берётся из пула. Когда объект больше не нужен, он не уничтожается, а возвращается в пул.

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

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

· Буферный кэш. Содержит страницы базы данных, считанные в память. Буферный кэш обычно забирает основную часть пула памяти.

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

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

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

· Кэш процедур. Используется для хранения планов исполнения операторов Transact-SQL (T-SQL) и хранимых процедур, когда происходит их выполнение.

2.3 Статистика

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

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

Статистику можно собирать по двум типам колонок:

· по тем, что являются частью индекса,

· по тем, что не входят в индекс, но используются в предикате запроса (в предложении WHERE).

Оставив принятые по умолчанию значения SQL Server для базы данных, вы разрешаете автоматическое создание обоих типов статистики в SQL Server. Статистика по индексированным колонкам создается при создании соответствующего индекса. Статистика по неиндексированным колонкам создается, когда она требуется для какого-либо запроса (только по одной колонке, а не по нескольким, как вы увидите в подразделе "Команда CREATE STATISTICS" этого раздела). Если статистика устарела (не использовалась в течение определенного периода времени), то SQL Server автоматически удаляет ее.

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

2.4 Планы обслуживания баз данных

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

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

· Оптимизации.

· Проверки целостности.

· Резервное копирование баз данных.

· Резервное копирование журнала транзакций.

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

Для создания плана обслуживания используется мастер Database Maintenance Plan Wizard.

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

• Reorganize data and index pages (Реорганизация страниц данных и индексов). Этот флажок указывает, что все индексы и все таблицы базы данных будут удалены и воссозданы с использованием указанного коэффициента заполнения (или количества свободного места на каждой странице), что может повысить производительность обновлений. В случае таблиц, предназначенных только для чтения, реорганизация страниц не является необходимостью. В случае таблиц, для которых часто выполняются вставки или изменения, свободное место, которое первоначально было доступно на ваших индексных страницах, постепенно заполняется, и начинает происходить фрагментация страниц.
• Update statistics used by query optimizer (Обновление статистики, используемой оптимизатором запросов) При установке этого флажка SQL Server выполнит перерасчет статистики распределения по всем индексам в соответствующей базе данных. SQL Server использует эту информацию для выбора оптимального плана исполнения для запросов.
• Remove unused space prom database files (Удалить неиспользуемое пространство из файлов базы данных) Этот флажок применяется для удаления неиспользуемого пространства; этот процесс также известен как уплотнение (сжатие) файла (file shrink). Вы можете задать, насколько большим должно стать неиспользуемое пространство, чтобы произошло сжатие файла, а также процент пространства, которое должно остаться свободным после сжатия.

Автоматизация административных задач осуществляется заданиями, оповещениями, операторами. С помощью службы SQLServerAgent можно управлять автоматизацией задач. Maintenance Plan Wizard и Create Job Wizard помогают конфигурировать задачи и планы работ. Служба SQLServerAgent имеет собственный журнал ошибок, который позволяет протоколировать любые действия, связанные со службой.

3.1 Задания

Задания – это административные задачи, которые определяются один раз и могут выполняться многократно. Вы можете запускать задание вручную, а также планировать запуск задания системой SQL Server в определенное время, в соответствии с регулярным расписанием или при возникновении оповещения. (Об оповещениях описаны далее в разделе "Оповещения".) Задания могут состоять из операторов Transact-SQL (T-SQL), команд Microsoft Windows NT или Microsoft Windows 2000, исполняемых программ или сценариев Microsoft ActiveX. Задания также автоматически создаются для вас, когда вы используете репликацию или создаете план обслуживания базы данных. Задание может состоять из одного или нескольких шагов, и каждый шаг может быть вызовом более сложного набора шагов, например обращением к хранимой процедуре. SQL Server автоматически следит за результатом выполнения заданий (успешное или неуспешное завершение); вы можете задавать оповещения, которые будут отправляться в каждом случае.

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

SQL Server поддерживает журнал (историю) с информацией о выполнении задания в таблице sysjobhistory системной базы данных msdb. Вы можете просмотреть информацию журнала выполнения задания с помощью Enterprise Manager или T-SQL.

Информация в лекции "10 Понятие типов данных" поможет Вам.

3.2 Оповещения

Оповещение – это действие, которое возникает на сервере в ответ на событие или состояние производительности. Оповещения могут реализоваться как уведомления операторам, могут инициировать запуск указанных заданий и могут перенаправлять события другому серверу. Событие – это ошибка или сообщение, которые записываются в журнал событий приложений Windows NT или Windows 2000 (вы можете просматривать этот журнал с помощью утилиты Event Viewer, поставляемой вместе с Windows NT или Windows 2000). Состояние производительности – это характеристика работы системы, доступная для мониторинга с помощью Performance Monitor (Windows NT) или System Monitor (Windows 2000), такая как процент использования ЦП или количество блокировок, используемых SQL Server. В этой лекции мы будем рассматривать System Monitor в Windows 2000, хотя Performance Monitor в Windows NT действует почти так же.

При возникновении какого-либо события служба SQLServerAgent сравнивает это событие со списком определенных вами оповещений, и если для этого события существует оповещение, то происходит запуск этого оповещения. Запуск оповещения для определенного состояния производительности происходит в том случае, если указанный объект SQL Server в System Monitor достигает определенного порогового значения производительности, например, счетчик User Connections (Количество пользовательских соединений) внутри объекта General Statistics (Общая статистика) в System Monitor. Например, вы можете указать запуск оповещения, если значение этого счетчика достигнет 50.

3.3 Операторы

Операторы – это отдельные люди, которые могут получать уведомление от SQL Server по завершении какого-либо задания или при возникновении какого-либо события. Оператор – это человек, ответственный за обслуживание одной или нескольких систем, на которых работает SQL Server. Вы уже знаете, как определять сообщение уведомления, которое будет отправляться оператору.

Имеется три метода, используемых для связи с операторами: отправка сообщений электронной почты, отправка на пейджер и использование команды NET SEND(которая отправляет сетевое сообщение на компьютер оператора.) Чтобы можно было применять каждый из этих методов, ваша система должна отвечать определенным требованиям. Для связи через электронную почту и с пейджером вы должны инсталлировать на сервере совместимый с MAPI-1 клиент ("MAPI" означает "Messaging API" – интерфейс прикладного программирования для сообщений), такой как Microsoft Outlook или Microsoft Exchange Client, и должны создать почтовый профиль для службы SQLServerAgent. Для пейджинговой связи вам нужно также инсталлировать на почтовом сервере программное обеспечение сторонних фирм для связи электронной почты с пейджером, которое обрабатывает входные сообщения электронной почты и преобразует их в пейджинговые сообщения. Чтобы использовать NET SEND, у вас должна работать операционная система Windows NT или Windows 2000, поскольку NET SEND не поддерживается в Microsoft Windows 95/98.