СОВЕТ

Оптимизация работы SQL Server за счёт грамотного использования файлов и filegroups
По материалам статьи 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

ИНФОРМАЦИЯ АВТОРА РАССЫЛКИ

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

ССЫЛКИ НА СТАТЬИ

Внедрение OLAP
Автор: N/A.
C чего начинается практическое внедрение OLAP-системы? Нет, вовсе не с ее приобретения. Прежде чем приобретать какой-либо конкретный программный продукт и приступить к его внедрению, необходимо решить, какой именно тип OLAP лучше всего подходит вашему предприятию. OLAP-системы отличаются одна от другой гораздо сильнее, чем, например, реляционные базы данных, языки программирования или текстовые редакторы, что увеличивает возможность ошибки при выборе. Поэтому вопрос внедрения — это прежде всего вопрос выбора... [подробнее]
Microsoft SQL Server 2000. Краткий обзор новинок
Автор: Алексей Федоров.
В данном обзоре мы ознакомимся с основными новинками в Microsoft SQL Server 2000 — следующей версии серверной СУБД фирмы Microsoft, выпуск которой ожидается в скором времени. Отдельная статья, публикуемая в данном номере, посвящена обсуждению Analysis Services в Microsoft SQL Server 2000 — новых OLAP- и Data Mining-средств, появившихся в SQL Server 2000... [подробнее]
Microsoft SQL Server 2000 — что нового?
Автор: Технология Клиент-Сервер 2000'3.
Итак, время SQL Server 7.0 истекло. На смену ему пришел Microsoft SQL Server 2000 — следующая версия сервера БД от фирмы Microsoft. Эта новая версия во многом похожа на S0L Server 7.0, и, возможно, если бы не страсть к переименованиям, ее можно было бы назвать версией 7.х... [подробнее]
Rational Rose, BPwin и другие — аспект анализа бизнес-процессов
Автор: Павел Сахаров.
В последнее время для целей анализа деятельности предприятий все большее распространение получает средство моделирования Rational Rose компании Rational Software. Подтверждение этому факту легко найти в Internet, проанализировав требования, которые формулируют различные компании к кандидатам на ИТ-вакансии. В большинстве случаев в состав требований обязательно включается знание Rational Rose и унифицированного языка моделирования (UML), на котором оно основано... [подробнее]
Centura Net.db - средство визуального создания Web-сайтов для доступа к СУБД
Автор: Большаков С. А.
Интернет технологии применяются в настоящее время практически в любых областях деятельности человека. В связи с этим растет число программных продуктов, ориентированных на построение информационных систем, клиентских приложений и новейших информационных средств общения и бизнеса. С одной стороны, для компании (организации, даже отдельного человека) не так сложно построить и опубликовать страницу или их взаимосвязанное множество в Интернет. Для достижения этой цели существует много программных продуктов: можно воспользоваться даже текстовыми редакторами или, наконец, вручную написать на HTML. Однако эти возможности значительно сужаются, когда речь заходит о возможности доступа к базам данных (БД), хранения и изменения структурированной информации... [подробнее]
Centura Web Developer для создания профессиональных сайтов с БД
Автор: Большаков С. А.
Интерес к Интернет-технологиям чрезвычайно велик - число сайтов во всем мире растет катастрофическими темпами. Однако многие уже на первых стадиях разработки понимают, что это трудоемкое и затратное занятие, а когда дело доходит до создания сайтов корпоративных систем, основанных на базах данных (БД), то энтузиазм пропадает... [подробнее]
Программное создание базы данных
Автор: Павел Сурменок.
Создание базы данных производится с помощью библиотеки DAO. Для начала нужно подключить эту библиотеку. Для этого в меню Project|References отметьте пункт "Microsoft DAO 3.6 Objects Library". В составе Visual Basic 6.0 поставляется библиотека версии 3.6. Если у Вас более ранняя версия - отметьте её, особых различий нет... [подробнее]

Новые технические статьи Microsoft

INF: How to Obtain the Latest SQL Server 2000 Service Pack (Q290211)
INFO: Known Issues with the Visual Basic T-SQL Debugger and SQL Server 2000 (Q310650)
INF: Troubleshooting Stored Procedure Recompilation (Q243586)
BUG: ATL OLE DB Provider Fails When Called from SQL 7.0 Query (Q198520)
SAMPLE: Embedcgi.exe CGI Application Shows How to Use Embedded SQL (Q242027)
FIX: Registry Permission Difference When You Install OLAP Services on Windows 2000 (Q241088)
SAMPLE: Sqlfstin: Speed Up OLE DB Inserts By Using IRowsetFastLoad() (Q246262)
FIX: Service Pack Installation May Save Standard Security Password in File (Q263968)
SAMPLE: How To Retrieve XML Data Using an SQL XML Query with ADO in Visual C++ (Q271780)
SAMPLE: How to Run Singleton SELECT Queries in Visual C++ (Q272176)
SAMPLE: Using OLEDB Templates to Retrieve XML Data Through XPath Query (Q272177)
SAMPLE: How to Retrieve Data Using a Template File in ATL OLE DB (Q272181)
SAMPLE: How to Retrieve Data Using a SQL XML Query in ATL OLE DB (Q272185)
FILE: Use Xpvmlog to Dump the Layout of Virtual Memory in SQL Server (Q279113)
SAMPLE: RLockMFC.exe Demonstrates Row-Level Locking Using MFC and SQL Server (Q288054)
FIX: Potential English Query Security Issue (Q297105)
SQL Server 2000 User-Defined Functions White Paper (Q303343)
FIX: Stored Procedure That Creates Temp Table [#a.b.c.d.e] Causes Access Violation (Q308817)
FIX: SQL Server Agent Reports "Succeeded" when Scheduled DTS Package Task Fails (Q308830)
FIX: Setup May Fail with "Setup is in resume mode in some of the cluster nodes" Error Message (Q312584)
FIX: Invalid Use of the Getdate Function May Cause Access Violation (Q308759)
FIX: Query with SET NO_BROWSETABLE Option Set ON May Generate Access Violation (Q302664)
FIX: Index Is Corrupt When Full-Text Catalog Population Is Interrupted on a Cluster (Q308716)
FIX: SQL Server Text Formatting Functions Contain Unchecked Buffers (Q304850)
FIX: SQL Server Text Formatting Functions Contain Unchecked Buffers (Q304851)
FIX: Busy Connections Leave Cursors Open After Call To SQLFreeStmt or SQLCloseCursor (Q253010)

ФОРУМ SQL.RU: САМЫЕ ПОПУЛЯРНЫЕ ТОПИКИ НЕДЕЛИ

Нумерация результатов SQL-запроса
Репликация на дискетах или по мылу
немного не в тему но всетаки Delphi Mssql
Вопрос по быстродействию,... курсоры или ..... (MS SQL 2000)
Восстановление @@SERVERNAME
Что лучше и быстрее, испольование IN или INNER JOIN ? (MS SQL 2000)
extended procedure on MSSQL7.0 & Delphi5
Покажите, как можно передать таблицу в хранимую процедуру, как параметр -SQL2000
Глюк при установке MS SQL 2000 на W2k
2-ой сервис-пак. История продолжается.
как администрировать SQL Server через internet ?
Запрос к базе.
Иерархии с сортировкой внутри уровня.

ФОРУМ SQL.RU: ВОПРОСЫ ОСТАЛИСЬ БЕЗ ОТВЕТА

репликация
ErWin 4.0, подскажите...
Обработка алертов - параметры ошибки