Параметры автозавода и автосхройки в SQL Server

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

Сводка

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

Дополнительные сведения

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

Настройка параметров

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

    Примечание

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

    Вы также можете настроить параметр autogrow при создании базы данных.

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

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

Соображения для AUTO_SHRINK

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

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

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

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

  3. Фоновая AUTO_SHRINK может занять ресурсы, если существует множество баз данных, которые требуют сокращения.

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

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

Соображения для AUTOGROW

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

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

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

Примечание

Если объединить параметры autogrow и autoshrink, можно создать ненужные накладные расходы. Убедитесь, что пороговые значения, которые вызывают операции роста и сжатия, не вызывают частых изменений размера вверх и вниз. Например, можно выполнить транзакцию, из-за которую к моменту совершения журнал транзакций вырастет на 100 МБ. Через некоторое время автосхирк запускает и сжимает журнал транзакций на 100 МБ. Затем вы запустите ту же транзакцию, и журнал транзакций снова вырастет на 100 МБ. В этом примере создается ненужная накладная часть и потенциально создается фрагментация файла журнала, любой из которых может отрицательно повлиять на производительность.

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

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

Лучшие практики для autogrow и autoshrink

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

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

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

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

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

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

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

  • Параметр autogrow не может увеличивать размер базы данных за пределы доступного дискового пространства на дисках, для которых определены файлы. Поэтому, если вы полагаетесь на функциональность autogrow для размера баз данных, вы все равно должны самостоятельно проверить доступное пространство жесткого диска. Параметр autogrow также ограничен параметром MAXSIZE, выбранным для каждого файла. Чтобы уменьшить вероятность на исходе пространства, можно отслеживать счетчик performance Monitor SQL Server: Объект базы данных: размер файла данных (S) и настроить оповещение о достижении базы данных определенного размера.

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

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

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

Ссылки