Рекомендации по параметрам автоматического увеличения и автоухрония в SQL Server

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 315512

Сводка

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

Вот некоторые моменты, которые следует учитывать, если вы решили настроить параметры автоматического увеличения и автоухладки.

Разделы справки настройка параметров

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

   • SQL Server Management Studio

   • Оператор ALTER DATABASE

     • Использование параметров файла и файловой группы для изменения параметров автоматического роста
     • Используйте параметры SET для настройки AUTO_SHRINK параметров.

   Примечание.

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

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

   Чтобы просмотреть текущие параметры, выполните следующую команду Transact-SQL:

   sp_helpdb [ [ @dbname= ] 'name' ]
   

2. Имейте в виду, что параметры автоматического увеличения предназначены для каждого файла. Поэтому их необходимо задать по крайней мере в двух местах для каждой базы данных (одно для основного файла данных и одно для первичного файла журнала). Если у вас есть несколько файлов данных и (или) журналов, необходимо задать параметры для каждого файла. В зависимости от среды для каждого файла базы данных могут быть заданы разные параметры.

Рекомендации по AUTO_SHRINK

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

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

• Если несколько баз данных подвергаются частым операциям сжатия и увеличения, это легко приведет к фрагментации на уровне файловой системы. Это может оказать серьезное влияние на производительность. Это верно независимо от того, используете ли вы автоматические параметры или вручную часто увеличиваете и сжимаете файлы.

• После AUTO_SHRINK успешного сжатия файла данных или журнала последующая операция DML или DDL может значительно замедлить работу, если требуется место и файлы должны увеличиться.

• Фоновая AUTO_SHRINK задача может занимать ресурсы при наличии большого количества баз данных, которые нуждаются в сжатии.

• Фоновая AUTO_SHRINK задача должна получить блокировки и другую синхронизацию, которая может конфликтовать с другими обычными действиями приложения.

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

Рекомендации по AUTOGROW

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

• Если выполняется большая транзакция, требующая увеличения журнала, другие транзакции, для которых требуется запись в журнал транзакций, также придется дождаться завершения операции увеличения.

• Если в файлах журнала много разрастания файлов, возможно, слишком большое количество виртуальных файлов журналов (VLF). Это может привести к проблемам с производительностью при запуске базы данных и операциях в сети, репликации, зеркального отображения и отслеживания измененных данных (CDC). Кроме того, иногда это может привести к проблемам с производительностью при изменении данных.

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

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

Рекомендации по автоматическому обрастание и автоматическое обхухрение

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

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

• Autoshrink и autogrow должны быть тщательно оценены обученным администратором базы данных (DBA); Они не должны оставаться неуправляемыми.

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

• \<MAXSIZE> Включите параметр для каждого файла, чтобы предотвратить рост любого файла до точки, когда он использует все доступное место на диске.

• Оставьте размер транзакций как можно меньше, чтобы предотвратить незапланированный рост файлов.

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

• Параметр автоматического увеличения не может увеличить размер базы данных за пределы доступного дискового пространства на дисках, для которых определены файлы. Таким образом, если вы полагаетесь на функциональность автоматического увеличения размера баз данных, необходимо по-прежнему независимо проверка свободного места на жестком диске. Параметр автоматического увеличения также ограничен параметром, MAXSIZE который вы выбираете для каждого файла. Чтобы уменьшить вероятность сокращения свободного места, можно отслеживать счетчик Монитор производительности SQL Server: Database Object: Data File(s) Size (КБ) и настроить оповещение, когда база данных достигнет определенного размера.

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

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

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

Ссылки

• Устранение неполадок с полным журналом транзакций (ошибка SQL Server 9002)

• Инициализация мгновенного файла базы данных

• Руководство по архитектуре и управлению журналом транзакций SQL Server

• Управление размером файла журнала транзакций