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

0

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

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

В программном коде Т-SQL параметры минимального и максимального объемов памяти устанавливаются с помощью системной хранимой процедуры sp_conf igure. Этот параметр принадлежит к группе дополнительных, поэтому может использоваться только в том случае, когда включен параметр сервера show advanced options:

ЕХЕС sp_configure ‘show advanced options’, 1 EXEC sp_configure ‘min server memory’, 16

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

Configuration option ‘min server memory (MB)’ changed from 0 to 16.

Run the RECONFIGURE statement to install.

Аналогично устанавливается и параметр максимального объема памяти:

ЕХЕС sp_configure ‘max server memory’, 128 Будет получен следующий результат:

Configuration option ‘max server memory (MB)’ changed from 128 to 128.

Run the RECONFIGURE statement to install.

Для автоматизации вычисления максимального объема памяти на основе объема физической памяти создана следующая хранимая процедура. В ней анализируется набор данных, возвращаемый хранимой процедурой xp_msver, и на основе найденного в нем объема физической памяти вычисляется значение, которое затем передается процедуре sp_conf igure для установки параметра:

CREATE PROC pSetMaxMemory (

@Safе INT = 64 )

AS

CREATE TABLE #PhysicalMemory (

[Index] INT,

[Name] VARCHAR(50),

[Internal_Value] INT,

[Character_Value] VARCHAR(50) )

DECLARE @Memory INT INSERT #PhysicalMemory

EXEC xp_msver 1PhysicalMemory’

SELECT @Memory =

(Select Internal_Value FROM #PhysicalMemory) – @safe EXEC sp_configure ‘max server memory’, @Memory RECONFIGURE

go

EXEC pSetMaxMemory — установка макс. объема памяти – 64Mb ЕХЕС pSetMaxMemory 32 — установка макс. объема памяти – 32МЬ

Команда reconfigure

После того как параметры конфигурации были изменены с помощью хранимой процедуры sp_conf igure, для того, чтобы они вступили в силу, нужно выполнить команду RECONFIGURE. Если этого не сделать, в поле conf ig_value будет отображаться измененный вариант значения, однако изменения не затронут поле run_value, даже если перезагрузить службу. В то же время некоторые параметры для своего вступления в силу требуют перезапуска SQL Server:

The command(s) completed successfully.

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

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

Независимо от объема памяти, выделяемой операционной системой серверу баз данных, диспетчер памяти Windows может выгрузить некоторые страницы SQL Server в файл подкачки, если SQL Server простаивает. Если в настройках SQL Server установлен фиксированный объем памяти, то такой выгрузки можно избежать, если установить в параметре Reserve Physical Memory значение true.

Параметр SQL Server Reserve Physical Memory можно установить программным путем с помощью передачи системной хранимой процедуре sp_conf igure аргумента set working set size:

EXEC sp_configure ‘set working set size’, 1 RECONFIGURE

Чтобы данная настройка вступила в силу, сервер баз данных нужно перегрузить.

Временами команда разработчиков SQL Server поражает меня детализацией элементов управления базой данных, которые передаются в руки администратора. При необходимости SQL Server может выделять каждому выполняемому запросу заданный объем памяти. Параметр min memory per query позволяет установить минимальный объем памяти, используемый каждым запросом. Несмотря на то что увеличение значения этого параметра относительно выделяемых по умолчанию 1 Мбайт может обеспечить определенное повышение производительности некоторых запросов, я не вижу оснований заменять автоматическое управление памятью сервером и тем самым подвергаться риску нехватки памяти. В следующем примере минимальный объем памяти, выделяемый для запросов, повышается до 2 Мбайт:

EXEС sp_configure ‘min memory per query’, 2048 RECONFIGURE

Шесть дополнительных параметров конфигурирования памяти не доступны в интерфейсе Management Studio, но могут быть установлены с помощью программного кода.

Время ожидания запроса

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

EXEC sp_configure ‘query wait’, 20 RECONFIGURE

В приведенном примере определено, что выполнение запроса либо должно начаться в течение 20 секунд, либо завершиться ошибкой превышения времени ожидания.

Расширение окна адресации памяти (AWE)

При обычных условиях СУБД SQL Server ограничена стандартным предельным объемом физической памяти в 3 Гбайт. В то же время редакция SQL Server 2005 Enterprise Edition, запущенная в среде Windows 2005 Datacenter, может оперировать 64 Гбайт физической памяти посредством API-интерфейса расширения окна адресации AWE. Параметр AWE Enabled позволяет включить в SQL Server AWE-адресацию памяти:

ЕХЕС sp_configure ‘AWE Enabled’, 2 0 RECONFIGURE

Выделение памяти для формирования индексов

Объем памяти, который использует SQL Server для выполнения сортировки при создании индексов, обычно конфигурируется автоматически. В то же время этим объемом можно управлять и вручную, используя хранимую процедуру sp_conf igure и устанавливая нужное значение в килобайтах. В следующем примере память, выделяемая для создания индекса, определяется в размере 8 Мбайт:

ЕХЕС sp_configure ‘index create memory’, 8096 RECONFIGURE

I Для просмотра текущих операций ввода-вывода и блокировок в каждом разде- SVS ле таблиЦЬ| или индекса базы данных выберите в динамическом представле- I * нии управления sysdm_db_index_operational_stats. Статистику исполь-

            ‘ зования индексов можно увидеть с помощью sysdm_db_index_usage_stats,

а физические детали, такие как статистика размеров и фрагментации, — с помощью sysdm_db_index_physical_stats.

Резервирование памяти для блокировок

Излишние блокировки могут в буквальном смысле поставить SQL Server на колени, как в отношении ожидания блокировок, так и в отношении памяти, потребляемой блокировками (одна блокировка использует 96 байт памяти). По умолчанию SQL Server резервирует для блокировок 2% отведенной для него памяти. В процессе работы это значение может быть динамически увеличено вплоть до 40% определенного в параметрах сервера максимального объема памяти. Чаще всего этого более чем достаточно. Если вы получили сообщение об ошибке, связанной с недостатком памяти для блокировок, не спешите увеличивать выделение для блокировок памяти — в данном случае проблема, скорее всего, связана с ошибками в программном коде. В следующем примере отключается динамическое выделение памяти для блокировок, при этом им отводится фиксированный объем памяти для 16767 блокировок (приблизительно 1,5 Мбайт): ЕХЕС sp_configure ‘locks’, 16767 RECONFIGURE

Максимальное количество открытых объектов

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

EXEC sp_configure ‘open objects’, 16767 RECONFIGURE

I                Вы всегда можете узнать минимальное и максимальное значения конкретного SVS параметра, если запустите на выполнение хранимую процедуру sp_conf igure

1          I * с указание имени параметра, но без указания его значения. Например, если вы

            * выполните команду EXEC sp_configure ‘open objects’, то узнаете, что

значение параметра open objects должно находиться в диапазоне от о до

2             147 483 647.

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

Параметры конфигурации процессора (табл. 34.3) управляют использованием симметричных многопроцессорных систем в SQL Server.

Таблица 34.3. Свойства конфигурации процессора

Параметр

Уровень

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

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

Назначение потоков процессорам

Сервер

Management Studio

EXEC sp configure ‘affinity mask’

Максимальное количество рабочих потоков

Сервер

Management Studio

EXEC sp_configure ‘max worker threads’

Повышение приоритета SQL Server в Windows

Сервер

Management Studio

EXEC sp_configure ‘priority boost’

Использование волокон Windows NT

Сервер

Management Studio

EXEC sp_configure ‘lightweight pooling’

Количество процессоров, используемых для параллельного выполнения запросов

Сервер

Management Studio

EXEC sp_conf igure ‘max degree of parallelsm’

Минимальная стоимость запроса для его разделения на параллельные процессы

Сервер

Management Studio

EXEC sp_configure ‘cost threshold for parallelism’

Время ожидания запроса

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

По теме:

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