Администрирование Microsoft SQL Server

Параметры конфигурирования памяти SQL Server

Следующие параметры конфигурирования SQL Server связаны с конкретными аспектами распределения памяти. Вы можете задать эти параметры с помощью SQL Server Enterprise Manager или с помощью хранимой процедуры sp_configure. Для просмотра всех этих параметров с помощью sp_configure у вас должно быть задано значение 1 для параметра show advanced options (показать дополнительные параметры).

Другие параметры динамического конфигурирования

В SQL Server имеется несколько параметров динамического конфигурирования или SQL Server Enterprise Manager (но не все параметры можно задать через Enterprise Manager). Чтобы задать какой-либо параметр через sp_configure, откройте Query Analyzer (Анализатор очередей) или osql-соединение в окне командной строки и запустите эту хранимую процедуру со следующими параметрами:

sp_configure "имя параметра", значение

Имя параметра – это имя параметра конфигурирования, а значение – это значение, которое вы хотите ему присвоить. Если запустить эту команду, не указывая значение, то SQL Server возвратит текущее значение указанного параметра. Чтобы увидеть список всех параметров и их значений, запустите sp_configure без какого-либо параметра. Несколько параметров считаются дополнительными. Для просмотра и конфигурирования этих параметров с помощью sp_configure вы должны сначала задать для параметра show advanced options (показать дополнительные параметры) значение 1, как это показано ниже:

sp_configure "show advanced options", 1

Параметр show advanced options не оказывает влияния на параметры, которые можно конфигурировать через Enterprise Manager.

Чтобы задать значение какого-либо параметра с помощью Enterprise Manager, сначала откройте в Enterprise Manager окно Properties (Свойства) для сервера, щелкнув правой кнопкой мыши на имени этого сервера и выбрав из контекстного меню пункт Properties (рис. 30.1).

Вы можете затем осуществлять доступ к определенным динамическим параметрам во вкладках этого окна. В следующих разделах описываются динамические параметры SQL Server, не связанные с памятью; в каждом разделе указывается, можно ли задавать соответствующий параметр в Enterprise Manager, и если да, то указывается местоположение этого параметра в окне Properties.

Параметр locks (блокировки)

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

Параметр recovery interval (интервал восстановления)

Параметр recovery interval указывает максимальное количество минут, которое может потратить SQL Server для восстановления после аварии. (См. ниже в этой лекции раздел "Контрольные точки" с более подробным описанием того, как действуют контрольные точки.) Время, которое требуется SQL Server для восстановления базы данных, зависит от момента, когда была создана последняя контрольная точка. Поэтому значение recovery interval используется в SQL Server для динамического определения моментов автоматического применения контрольных точек.

Например, когда происходит "чистое" закрытие SQL Server, контрольные точки создаются по всем базам данных, поэтому при перезапуске SQL Server восстановление занимает немного времени. Но если происходит вынужденная остановка SQL Server без "чистого" отключения (из-за отказа источника питания или аварии другого типа), то при повторном запуске SQL Server должен восстанавливать каждую базу данных путем отката всех транзакций, которые не были фиксированы, и повторного выполнения транзакций, которые были фиксированы, но их изменения еще не были записаны на диск к моменту аварии. Если последняя контрольная точка была создана незадолго до аварии системы, то на восстановление уйдет немного времени. Если последняя контрольная точка была создана задолго до аварии системы, это потребует более длительного времени восстановления.

SQL Server определяет, насколько часто нужно создавать контрольные точки, согласно встроенному алгоритму и, как уже говорилось, в соответствии со значением recovery interval. Например, если вы зададите для recovery interval значение 5, то SQL Server будет создавать контрольные точки по каждой базе данных с такой частотой, чтобы восстановление после аварии занимало бы примерно 5 минут. По умолчанию значение recovery interval равно 0, указывая на автоматическое конфигурирование интервала в SQL Server. Если используется это значение по умолчанию, то время восстановления меньше 1 минуты и контрольные точки создаются для активных баз данных приблизительно каждую минуту. Во многих случаях преимущества частого создания контрольных точек теряются за счет снижения производительности, вызываемого созданием контрольных точек. Со временем вам придется снизить количество создаваемых контрольных точек, увеличив значение recovery interval. Выбираемое вами значение будет зависеть от деловых требований, связанных с тем, сколько времени могут ждать пользователи в случае восстановления системы после аварии. Обычно следует использовать значение в интервале от 5 до 15 минут, соответствующее времени восстановления от 5 до 15 минут.

Параметр recovery interval входит в группу дополнительных параметров. Вы можете задать его в Enterprise Manager во вкладке Database Settings (Параметры базы данных) окна Properties в поле Recovery Interval (min) (рис. 30.2).

Параметр user connections (количество соединений с пользователями)

SQL Server динамически конфигурирует допустимое количество соединений пользователей с SQL Server. В SQL Server допускается до 32767 соединений с пользователями Задавая значение user connections, отличное от 0, вы указываете максимально допустимое количество одновременных подсоединений пользователей к SQL Server. (Количество допустимых подсоединений пользователей также зависит от ограничений ваших приложений и оборудования.) Количество подсоединений пользователей будет также динамически конфигурироваться вплоть до указанного максимума.

Например, если подсоединяются только 10 пользователей, будет выделено только 10 объектов-соединений с пользователями (user connection). Если достигнуто максимальное значение, а SQL Server требуются новые соединения с пользователями, то вы получите сообщение об ошибке, где указывается, что достигнуто максимальное значение по количеству соединений с пользователями.

В большинстве случаев принятое по умолчанию значение параметра user connections изменять не требуется. Отметим, что для каждого соединения требуется порядка 40 Кб памяти.

Чтобы определить максимальное количество соединений с пользователями, допустимое в вашей системе, вы можете использовать SQL Server Query Analyzer или следующий оператор T-SQL:

SELECT @@MAX_CONNECTIONS

Параметр user connections входит в группу дополнительных параметров. Вы можете задать его в Enterprise Manager во вкладке Connections (Соединения) окна Properties в поле-счетчике Maximum Concurrent User Connections (Максимальное число одновременных соединений с пользователями) (рис. 30.3).

Параметр open objects (количество открытых объектов)

Параметр open objects входит в группу дополнительных параметров; его можно задать только с помощью процедуры sp_configure. Этот параметр определяет максимально допустимое количество одновременно открытых объектов базы данных, таких как таблицы, представления, хранимые процедуры, триггеры, правила и значения по умолчанию. Принятое по умолчанию значение 0 указывает, что SQL Server будет динамически регулировать допустимое количество одновременно открытых объектов в данной системе. Вам следует оставить это значение по умолчанию без изменений. Если вы все же измените его, а SQL Server потребуется больше открытых объектов, чем вы сконфигурировали, то появится сообщение об ошибке от SQL Server, где указывается, что вы превысили допустимое количество открытых объектов. Кроме того, для каждого открытого объекта требуется некоторое количество памяти, поэтому вашей системе может потребоваться большее количество физической памяти, чтобы поддерживать необходимое количество открытых объектов.

Статистика

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

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

В Enterprise Manager вы можете активизировать или отключать автоматическое создание статистики по базе данных. Для этого сначала откройте окно Properties этой базы данных. Во вкладке Options вы увидите флажок Auto Create Statistics (Автоматическое создание статистики). (На рис. 30.4 этот флажок установлен для базы данных MyDB.) Этот флажок установлен (активизирован) по умолчанию.

В окне Properties соответствующей базы данных вы также увидите флажок Auto Update Statistics (Автоматическое обновление статистики). Этот флажок, который установлен по умолчанию для каждой базы данных, указывает, что SQL Server при необходимости будет автоматически обновлять статистику по колонкам таблицы. Статистику требуется обновлять в тех случаях, когда изменилось большое количество (или большой процент) строк таблицы (посредством операций обновления, вставки и удаления). При большом числе изменений в данных текущая статистика становится менее точной. SQL Server автоматически определяет, когда требуется обновить статистику. Если вы решили отключить автоматическое создание статистики, сбросив этот флажок, то вы должны выполнять эти задачи вручную, чтобы убедиться в нормальной работе вашей базы данных. В следующих разделах описывается, как создавать и обновлять статистику вручную.

Команда CREATE STATISTICS

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

CREATE STATISTICS имя_статистики ON
     имя_таблицы ( колонка [ , колонка... ] )
  [   [WITH [ FULLSCAN | SAMPLE размер PERCENT ]
  [   , NORECOMPUTE ]

Вам следует ввести имя для набора статистики, который вы создаете, имя таблицы, а также имя хотя бы одной колонки. Вы можете указать несколько имен колонок для сбора статистики по комбинации колонок. Отметим, что вы не можете указывать для статистики расчетные колонки или колонки с типом данных ntext, text или image. Для сбора статистики можно указывать полное сканирование ( FULLSCAN ) или выборку данных ( SAMPLE ). Для полного сканирования требуется больше времени, чем для выборки, поскольку сканируется каждая строка таблицы, но результаты могут оказаться более точными. Используя выборку, вы должны указать процент данных, включаемых в выборку. Ключевое слово NORECOMPUTE указывает, что автоматическое обновление этой статистики отключено, что позволяет использовать статистику, которая уже не отражает текущее состояние данных.

Вам может потребоваться создание статистики по колонкам, которые совместно используются в предикате запроса. Например, вы можете создать статистику по колонкам FirstName (Имя) и LastName (Фамилия) таблицы Employees (Сотрудники) базы данных Northwind для поиска сотрудника по имени и фамилии. Для этого используется следующая последовательность T-SQL:

CREATE STATISTICS name
ON Northwind..Employees (FirstName, LastName)
WITH FULLSCAN, NORECOMPUTE

Этот оператор рассчитывает статистику для всех строк колонок FirstName и LastName и отключает автоматический перерасчет статистики.

Если вы хотите запустить статистику для всех колонок всех таблиц базы данных вручную без ввода операторов CREATE STATISTICS для каждой колонки каждой таблицы, то можете использовать хранимую процедуру sp_createstats. Эта хранимая процедура описана в следующем разделе.

Процедура sp_createstats

Вы можете запустить статистику для всех допустимых колонок всех таблиц пользовательских базы данных с помощью хранимой процедуры sp_createstats. Статистика будет создана для всех колонок, по которым еще не создана статистика. Каждый набор статистики будет создан по какой-либо одной колонке. Процедура sp_createstats имеет следующий синтаксис:

sp_createstats [ 'indexonly' ] [ , 'fullscan' ]  [ , 'norecompute' ]

Параметр indexonly указывает, что статистика будет создана только по колонкам, включаемым в индекс. Параметр fullscan указывает, что будет выполнено полное сканирование всех строк, а не случайная выборка; иначе говоря, будет использована выборка 100 процентов данных. Параметр norecompute указывает, что по этой новой статистике будет отключено автоматическое обновление статистики. Новой статистике присваивается имя колонки, по которой она создается.

Команда UPDATE STATISTICS

По умолчанию SQL Server автоматически обновляет статистику. Вы можете отключить эту возможность с помощью команды UPDATE STATISTICS и затем обновлять статистику вручную, чтобы она соответствовала текущему состоянию данных. Эта команда позволяет вам обновлять статистику по индексированным колонкам и неиндексированным колонкам. Возможно, вы создадите сценарий, который будет выполнять команду UPDATE STATISTICS для наиболее часто модифицируемых таблиц, и затем будете периодически запускать этот сценарий как задание SQL Server. Это позволит поддерживать статистику в соответствии с текущим состоянием данных и поддерживать более высокую производительность запросов. (О синтаксисе и параметрах команды UPDATE STATISTICS см. в разделе "Перестроение индексов" [Rebuilding Indexes] лекция 17.) Чтобы активизировать или отключить автоматическое обновление для определенной статистики, вы можете использовать хранимую процедуру sp_autostats, которая описывается далее.

Процедура sp_autostats

Используя системную хранимую процедуру sp_autostats, вы можете активизировать или отключить автоматическое обновление определенной статистики. Запуск этой процедуры не приводит к обновлению данной статистики; она просто определяет, должно ли происходить автоматическое обновление статистики. Вызов этой хранимой процедуры происходит с одним, двумя или тремя параметрами: имя таблицы и – дополнительно – флаг и имя статистики. Флаг указывает состояние автоматического обновления и может принимать значения ON (включено) или OFF (отключено). Чтобы вывести текущий статус обновления для всех наборов статистики по определенной таблице (статистика по индексированным колонкам и неиндексированным колонкам), запустите эту команду с именем этой таблицы. Следующая команда выводит этот статус для наборов статистики по таблице Customers:

USE Northwind
GO
sp_autostats Customers
GO

Будет выведено имя каждого набора статистики независимо от значения флага автоматического обновления ( ON или OFF ) и время последнего обновления. Не обращайте внимания на заголовок первой колонки Index Name (Имя индекса). Он относится ко всем наборам статистики, а не только к индексам. Если вы не отключили вручную обновление для этих наборов статистики, то они будут представлены со статусом ON, поскольку это принятое по умолчанию состояние в SQL Server.

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

USE Northwind
GO
sp_autostats Customers, 'OFF'
GO

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

USE Northwind
GO
sp_autostats Customers, 'ON', 'PK_Customers'
GO

Статус всех других наборов статистики по таблице Customers не изменится.

Рост файлов

Работая с SQL Server 2000, вы можете конфигурировать файлы данных для их автоматического увеличения по мере необходимости. Это средство полезно, поскольку оно препятствует случайному выходу файлов за пределы допустимого дискового пространства. Однако использование это средства не освобождает вас от обязанности мониторинга размера ваших баз данных и выполнения время от времени процедур планирования мощности. Вы также должны следить за тем, как быстро происходит рост таблиц, чтобы вы могли определить, нужно ли вам выполнять регулярное удаление ненужных, возможно, устаревших данных в некоторых таблицах, чтобы сдерживать рост ваших таблиц. По мере роста количества данных в таблице запросы могут занимать больше времени, что приводит к снижению уровня производительности. Тема конфигурирования автоматического роста при создании базы данных была затронута в лекции 9, а здесь вы узнаете, как изменять параметры увеличения файлов для существующей базы данных. Параметры автоматического роста файлов можно сконфигурировать в Enterprise Manager. Для этого выполните следующие шаги.

Контрольные точки

SQL Server выполняет операции с контрольными точками автоматически. Частота создания контрольных точек автоматически рассчитывается в соответствии со значением, которые вы задали для параметра конфигурирования SQL Server recovery interval. Этот параметр указывает длительность вашего ожидания в минутах при восстановлении базы данных после аварии системы. Контрольные точки создаются достаточно часто, чтобы время восстановления системы не превысило указанного вами значения в минутах. Кроме того, контрольные точки автоматически создаются при отключении SQL Server с помощью оператора SHUTDOWN или Service Control Manager (Диспетчер управления службами). Вы можете также создавать контрольные точки вручную с помощью оператора CHECKPOINT.

Если вы хотите, чтобы система работала оптимальным образом и если вы готовы подождать подольше, то можете задать для параметра recovery interval достаточно большое значение, например, 60. Это означает, что при аварии вашей системы автоматическое восстановление будет занимать до 60 минут. При создании контрольных точек выполняется большое количество операций записи на диск, а они могут отбирать часть ресурсов обработки у пользовательских транзакций, увеличивая время отклика на запросы пользователей. Вот почему менее частое создание контрольных точек может помочь в повышении производительности по транзакциям в целом. Конечно, слишком большое значение параметра может приводить к слишком длительному простою после аварии. Обычно для recovery interval задают значение от 5 до 15 (минут).

По умолчанию для recovery interval задано значение 0. Это значение указывает, что SQL Server будет определять наилучшие моменты создания контрольных точек, исходя из загруженности системы. Обычно при использовании этого значения по умолчанию контрольные точки создаются приблизительно раз в минуту. Если вы считаете, что они создаются слишком часто, то можете изменить значение recovery interval. Чтобы определить, не слишком ли часто SQL Server создает контрольные точки, используйте флаг трассировки SQL Server -T3502. Это флаг указывает запись информации по контрольным точкам в журнал ошибок SQL Server. Отметим, что контрольные точки создаются по каждой базе данных.

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

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

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

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

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

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

Для использования мастера Database Maintenance Plan Wizard выполните следующие шаги.

Reorganize data and index pages (Реорганизация страниц данных и индексов).Этот флажок указывает, что все индексы и все таблицы базы данных будут удалены и воссозданы с использованием указанного коэффициента заполнения (или количества свободного места на каждой странице), что может повысить производительность обновлений. В случае таблиц, предназначенных только для чтения, реорганизация страниц не является необходимостью. В случае таблиц, для которых часто выполняются вставки или изменения, свободное место, которое первоначально было доступно на ваших индексных страницах, постепенно заполняется, и начинает происходить фрагментация страниц. Установите этот флажок, чтобы выполнить повторное создание ваших индексов и образовать свободное место для будущего роста во избежание задержек и нагрузок, вызываемых фрагментацией страниц.

Вы можете выбрать между повторным созданием индексов с исходным количеством свободного места или указать новый процент свободного места на одну страницу. Если задать слишком большой процент, то вы рискуете снизить производительность операций чтения. Установив этот флажок, вы не можете установить следующий флажок – Update statistics used by query optimizer.

Совет.Удаление и повторное создание индексов может оказаться более длительным, чем использование DBCC DBREINDEX, см. раздел "Перестроение индексов" (Rebuilding Indexes) лекции 17. Вы можете также создать свое собственное задание по перестроению индексов вместо использования этого средства. (рис 30.9) Окно Update Data Optimization Information (Реорганизация страниц данных и индексов)

Remove unused space prom database files (Удалить неиспользуемое пространство из файлов базы данных) Этот флажок применяется для удаления неиспользуемого пространства; этот процесс также известен как уплотнение (сжатие) файла (file shrink). Вы можете задать, насколько большим должно стать неиспользуемое пространство, чтобы произошло сжатие файла, а также процент пространства, которое должно остаться свободным после сжатия. Удалив свободное пространство, вы можете использовать DBCC SHRINKFILE для снижения размера данного файла. Если нужно, вы можете сделать его меньше, чем при первоначальном создании. Это позволит использовать для других целей дисковое пространство, которое раньше было занято файлом. Кроме того, сжатие файла за счет удаления неиспользуемого пространства может повысить производительность. В случае таблиц, предназначенных только для чтения, реорганизация страниц не является необходимостью.

Вы можете задать время, за которое должны выполняться эти задачи, щелкнув на кнопке Change (Изменить) и введя новое расписание в появившемся диалоговом окне Edit Recurring Job Schedule (Редактировать расписание повторяющихся заданий) (рис. 30.10). Эти задачи следует выполнять в периоды небольшой загруженности системы, например, в выходные дни или ночью, поскольку это требует определенного времени и может увеличивать время отклика на запросы пользователей.

(рис 30.10) Диалоговое окно Edit Recurring Job Schedule (Редактировать расписание повторяющихся заданий)

Отображение заданий плана обслуживания

Для нашего примера плана обслуживания мы создали по одной задаче в каждой из четырех категорий. Чтобы увидеть список этих заданий (запланированных задач), раскройте папку Management в левой панели Enterprise Manager, раскройте агент SQL Server Agent и затем щелкните на строке Jobs (Задания) (рис. 30.18).

Редактирование плана обслуживания

Чтобы отредактировать план обслуживания, щелкните в левой панели Enterprise Manager на имени базы данных, для которой создан план обслуживания, и затем выберите имя этого плана под заголовком Maintenance в правой панели. Возможно, вам потребуется выполнить прокрутку, чтобы увидеть заголовок Maintenance. Появится диалоговое окно Database Maintenance Plan (рис. 30.19).

Во вкладке General вы можете указывать, какие базы данных будет затрагивать ваш план обслуживания. В других вкладках вы можете изменять значения параметров, которые сконфигурировали с помощью мастера Database Maintenance Plan Wizard. Закончив редактирование плана, щелкните на кнопке OK. Ваш план теперь начнет выполняться в соответствии с указанным вами расписанием.

Заключение

В этой лекции вы узнали о средствах динамического конфигурирования SQL Server 2000, которые помогают снижать объем работы по настройке, выполняемой администраторами баз данных (DBA). Вы также узнали, как создавать план обслуживания баз данных для автоматического выполнения административных задач. В лекции 31 вы узнаете, как использовать SQL Server Agent для определения заданий и оповещений. Создавая задания и оповещения, вы можете еще больше автоматизировать административные задачи.