Sp configure: примеры (SQL)

Примеры использования sp_configure

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

EXEC sp_configure;

name                          minimum maximum config_value run_value
------------------------------ ------- ------- ------------ ---------
access check cache bucket      0       16384   0            0
access check cache quota       0       2147483 0            0
... (много строк)

Просмотр конкретного параметра, например, 'max server memory'.

EXEC sp_configure 'max server memory';

name             minimum maximum config_value run_value
---------------- ------- ------- ------------ ---------
max server memory 16     2147483 2147483      2147483

Изменение значения параметра 'clr enabled' с 0 на 1.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Конфигурация параметра 'clr enabled' изменена с 0 на 1. Для установки параметра запустите инструкцию RECONFIGURE.

Похожие средства в MS SQL

Представление sys.configurations: Предоставляет информацию о всех параметрах сервера в виде набора строк, что удобно для запросов и соединений. Не позволяет изменять значения.

Команда RECONFIGURE: Применяет измененные значения, установленные через sp_configure. RECONFIGURE WITH OVERRIDE применяет настройки, даже если они не проходят проверку на допустимость.

Хранимая процедура sp_configure является основным инструментом для изменения глобальных настроек. Представление sys.configurations предпочтительнее для программного чтения и мониторинга. Для изменения параметров уровня базы данных используются команды ALTER DATABASE.

Аналоги в других системах

MySQL: Для просмотра переменных используется SHOW VARIABLES или запрос к INFORMATION_SCHEMA.GLOBAL_VARIABLES. Изменение - SET GLOBAL или SET PERSIST.

SHOW GLOBAL VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 200;

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Oracle: Параметры управляются через представления V$PARAMETER и V$SYSTEM_PARAMETER. Изменение - ALTER SYSTEM.

SELECT name, value FROM v$parameter WHERE name = 'processes';
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;

PostgreSQL: Используются команды SHOW и SET, а также просмотр pg_settings. Большинство изменений требует перезагрузки.

SHOW shared_buffers;
SET shared_buffers = '256MB'; -- только для сессии

SQLite: Используются прагмы для настройки параметров базы данных.

PRAGMA journal_mode = WAL;

В отличие от MS SQL, где sp_configure централизованно управляет параметрами сервера, в других системах часто используется комбинация команд и представлений.

Распространенные ошибки

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

EXEC sp_configure 'max server memory', 10;

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
Значение параметра конфигурации "max server memory" (10) должно находиться в диапазоне от 16 до 2147483.

Попытка изменить расширенный параметр без предварительного включения настройки 'show advanced options'.

EXEC sp_configure 'cost threshold for parallelism', 30;

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
Параметр конфигурации 'cost threshold for parallelism' не существует или является расширенным.

Отсутствие разрешений ALTER SETTINGS на уровне сервера.

EXEC sp_configure 'max server memory', 1024;

Msg 5808, Level 16, State 1, Procedure sp_configure, Line 62
Для изменения этого параметра требуются разрешения ALTER SETTINGS.

Изменения в последних версиях

В SQL Server 2019 появились новые параметры, такие как 'tempdb metadata memory-optimized', для перемещения метаданных tempdb в оптимизированные для памяти структуры. Также были добавлены параметры, связанные с интеллектуальной обработкой запросов, например, 'scoped configuration' для вторичных реплик в группах доступности.

Поведение процедуры sp configure в целом остается стабильным, но список доступных параметров постоянно расширяется с каждым крупным обновлением SQL Server, отражая новые функциональные возможности ядра СУБД.

Расширенные примеры настройки

Включение расширенных параметров и последующее изменение нескольких настроек производительности.

-- Показ расширенных опций
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Установка порога для параллелизма и максимальной степени параллелизма
EXEC sp_configure 'cost threshold for parallelism', 35;
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

Конфигурация параметра 'show advanced options' изменена с 0 на 1.
...

Использование RECONFIGURE WITH OVERRIDE для параметра 'user options', который требует принудительного применения.

EXEC sp_configure 'user options', 256;
RECONFIGURE WITH OVERRIDE;

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

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_configure ''' + name + ''', ' + CAST(config_value AS NVARCHAR) + '; RECONFIGURE;'
FROM sys.configurations 
WHERE is_dynamic = 1 AND name NOT IN ('max server memory', 'min server memory');

EXEC sp_executesql @sql;

Настройка параметров аварийного завершения запросов (query wait и query governor cost limit).

EXEC sp_configure 'query wait', -1; -- Неограниченное ожидание
EXEC sp_configure 'query governor cost limit', 120; -- Ограничение в 120 секунд
RECONFIGURE;

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