Оптимизация производительности SQL Serever c использованием файлов и файловых групп

Перевод: , июль 2001 (оригинал статьи на swynk.com)

Основные понятия

В SQL Server 7.0 и 2000 нет устройств и сегментов. Теперь база данных находится в файлах операционной системы. Существует три типа таких файлов:

• Основной
• Вспомогательный
• Файл транзакций

Каждая база данных состоит как минимум из двух файлов: один из них это основной файл данных (по умолчанию имеющий расширение .mdf) и файл транзакций (по умолчанию имеющий расширение .ldf). База может иметь только один основной файл данных и ноль или более вспомогательных файлов. Каждый файл может находиться в распоряжении только одной базы. То есть, не возможна ситуация (как это было в SQL Server 6.5) когда вы можете создать некоторую базу вместе с разделом транзакций на одном и том же устройстве (в том же файле с расширением .dat).

Файлы базы данных объединяются в файловые группы. Каждый файл может принадлежать только одной группе, однако файл транзакций может принадлежать любой файловой группе. Другими словами файл транзакций управляется отдельно от других. Имеется три типа файловых групп:

• Основная
• Пользовательская
• Группа по умолчанию

Каждая база может иметь только одну основную файловую группу, только одну группу по умолчанию и ноль или более пользовательских групп. Если вы не создадите пользовательские группы, то  ваша база будет состоять из одной файловой группы, которая также будет группой по умолчанию.  Основная файловая группа содержит основной файл данных со всеми системными объектами в нем (системные таблицы, хранимые процедуры, расширенные хранимые процедуры и прочие). Вы не можете удалить системные объекты из основной файловой группы, но вы можете создавать пользовательские объекты  в пользовательских группах для подходящего размещения, увеличения производительности и удобства администрирования. Чтобы создать пользовательскую группу вы должны использовать команду CREATE DATABASE или ALTER DATABASE с ключевым словом FILEGROUP. Группой по умолчанию является группа, в которой создаются все новые пользовательские объекты.  Вы можете изменить группу по умолчанию с помощью команды ALTER DATABASE с ключевым словом DEFAULT.

Файлы базы данных SQL Server могут быть настроены  для автоматического увеличения и уменьшения занимаемого ими пространства, что уменьшает  необходимость управления базой и исключает многие проблемы, которые случаются когда размер базы данных или файла транзакций выходит за допустимый предел. Функция автоувеличения устанавливается по умолчанию; функция автоматического сокращения устанавливается по умолчанию только для Desktop Edition SQL Server 7.0.  Когда вы создаете базу данных, вы должны указать начальный размер файла данных и файла транзакций. Если вы хотите чтобы размер файлов базы данных увеличивался автоматически, вы должны также указать приращение в Мб, Кб, Тб или %. По умолчанию приращение указывается в Мб. Вы также можете указать максимальный размер файла чтобы предотвратить переполнение диска.

Методы оптимизации

Установите разумный размер вашей базы

Перед созданием базы данных вы должны оценить размер вашей базы данных. Чтобы оценить размер базы данных, сначала вы должны оценить размер каждой таблицы и затем сложить эти значения. Подробнее смотрите ссылку.

Установите разумный размер файла транзакций

Общепринятым считается правило устанавливать размер файла транзакций 20-25 % размера базы данных. При меньшем размере базы данный процент увеличивается и наоборот. Например, если размер базы 10 Мб, то размер файла транзакций должен быть 4-5 Мб. Если же размер базы 500 Мб, то для файла транзакций 50 Мб вполне хватит.

Разрешите возможность автоматического увеличения размера файлов данных и файла транзакций

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

Установите разумный размер автоматического приращения

Установите максимальный размер файлов данных и транзакций

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

Создайте пользовательскую файловую группу и сделайте ее группой по умолчанию

Хорошим решением будет хранить и управлять системными и пользовательскими объектами отдельно друг от друга. Причина этого в том, что пользовательские объекты не будут бороться за место в основной файловой группе с системными объектами. Обычно пользовательские группы не создаются для небольших баз. Например, когда размер вашей базы меньше 100 Мб.

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

Команды LOAD TABLE и DUMP TABLE больше не поддерживаются в SQL Server 7.0 (и выше), однако вы можете размещать таблицы в ее собственной файловой группе и можете архивировать и восстанавливать только эту таблицу. Это позволит вам сгруппировать пользовательские объекты со сходными требованиями к обслуживанию в одну и ту же файловую группу.

Если у вас есть несколько дисков, попробуйте создать столько файлов в файловой группе, сколько имеется дисков, и разместите по одному файлу на диск. (Перев.:  по-моему, смысл такой: создать одну файловую группу, состоящую из нескольких файлов, каждый их которых лежит на своем диске. Если я не прав, просьба поправить)

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

Не создавайте много файлов данных и транзакций на одном и том же диске

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

«Труднодоступные» таблицы с колонками содержащими большие текстовые/графические данные размещайте в одной файловой группе, а такие колонки в другой файловой группе на других дисках

Вы можете использовать команду CREATE TABLE с опцией TEXTIMAGE_ON для размещения колонок типа text/image в другой файловой группе. Подробнее смотрите SQL BOL.

Разместите файлы транзакций на отдельном от файлов данных диске

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

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

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

Используйте Windows NT Performance Monitor для определения соответствующего количества файлов данных и транзакций, проверяя счетчик Disk Queue Length.

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

Литература

1. SQL Server Books Online. (Интерактивная помощь в составе SQL Server)

2. Microsoft SQL Server 7.0 Performance Tuning Guide (Руководство по настройке производительности)

3. Microsoft SQL Server 7.0 Storage Engine (Архитектура системы хранения)

4. Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips (Методы планирования емкости)

 наверх