Поделиться через

ALTER SERVER CONFIGURATION (Transact-SQL)

  • Статья

Область применения: SQL Server

Изменяет глобальные параметры конфигурации для текущего сервера в SQL Server.

Соглашения о синтаксисе Transact-SQL

Синтаксис

ALTER SERVER CONFIGURATION  
SET <optionspec>   
[;]  
  
<optionspec> ::=  
{  
     <process_affinity>  
   | <diagnostic_log>  
   | <failover_cluster_property>  
   | <hadr_cluster_context>  
   | <buffer_pool_extension>  
   | <soft_numa>  
   | <memory_optimized>
   | <hardware_offload>
   | <suspend_for_snapshot_backup>
}  
  
<process_affinity> ::=   
   PROCESS AFFINITY   
   {  
     CPU = { AUTO | <CPU_range_spec> }   
   | NUMANODE = <NUMA_node_range_spec>   
   }  
   <CPU_range_spec> ::=   
      { CPU_ID | CPU_ID  TO CPU_ID } [ ,...n ]   
  
   <NUMA_node_range_spec> ::=   
      { NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,...n ]  
  
<diagnostic_log> ::=   
   DIAGNOSTICS LOG   
   {   
     ON    
   | OFF    
   | PATH = { 'os_file_path' | DEFAULT }    
   | MAX_SIZE = { 'log_max_size' MB | DEFAULT }    
   | MAX_FILES = { 'max_file_count' | DEFAULT }    
   }  
  
<failover_cluster_property> ::=   
   FAILOVER CLUSTER PROPERTY <resource_property>  
   <resource_property> ::=  
      {  
        VerboseLogging = { 'logging_detail' | DEFAULT }    
      | SqlDumperDumpFlags = { 'dump_file_type' | DEFAULT }  
      | SqlDumperDumpPath = { 'os_file_path'| DEFAULT }  
      | SqlDumperDumpTimeOut = { 'dump_time-out' | DEFAULT }  
      | FailureConditionLevel = { 'failure_condition_level' | DEFAULT }  
      | HealthCheckTimeout = { 'health_check_time-out' | DEFAULT }  
      }  
  
<hadr_cluster_context> ::=  
   HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }  
  
<buffer_pool_extension>::=  
    BUFFER POOL EXTENSION   
    { ON ( FILENAME = 'os_file_path_and_name' , SIZE = <size_spec> )   
    | OFF }  
  
    <size_spec> ::=  
        { size [ KB | MB | GB ] }  
  
<soft_numa> ::=  
    SOFTNUMA  
    { ON | OFF }  

<memory-optimized> ::=   
   MEMORY_OPTIMIZED   
   {   
     ON 
   | OFF
   | [ TEMPDB_METADATA = { ON [(RESOURCE_POOL='resource_pool_name')] | OFF }
   | [ HYBRID_BUFFER_POOL = { ON | OFF }
   }  

<hardware_offload> ::=
   HARDWARE_OFFLOAD
   {   
     ON 
   | OFF
   }

<suspend_for_snapshot_backup> ::=
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF } [ ( GROUP = ( <database>,...n) [ , MODE = COPY_ONLY ] ) ]

Аргументы

<process_affinity> ::=

PROCESS AFFINITY
Включает связывание потоков оборудования с процессорами.

CPU = { AUTO | <CPU_range_spec> }
Распределяет рабочие потоки SQL Server на каждый ЦП в заданном диапазоне. Для процессоров вне заданного диапазона не назначены потоки.

АВТОМАТИЧЕСКИ
Указывает, что для потока не назначен ЦП. Разрешено свободное перемещение потоков операционной системой между процессорами в зависимости от рабочей нагрузки сервера. Это рекомендуемое значение по умолчанию.

<CPU_range_spec> ::=
Указывает ЦП или диапазон процессоров, которым будут назначаться потоки.

{ CPU_ID | CPU_ID TO CPU_ID } [ ,...n ]
Список из одного или нескольких процессоров. Идентификаторы ЦП начинаются с 0 и имеют целочисленное значение.

NUMANODE = <NUMA_node_range_spec>
Назначает потоки всем процессорам, принадлежащим заданному узлу NUMA или диапазону узлов.

<NUMA_node_range_spec> ::=
Указывает номер узла NUMA или диапазон узлов NUMA.

{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,...n ]
Это список из одного или нескольких узлов NUMA. Идентификаторы NUMA начинаются с 0 и имеют целочисленное значение.

<diagnostic_log> ::=

Область применения: SQL Server (начиная с SQL Server 2012 (11.x)).

DIAGNOSTICS LOG
Запускает или останавливает запись в журнал диагностических данных, полученных с помощью хранимой процедуры sp_server_diagnostics. Этот аргумент также задает параметры конфигурации журналов SQLDIAG, такие как количество переключений файлов журнала, размер файлов журнала и расположение файлов. Дополнительные сведения см. в статье Просмотр и чтение журнала диагностики экземпляра отказоустойчивого кластера.

DNS
Запускает диагностические данные журнала SQL Server в расположении, указанном в параметре PATH-файла. Это аргумент по умолчанию.

ВЫКЛ.
Прекращает запись в журнал диагностических данных.

PATH = { 'os_file_path' | DEFAULT }
Путь, определяющий расположение журналов диагностики. Расположение по умолчанию — <\MSSQL\Log> в папке установки экземпляра отказоустойчивого кластера SQL Server.

MAX_SIZE = { 'log_max_size' MB | DEFAULT }
Максимальный размер каждого из журналов диагностики в мегабайтах. Значение по умолчанию равно 100 МБ.

MAX_FILES = { 'max_file_count' | DEFAULT }
Максимальное число файлов журналов диагностики, которые могут храниться на компьютере, прежде чем имеющиеся файлы будут очищены и использованы для новых журналов диагностики.

<failover_cluster_property> ::=

Область применения: SQL Server (начиная с SQL Server 2012 (11.x)).

FAILOVER CLUSTER PROPERTY
Изменяет свойства закрытого ресурса отказоустойчивого кластера SQL Server.

VERBOSE LOGGING = { 'logging_detail' | DEFAULT }
Задает уровень ведения журнала для отказоустойчивого кластера SQL Server. Параметр можно включить для записи дополнительных сведений в журналы ошибок в целях устранения неполадок.

  • 0 — ведение журнала отключено (по умолчанию)

  • 1 — только ошибки

  • 2 — ошибки и предупреждения

В сценариях отработки отказа ресурсов библиотека DLL ресурсов SQL Server может получить файл дампа, прежде чем произойдет отработка отказа. Это относится к технологиям FCI и групп доступности. Когда библиотека DLL ресурсов SQL Server определяет, что ресурс SQL Server завершился сбоем, она использует служебную программу Sqldumper.exe для получения файла дампа процесса SQL Server. Чтобы убедиться, что программа Sqldumper.exe успешно создает файл дампа при отработке отказа ресурсов, необходимо задать следующие три свойства в качестве необходимых компонентов: SqlDumperDumpTimeOut, SqlDumperDumpPath, SqlDumperDumpFlags.

SQLDUMPEREDUMPFLAGS
Определяет тип файлов дампа, создаваемых служебной программой SQLDumper в SQL Server. Значение по умолчанию — 0. Для этого параметра используются десятичные, а не шестнадцатеричные значения. Для мини-дампа используйте 288, для мини-дампа с косвенным использованием памяти — 296, для фильтрованного дампа используйте 33024. Для получения дополнительных сведений см. статью базы знаний о служебной программе Dumper сервера SQL Server.

SQLDUMPERDUMPPATH = { 'os_file_path' | DEFAULT }
Место, где служебная программа SQLDumper сохраняет файлы дампов. Для получения дополнительных сведений см. статью базы знаний о служебной программе Dumper сервера SQL Server.

SQLDUMPERDUMPTIMEOUT = { 'dump_time-out' | DEFAULT }
Максимальное время создания дампа программой SQLDumper в случае сбоя SQL Server (в миллисекундах). Значение по умолчанию равно 0, то есть время создания дампа неограниченно. Для получения дополнительных сведений см. статью базы знаний о служебной программе Dumper сервера SQL Server.

FAILURECONDITIONLEVEL = { 'failure_condition_level' | DEFAULT }
Условия, при которых должны выполняться отработка отказа или перезапуск экземпляра отказоустойчивого кластера SQL Server. Значение по умолчанию, равное 3, означает, что ресурс SQL Server будет переключаться на резервный ресурс или перезапускаться в случае критической ошибки сервера. Дополнительные сведения об этом и других уровнях условий ошибки см. в разделе Настройка параметров свойства FailureConditionLevel.

HEALTHCHECKTIMEOUT = { 'health_check_time-out' | DEFAULT }
Время, в течение которого библиотека ресурсов компонента SQL Server Database Engine будет ждать сведений о состоянии сервера, прежде чем сервер переводится в категорию неотвечающих. Время ожидания указывается в миллисекундах. Значение по умолчанию равно 60 000 миллисекунд (60 секунд).

<hadr_cluster_context> ::=

Область применения: SQL Server (начиная с SQL Server 2012 (11.x)).

HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }
Переключает контекст кластера HADR экземпляра сервера на указанный отказоустойчивый кластер Windows Server (WSFC). Контекст кластера HADR определяет кластер WSFC, который управляет метаданными для реплик доступности, размещенных в экземпляре сервера. Используйте параметр CONTEXT КЛАСТЕРА SET HADR только во время миграции групп доступности AlwaysOn между кластерами в экземпляр SQL Server 2012 с пакетом обновления 1 (SP1(11.0.3x) или более поздней версии в новом R WSFC.

Переключать контекст кластера HADR можно только с локального WSFC на удаленный. Затем вы можете переключиться обратно с удаленного WSFC на локальный WSFC. Контекст кластера HADR можно переключать на удаленный кластер, только если экземпляр SQL Server не размещает реплики доступности.

Удаленный контекст кластера HADR можно переключить обратно на локальный кластер в любое время. Однако контекст нельзя переключать повторно, пока на экземпляре сервера содержатся реплики доступности.

Для определения целевого кластера укажите одно из следующих значений:

кластер_windows
Сетевое имя кластера WSFC. Вы можете указать короткое имя или полное имя домена. Для поиска целевого IP-адреса короткого имени ALTER SERVER CONFIGURATION использует разрешение DNS. В некоторых ситуациях краткое имя может вызвать затруднения, и DNS может вернуть неправильный IP-адрес. Рекомендуется указывать полное имя домена.

Примечание.

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

ЛОКАЛЬНО
Локальный кластер WSFC.

Дополнительные сведения см. в разделе Смена контекста кластера HADR экземпляра сервера (SQL Server).

<buffer_pool_extension>::=

Область применения: SQL Server (начиная с SQL Server 2014 (12.x)).

DNS
Обеспечивает возможность расширения буферного пула. Этот параметр расширяет размер буферного пула за счет использования энергонезависимого хранилища. Энергонезависимое хранилище, такое как твердотельные накопители (SSD), сохраняет чистые страницы данных в пуле. Дополнительные сведения об этой возможности см. в статье Buffer Pool Extension. Расширение буферного пула поддерживается не во всех выпусках SQL Server. Дополнительные сведения см. в выпусках и поддерживаемых функциях SQL Server 2022.

FILENAME = 'os_file_path_and_name'
Определяет путь к каталогу и имя файла кэша расширения буферного пула. Файл должен иметь расширение BPE. Отключите BUFFER POOL EXTENSION, прежде чем изменить FILENAME.

SIZE = size [ KB | MB | GB ]
Определяет размер кэша. Указание размера по умолчанию — KB. Минимальный размер — значение параметра Max Server Memory. Максимальный размер в 32 раза больше значения параметра Max Server Memory. Дополнительные сведения о параметре Max Server Memory см. в статье sp_configure (Transact-SQL).

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

ВЫКЛ.
Отключает параметр расширения буферного пула. Перед изменением любых связанных параметров, например размера или имени файла, отключите параметр расширения буферного пула. Если этот параметр отключен, все связанные данные конфигурации удаляются из реестра.

Предупреждение

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

<soft_numa>

Область применения: SQL Server (начиная с SQL Server 2016 (13.x)).

DNS
Включает автоматическое секционирование для разбиения крупных аппаратных узлов NUMA на более мелкие узлы NUMA. При изменении текущего значения потребуется перезапустить ядро СУБД.

ВЫКЛ.
Отключает автоматическое секционирование для разбиения крупных аппаратных узлов NUMA на более мелкие узлы NUMA. При изменении текущего значения потребуется перезапустить ядро СУБД.

Предупреждение

Существуют известные проблемы с работой инструкции ALTER SERVER CONFIGURATION с параметром SOFT NUMA и агентом SQL Server. Ниже приведена рекомендуемая последовательность операций.

  1. Остановите экземпляр агента SQL Server.
  2. Выполните инструкцию ALTER SERVER CONFIGURATION с параметром SOFT NUMA.
  3. Повторно запустите экземпляр SQL Server.
  4. Запустите экземпляр агента SQL Server.

Дополнительные сведения. Если вы запускаете команду ALTER SERVER CONFIGURATION с помощью команды SET SOFTNUMA перед перезапуском службы SQL Server, то при остановке службы агент SQL Server выполняется команда T-SQL RECONFIGURE, которая возвращает параметры SOFTNUMA обратно в то, что они были до конфигурации ALTER SERVER.

<memory_optimized> ::=

Область применения: SQL Server (начиная с SQL Server 2019 (15.x)).

ON
Включает все функции уровня экземпляра, которые являются частью семейства функций выполняющейся в памяти базы данных. Сейчас к ним относятся оптимизированные для памяти метаданные tempdb и гибридный буферный пул. Для вступления в силу требуется перезагрузка.

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

TEMPDB_METADATA = ON | ОТ
Включает или отключает только оптимизированные для памяти метаданные tempdb. Для вступления в силу требуется перезагрузка.

RESOURCE_POOL='resource_pool_name'
В сочетании с TEMPDB_METADATA = ON задает пользовательский пул ресурсов, который нужно использовать для tempdb. Если значение не указано, используется пул по умолчанию. Этот пул должен уже существовать. Если пул недоступен при перезапуске службы, tempdb будет использовать пул по умолчанию.

HYBRID_BUFFER_POOL = ON | ОТ
Включает или отключает гибридный буферный пул на уровне экземпляра. Для вступления в силу требуется перезагрузка.

<hardware_offload> ::=

Область применения: SQL Server (начиная с SQL Server 2022 (16.x)).

ON
Включает использование интегрированного ускорения и разгрузки для экземпляра. Требуется перезагрузка.

ОТ
Отключает использование интегрированного ускорения и разгрузки на уровне экземпляра. Для вступления в силу требуется перезагрузка.

Дополнительные сведения см. в разделе Интегрированное ускорение и разгрузка.

<> suspend_for_snapshot_backup ::=

Область применения: SQL Server (начиная с SQL Server 2022 (16.x))

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

SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF }

Приостановка или отмена приостановки баз данных. По умолчанию OFF.

GROUP = ( <база данных>,... n)

Необязательно. Определяет группу одной или нескольких баз данных для приостановки. Если этот параметр не указан, параметр применяется ко всем базам данных.

MODE = COPY_ONLY

Необязательно. Использует режим COPY_ONLY для всех резервных копий базы данных.

Общие замечания

Для этого оператора не требуется перезапуск SQL Server, если явно не указано в противном случае. Если это экземпляр отказоустойчивого кластера SQL Server, он не требует перезапуска ресурса кластера SQL Server.

Ограничения

Эта инструкция не поддерживает триггеры DDL.

Разрешения

Требования:

  • Разрешения ALTER SETTINGS для параметра сопоставления процессов.
  • Разрешения ALTER SETTINGS и VIEW SERVER STATE для параметров журнала диагностики и свойств отказоустойчивого кластера.
  • Разрешение CONTROL SERVER для параметра контекста кластера HADR.
  • Разрешение ALTER SERVER STATE для параметра расширения буферного пула.

Библиотека DLL ресурсов SQL Server ядро СУБД выполняется под учетной записью локальной системы. В результате в учетной записи Local System должен быть доступ на чтение и запись к пути, указанному в параметре журнала диагностики.

Примеры

Категория Используемые элементы синтаксиса
Установка соответствия процессов CPU • NUMANODE • AUTO
Настройка параметров журнала диагностики ON • OFF • PATH • MAX_SIZE
Установка свойств отказоустойчивого кластера HealthCheckTimeout
Изменение контекста кластера для реплики доступности ' windows_cluster '
Установка расширения буферного пула РАСШИРЕНИЕ БУФЕРНОГО ПУЛА
Настройка параметров выполняющейся в памяти базы данных MEMORY_OPTIMIZED

Установка соответствия процессов

В примерах этого раздела показано соответствие процессов центральным процессорам (ЦП) и узлам NUMA. Сервер в этих примерах состоит из 256 процессоров, организованных в четыре группы по 16 узлов NUMA в каждой. Потоки не назначаются какому-либо узлу NUMA или ЦП.

  • Группа 0: узлы NUMA от 0 до 3, процессоры от 0 до 63
  • Группа 1: узлы NUMA от 4 до 7, процессоры от 64 до 127
  • Группа 2: узлы NUMA от 8 до 12, процессоры от 128 до 191
  • Группа 3: узлы NUMA от 13 до 16, процессоры от 192 до 255

А. Задание привязки для всех процессоров в группах 0 и 2

В следующем примере задается соответствие для всех процессоров в группах 0 и 2.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=0 TO 63, 128 TO 191;

B. Задание привязки для всех процессоров в узлах NUMA 0 и 7

В следующем примере задается привязка процессоров только к узлам 0 и 7.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY NUMANODE=0, 7;

В. Задание привязки к процессорам с номерами от 60 до 200

В следующем примере задается соответствие для процессоров от 60 до 200.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=60 TO 200;

D. Задание привязки к процессору 0 в системе с двумя процессорами

В следующем примере демонстрируется задание соответствия для CPU=0 на компьютере с двумя процессорами. Перед выполнением следующей инструкции использовалась внутренняя битовая маска соответствия 00.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0;

Е. Задание привязки AUTO

В этом примере параметр соответствия устанавливается на AUTO.

ALTER SERVER CONFIGURATION  
SET PROCESS AFFINITY CPU=AUTO;

Настройка параметров журнала диагностики

Область применения: SQL Server (начиная с SQL Server 2012 (11.x)).

В примерах этого раздела показана установка значений параметра журнала диагностики.

А. Запуск регистрации диагностических данных в журнале

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

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

B. Останов регистрации диагностических данных в журнале

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

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

В. Задание расположения журналов диагностических данных

В следующем примере для журналов диагностических данных задается расположение по указанному пути к файлам.

ALTER SERVER CONFIGURATION  
SET DIAGNOSTICS LOG PATH = 'C:\logs';

D. Задание максимального размера каждого из журналов диагностики

В следующем примере задан максимальный размер каждого из журналов диагностики, равный 10 мегабайтам.

ALTER SERVER CONFIGURATION   
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

Установка свойств отказоустойчивого кластера

Область применения: SQL Server (начиная с SQL Server 2012 (11.x)).

В следующем примере показано задание значений свойств ресурса отказоустойчивого кластера SQL Server.

А. Указание значения свойства HealthCheckTimeout

В следующем примере устанавливается параметр HealthCheckTimeout, равный 15 000 миллисекунд (15 секунд).

ALTER SERVER CONFIGURATION   
SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000;

B. Изменение контекста кластера для реплики доступности

В следующем примере изменяется контекст кластера HADR экземпляра SQL Server. Для задания целевого кластера WSFC clus01 в примере указывается полное имя объекта кластера — clus01.xyz.com.

ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT = 'clus01.xyz.com';

Задание параметров расширения буферного пула

А. Установка параметра расширения буферного пула

Область применения: SQL Server (начиная с SQL Server 2014 (12.x)).

В следующем примере выполняется включение параметра расширения буферного пула и задается имя и размер файла.

ALTER SERVER CONFIGURATION   
SET BUFFER POOL EXTENSION ON  
    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 50 GB);

B. Изменение параметров расширения буферного пула

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

ALTER SERVER CONFIGURATION   
SET BUFFER POOL EXTENSION OFF;  
GO  
EXEC sp_configure 'max server memory (MB)', 12000;  
GO  
RECONFIGURE;  
GO  
ALTER SERVER CONFIGURATION  
SET BUFFER POOL EXTENSION ON  
    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 60 GB);  
GO

Настройка параметров базы данных в памяти

Область применения: SQL Server (начиная с SQL Server 2019 (15.x)).

А. Включение всех функций выполняющейся в памяти базы данных с параметрами по умолчанию

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ON;
GO

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
GO

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');
GO

D. Включение гибридного буферного пула

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;
GO

См. также

Архитектура Soft-NUMA (SQL Server)
Изменение контекста кластера HADR экземпляра сервера (SQL Server)
sys.dm_os_schedulers (Transact-SQL)
sys.dm_os_memory_nodes (Transact-SQL)
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)
Расширение буферного пула