ALTER SERVER CONFIGURATION (Transact-SQL)

  • Статья
  • Чтение занимает 13 мин

Применимо к: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 ] ) ]

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

<process_affinity> ::=

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

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

AUTO
Указывает, что для потока не назначен ЦП. Разрешено свободное перемещение потоков операционной системой между процессорами в зависимости от рабочей нагрузки сервера. Это рекомендуемое значение по умолчанию.

<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, такие как количество переключений файлов журнала, размер файлов журнала и расположение файлов. Дополнительные сведения см. в статье Просмотр и чтение журнала диагностики экземпляра отказоустойчивого кластера.

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

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

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, который управляет метаданными для реплик доступности, размещенных в экземпляре сервера. Используйте параметр SET HADR CLUSTER CONTEXT только во время миграции с кластера Группы доступности AlwaysOn на экземпляр SQL Server 2012 с пакетом обновлений 1 (SP1) версии 11.0.3x или более новой версии в новом кластере WSFC.

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

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

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

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

Примечание

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

LOCAL
Локальный кластер WSFC.

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

<buffer_pool_extension>::=

Применимо к: SQL Server (начиная с SQL Server 2014 (12.x)).

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

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 для освобождения памяти. В противном случае заданный размер должен совпадать с текущим размером или превышать его.

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

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

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

<soft_numa>

Применимо к: SQL Server (начиная с SQL Server 2016 (13.x);).

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

OFF
Отключает автоматическое секционирование для разбиения крупных аппаратных узлов 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 CONFIGURATION.

<memory_optimized> ::=

Применимо к: SQL Server (начиная с SQL Server 2019 (15.x)).

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

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

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

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

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

<hardware_offload> ::=

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

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

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

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

<> suspend_for_snapshot_backup ::=

Применимо к: SQL Server (начиная с SQL Server 2022 (16.x))

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

SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | 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. В результате в учетной записи 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

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

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

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

Б. Задание привязки для всех процессоров в узлах 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;

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

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

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0;

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

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

ALTER SERVER CONFIGURATION  
SET PROCESS AFFINITY CPU=AUTO;

Setting diagnostic log options

Применимо к: SQL Server (начиная с SQL Server 2012 (11.x)).

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

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

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

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

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

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

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

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

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

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

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

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

ALTER SERVER CONFIGURATION   
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

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

Применимо к: SQL Server (начиная с SQL Server 2012 (11.x)).

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

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

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

ALTER SERVER CONFIGURATION   
SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000;

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

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

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

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

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

Применимо к: SQL Server (начиная с SQL Server 2014 (12.x)).

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

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

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

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

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)).

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ON;
GO

Б. Включение оптимизированных для памяти метаданных 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

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

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)
Расширение буферного пула