По материалам статьи Alexander Chigrik на swynk.com:
Optimizing
SQL Server Performance by using File and Filegroups
Общая концепция
В SQL Server 7.0 и SQL Server 2000 нет больше таких
понятий, как устройства и сегменты, которые использовались в
SQL Server 6.5. Теперь базы данных постоянно находятся в
файлах операционной системы. Существует три типа таких
файлов:
- primary
- secondary
- log
Каждая база данных состоит по крайней мере из двух
файлов: один - первичный файл данных (по умолчанию, с
расширением .mdf), второй - журнал транзакций (по умолчанию, с
расширением .ldf). Также, могут использоваться вторичные файлы
данных (по умолчанию, с расширением .ndf). База данных может
иметь только один первичный файл данных, и одного или
несколько вторичных файлов данных, а также один или более
журналов. Каждый файл базы данных может использоваться только
одной базой. Так что не может возникнуть такой ситуации (как в
SQL Server 6.5), когда можно было создать несколько баз данных
вместе с журналами на одном и том же устройстве (в одном файле
с расширением .dat).
Файлы базы данных объединены в группы
(filegroups). Каждый файл данных может быть членом только
одной filegroup, но журналы транзакций не могут входить ни в
какие filegroups. Другими словами, журналы управляются
отдельно друг от друга. Есть три типа filegroups:
- primary
- user-defined
- default
Каждая база данных может иметь только оду первичную
filegroup, только одну filegroup по умолчанию и ни одной или
несколько определяемых пользователем filegroups. Если Вы не
используете определяемые пользователем filegroups, ваша база
данных будет иметь только одну первичную filegroup, которая
также будет являться filegroup по умолчанию. Первичная
filegroup содержит первичный файл данных со всеми системными
объектами (системные таблицы, системные хранимые процедуры,
расширенные хранимые процедуры и так далее). Вы не можете
удалять системные объекты из первичной filegroup, но Вы можете
создавать пользовательские объекты в определяемой
пользователем filegroups, в целях повышения эффективности и
удобства администрирования. Чтобы создавать определяемую
пользователем filegroup, Вы можете применить команды CREATE
DATABASE или ALTER DATABASE с ключевым словом FILEGROUP.
В
filegroup заданной по умолчанию будут созданы все новые
пользовательские объекты. Вы можете изменять назначение
filegroup по умолчанию, используя команду ALTER DATABASE с
ключевым словом DEFAULT.
Файлы базы данных SQL Server могут
быть сконфигурированы так, чтобы они росли и усекались
автоматически, что сокращает расходы на администрирование баз
данных и устраняет часто возникающие проблемы, связанные с
исчерпанием свободного дискового пространства журналами и
базами. Автоприращение установлено по умолчанию. Автоусечение
установлено по умолчанию только для SQL Server 7.0 Desktop
Edition. Когда Вы создаете базу данных, Вы устанавливаете
первоначальный размер для данных и журналов. Если Вы хотите
заставить файлы базы данных расти автоматически, Вы должны
также определить автоприращение в мегабайтах, килобайтах,
гигабайтах, терабайтах или процентах. Значение по умолчанию -
мегабайт. Также, Вы можете определить максимальный размер
файла, чтобы предотвратить исчерпание всего дискового
пространства.
Советы по оптимизации
Установите разумный размер для вашей базы
данных.
Прежде всего, перед созданием базы данных, Вы
должны оценить насколько большой ваша база данных будет в
будущем. Чтобы оценить разумный размер базы данных, сначала
оценивают размер каждой отдельной таблицы, а затем суммируют
полученные значения. Следующие шаги могут использоваться для
оценки размера дискового пространства, требуемого для
размещения данных в таблице:
1. Определите текущее
количество строк в таблице: Число строк в таблице =
Num_Rows
2. Если у таблицы есть столбцы фиксированной и
переменной длины, вычисляйте необходимое для неё место, с
учётом каждого из этих типов столбцов в строке данных. Размер
столбца зависит от спецификации длины и типа данных. Для
получения дополнительной информации, см. Типы
данных.
Число столбцов = Num_Cols
Сумма байтов по
всем fixed-length columns = Fixed_Data_Size
Число столбцов
переменной длины = Num_Variable_Cols
Максимальный размер
всей variable-length columns = Max_Var_Size
3. Если в
таблице есть столбцы фиксированной длины, та часть строки,
которая называется null bitmap, должна рассматриваться, как
nullability столбцы. Вычислите их размер:
Null Bitmap
(Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
В расчёте должна
использоваться только целочисленная часть этого выражения.
Остаток отбрасывается.
4. Если в таблице есть столбцы
переменной длины, следующая формула используется для
определения необходимого места для хранения данных этих
столбцов в строках:
Total size of variable-length columns
(Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) +
Max_Var_Size
Если нет столбцов переменной длины, установите
Variable_Data_Size = 0.
Эта формула предполагает, что все
столбцы переменной длины (100 процентов). Если у Вас этот
процент ниже, Вы можете откорректировать результат с учётом
этого процента, что позволит получить более точную оценку
полного размера таблицы.
5. Вычисление размера
строки:
Полный размер строки (Row_Size) = Fixed_Data_Size +
Variable_Data_Size + null_Bitmap +4
Последнее число 4
определяет размер заголовка строки данных.
6. Вычисляем
число строк на странице (8096 байт на странице):
Число
строк на странице Rows_Per_Page = (8096) / (Row_Size +
2)
Поскольку строки не занимают всю страницу, число строк
на странице должно быть округлено до числа помещающихся на
странице целых строк.
7. Если у таблицы должен быть создан
кластерный индекс, вычисляйте число размещаемых на странице
строк с учётом значения fill factor. Для получения
дополнительной информации, см. fill
factor
Если кластерный индекс не создаётся, используйте
значение Fill_Factor = 100. Число свободных строк на странице
(Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) /
(Row_Size + 2)
В этой формуле лучше использовать
целочисленное значение fill factor.
Поскольку строки не
занимают страницы целиком, число строк на страницу должен быть
округлен до самого большого целого числа. Увеличение fill
factor позволяет размещать на одной странице большее
количество данных и будет занято меньшее количество
страниц.
8. Вычисляем число страниц, необходимых для
хранения всех строк:
Число страниц (Num_Pages) = Num_Rows /
(Rows_Per_Page - Free_Rows_Per_Page)
Число рассчитанных
страниц должен быть округлено.
9. Вычисляем количество
места, необходимого для хранения данные в таблице (8192 байта
на странице):
Table size (bytes) = 8192 x Num_Pages
Установите разумный размер для журнала регистрации
транзакций (transaction log).
Стандартное руководство
рекомендует устанавливать размер transaction log на 20-25
процентов от размера базы данных. Реально, для маленьких баз
данных, размер transaction log может быть относительно
большим, а для больших баз может оказаться достаточно иметь
журнал, не превышающий 10-ти процентов от размера базы.
Например, если оценочный размер базы данных будет равен 10МБ,
Вы можете установить размер transaction log в 4-5Mb, но если
оценочный размер базы данных достигает 500МБ, то может
оказаться достаточно и 50МБ для эффективной работы transaction
log.
Оставьте включённой опцию автоприращения для файлов
данных и журналов.
Использование этой возможности
позволяет увеличивать ресурсы SQL Server когда это необходимо,
и без вмешательства DBA. Автоприращение необходимо, когда в
вашей фирме нет DBA, или его опыт ограничен.
Установите разумную дискретность для
автоприращения.
Процесс автоматического увеличения
файла может привести к некоторой потере эффективности, поэтому
Вы должны выбрать разумный размер для величины автоприращения,
стараясь избегать слишком частого увеличения размера файлов.
Попробуйте установить начальный размер базы данных и размера
автоприращения так, чтобы автоматический рост возникал не чаще
одного раза в неделю.
Не устанавливайте опцию автоусечения.
Во время
выполнения операции автоусечения файлов происходит
значительное снижение эффективности, поэтому, Вам лучше
усекать базу данных вручную или создать задачу по
периодическому усечению базы данных в то время, когда нет
рабочей нагрузки на ресурсы сервера.
Определите максимальный размер баз данных и
журналов.
Определите максимальный размер, до которого
файлы могут вырасти, чтобы предотвратить ситуацию, когда
дисковое пространство будет полностью исчерпано.
Создайте определяемую пользователем filegroup, и
сделайте её файловой группой по умолчанию.
Вообще, это
хорошее решение, хранить и управлять системными и
пользовательскими объектами отдельно друг от друга. Это
выгодно потому, что пользовательские объекты не будут
конкурировать с системными за первичную filegroup. Обычно,
определяемые пользователем filegroup не создаются для
маленьких баз данных, если, например, ваша база данных меньше
100Мб.
Создайте определяемые пользователем группы файлов, и
разместите некоторые таблицы в этих группах, это позволит
упростить многие задачи администрирования (резервирование,
DBCC, обновление статистики и так далее) для этих
таблиц.
Команды LOAD TABLE и DUMP TABLE начиная с SQL
Server 7.0 (и выше) больше не поддерживаются, но Вы можете
разместить таблицу в её собственной filegroup, и потом можете
резервировать и восстанавливать только одну эту таблицу. Это
также позволит Вам разместить объекты со схожими требованиями
к обслуживанию в одну и ту же пользовательскую группу
(filegroup).
Если у Вас в распоряжение имеется несколько физических
дисков, пробуйте создать столько файлов filegroup, сколько
есть физических дисков, и поместить по одному файлу на каждый
диск.
Это повысит эффективность потому, что обращение к
таблицам происходит последовательно и для каждого файла будет
создан отдельный поток, что позволяет распараллелить чтение
данных этих таблиц.
Не создавайте много баз данных и журналов на одном и том
же физическом диске.
Опция автоприращения,
установленная для баз и журналов может стать причиной
фрагментации этих файлов, если таких файлов много на одном
диске. В большинстве случаев, достаточно иметь 1-2 файла базы
данных на одном физическом диске.
Большую или часто используемую таблицу разместите в
отдельную filegroup, и разместите индексы таблицы в другой
filegroup на физически другом диске.
Это повысит
эффективность, потому что для таблицы и индексов будут
создаваться отдельные процессы и работать они будут
параллельно.
Для большой или часто используемой таблицы со столбцами
text/image, создайте отдельную filegroup, и разместите столбцы
text/image в разных filegroup на разных физических
дисках.
Вы можете использовать инструкцию CREATE TABLE
с ключевым словом TEXTIMAGE_ON, чтобы разместить столбцы
text/image в разный filegroup. Для дополнительной информации,
см. SQL BOL.
Размещайте журналы и файлы данных на разных физических
дисках.
Поскольку журналы регистрации транзакций очень
активно используются для записи, важно, что бы их диски имели
достаточно высокую пропускную способность I/O.
Если один из запросов к данным на объединение
используется очень часто, разместите таблицы, используемые в
этом запросе, в различных filegroups на различных физических
дисках.
Если Вы используете read-only таблицы, размещаете эти
таблицы в разный filegroups на разных физических дисках и
используете инструкции ALTER DATABASE, чтобы сделать read-only
только эти filegroups.
Этот не только увеличивает
эффективность операций чтения, но и предотвратит любые
изменения данных и позволит Вам управлять разрешениями на эти
данные.
Используйте Windows NT Performance Monitor для
определения воздействия разного числа баз данных и журналов на
производительность сервера, анализируя счетчик длины очереди к
диску (Disk Queue Length).
Чем большее количество
файлов баз данных и filegroups, тем более трудно управлять
этой базой данных. Рассмотрите возможность сокращения числа
файлов, если Disk Queue Length превышает число 3, и
продолжайте контроль.
Литература:
1. SQL Server Books Online.
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