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

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

  • Статья

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

Итоги

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

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

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

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

    Примечание.

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

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

    Чтобы просмотреть текущие параметры, выполните следующую команду 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). Кроме того, иногда это может привести к проблемам с производительностью изменений данных.

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылки