Как администратор SQL Server, важно оптимизировать производительность вашего сервера, чтобы обеспечить плавную работу. Одной из распространенных проблем, которые могут повлиять на производительность, является неправильная настройка параметров Auto_Close и Auto_Shrink. В этой статье мы рассмотрим эти параметры и почему вам следует избегать их использования в производственной среде.
Auto_Close
Параметр Auto_Close, когда установлен в значение ON/TRUE, автоматически закрывает и выключает базу данных, когда все процессы в базе данных завершаются и последний пользователь выходит. Это освобождает ресурсы, занимаемые базой данных. Однако, при установке нового соединения, база данных автоматически открывается снова. Этот параметр обычно установлен в значение ON/TRUE в SQL Server Desktop Edition, но для всех других редакций он установлен в значение OFF/FALSE.
В серверной среде большинство баз данных используются приложениями, которые повторно открывают и закрывают соединения. Затраты на закрытие и повторное открытие баз данных между каждым соединением могут значительно сказаться на производительности. Количество памяти, сэкономленное этим параметром, незначительно по сравнению с затратами на повторную инициализацию базы данных. В то время как Auto_Close может иметь преимущества в личных рабочих сценариях, его не рекомендуется использовать в правильных серверных средах.
Auto_Shrink
Параметр Auto_Shrink, когда установлен в значение ON/TRUE, автоматически уменьшает размер файлов базы данных, когда более 25 процентов файла содержит неиспользуемое пространство. Этот параметр обычно установлен в значение ON/TRUE в SQL Server Desktop Edition, но для всех других редакций он установлен в значение OFF.
Уменьшение размера базы данных может потреблять много ресурсов и занимать много времени. Это нагружает процессор и требует настройки любых индексов на кучах (таблицы без кластеризованного индекса), затронутых уменьшением. Эта дополнительная работа для процессора может еще больше ухудшить производительность. В то время как Auto_Shrink может быть полезен в сценариях с ограниченными ресурсами дискового пространства, его не рекомендуется использовать в производственных системах, где возможны резкие скачки производительности, когда SQL Server решает уменьшить файлы.
Проверка статуса Auto_Close и Auto_Shrink
Если вы хотите проверить статус этих параметров для ваших баз данных, вы можете использовать следующий скрипт:
SET NOCOUNT ON
SELECT [name] AS DatabaseName,
CONVERT(varchar(100), DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,
CONVERT(varchar(10), DATABASEPROPERTYEX([Name], 'IsAutoClose')) AS AutoClose,
CONVERT(varchar(10), DATABASEPROPERTYEX([Name], 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
ORDER BY DatabaseName
Этот скрипт предоставит вам список ваших баз данных вместе со статусом параметров Auto_Close и Auto_Shrink.
Заключение
Правильная настройка вашего SQL Server является важным условием для оптимальной производительности. Избегание использования параметров Auto_Close и Auto_Shrink в производственной среде может помочь предотвратить ненужное снижение производительности. Понимая эти параметры и их влияние, вы можете обеспечить эффективную работу вашего SQL Server и удовлетворить требования ваших приложений.