Параметры автоматического увеличения и сжатия в SQL Server

Переводы статьи Переводы статьи
Код статьи: 315512 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

Аннотация

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

Дополнительная информация

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

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

  1. Для настройки параметров автоматического увеличения и автоматического сжатия воспользуйтесь одним из следующих способов.
    • Инструкция ALTER DATABASE (недоступно в SQL Server 7.0)
    • SQL Server Management Studio или SQL Enterprise Manager
    • Хранимая процедура sp_dboption (устарело в SQL Server 2005)
    Примечание. В SQL Server 2005 используйте SQL Server Management Studio вместо SQL Enterprise Manager. Дополнительные сведения о настройке этих параметров в SQL Server 2005 см. на следующих веб-сайтах MSDN:
    Как добавить данные или файлы журналов в базу данных (среда SQL Server Management Studio)
    http://msdn.microsoft.com/ru-ru/library/ms189253.aspx
    Свойства базы данных (страница "Файлы")
    http://msdn.microsoft.com/ru-ru/library/ms180254.aspx
    Параметр автоматического увеличения также можно настроить при создании базы данных.

    Текущие настройки параметров можно просмотреть в свойствах базы данных в приложении SQL Enterprise Manager. Также можно выполнить следующую команду Transact-SQL:
    sp_helpdb [ [ @dbname= ] 'name' ]
  2. Имейте в виду, что параметры автоматического увеличения применяются к отдельным файлам. Поэтому их необходимо настраивать по крайней мере два раза для каждой базы данных (для первичного файла данных и для первичного файла журнала). При наличии нескольких файлов данных или журнала необходимо настроить параметры для каждого файла. В зависимости от потребностей среды для каждого файла базы данных могут быть настроены различные параметры.

Влияние на производительность

  • Если при выполнении транзакции недостаточно места для журнала, а для журнала транзакций базы данных включен параметр автоматического увеличения, время выполнения транзакции возрастет на время, необходимое для увеличения журнала транзакций на заданное значение. Если шаг роста велик или увеличение журнала длится долго по иным причинам, запрос, в котором открыта транзакция, может быть не выполнен из-за истечения времени ожидания. Такая же проблема может возникать и при автоматическом увеличении файлов данных базы данных. Сведения об изменении параметра автоматического увеличения см. в разделе "ALTER DATABASE" электронной документации по SQL Server.
  • При выполнении транзакции, требующей увеличения журнала, другие транзакции, производящие запись в журнал транзакций, также будут ожидать завершения операции увеличения журнала.
  • Совместное использовании параметров автоматического увеличения и автоматического сжатия может приводить к росту временных затрат. Убедитесь в том, что пороговые значения, при достижении которых запускаются операции увеличения и сжатия, не будут приводить к слишком частому изменению размера журнала. Например, выполняется транзакция, ко времени завершения которой журнал транзакций увеличивается на 100 МБ. Через некоторое время запускается операция автоматического сжатия, и размер журнала транзакций уменьшается на 100 МБ. После этого данная транзакция запускается повторно, и журнал транзакций снова увеличивается на 100 МБ. В этом примере создаются ненужные временные затраты и, возможно, происходит фрагментация файла журнала. И то, и другое отрицательно влияет на производительность.
  • Физическая фрагментация в результате изменения размеров файлов данных или журнала может привести к серьезному снижению производительности. Это может происходить при частом увеличении и сжатии файлов как автоматически, так и вручную.
  • Увеличение размера базы данных с небольшим приростом или поочередное увеличение и уменьшение ее размера может привести к фрагментации диска. В некоторых условиях фрагментация диска может приводить к снижению производительности. При использовании небольшого шага роста также может снижаться производительность системы.
  • В SQL Server 2005 и более поздних версиях можно включить мгновенную инициализацию файлов. Мгновенная инициализация файлов ускоряет размещение только файлов данных. К файлам журнала она не применяется.
  • При частом увеличении размера файлов журнала число виртуальных файлов журнала (VLF) может чрезмерно вырасти. Это может привести к проблемам с производительностью при запуске баз данных, выполнении сетевых операций, репликации, зеркальном отображении и отслеживании изменений в данных. Кроме того, иногда может снижаться производительность при изменении данных.

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

  • В управляемой производственной системе функцию автоматического увеличения следует использовать только как средство увеличения размера файлов в чрезвычайных обстоятельствах. Не используйте ее для повседневного управления ростом объема данных и журнала.
  • Для наблюдения за размерами файлов и их заблаговременного увеличения можно воспользоваться оповещениями или программами мониторинга. Это позволит избежать фрагментации и перенести выполнение этих операций по обслуживанию на часы, когда нагрузка минимальна.
  • Возможность использования параметров автоматического увеличения и автоматического сжатия должна быть тщательно проанализирована опытным администратором баз данных.
  • Шаг автоматического увеличения должен быть достаточно большим во избежание потерь производительности по причинам, рассмотренным в предыдущем разделе. Точное значение, которое следует использовать в данной конфигурации, и способ изменения размера файлов (в процентах или в МБ) зависят от различных факторов среды. На практике при тестировании можно задать для параметра автоматического увеличения значение, равное одной восьмой размера файла.
  • Отключите параметр <MAXSIZE> для каждого файла во избежание ситуации, когда файл занимает все свободное дисковое пространство.
  • По возможности минимизируйте размер транзакций во избежание неконтролируемого увеличения размера файлов.

Почему необходимо следить за объемом дискового пространства, если размеры файлов контролируется автоматически?

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

Ссылки

Дополнительные сведения об изменении размеров файлов базы данных и журнала см. в следующих статьях базы знаний Майкрософт.
256650 Уменьшение размера журнала транзакций в SQL Server 7.0 (Эта ссылка может указывать на содержимое полностью или частично на английском языке.)
272318 INF: Использование оператора DBCC SHRINKFILE для сжатия журнала транзакций в SQL Server 2000
317375 Неконтролируемое увеличение размера и переполнение журнала транзакций на компьютере с SQL Server (Эта ссылка может указывать на содержимое полностью или частично на английском языке.)
247751 ОШИБКА: невозможно уменьшить размер базы данных в соответствии с планом обслуживания базы данных (Эта ссылка может указывать на содержимое полностью или частично на английском языке.)
305635 Превышено время ожидания при автоматическом увеличении размера базы данных (Эта ссылка может указывать на содержимое полностью или частично на английском языке.)
949523 При небольших значениях свойств "Исходный размер" и "Авторасширение" в SQL Server 2005 наблюдается длительная задержка репликации транзакций (Эта ссылка может указывать на содержимое полностью или частично на английском языке.)
Дополнительные сведения об инициализации файлов баз данных см. на следующем веб-сайте MSDN:
Инициализация файлов базы данных
Дополнительные сведения о включении мгновенной инициализации файлов см. на веб-сайте Майкрософт по следующему адресу:
Причины и способы включения мгновенной инициализации файлов
Дополнительные сведения о физической архитектуре журнала транзакций см. на следующей странице веб-сайта MSDN:
Физическая архитектура журнала транзакций

Разделы электронной документации по SQL Server: "Физическая архитектура журнала транзакций"; "Уменьшение размера журнала транзакций"

Свойства

Код статьи: 315512 - Последний отзыв: 17 сентября 2011 г. - Revision: 3.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Ключевые слова: 
kbsqlmanagementtools kbinfo KB315512

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com