Microsoft SQL Server 7.0
Руководство по оптимизации

 Генри Лау


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

Настоящий документ предназначен только для информационных целей. MICROSOFT НЕ ДАЕТ В ЭТОМ ДОКУМЕНТЕ НИКАКИХ ЯВНЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ ГАРАНТИЙ.

©1998 Microsoft Corporation. Все права защищены.

Microsoft, BackOffice, логотип BackOffice, Windows и Windows NT - являются зарегистрированными товарными знаками или товарными знаками корпорации Microsoft на территории США и/или других стран.

Другие упоминаемые в документе названия продуктов и компаний могут представлять собой товарные знаки соответствующих владельцев.

Номер публикации: 098-81529

Содержание

Целевая аудитория............................................................................................................................................... 5

Введение................................................................................................................................................................... 6

Microsoft SQL Server, версии 6.x и 7.0: сравнение возможностей оптимизации............................ 6

Принципы оптимизации SQL Server............................................................................................................ 8

Проверка параметра max async IO при настройке сервера................................................................ 10

Основные компоненты, потребляющие ресурсы процессора и системы обмена с диском... 11

Рабочие потоки............................................................................................................................................. 11

Служба Lazy Writer...................................................................................................................................... 11

Контрольная точка...................................................................................................................................... 12

Утилита Log Manager.................................................................................................................................. 13

Диспетчер Read Ahead Manager.............................................................................................................. 14

Оптимизация обращения к диску............................................................................................................... 16

Паспортные скорости передачи данных и SQL Server...................................................................... 16

Операции последовательного и непоследовательного обращения к диску................................ 17

Скорость обмена с диском и быстродействие шины PCI.................................................................. 18

Технология RAID.......................................................................................................................................... 19

Аппаратные RAID-контроллеры со встроенным кэшем........................................................... 21

Варианты RAID-массивов................................................................................................................. 21

Наиболее распространенные варианты RAID-массивов......................................................... 22

Оперативное расширение RAID-массива...................................................................................... 22

Утилита Performance Monitor и RAID-массивы........................................................................... 22

Программный RAID-контроллер в операционной системе Windows NT............................. 24

Параллельный доступ к дискам............................................................................................................... 24

Индексы в SQL Server....................................................................................................................................... 28

Кластеризованные индексы...................................................................................................................... 29

Некластеризованные индексы.................................................................................................................. 29

Групповые индексы..................................................................................................................................... 30

Автоматические групповые индексы и групповые запросы............................................................ 31

Выбор индексов............................................................................................................................................ 31

Выбор кластеризованных индексов........................................................................................................ 32

Пример выбора кластеризованного индекса................................................................ 33

Параметры FILLFACTOR и PAD_INDEX.............................................................................................. 35

Средства оптимизации SQL Server.............................................................................................................. 38

Пробные данные и тестовая нагрузка.................................................................................................... 38

Утилита SQL Server Profiler........................................................................................................................ 38

Использование SQL Server Profiler совместно с мастером Index Tuning Wizard................. 38

Анализ информации в SQL Server Profiler...................................................................................... 40

Анализатор SQL Server Query Analyzer.................................................................................................. 41

Статистика ввода/вывода.................................................................................................................. 41

Служба ShowPlan................................................................................................................................. 42

Примеры информации, предоставляемой службой ShowPlan......................................... 42

Утилита Performance Monitor................................................................................................................... 46

Основные регистры Performance Monitor...................................................................................... 48

(Physical or Logical) Disk Queue > 2........................................................................................... 48

System: Processor Queue Length > 2 (на каждый процессор)............................................. 49

Аппаратное обращение к страничной памяти: Memory: Pages/Sec > 0 и Memory: Page Reads/Sec > 5............................................................................................................................. 49

Программное обращение к страничной памяти: Memory: Pages Faults/Sec > 0......... 50

Контроль за работой процессоров.................................................................................................. 51

Регистры обращений к диску............................................................................................................ 52

Графическое представление результатов в Performance Monitor.......................................... 53

Другие способы повышения быстродействия......................................................................................... 54

Снижение сетевого трафика и уменьшение количества потребляемых ресурсов..................... 54

Взаимная блокировка.................................................................................................................................. 54

Нежелательные выражения....................................................................................................................... 55

Грамотная нормализация.......................................................................................................................... 55

Представления с разбиением.................................................................................................................... 55

Быстродействие при репликации и создании резервных копий..................................................... 56

Пример оптимизации обращений к дискам ЕМС................................................................................ 56

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

Целевая аудитория

Данное руководство по оптимизации работы Microsoft® SQL Server™ 7.0 предназначено для администраторов баз данных, перед которыми стоит задача настройки сервера для оптимальной работы. Данное руководство может также помочь администратору базы данных при необходимости определить причину низкой эффективности работы отдельных элементов SQL Server. В публикации приводятся сведения об использовании индексов и средств оптимизации SQL Server, предназначенных для анализа эффективности процедур ввода/вывода информации при выполнении запросов.

Введение

Данная таблица может оказаться полезной для тех администраторов, знакомых с предыдущими версиями Microsoft SQL Server. В ней приводится сравнение возможностей SQL Server 7.0 с предыдущими версиями.

Microsoft SQL Server, версии 6.x и 7.0: сравнение возможностей оптимизации

Особенности версий SQL 6.x

Принципы работы и усовершенствования в версии SQL Server 7.0

При оптимизации работы сервера возможно большое количество вариантов оптимизации

В Microsoft SQL Server 7.0 механизм баз данных стал самонастраивающимся, самоуправляющимся и приобрел возможность автоматической оптимизации параметров настройки. Службы Lazy writer и Read Ahead Manager имеют самонастраивающийся характер. Опция max async IO является единственной опцией класса sp_configure, которую необходимо настраивать вручную, и то только в том случае, когда используются серверы с большим объемом памяти. Такое снижение к требованиям настройки параметров сервера позволяет администратору высвободить время для решения более важных задач.

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

Иногда требуется ручная настройка службы lazy writer.

В отличие от более ранних версий, Microsoft SQL Server 7.0 обеспечивает автоматическую настройку и оптимизацию службы lazy writer. Администратору более не требуется вручную настраивать такие параметры как free buffer и max lazywrite IO. За использованием свободного пространства буфера и процедурами ввода/вывода данных при работе lazy writer по-прежнему можно следить с помощью объектов утилиты Performance Monitor, имеющихся в SQL Server 7.0.

Иногда требуется ручная настройка контрольной точки.

В более ранних версиях Microsoft SQL Server опция recovery interval использовалась также и для оптимизации процесса настройки контрольной точки. Если параметру recovery interval присваивается значение 0, задаваемое по умолчанию, то Microsoft SQL Server 7.0 автоматически следит за интервалом восстановления и настраивает его величину. Значение, используемое по умолчанию, обеспечивает интервал восстановления меньше одной минуты для всех баз данных, если только в системе не применяются чрезвычайно длинные транзакции. Более подробную информацию можно получить в разделах Checkpoints и Active Portion of the Log в SQL Server 7.0 Books Online, а также в результате поиска по ключевому слову troubleshooting recovery interval.

Страницы файла регистрации SQL Server 6.x используют оперативную память совместно со страницами данных. Иногда требуется ручная настройка службы log manager.

Диспетчер журнала регистрации SQL Server log manager в Microsoft SQL Server 7.0 претерпел значительные изменения. В SQL Server 7.0 эта служба имеет свой собственный кэш. Новая версия SQL Server не зависит от таблицы syslogs, как это было в более ранних версиях SQL Server. Разделение функций управления файлом регистрации и кэшем данных повышает эффективность работы обеих компонент.

Диспетчер файла регистрации SQL Server log manager позволяет также производить обмен данными с диском большими блоками, чем это было возможно в предыдущих версиях SQL Server. Кроме того, больший размер блоков ввода/вывода и последовательный режим обмена повышают быстродействие процесса регистрации. SQL Server 7.0 обеспечивает автоматическую оптимизацию работы диспетчера файла регистрации. В новой версии не требуется ручной настройки параметра logwrite sleep класса sp_configure: она удалена из SQL Server 7.0. Более подробную информацию можно получить в результате поиска по ключевому слову logwrite sleep в SQL Server 7.0 Books Online.


 

Разбиение страниц значительно повышает нагрузку на сервер базы данных.

В более ранних версиях Microsoft SQL Server страницы индексов В-дерева требовали больших затрат на их обработку из-за того, что при разбиении страниц (возникающем, когда вставляемые строки заполняют страницу данных или страницу индексов) требуется производить значительные объемы вычислений, связанных с указателем строки. В SQL Server 7.0 структура хранения данных позволяет свести к минимуму этот нежелательный эффект. В страницах некластеризованных индексов теперь используется либо фиксированный идентификатор строки (Fixed RID), если речь идет о таблице, в которой нет кластеризованных индексов (такие таблицы называются «кучами»-heaps), либо ключ Clustering Key, если речь идет о таблице, содержащей кластеризованный индекс. Затраты ресурсов на обработку В-дерева при операциях вставления строки или разбиения страницы теперь значительно сокращены. В результате обработка страницы происходит значительно быстрее, и поэтому в пределах одной таблицы теперь можно создавать большее количество некластеризованных индексов при общем снижении затрат ресурсов сервера, которые требуются для обработки скорректированных данных. Более подробную информацию на эту тему можно получить в разделах Architecture Enhancements и Table and Index Architecture в SQL Server 7.0 Books Online и в результате поиска по ключевому слову page splits.

Служба SQL Trace

Утилита SQL Server Profiler в Microsoft SQL Server 7.0 заменяет службу SQL Trace в SQL Server 6.5. Новая утилита имеет те же функции, что и SQL Trace, но работает значительно эффективнее.

Служба ISQL/W

Утилита SQL Server Query Analyzer в SQL Server 7.0 заменяет службу ISQL/W, использовавшуюся в более ранних версиях SQL Server.

Устройства и сегменты

В отличие от предыдущих версий, в которых применялась архитектура устройств и сегментов, Microsoft SQL Server 7.0 использует архитектуру файлов и файловых групп. Эта архитектура позволяет применять более удобный способ равномерного распределения данных по дискам и RAID-массивам. Более подробную информацию можно найти в разделах Placing Indexes on Filegroups, Placing Tables on Filegroups, Files and Filegroups, и Using Files and Filegroups to Manage Database Growth в SQL Server 7.0 Books Online.

Принципы оптимизации SQL Server

В Microsoft SQL Server 7.0 содержатся как совершенно новые, так и усовершенствованные методы и средства оптимизации работы SQL Server. При настройке и оптимизации параметров сервера полезно придерживаться следующих принципов:

·     Позволять серверу автоматически настраивать как можно больше параметров.

Разработчики Microsoft SQL Server внесли в этот продукт значительные изменения по сравнению с его более ранними версиями и придали этому серверу баз данных характер самонастраивающегося сервера с возможностью автоматической оптимизации параметров.

·     Учитывать ограниченность ресурсов оперативной памяти

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

·     Использование корректных индексов

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

·     Оценивать эффективность работы дисковой подсистемы ввода/вывода.

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

·     Настраивать приложения и операции запросов.

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

·     Использовать возможности служб SQL Server Profiler и Index Tuning Wizard.

Утилиту SQL Server Profiler можно использовать для слежения за нагрузкой на сервер и регистрации ее изменений. Полученные данные можно затем передать мастеру Index Tuning Wizard, который поможет повысить быстродействие системы путем оптимизации индексов. Регулярное использование утилиты SQL Server Profiler и мастера Index Tuning Wizard поможет администратору оптимизировать индексы и создаст оптимальные условия для работы сервера в условиях переменной нагрузки.

·     Использовать утилиту SQL Server Performance Monitor.

В SQL Server 7.0 обновлен набор объектов утилиты Performance Monitor и введены регистры (counters), которые специально разработаны для того, чтобы предоставлять информацию, необходимую для слежения за работой SQL Server и последующего проведения анализа его работы. В документе рассматриваются основные регистры утилиты Performance Monitor, на которые следует обратить внимание.

·     Использовать службу Graphical Showplan и анализатор SQL Server Query Analyzer.

Анализатор Query Analyzer в SQL Server 7.0 содержит службу Graphical Showplan, которая является усовершенствованным средством анализа запросов типа Transact-SQL, нередко служащих источниками различных проблем. Служба Query Analyzer включает в себя также опцию STATISTICS IO – еще одно важное средство оптимизации обработки запросов.

Проверка параметра max async IO при настройке сервера

При первичной настройке Microsoft SQL Server 7.0 следует проверить и при необходимости уточнить значение параметра max async IO.

Значение параметра max async IO, установленное по умолчанию, равняется 32 – этой величины вполне достаточно для дисковых подсистем начального уровня. Если к серверу баз данных, способному обеспечивать более высокие скорости обмена данными с диском, подключена более совершенная система накопителей типа RAID-массива, то величина 32 может оказаться недостаточной, поскольку подсистема RAID способна обслуживать гораздо большее число одновременных запросов на обмен данными с диском. Если операция чтения сервера также требует увеличения скорости обмена с диском, то значение параметра max async IO следует увеличить.

Целесообразно установить такое значение max async IO, чтобы оно позволяло завершить операцию контрольной точки прежде, чем потребуется новая операция контрольной точки (исходя из установленных параметров восстановления), однако данная операция не должна быть завершена слишком быстро, чтобы система не испытала от этого серьезной перегрузки (признаком перегрузки является образование очередей запросов к диску; эта ситуация будет более подробно рассмотрена в последующих разделах данной публикации).

Общим правилом присвоения значения параметра max async IO в SQL Server обслуживающем большие дисковые подсистемы, является умножение на 2 или ни 3 числа физических дисков, на которых возможны одновременные операции ввода/вывода. При этом следует с помощью Performance Monitor проверить, не возникает ли проблем, связанных с образованием очередей обращения к дискам. Если значение параметра max async IO было установлено слишком большим, то нежелательным следствием этого может стать ситуация, когда операция контрольной точки монополизирует полосу пропускания дисковой подсистемы, которая используется другими операциями обмена с сервером, например, операцией чтения данных.

Для того чтобы задать значение параметра max async IO, следует с помощью анализатора запросов SQL Server Query Analyzer выполнить команду sp_configuremax async io’, value, где value равняется числу одновременных запросов к диску на обмен данными, которые SQL Server может послать операционной системе Windows® во время выполнения операции контрольной точки, которые, в свою очередь, посылают запросы к подсистеме физических дисков. Более подробную информацию об этом можно найти в разделе «Оптимизация обращения к диску» данной публикации. Данная опция настраивается динамически, и изменение ее значения не требует остановки и перезапуска SQL Server.

Более подробные сведения о настройке данного параметра можно найти в разделе I/O Architecture в SQL Server 7.0 Books Online и в результате поиска по ключевому слову max async io.

Основные компоненты, потребляющие ресурсы процессора и системы обмена с диском

Работу сервера можно оптимизировать, тщательно учитывая особенности тех компонентов Microsoft SQL Server, которые расходуют ресурсы системы.

Рабочие потоки

Microsoft SQL Server в процессе работы поддерживает пул потоков операционной системы Windows, которые обслуживают последовательности команд сервера, адресованных серверу баз данных. Полное количество таких потоков (называемых рабочими потоками), которые имеются в каждый данный момент для обслуживания всех входящих командных последовательностей, определяется значением параметра max worker threads класса sp_configure. Если число соединений, посылающих на сервер такие последовательности, превышает величину, установленную в качестве параметра max worker threads, то все имеющиеся рабочие потоки используются совместно всеми такими соединениями. Величина параметра, задаваемая по умолчанию, равняется 255 и в большинстве случаев оказывается вполне достаточной.

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

Более подробную информацию о рабочих потоках можно получить в разделах Optimizing Server Performance Using Memory Configuration Options, SQL Server Memory Pool, Transaction Recovery, Write-Ahead Transaction Log, Freeing and Writing Buffer Pages в SQL Server 7.0 Books Online, а также в результате поиска по ключевому слову SQL Server threads.

Служба Lazy Writer

Служба Lazy writer, входящая в состав SQL Server, позволяет освобождать буферы, каждый из которых представляет собою страницу кэша данных размером 8 Кб, в которой не содержится никаких данных. По мере того, как эта служба переписывает каждый 8-килобайтный буфер на диск, она создает новую станицу кэша, на которую могут быть записаны другие данные. Служба создает свободные буферы в периоды низкой интенсивности обмена данными с диском, поэтому ресурсов, используемых при операциях обмена с диском, всегда оказывается достаточно, и операции создания пустых буферов оказывают минимальное воздействие на другие операции SQL Server.

SQL Server 7.0 автоматически настраивает и поддерживает необходимое количество свободных буферов. Проследить за тем, что число свободных буферов остается постоянным, можно с помощью объекта SQL Server: Buffer Manager - Free Buffers. Служба lazy writer заботится о том, чтобы число свободных буферов соответствовало потребности в них со стороны пользователя. Значение объекта SQL Server: Buffer Manager - Free Buffers не должно снижаться до нуля, поскольку это будет означать, что в работе сервера бывали такие моменты, когда пользовательская нагрузка требовала большего количества свободных буферов, чем могла предоставить служба Lazy writer.

Если службе не удается сохранять число свободных буферов постоянным или хотя бы положительным, это может означать, что дисковая подсистема не может обеспечить службу необходимым количеством ресурсов, которые требуются для обмена данными с диском и которые нужны для поддержания постоянным числа свободных буферов (проверить, действительно ли недостаток свободных буферов связан с возможностями дисковой подсистемы, можно сопоставив снижение количества свободных буферов с возникновением очередей при обращении к диску). Одним из возможных решений проблемы, связанной с образованием очередей обращений к диску, является увеличение количества физических дисков (их иногда называют также дисководами) в дисковой подсистеме сервера базы данных и увеличение таким образом ресурсов, требующихся для обмена данными с диском. Значение объекта SQL Server: Buffer Manager – Lazy Writes/sec показывает число 8-килобайтных страниц, записываемых на диск службой Lazy writer.

Утилита Performance Monitor помогает администратору следить за количеством очередей обращений к диску и наблюдать за значениями регистров объектов Disk: Average Disk Queue (логический или физический диск) и Current Disk Queue и следить за тем, чтобы число очередей обращений к диску было менее 2 на каждый физический диск для любой операции SQL Server. Для серверов баз данных, которые используют аппаратные RAID-контроллеры и массивы дисков, значения регистров логических или физических дисков следует делить на количество физических дисков, ассоциированных с соответствующей буквой дисковода, или на число жестких дисков, определяемых программой Windows NT® Disk Administrator. Операционная система Windows и SQL Server не распознают фактического количества жестких дисков, подключенных к RAID-контроллеру. Чтобы правильно интерпретировать величину очередей обращения к диску, сообщаемую утилитой Performance Monitor, администратору следует знать число жестких дисков, ассоциированных с контроллером RAID-массива.

Объем запросов на ресурсы обмена с диском, которые выставляет служба lazy writer, можно регулировать с помощью параметра max async IO, которая управляет количеством запросов о записи на диск (все эти записи, включая запросы от службы lazy writer, операции контрольной точки и рабочих потоков, имеют объем 8 Кб каждая), которые SQL Server может одновременно послать операционной системе Windows и, следовательно, дисковой подсистеме. Если уровень образования очередей обращений к диску неприемлемо высок, следует уменьшить значение параметра max async IO. Если по каким-то причинам установленное значение этого параметра должно оставаться неизменным, то придется увеличивать количество дисков в дисковой подсистеме до тех пор, пока уровень образования очередей обращений к диску не снизится до приемлемых значений.

Более подробную информацию по этой теме можно найти в разделах Freeing and Writing Buffer Pages и Write-Ahead Transaction Log в SQL Server 7.0 Books Online.

Контрольная точка

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

Использование контрольной точки позволяет рабочим потокам и службе lazy writer выполнять главный объем работы по записи использованных страниц, синхронизуя операции записи с контрольными точками. Это предоставляет рабочим потокам и службе lazy writer больше времени для записи. Условия, при которых может быть обеспечено увеличение этого времени, подробно описаны в документации на SQL Server. Дополнительное время ожидания, обеспечиваемое благодаря использованию контрольных точек, позволяет выравнивать во времени нагрузки, возникающие при обмене информацией с диском.

Для повышения эффективности использования контрольных точек в ситуации, когда из кэша требуется переписать большое количество страниц, SQL Server сортирует эти страницы в той очередности, в какой они были размещены на диске. Это позволяет сократить количество движений магнитной головки дисковода при высвобождении кэша и, в принципе, позволяет использовать достоинства последовательного режима ввода/вывода информации. Кроме того, операция контрольной точки в асинхронном режиме посылает дисковой подсистеме запросы на обмен данными объемом 8 Кб. Такой способ позволяет SQL Server быстрее произвести необходимые операции запросов на обмен с диском, поскольку при этом контрольная точка не ожидает от дисковой подсистемы подтверждения о фактическом выполнении записи.

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

Настроить режим записи использованных страниц на диск посредством операции контрольной точки можно с помощью параметра max async IO. Опция max async IO класса sp_configure задает количество страниц буфера размером 8 Кб каждая, которые можно одновременно передать операционной системе Windows и, следовательно, дисковой подсистеме. Если уровень образования очередей обращений к диску оказывается неприемлемо высок, следует уменьшить значение параметра max async IO. Если по каким-то причинам SQL Server должен сохранять установленное значение этого параметра неизменным, то нужно будет увеличивать количество дисков в дисковой подсистеме до тех пор, пока уровень образования очередей обращения к диску не снизится до приемлемых значений.

Если необходимо повысить скорость, с которой SQL Server выполняет операцию контрольной точки, а дисковая подсистема является достаточно мощной, чтобы справиться с возросшей нагрузкой обращений к диску без образования очередей, можно увеличить значение параметра max async IO: это позволит серверу посылать большее количество асинхронных запросов к диску. Изменив величину параметра max async IO, следует внимательно следить за значениями регистров, показывающих образование очередей обращений к диску. Нужно контролировать процесс образования очередей запросов не только на запись, но и чтение. Если значение параметра max async IO установлено слишком большим для данной дисковой подсистемы, то контрольная точка может создать очередь из запросов к диску на запись, что, в свою очередь, может привести к блокировке операций чтения на сервере. Объекты утилиты Performance Monitor, соответствующие физическим и логическим дискам, содержат регистр Average Disk Read Queue Length, который можно использовать для слежения за очередью запросов к диску на чтение. Если образование очереди запросов на чтение вызвано контрольной точкой, то можно либо уменьшить значение параметра max async IO, либо увеличить количество жестких дисков в дисковой подсистеме, чтобы операции контрольной точки и запросы к диску на чтение производились бы одновременно.

Утилита Log Manager

Как и другие реляционные СУБД, SQL Server содержит средства, которые обеспечивают защиту любых процедур записи (занесение, удаление или корректировка данных) от потери информации в случае, если какие-либо обстоятельства приведут к сбою в работе сервера (например, при отключении электричества, возникновении неисправности в работе жесткого диска, пожаре в информационном центре и т. п.). Процедура входа в систему позволяет гарантировать возможность восстановления данных. Прежде чем будет завершена любая неявная (отдельный запрос типа Transact-SQL) или явная (использующая оператор BEGIN TRANSACTION, COMMIT или ROLLBACK) транзакция, диспетчер файла регистрации SQL Server должен получить от дисковой подсистемы сигнал о том, что все изменения в данных, связанные с текущей транзакцией, были успешно записаны в соответствующий файл регистрации. Выполнение этого правила гарантирует, что файл регистрации транзакций можно будет при необходимости прочитать и воспроизвести на SQL Server, когда сервер будет повторно включен после нерегламентного отключения, в ходе которого транзакции, записанные в кэш данных, не были переписаны в файлы данных на диске. За переписывание данных из буферов на диск отвечают операция контрольной точки и служба lazy writer. Процедура чтения содержимого файла регистрации после остановки сервера и выполнения записанных в нем транзакций называется восстановлением.

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

Метод слежения за образованием очередей обращений к диску применительно к файлам регистрации отличается от метода, используемого для файлов баз данных. Для просмотра запросов на запись в файлы регистрации, стоящих в очереди на обработку дисковой подсистемой, можно воспользоваться регистрами SQL Server: Databases database instance : Log Flush Waits Times и SQL Server: Databases database instance : Log Flush Waits/sec утилиты Performance Monitor.

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

Более подробные сведения о контроллерах с кэшем можно почерпнуть в данной публикации в разделе «Аппаратные RAID-контроллеры со встроенным кэшем», а также в разделах Transaction Recovery и Optimizing Transaction Log Performance в SQL Server 7.0 Books Online и в результате поиска по ключевому слову log manager object.

Диспетчер Read Ahead Manager

Диспетчер Read Ahead Manager в Microsoft SQL Server 7.0 является полностью самонастраивающейся и автоматически оптимизирующейся службой. Диспетчер Read Ahead Manager тесно связан с операциями процессора запросов в SQL Server. Служба SQL Server Query Processor распознает и передает на обработку диспетчеру Read Ahead Manager те ситуации, в которых можно повысить быстродействие за счет операций предварительного чтения (read-ahead). Такими ситуациями являются просмотр больших таблиц, просмотр больших последовательностей индексов, а также обращения к В-деревьям кластеризованных и некластеризованных индексов. Операции предварительного чтения выполняются посредством обмена с диском информационными фрагментами объемом 64 Кб каждый, что позволяет увеличить информационную пропускную способность обмена с дисковой подсистемой по сравнению с операциями обмена, выполняемыми посредством 8-килобайтных фрагментов. Если необходимо получить с сервера большую порцию данных, то наилучший способ для этого – операции предварительного чтения.

Диспетчер Read Ahead Manager обязан своими достоинствами структуре хранения данных, положенной в основу упрощенной и более эффективной таблицы Index Allocation Map (IAM). Использование этой таблицы в SQL Server 7.0 позволяет регистрировать расположение экстентов (на каждый экстент выделяется 8 страниц, на которых размещаются либо данные, либо информация об индексах, что в сумме составляет 64 Кб на каждый экстент). Таблица IAM представляет собой страницу размером 8 Кб, на которой в битовой матрице плотно упакована информация о том, в каких экстентах (из набора всех экстентов, обслуживаемых данной таблицей) находится нужная информация. Компактные страницы IAM читаются быстро, и поэтому те из них, которые используются часто, как правило, постоянно находятся в буферном кэше.

Диспетчер Read Ahead Manager может генерировать многократные последовательные запросы на чтение, объединяя информацию от процессора запросов и с помощью таблицы (или таблиц) IAM оперативно находя расположение всех экстентов, информацию с которых требуется прочесть. Последовательные процедуры чтения диска объемом 64 Кб каждая обеспечивают прекрасное быстродействие операциям обмена с диском.

Информация об операциях предварительного чтения отражается в регистре SQL Server: Buffer Manager - Readahead Pages. Более подробные сведения об операции предварительного чтения можно получить, выполнив инструкцию DBCC PERFMON (IOSTATS). Помимо прочего, там приводится информация о страницах, найденных к кэше (RA Pages Found in Cache) и о страницах, помещенных в кэш (RA Pages Placed in Cache). Если страница уже хэширована (то есть приложение сначала прочло ее, а затем операция предварительного чтения исказила информацию), то это – страница, найденная в кэше. Если страница не хэширована (то есть операция предварительного чтения прошла успешно), то это – страница, помещенная в кэш.

Слишком высокая интенсивность операций предварительного чтения может отрицательно сказаться на общем быстродействии системы, поскольку при этом кэш может оказаться заполнен ненужными страницами, что приведет к дополнительному расходованию ресурсов центрального процессора и лишним операциям обмена с диском, которые можно было бы использовать более эффективно. Оптимальной ситуацией является такая ситуация, когда все запросы типа Transact-SQL производятся так, что при этом в буферный кэш помещается минимальное количество страниц. Чтобы этого добиться, приходится аккуратно использовать сочетание индексов и задач. Кластеризованные индексы следует использовать для просмотра больших последовательностей, а некластеризованные индексы лучше подходят для быстрого поиска отдельных строк или небольших групп строк.

Более подробную информацию можно получить в разделах Reading Pages, Table and Index Architecture, Heap Structures и DBCC PERFMON в SQL Server 7.0 Books Online, а также в результате поиска по ключевому слову read-ahead pages.

Оптимизация обращения к диску

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

Паспортные скорости передачи данных и SQL Server

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

Обычный дисковод жесткого диска при работе с операционной системой Windows и SQL Server обеспечивает около 75 случайных и 150 последовательных операций обмена в секунду. Паспортные скорости передачи данных для таких дисков составляют порядка 40 Мб в секунду. Однако гораздо более вероятно, что быстродействие сервера базы данных будет ограничиваться первым из указанных параметров (75/150 операций обмена), нежели собственно скоростью передачи данных. Это можно проиллюстрировать на следующем примере:

 (75 случайных операций обмена в секунду) X (порция данных объемом 8 Кб) = 600 Кб в секунду

Этот расчет показывает, что, производя операции строго случайного чтения и записи на жесткий диск, от него можно ожидать быстродействия при операциях обмена данными не выше 600 Кб (0,6 Мб) в секунду. Эта величина оказывается гораздо ниже, чем паспортная величина в 40 Мб в секунду. Такие службы SQL Server как рабочие потоки, контрольная точка и lazy writer осуществляют операции обмена с диском порциями по 8 Кб.

(150 последовательных операций обмена в секунду) X (порция данных объемом 8 Кб) = 1200 Кб в секунду

Этот расчет показывает, что, производя операции строго последовательного чтения и записи на жесткий диск, при операциях обмена данными от него можно ожидать быстродействия не выше 1200 Кб (1,2 Мб) в секунду.

 (75 случайных операций обмена в секунду) X (порция данных объемом 64 Кб) = 4800 Кб (4,8 Мб) в секунду

Этот расчет иллюстрирует наихудший случай для операций предварительного чтения в предположении, что все операции обмена производятся в случайном порядке. Даже в ситуации строго случайного обмена с диском порциями по 64 Кб скорость обмена с диском (4,8 Мб в секунду) оказывается гораздо выше, чем при постраничном обмене, то есть порциями по 8 Кб (0,6 или 1,2 Мб в секунду).

 (150 последовательных операций обмена в секунду) X (порция данных объемом 64 Кб) = 9600 Кб (9,6 Мб) в секунду

Этот расчет показывает, что при операциях строго последовательного чтения и записи на жесткий диск можно ожидать от диска быстродействия не более 9,6 Мб в секунду. Эта величина оказывается значительно выше, чем в случае случайного обмена. Диспетчер Read Ahead Manager выполняет операции обмена с диском порциями по 64 Кб и стремится организовать их таким образом, чтобы предварительное чтение производилось последовательно (или в порядке записи на диск). Хотя Read Ahead Manager и выполняет операции обращения к диску последовательно, разбиение страниц приводит к тому, что экстенты чаще всего читаются случайным, а не последовательным образом. В этом заключается одна из причин, по которым следует избегать разбиения страниц.

Служба Log manager производит запись информации в файлы регистрации последовательным образом порциями по 32 .

Операции последовательного и непоследовательного обращения к диску

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

·     При поиске информации, которую затребовала операционная система Windows или SQL Server, головки чтения/записи и соответствующие им рычаги должны перемещаться над дисковой поверхностью. Если искомые данные расположены в разрозненных (непоследовательных) областях дисковой поверхности, жесткому диску требуется значительно больше времени для перемещения рычага магнитной головки между всеми необходимыми областями дисковой поверхности. В случае последовательного чтения, когда все необходимые данные располагаются в одной непрерывной области на дисковой поверхности, ситуация оказывается гораздо более благоприятной. Рычаг с магнитной головкой при чтении всех необходимых данных перемещается в этом случае лишь на минимально необходимое расстояние. Разница в затратах времени между непоследовательным и последовательным чтением/записью довольно значительна. При непоследовательном чтении/записи поиск нужной области на диске составляет в среднем около 50 миллисекунд, а при последовательном – около 2-3 миллисекунд. Данные временные оценки являются довольно грубыми и могут сильно изменяться в зависимости от того, насколько далеко друг от друга расположены на диске области непоследовательного чтения/записи, с какой скоростью могут вращаться дисковые поверхности, а также от ряда других физических характеристик жесткого диска. Суть дела заключается в том, что операции последовательного чтения/записи являются более предпочтительными с точки зрения оптимизации работы сервера.

·     Обычный накопитель на жестком диске поддерживает около 75 операций непоследовательного и около 150 операций последовательного обмена с диском в секунду. На процедуру чтения или записи информации объемом 8 Кб затрачивается практически столько же времени, сколько и на операцию чтения/записи порции данных объемом 64 Кб. При операции обмена с диском каждой порцией информации объемом от 8 до 64 Кб практически все время затрачивается на перемещение рычага и магнитной головки. Поэтому в тех случаях, когда необходимо обменяться с диском данными, объем которых превышает 64 Кб, следует стремиться к тому, чтобы по возможности выполнять операции обмена данными порциями объемом 64 Кб, поскольку при этом затраты времени окажутся такими же, как и при передаче данных порциями по 8 Кб, а объем переданной информации окажется в 8 раз больше. Диспетчер Read Ahead Manager обменивается с диском порциями информации размером 64 Кб (они называются экстентами SQL Server - SQL Server extent). Служба Log manager тоже выполняет операции записи информации большими порциями. Грамотно пользуясь службой Read Ahead Manager и отделяя файлы регистрации сервера от других файлов с непоследовательным доступом, можно повысить быстродействие SQL Server.

Более подробные сведения об устройстве и работе жестких дисков можно почерпнуть в публикации компании Compaq “Disk Subsystem Performance and Scalability”. О том, где можно найти этот документ, см. раздел «Дополнительная информация» ниже в данной публикации.

Скорость обмена с диском и быстродействие шины PCI

Максимальная скорость обмена информацией с обычным жестким диском составляет около 40 Мб в секунду, что соответствует 75 операциям непоследовательного (или 150 операциям последовательного) обмена с диском в секунду. Паспортная скорость передачи данных RAID-контроллером составляет около 40 Мб в секунду или примерно 2 000 операций обмена с диском в секунду. Паспортное быстродействие шин PCI составляет около 133 Мб в секунду и более. Реально достижимые скорости обмена с устройством обычно отличаются от паспортных значений. Рассмотрим, как можно использовать эти значения для того, чтобы определить, сколько накопителей жестких дисков можно подключать к каждому RAID-контроллеру и какое количество дисков и RAID-контроллеров можно подключить к шине PCI, не создавая при этом в системе звеньев, работающих с критической нагрузкой.

Выше был приведен расчет, показывающий, что максимальный объем данных, которые SQL Server может записать на диск или считать с него за одну секунду, составляет 9,6 Мб. Предполагая, что пропускная способность RAID-контроллера составляет 40 Мб в секунду, можно определить количество накопителей на жестких дисках, которые можно подключить к одному контроллеру. Разделив для этого 40 на 9,6, получим величину около 4. Это означает, что если SQL Server выполняет только операции последовательного обращения к диску, обмениваясь с ним порциями информации объемом 64 Кб, то к одному контроллеру можно подключить не более четырех накопителей. Аналогичным образом было рассчитано, что при операциях строго непоследовательного обращения к диску и обмене с ним порциями информации по 64 Кб, максимальная скорость передачи данных между накопителем и контроллером составляет 4,8 Мб в секунду. Разделив величину 40 Мб в секунду на величину 4,8 Мб в секунду, получим величину, примерно равную 8. Это означает, что к одному контроллеру можно подключать до восьми накопителей, если обмен с диском производятся порциями по 64 Кб, и при этом используются только операции непоследовательного обмена. Вариант обмена 8-килобайтными порциями данных в случайном режиме обращения к диску позволяет подключать наибольшее количество дисков. Разделив 40 на 0,6, получим величину 66, то есть столько накопителей потребуется, чтобы полностью загрузить работой RAID-контроллер при выполнении только операций случайного чтения и записи порциями информации по 8 Кб каждая. Однако такая ситуация вряд ли может считаться реалистической, поскольку операции предварительного чтения и записи данных в файл регистрации используют порции информации больше 8 Кб; кроме того, маловероятно, чтобы сервер выполнял только операции случайного чтения/записи.

Какое количество дисков можно подключить к RAID-контроллеру, можно определить, исходя не только из скоростей обмена, выраженных числом килобайт в секунду, но и исходя из числа операций в секунду, известного для каждого устройства. Если накопитель на жестком диске может выполнять 75 операций непоследовательного (случайного) обмена в секунду, то примерно 26 накопителей, работая совместно, могут – теоретически - произвести 2 000 операций ввода/вывода информации в секунду, то есть как раз столько, сколько может обработать за такое же время один RAID-контроллер. Аналогично можно рассчитать, что потребуется только 13 накопителей на жестких дисках, чтобы, работая совместно, они могли бы произвести 2 000 операций обращения к RAID-контроллеру и полностью загрузить его, поскольку один накопитель может обеспечить 150 операций последовательного чтения/записи в секунду.

Критические ситуации, связанные с пропускной способностью RAID-контроллера или шины PCI, встречаются на практике далеко не столь часто, как перегрузки, которые связаны с работой жесткого диска. Предположим для примера, что набор из нескольких накопителей, подключенный к одному контроллеру, передает ему 40 Мб информации в секунду. Определим число RAID-контроллеров, которые можно подключить к шине, не опасаясь перегрузить ее. Чтобы получить приближенную оценку этой величины, разделим быстродействие шины на скорость обработки данных контроллером, то есть 133 Мб в секунду надо разделить на 40 Мб в секунду. В результате получаем, что к одной шине PCI можно подключить примерно три RAID-контроллера. Большинство крупных серверов снабжаются более чем одной шиной PCI, поэтому число RAID-контроллеров, которые можно подключить к системе, на практике оказывается еще большим.

Подобные оценки призваны лишь проиллюстрировать соотношение между различными скоростями обмена информацией, которые поддерживают различные компоненты дисковой подсистемы ввода/вывода (накопители, RAID-контроллеры и шина PCI), и их ни в коем случае воспринимать как точные расчеты. Во всех этих расчетах предполагается, что при работе сервера производятся либо только операции последовательного обмена, либо только операции непоследовательного (случайного) обмена информацией, а в реальных условиях работы сервера баз данных такие ситуации маловероятны. На практике всегда имеет место комбинация операций последовательного и непоследовательного обмена порциями данных размером 8 и 64 Кб. Кроме того, имеются и другие факторы, которые также влияют на количество запросов на обмен данными, которые могут быть посланы на набор жестких дисков в единицу времени. Встроенные кэши чтения и записи, имеющиеся во многих моделях RAID-контроллеров, повышают количество операций обмена, которые может выполнить набор жестких дисков. Насколько значительным может оказаться такое увеличение, в общем случае сказать трудно, поскольку невозможно точно определить соотношение между количеством 8-килобайтных и 64-килобайтных порций информации, которые будет генерировать сервер при своей работе.

Технология RAID

При управлении сервером баз данных объемом более нескольких гигабайт важно хотя бы в общих чертах понимать суть технологии RAID (Redundant Array of Inexpensive Disks – массив дешевых дисков с информационной избыточностью) и ее взаимосвязь с быстродействием работы сервера баз данных. Основными достоинствами технологии RAID являются:

·     Быстродействие

Аппаратно реализованные RAID-контроллеры разделяют всю информацию, проходящую через них и адресованную операционной системе Windows или приложениям типа Microsoft SQL Server, на кванты (slices), размер которых обычно составляет от 16 до 128 Кб, и которые равномерно распределяются между всеми дисками, входящими в данный RAID-массив. Распределение информации по разным физическим жестким дискам RAID-массива позволяет более равномерно распределить по ним и операции ввода/вывода данных. Благодаря этому повышается быстродействие накопителя при выполнении операций ввода/вывода, поскольку все диски RAID-массива испытывают одинаковую нагрузку и минимизируется вероятность возникновения критических нагрузок при выполнении операций ввода/вывода.

·     Отказоустойчивость

Технология RAID обеспечивает защиту данных на диске от потери двумя способами: зеркалирования (mirroring) и контроля по четности (parity).

Смысл зеркалирования состоит в том, что одна и та же информация записывается на два набора жестких дисков, связанных друг с другом попарно и являющихся точными копиями друг друга. Если на сервере с зеркалированием происходит сбой в работе жесткого диска, то данные, хранившиеся на этом диске, можно восстановить, заменив диск на новый и воссоздав их на новом диске с помощью зеркального диска. Большинство RAID-контроллеров позволяют производить замену неисправного диска и воссоздание информации на новом диске с его зеркального двойника, не прекращая работу операционной системы Windows и самого SQL Server (такие контроллеры обычно называются контроллерами, поддерживающими «горячую» коммутацию). Зеркалирование является наилучшим с точки зрения быстродействия вариантом технологии RAID, если требуется высокая отказоустойчивость системы. Каждая операция записи, выполняемая SQL Server в условиях зеркалирования данных, требует двух операций ввода/вывода: по одной на каждый из зеркалированных дисков. Зеркалирование также обеспечивает более высокую отказоустойчивость, чем варианты RAID-технологии с контролем по четности. Система с зеркалированием данных может работать при как минимум одном неисправном диске и не приводит к аварийной ситуации в работе сервера даже при условии выхода из строя половины зеркалированных дисков, не принуждая администратора системы прекращать ее функционирование и производить экстренное восстановление информации с резервных копий. Недостатком зеркалирования является высокая стоимость системы. На каждый диск с данными требуется иметь еще один диск для хранения его копии. Такие варианты RAID-массивов как RAID 1 и его разновидность, RAID 0+1, основаны на принципе зеркалирования информации.

Сутью контроля по четности (parity) является вычисление некоторой информации о данных, записанных на диск (которая в случае утери данных поможет восстановить их) и записи этой информации на другие диски того же RAID-массива. В случае выхода диска из строя он удаляется из системы, на его место в RAID-массиве устанавливается новый диск, а данные с неисправного диска восстанавливаются с помощью той информации, которая была вычислена ранее и размещена на других дисках того же массива. Таким образом на новом диске можно регенерировать данные с неисправного диска. Массивы типа RAID 5 и его разновидности используют принципа контроля по четности. Достоинством такого варианта является его низкая стоимость. Чтобы защитить этим методом данные в массиве RAID 5, требуется только один дополнительный диск. Информация, необходимая для восстановления данных, равномерно распределяется по всем дискам, входящим в массив RAID 5. Недостатками метода контроля по четности являются низкое быстродействие и малая отказоустойчивость. Расчет и запись на диск информации, используемой для восстановления данных, требуют дополнительных затрат ресурсов, и в массиве RAID 5 на каждую операцию записи, инициированную операционной системой Windows NT и SQL Server, приходится четыре (а не две, как в случае зеркалирования) операции обращения к диску. Затраты ресурсов при выполнении операций чтения с диска для обоих вариантов одинаковы. Кроме того, массив RAID 5 может продолжать работу только при одном неисправном диске; при дальнейшем увеличении числа неисправностей массив необходимо отключить и провести восстановление данных с резервных носителей.

Общее правило заключается в том, что следует стремиться распределять данные по нескольким дискам, добиваясь при этом высоких показателей быстродействия системы ввода/вывода данных. В случае возникновения информационной перегрузки на каком-либо диске в RAID-массиве утилита Performance Monitor покажет, в каком месте эта перегрузка произошла. Всегда следует быть готовым к тому, чтобы установить дополнительные диски или перераспределить данные по RAID-массивам и/или SCSI-каналам, чтобы равномерно распределить операции обращения к дискам и сохранить быстродействие системы на максимально возможном уровне.

Аппаратные RAID-контроллеры со встроенным кэшем

Многие модели аппаратных RAID-контроллеров имеют тот или иной вариант встроенного кэша для чтения и/или записи данных. Система SQL Server поддерживает использование таких кэшей и позволяет увеличить эффективное значение быстродействия дисковой подсистемы при выполнении ею операций ввода/вывода данных. Принцип работы встроенного кэша контроллера состоит в том, что он собирает небольшие и, скорее всего, непоследовательные запросы на обмен данными, поступающие от главного сервера (то есть от SQL Server), и пытается объединить их в более длинные последовательности запросов таким образом, чтобы эти укрупненные запросы (размером от 32 до 128 Кб), возможно, превратились бы в запросы последовательного обмена; после этого кэш посылает их жесткому диску. Такой метод позволяет добиться более высоких значений скоростей обмена данными с диском при фиксированном количестве обращений, которые можно организовать между жесткими дисками и RAID-контроллером. Кэш RAID-контроллера группирует приходящие запросы, стремясь как можно более эффективно использовать возможности жестких дисков по обработке операций обмена данными.

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

Варианты RAID-массивов

Массивы типа RAID 1 и RAID 0+1 обеспечивают наилучшие условия защиты данных и наивысшее быстродействие среди всех вариантов RAID-массивов, но для них требуется больше дисков. Если стоимость жестких дисков не является ограничивающим фактором, RAID 1 и RAID 0+1 наилучшим образом позволяют решить задачу повышения быстродействия и обеспечения отказоустойчивости.

Массив RAID 5 обеспечивает отказоустойчивость при минимальном уровне затрат, но его быстродействие при операциях записи вдвое меньше, чем у массивов RAID 1 и 0+1, поскольку ему приходится производить дополнительные операции чтения и записи при сохранении вспомогательной (parity) информации на диске. Массив RAID 5 является не столь отказоустойчивым, как массивы RAID 1 и 0+1.

Наибольшим быстродействием при выполнении операций обращения к диску характеризуется массив RAID 0 (распределение данных по дискам без защиты от сбоев), но поскольку у этого массива не имеется защиты от сбоев, массивы этого типа используются, как правило, при разработке серверов баз данных и в других тестовых системах.

Многие контроллеры RAID-массивов поддерживают вариант RAID 0+1 (иногда он также обозначается RAID 1/0 или RAID 10). RAID 0+1 – это комбинированное, или «гибридное», решение. На нижнем уровне рассмотрения его работы эта разновидность RAID-массива зеркалирует все данные, как это происходит в массиве RAID 1. На более высоком уровне можно говорить о том, что контроллер равномерно распределяет все данные по всем имеющимся дискам, как это делается в массиве RAID 0. Таким образом, массив RAID 0+1 сочетает в себе максимальный уровень защиты данных (зеркалирование) и высокое быстродействие (равномерное распределение данных). Операции зеркалирования и распределения данных происходят в прозрачном для Windows NT и системы SQL Server режиме, поскольку они управляются RAID-контроллером. Различия между контроллерами массивов RAID 1 и RAID 0+1 заключаются в различных аппаратных решениях. Для хранения одинакового количества информации массивам RAID 1 и RAID 0+1 требуется одинаковое количество дисков. Более подробные сведения о различных моделях контроллеров типа RAID 0+1 можно узнать у поставщиков контроллеров.

На приведенном ниже рисунке представлены различные варианты RAID-контроллеров. Для хранения четырех дисков данных массивам типа RAID 1 и RAID 0+1 требуется восемь жестких дисков, тогда как модели RAID 5 нужно только пять дисков. Более подробную информацию о конкретном воплощении различных моделей RAID-массивов и их использовании в сервере баз данных следует искать у поставщиков аппаратного обеспечения.

Наиболее распространенные варианты RAID-массивов

 

На рисунке:

Common RAID Levels -- Наиболее распространенные варианты RAID-массивов

Hard Disk Contents – Содержимое жестких дисков

Data – Данные

Data used to maintain fault tolerance information – Данные, используемые для обеспечения защиты от сбоев

Оперативное расширение RAID-массива

Оперативное расширение RAID-массива – функция, которая позволяет при наличии слотов оперативного подключения динамически увеличивать количество жестких дисков в массиве без отключения сервера. Многие поставщики оборудования предлагают аппаратные RAID-контроллеры, поддерживающие такую функцию. Данные автоматически равномерно распределяются по всем имеющимся дискам, в том числе и по вновь подключенному, и при этом не нужно отключать ни SQL Server, ни систему Windows. Для того, чтобы такой возможностью при необходимости можно было воспользоваться, следует оставлять слоты оперативного подключения жестких дисков свободными. Таким образом, если SQL Server регулярно слишком сильно загружает RAID-массив запросами на обмен данными (признаком этого является увеличение очереди запросов к диску, ассоциированному в операционной системе Windows с некоторой буквой латинского алфавита), можно добавить в массив один или несколько новых жестких дисков, воспользовавшись для этого слотами оперативного подключения, не прерывая при этом работу сервера. RAID-контроллер перераспределит имеющиеся данные с учетом новых дисков так, что они будут равномерно распределены по всем дискам данного массива. Ресурсы обмена данными новых дисков (75 операций непоследовательного чтения или 150 операций последовательного чтения в секунду на каждый диск) будут добавлены к общему объему ресурсов обмена RAID-массива.

Утилита Performance Monitor и RAID-массивы

В рамках утилиты Performance Monitor объекты логических и физических дисков характеризуются одинаковым набором параметров. Разница между ними состоит в том, что логические диски ассоциированы с объектами, распознаваемыми операционной системой Windows NT как буквы, присвоенные логическим дискам. Физические же диски в утилите Performance Monitor ассоциированы с объектами, которые Windows NT распознает как физический диск.

Чтобы инициировать регистры утилиты Performance Monitor, следует выполнить команду diskperf.exe из командной строки в окне выполнения команд. Выполнение команды diskperf –y заставит Performance Monitor сообщать о состоянии регистров, отвечающих за логические и физические диски, даже в том случае, если жесткие диски или массивы дисков и RAID-контроллеры включены в систему без использования программного обеспечения RAID-массива, входящего в Windows NT.

Если программное обеспечение RAID-массива, входящее в Windows NT, установлено, то можно использовать команду diskperf –ye. Тогда Performance Monitor будет сообщать корректные значения регистров, отвечающих за физические диски во всех дисковых массивах, включенных в систему. Когда команда diskperf –ye используется в связи с физическими дисками, логические регистры содержат неверную информацию, и их значения не стоит принимать во внимание. Если все же требуется информация из логических регистров, то нужно использовать команду diskperf –y. В этом случае информация из логических регистров будет корректной, но теперь информация в физических регистрах будет неверной, и ее не следует учитывать.

Команда diskperf -y не возымеет действия до тех пор, пока система Windows NT не будет загружена заново.

Аппаратные RAID-контроллеры представляют операционной системе Windows несколько жестких дисков, составляющих единый набор зеркалированных данных (mirrorset) или наборов данных с чередованием (stripeset), в качестве одного физического жесткого диска. Утилита Disk Administrator используется для того, чтобы ассоциировать буквы логических дисков с одним физическим диском, и с ее точки зрения не важно, какое количество жестких дисков на самом деле ассоциировано с единым жестким диском, представляемым системе посредством одного RAID-контроллера.

Администратор сервера должен знать, сколько жестких дисков входит в RAID-массив, тогда он сможет определить, сколько запросов на обмен информацией с каждым их жестких дисков посылают операционная система Windows и SQL Server. Для этого нужно количество запросов на обращение к диску, сообщаемое утилитой Performance Monitor, разделить на число жестких дисков, входящих в данный RAID-массив.

Чтобы определить уровень загрузки операциями обращения к каждому из дисков, входящих в RAID-массив, следует умножить число обращений к диску для записи (оно сообщается утилитой Performance Monitor) либо на два (в случае массивов RAID 1 и 0+1), либо на четыре (в случае массива RAID 5). Такой расчет точно отражает число обращений к физическим дискам. Именно в этом отношении корректно говорить о быстродействии жестких дисков (75 операций непоследовательного чтения/записи или 150 операций последовательного чтения/записи в секунду на каждый диск). Однако точно рассчитать подобным же образом, сколько обращений к жесткому диску происходит в том случае, когда аппаратный RAID-контроллер использует встроенный кэш, становится невозможным, так как при передаче информации от контроллера к диску через кэш число обращений к жесткому диску может измениться.

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

Более подробную информацию о системе SQL Server и технологии в SQL Server 7.0 Books Online: RAID Levels and SQL Server, Comparing Different Implementations of RAID Levels, Monitoring Disk Activity, Performance Monitoring Example: Identifying Bottlenecks, About Hardware-based Solutions, and RAID.

Программный RAID-контроллер в операционной системе Windows NT

Операционная система Windows NT содержит средства, повышающие отказоустойчивость при эксплуатации жесткого диска путем организации зеркалирования (mirrorsets) и чередования данных (stripesets) (с поддержкой отказоустойчивости или без нее). Эти средства операционной системы являются программным эквивалентом аппаратного RAID-контроллера. С помощью утилиты Windows NT Disk Administrator можно задать тип защиты информации от потери – либо путем зеркалирования (RAID 1), либо путем распределения информации с контролем по четности (RAID 5). Утилита Windows NT Disk Administrator позволяет также задавать режим распределения информации без обеспечения отказоустойчивости (RAID 0).

Программный RAID-контроллер потребляет больше ресурсов центрального процессора, поскольку в этом случае операционная система берет на себя функции аппаратного RAID-контроллера. Поэтому при уровне нагрузки на системные процессоры, близком к 100 процентам, быстродействие программного RAID-контроллера при том же количестве накопителей в системе должно быть на несколько процентов ниже, чем в случае аппаратного RAID-контроллера. Однако использование программного RAID-контроллера в Windows NT, в среднем, как правило, позволяет набору жестких дисков более легко решать задачу обслуживания запросов от SQL Server, чем если бы эта же задача решалась каждым из жестких дисков в отдельности. Кроме того, программный RAID-контроллер помогает снизить возможные информационные перегрузки на отдельных элементах, повысить эффективность использования сервером ресурсов центрального процессора и повысить скорость передачи информации. Программный RAID-контроллер, реализованный в системе Windows NT, является более дешевым решением задачи обеспечения отказоустойчивости набора жестких дисков.

Более подробные сведения о настройке программного RAID-контроллера в системе Windows NT можно в Windows NT Server Online Help. Кроме того, дополнительные сведения по этой проблеме содержатся в разделах About Windows NT-based Disk Mirroring and Duplexing и About Windows NT-based Disk Striping and Striping with Parity в SQL Server 7.0 Books Online.

Параллельный доступ к дискам

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

Вместо прежней модели устройств и сегментов, применявшейся в более ранних версиях, в Microsoft SQL Server 7.0 используется модель файлов и файловых групп. Такая модель обеспечивает более удобный способ пропорционального распределения данных по физическим дискам и дисковым RAID-массивам. Более подробные сведения об этом можно найти в разделах Placing Indexes on Filegroups, Placing Tables on Filegroups, Files and Filegroups, и Using Files and Filegroups to Manage Database Growth в SQL Server 7.0 Books Online.

Параллельный доступ к дискам достигается посредством создания единого «дискового пула», который обслуживает все файлы базы данных сервера, за исключением файлов регистрации транзакций. Дисковым пулом может служить RAID-массив, который представлен в операционной системе Windows NT как один физический жесткий диск. Пул большого объема может быть составлен из нескольких RAID-массивов и содержать много файлов (файловых групп) SQL Server. Каждому RAID-массиву можно поставить в соответствие один файл сервера, и несколько таких файлов можно объединить в файловую группу. Затем на основе такой группы можно создавать базу данных, информация в которой будет равномерно распределена по всем жестким дискам и RAID-контроллерам. Принцип организации дискового пула использует способность RAID-контроллеров распределять данные по жестким дискам и обеспечивать параллельный доступ к данным на них при выполнении инструкций, приходящих от сервера баз данных.

Принцип организации дискового пула упрощает задачу оптимизации быстродействия SQL Server, поскольку в этом случае администратор базы данных знает, что в его распоряжении имеется всего одна физическая область, в которой он может создавать объекты базы данных. Дисковый пул можно контролировать на предмет образования очередей запросов к дискам. При необходимости его объем можно увеличивать, подключая новые жесткие диски и тем самым снижая величину очередей запросов. Этот метод очень удобен в наиболее распространенном случае работы с базами данных, когда точно неизвестно, какие именно области баз данных будут использоваться наиболее интенсивно. Поэтому не следует особо выделять какую-то часть ресурсов на разбиении жесткого диска, так как может оказаться, что SQL Server будет обращаться к этой области всего в пяти случаях из ста. Принцип использования единого дискового пула позволяет сделать все имеющиеся ресурсы ввода/вывода доступными для обработки операций, требуемых SQL Server.

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

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

В системе SQL Server можно выделить несколько типов операций, которые должны быть разделены по разным жестким дискам, RAID-контроллерам, PCI-каналам и их комбинациям:

·     Файлы регистрации транзакций

·     База данных tempdb

·     Файлы баз данных

·     Таблицы, обусловливающие создание больших очередей или высокую интенсивность операций записи

·     Некластеризованные индексы, обусловливающие создание больших очередей или высокую интенсивность операций записи

Физическое разделение различных типов операций в SQL Server удобно проводить с помощью аппаратных RAID-контроллеров, оперативно подключаемых RAID-дисководов и оперативного наращивания RAID-массивов. Метод, который обеспечивает наибольшую гибкость, состоит в подключении RAID-контроллеров таким образом, чтобы для каждого типа операций в базе данных был выделен отдельный SCSI-канал. Каждый SCSI-канал в RAID-массиве должен быть связан с отдельным оперативно подключаемым диском. В этом случае выигрыш при наращивании объема RAID-массива (если это позволяет RAID-контроллер) будет наибольшим. Операционная система Windows ассоциирует отдельную букву латинского алфавита с каждым RAID-массивом, и поэтому различные файлы SQL Server можно разделить по разным RAID-массивам.

Разделение типов операций по дискам позволяет установить связь между образованием очередей к диску и определенным SCSI-каналом (и соответствующим ему дисководом), так как утилита Performance Monitor сообщает данные об образовании очередей при тестовых нагрузках на систему или в периоды высоких эксплуатационных нагрузок. Если RAID-контроллер и массив жестких дисков поддерживают режим оперативного наращивания объема массива и в массиве имеются свободные слоты оперативного подключения дисков, то устранить образование очередей в таком RAID-массиве можно, увеличивая количество дисков до тех пор, пока утилита Performance Monitor не сообщит, что образование очередей при обращении к данному RAID-массиву достигло приемлемого уровня (для служебных файлов SQL Server эта величина составляет менее 2). Вся эта операция может быть проведена без остановки системы SQL Server.

База данных tempdb в системе SQL Server создается для ее совместного использования целым рядом объектов и процессов, таких как временные таблицы, сортировка, подзапросы и множества, реализуемые посредством операторов GROUP BY или ORDER BY, запросов с помощью оператора DISTINCT (временные рабочие таблицы создаются для уничтожения продублированных строк), курсоров и ненужных соединений. Следует установить режим параллельного выполнения операций ввода/вывода базы данных tempdb и операций ввода/вывода соответствующих транзакций. Поскольку база данных tempdb представляет собой рабочую область памяти и требует больших ресурсов на обновление (корректировку) хранящейся в ней информации, массив типа RAID 5 для ее размещения будет не столь выгоден, как массив типа RAID 1 или 0+1. База данных tempdb генерируется заново при каждом новом запуске сервера баз данных, поэтому в условиях промышленных SQL Server для ее размещения можно использовать массивы типа RAID 0. Массивы этого типа обеспечивают для базы данных tempdb наилучшее быстродействие при наименьшем количестве жестких дисков. Недостатком использования массивов типа RAID 0 в условиях промышленной базы данных может являться то обстоятельство, что в случае выхода из строя жесткого диска в массиве RAID 0 систему SQL Server придется остановить и запустить заново. Если же база данных tempdb размещена в массиве типа RAID 1 или 0+1, то при сбое в работе диска систему останавливать не обязательно.

Для того, чтобы переместить базу данных tempdb, следует использовать команду ALTER DATABASE, при выполнении которой будет изменено место физического расположения логического имени файла SQL Server, связанного с базой данных tempdb. Например, для того, чтобы переместить базу данных tempdb и связанный с ней файл регистрации в новые места на диске (E:\MsSQL7 и C:\Temp), нужно выполнить следующую последовательность команд:

alter database tempdb modify file (name='tempdev',filename= 'e:\mssql7\tempnew_location.mDF')

alter database tempdb modify file (name='templog',filename= 'c:\temp\tempnew_loglocation.LDF')

Базы данных master, msdb и model при производстве СУБД используются гораздо реже, чем в пользовательских базах данных, поэтому они, как правило, мало влияют на оптимизацию работы системы. База данных master используется только при создании новых регистрационных имен, баз данных и других системных объектов.

Некластеризованные индексы располагаются в структурах типа В-дерева, которые можно отделить от соответствующих им таблиц базы данных при помощи оператора ALTER DATABASE. В приведенном ниже примере первый оператор ALTER DATABASE создает файловую группу. Второй оператор ALTER DATABASE создает файл, связанный с этой группой, но физически размещенный отдельно от нее. На этом этапе в файловой группе можно создать индексы (в приведенном примере это index1). Стандартная процедура sp_helpfile сообщает о наличии файлов и файловых групп, созданных для той или иной базы данных. В выходных данных процедуры sp_help tablename имеется раздел, в котором содержится информация о соотношениях между индексами таблиц и файловыми группами.

alter database testdb add filegroup testgroup1

alter database testdb add file (name = 'testfile',

       filename = 'e:\mssql7\test1.ndf') to filegroup testgroup1

create table test1(col1 char(8))

create index index1 on test1(col1) on testgroup1

sp_helpfile

sp_help test1

Более подробные сведения по данной теме можно получить в разделах Files and Filegroups, Placing Indexes on Filegroups, Monitoring Disk Activity и Physical Database Files and Filegroups в SQL Server 7.0 Books Online, а также в результате поиска по ключевым словам ALTER DATABASE, sp_helpfile, adding and deleting data и transaction log files.

Индексы в SQL Server

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

Каждая страница данных и страница индексов в SQL Server имеет размер 8 Кб. Страницы данных в SQL Server содержат все данные о строках таблицы за исключением данных типа text и image. Для данных этих двух типов на странице данных в SQL Server, содержащей строку, соответствующую тому столбцу, в котором находятся данные типа text и image, располагается указатель на структуру типа В-дерева, состоящую из одной или нескольких страниц размером 8 Кб каждая.

Страницы индексов в SQL Server содержат данные только из тех столбцов, которые составляют определенный индекс, поэтому на таких страницах в сжатом виде содержится информация, относящаяся к гораздо большему количеству строк, чем помещается на странице данных такого же размера. В результате такого уплотнения информации операции ввода/вывода данных со страниц индексов выполняются значительно быстрее. Чем меньший процент от объема табличной строки составляют столбцы, выбранные для формирования индекса, тем выше будет быстродействие системы. Когда SQL Server запрашивает несколько строк из таблицы, в которой запрашиваемые столбцы совпадают со значениями в строках, SQL Server может сэкономить операции ввода/вывода, читая страницы индексов для значений в строках и затем обращаясь только к тем строкам, которые необходимы для выполнения запроса. В этом случае SQL Server не должен выполнять операции ввода/вывода, связанные с просмотром всех строк таблицы и нахождением нужных строк.

Индексы в системе SQL Server организованы на базе структур типа В-дерева, образованных из нескольких страниц индексов размером 8 Кб каждая. Разница между кластеризованными и некластеризованными индексами проявляется в нижней части В-дерева (она носит название концевого или листового уровня (leaf level)). Верхние части этой структуры носят название неконцевых (nonleaf) уровней индекса. Структура типа В-дерева создается для каждого индекса, определенного в таблице SQL Server.

На приведенном ниже рисунке показано различие между кластеризованными и некластеризованными индексами. В случае некластеризованных индексов концевые вершины содержат только те данные, которые включены в индекс. В таких вершинах строки индекса содержат указатели на остальные данные в строке соответствующей страницы данных. В худшем случае каждая операция доступа к строке некластеризованного индекса потребует одну дополнительную операцию непоследовательного обращения к диску для поиска данных из указанной строки. В лучшем случае многие из искомых строк окажутся на одной странице данных и позволят организовать доступ к нескольким строкам на каждой из искомых страниц данных. В случае кластеризованных индексов концевые вершины индекса сами являются строками данных таблицы. Поэтому для поиска табличных данных не требуется производить поиск разбиений. Просмотры непрерывных областей (диапазонов) данных, основанные на использовании кластеризованных индексов, производятся достаточно эффективно, поскольку концевой уровень кластеризованного индекса (а значит, и все строки этой таблицы) рассортирован в непрерывной области на жестком диске по столбцам, которые составляют кластеризованный индекс и будут передаваться серверу квантами по 64 Кб. При этом обмен данными с диском будет носить последовательный характер, если разбиения страниц в В-дереве (на всех его уровнях) кластеризованного индекса встречаются не слишком часто. Пунктирная линия на рисунке означает, что в В-деревьях имеются и другие страницы размером 8 Кб, которые для простоты не изображены.

На рисунке:

Nonclustered Index – некластеризованный индекс

Clustered Index – кластеризованный индекс

rowdata – строковые данные

Leaf level of the Nonclustered and Clustered Index B-tree structures – концевой уровень в В-деревьях некластеризованных и кластеризованных индексов

Кластеризованные индексы

Каждой странице может соответствовать только один кластеризованный индекс, поскольку в то время как организация верхних уровней В-дерева кластеризованного индекса совпадает с организацией структуры В-дерева некластеризованного индекса, нижний уровень дерева кластеризованного индекса состоит из страниц данных размером по 8 Кб каждая, ассоциированных с данной таблицей. Отсюда вытекают следующие выводы:

·     При поиске данных по ключу с помощью кластеризованного индекса и доступе к соответствующей странице данных не требуется поиска разбиений (и вероятного в этом случае непоследовательного изменения положения магнитной головки на диске), поскольку концевой уровень кластеризованного индекса уже ассоциирован со страницей данных.

·     Концевой уровень кластеризованного индекса рассортирован по столбцам, которые и составляют кластеризованный индекс. Поскольку концевой уровень кластеризованного индекса содержит 8-килобайтные страницы табличных данных, то строковые данные всей таблицы физически располагаются на жестком диске в том порядке, который определен для них кластеризованным индексом. Благодаря этому оказывается возможным повысить скорость обмена данными при передаче большого количества строк таблиц размером более 64 Кб с использованием значения кластеризованного индекса, поскольку при этом применяется последовательный ввод/вывод (если в таблице нет разбиений страниц). Более подробную информацию о разбиении страниц можно получить, ознакомившись с разделом «Параметры FILLFACTOR и PAD_INDEX” в данной публикации. Кластеризованный индекс для таблицы следует выбирать исходя из того столбца, который используется для организации просмотра областей и поиска большого количества строк.

Некластеризованные индексы

Некластеризованные индексы наиболее полезны при поиске небольшого количества строк с хорошей селективностью в больших таблицах при помощи ключевого значения. Некластеризованные индексы представляют собою В-деревья, состоящие из страниц индексов размером 8 Кб каждая. Нижний, или концевой, уровень В-дерева страниц индексов содержит все данные из столбцов, составляющих этот индекс. Когда некластеризованный индекс используется для поиска информации в таблице по ключевому значению, происходит просмотр всего В-дерева индекса до тех пор, пока на концевом уровне индекса не будет встречено это ключевое значение. Поиск разбиений производится в том случае, когда требуются те столбцы таблицы, которые не использовались при создании индекса. При поиске разбиения, вероятнее всего, потребуются операции непоследовательного обращения к диску. Если таблица и соответствующее ей В-дерево (или В-деревья) имеют большой размер, то при поиске разбиения может потребоваться чтение данных с другого жесткого диска. Если поиск нескольких разбиений привел к одной и той же 8-килобайтной странице данных, то интенсивность операций обращения к диску будет ниже, так как при этом прочесть и поместить страницу в кэш данных требуется только один раз. При запросе от SQL Server, в котором используется поиск с некластеризованным индексом, для каждой найденной строки потребуется провести один поиск разбиения. Благодаря поиску разбиений некластеризованные индексы лучше подходят для таких запросов, при которых требуется найти всего одну или небольшое число табличных строк. Запросы, при которых требуется найти большое количество строк, лучше обрабатываются с помощью кластеризованных индексов.

Более подробные сведения о некластеризованных индексах можно получить, проведя поиск по ключевому слову nonclustered index в в SQL Server 7.0 Books Online.

Групповые индексы

При использовании некластеризованных индексов может возникнуть особая ситуация, которая получила название группового индекса (covering index). Групповой индекс – это некластеризованный индекс, организованный из всех столбцов, потребовавшихся для обработки запроса от SQL Server с учетом критериев выбора и параметров условного оператора WHERE. Групповые индексы могут способствовать снижению числа операций ввода/вывода и повышать скорость обработки запроса. Следует, однако, находить компромисс между затратами ресурсов на создание нового индекса (и связанных с этим затрат на поддержание структуры В-дерева) и тем выигрышем в быстродействии, который может обеспечить использование группового индекса. Если групповой индекс позволит повысить эффективность обработки запроса или группы запросов, которые часто генерируются сервером, то создание такого индекса может считаться целесообразным.

Рассмотрим следующий пример:

SELECT col1,col3 FROM table1 WHERE col2 = 'value'

       CREATE INDEX indexname1 ON table1(col2,col1,col3)

Создать групповой индекс можно также запустив утилиту SQL Server Enterprise Manager и воспользовавшись в ней мастером Create Index Wizard.

В этом примере индекс indexname1 является групповым индексом, так как он включает в себя все столбцы, указанные в операторе SELECT и условном операторе WHERE. При обработке этого запроса SQL Server не требуется обращаться к страницам данных, ассоциированных с таблицей table1. Сервер может получить все данные, необходимые ему для обработки запроса, используя индекс indexname1. Просмотрев В-дерево, связанное с indexname1, и найдя последовательность индексных ключей, в которых параметр col2 равняется значению value, сервер получает все необходимые данные (col1, col2, col3) из концевого уровня группового индекса. При этом эффективность обмена данными возрастает по дум причинам:

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

·     Групповой индекс организует все необходимые данные в столбце col2, физически записываемом на жесткий диск. Жесткие диски хранят все строки кластеризованного индекса, ассоциированные с условным оператором WHERE (col2 = value) в непрерывной области, что повышает эффективность операций ввода/вывода. С точки зрения операций ввода/вывода, при таком запросе, а также при любом другом запросе, который может быть полностью обработан с помощью столбцов группового индекса, групповой индекс можно рассматривать как кластеризованный индекс.

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

Автоматические групповые индексы и групповые запросы

Утилита Query Processor в Microsoft SQL Server 7.0 обеспечивает пересечение индексов. Пересечение индексов позволяет процессору запросов анализировать несколько индексов с одной страницы, строить на базе этих таблиц хэш-таблицы и использовать их для снижения интенсивности операций ввода/вывода при обработке запроса. Хэш-таблица, образующаяся при пересечении индексов, становится групповым индексом и обеспечивает те же преимущества в повышении эффективности ввода/вывода, что и групповые индексы. Пересечение индексов обеспечивает большую гибкость при работе сервера с пользовательскими базами данных, в которых, как правило, бывает трудно выделить все запросы, которые направлены к базе данных. В таких случаях целесообразно определить некластеризованные индексы, состоящие из одного столбца, для всех столбцов, к которым ожидаются частые запросы и предоставить пересечению индексов обрабатывать те случаи, в которых требуется создание группового индекса.

Более подробную информацию можно получить в разделах Query Tuning Recommendations и Designing an Index в SQL Server 7.0 Books Online.

Рассмотрим следующий пример:

SELECT col3 FROM table1 WHERE col2 = 'value'

       CREATE INDEX indexname1 ON table1(col2)

       CREATE INDEX indexname2 ON table1(col3)

В приведенном выше примере indexname1 и indexname2 являются некластеризованными индексами, состоящими из одного столбца и созданными на базе таблицы SQL Server, называющейся table1. При обработке запроса процессор запросов определяет, что в данной ситуации предпочтительнее использовать пересечение индексов. Оптимизатор запросов автоматически смешивает два индекса, так же автоматически уменьшая при этом количество операций ввода/вывода при обработке запроса. При этом он не требует никаких дополнительных указаний. Запросы, обработанные с помощью групповых индексов (либо посредством явных групповых индексов, либо посредством пересечения индексов), называются групповыми запросами.

Выбор индексов

От того, как были выбраны индексы, существенно зависит объем операций ввода/вывода и, соответственно, быстродействие системы. Некластеризованные индексы удобно использовать для поиска строк, а кластеризованные индексы лучше подходят для организации просмотра больших областей данных. Кроме того, следует стараться использовать индексы небольшого размера (в несколько столбцов и байт). Это особенно важно для кластеризованных индексов, поскольку они используются некластеризованными индексами при поиске строковых данных. Более подробные сведения об этом можно получить, ознакомившись с разделами Using Clustered Indexes, Index Tuning Recommendations и Designing an Index в SQL Server 7.0 Books Online.

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

Кластеризованные индексы гораздо удобнее использовать при запросе к столбцам или последовательностям столбцов, в которых встречается мало неповторяющихся значений, поскольку в кластеризованном индексе данные физически рассортированы, и их можно передавать в результате последовательного ввода/вывода информации порциями по 64 Кб. Кластеризованные индексы хорошо подходят для таких объектов как названия штатов, филиалов компаний, дат продажи, почтовых индексов и т. п. Формирование кластеризованного индекса для тех столбцов, которые содержат неповторяющиеся данные, принесет выигрыш в быстродействии, если типичные запросы будут адресоваться к последовательным диапазонам неповторяющихся значений. Чтобы определить, какой столбец в данной таблице наилучшим образом подходит для создания кластеризованного индекса, следует постараться ответить на вопрос, будет ли производиться много запросов к большому количеству строк с использованием порядка данного столбца. Общего ответа здесь не существует, так как он сильно зависит от характера конкретной пользовательской среды. В одной организации может производиться больше запросов, основанных на диапазонах дат, а в других будут преобладать запросы, основанные на диапазонах названий банковских филиалов.

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

…WHERE <column_name> > some_value

…WHERE <column_name> BETWEEN some_value AND some_value

…WHERE <column_name> < some_value

Выбор кластеризованных индексов

Строго говоря, процедура выбора кластеризованного индекса состоит из двух частей. Сначала нужно определить столбец таблицы, использование которого будет наиболее выгодным за счет последовательного ввода/вывода при просмотре сплошных областей данных. Затем с помощью кластеризованного индекса нужно так организовать физическое размещение табличных данных, чтобы избежать образования «горячих зон» на диске. «Горячие зоны» образуются, если к некоторой группе данных, размещенных в одной области диска, происходит много одновременных запросов на чтение или запись. Это приводит к возникновению перегрузки при обращении к диску, так как на диск приходит больше одновременных запросов, чем он может обработать. Решением данной проблемы может быть либо снижение частоты обращений к диску, либо распределение данных по нескольким дискам при сохранении прежней частоты обращения к данным. Физическое распределение данных по нескольким дискам может сыграть решающую роль при обеспечении высокой эффективности доступа к данным в случае эксплуатации системы, объединяющей сотни и тысячи пользователей.

Два указанных выше соображения часто входят в противоречие друг с другом, и в этом случае наилучшим решением будет поиск компромисса. В ситуациях с высокой пользовательской нагрузкой улучшение условий одновременного доступа (за счет устранения «горячих зон») может оказаться более предпочтительным, чем выигрыш в быстродействии, полученный за счет выбора столбца для некластеризованного индекса.

При поиске строк данных некластеризованные индексы в SQL Server 7.0 будут использовать кластеризованный индекс, если он присутствует в таблице данных. Поскольку все некластеризованные индексы должны содержать в своих В-деревьях кластеризованные ключи, то с точки зрения повышения быстродействия имеет смысл стремиться к тому, чтобы общий объем ключей некластеризованных индексов в байтах был как можно меньше. Для этого следует минимизировать число столбцов кластеризованного индекса и строго контролировать размер в байтах каждого из столбцов, включаемых в кластеризованный индекс. Такой подход позволяет уменьшить размер кластеризованного индекса, а значит, и всех некластеризованных индексов в таблице. Более компактные В-деревья читаются быстрее, что способствует повышению быстродействия системы. Более подробную информацию можно получить в разделе Using Clustered Indexes в SQL Server 7.0 Books Online.

В более ранних версиях SQL Server строки, вставляемые в таблицы без кластеризованных индексов (они назывались кучами), помещались на диске в конце таблицы. Это приводило к возможности образования «горячих зон» в часто используемых таблицах. Алгоритмы управления памятью в SQL Server 7.0 позволяют управлять свободной памятью и поэтому устраняют указанный недостаток. Когда в кучи вставляются новые строки, SQL Server с помощью страниц Page Free Space (PFS) быстро находит свободное место в таблице, на которое и вставляется новая строка. Страницы PFS находят в таблице свободные места, используя области, информация с которых была ранее стерта, и тем самым предотвращает возникновение «горячих зон». Управление свободной памятью влияет на выбор кластеризованных индексов. Поскольку кластеризованные индексы влияют на физическое расположение данных на диске, то не исключена вероятность образования «горячих зон». Это происходит в тех случаях, когда кластеризованный индекс располагает данные в непрерывной области на диске, исходя из столбца, в котором одновременно вставляется много строк, располагающихся в одной области жесткого диска. В столбцах с монотонно возрастающими значениями данных кластеризованный индекс последовательно сортирует строки данных на диске. Если индекс размещается в другом столбце или не включается в таблицу, то последовательное размещение данных на диске переносится на другой диск или не производится вообще.

Механизм возникновения «горячих зон» можно пояснить и при помощи модели выбора данных. Если много пользователей выбирают данные с ключевыми значениями, которые размещены в близких, но не в одной строке, то большинство операций обращения к диску будет выполняться в пределах одной небольшой области жесткого диска. Нагрузку от операций ввода/вывода можно распределить более равномерно, сформировав кластеризованный индекс для этой таблицы на том столбце, который равномерно распределяет ключевые значения по жесткому диску. Если все выбранные данные используют одно и то же ключевое значение, то применение ключевого индекса не приводит к выравниванию нагрузки от обращений к таблице. Проблему можно частично разрешить, если использовать RAID-контроллер (программный или аппаратный), который распределит нагрузку по нескольким жестким дискам. Такой метод можно назвать одновременным доступом к дискам, при котором не возникает конфликтных ситуаций.

Пример выбора кластеризованного индекса

Процедуру выбора кластеризованного индекса можно проиллюстрировать на следующем примере. Допустим, что некоторая таблица содержит столбец с датами выписки счетов, столбец с неповторяющимися номерами счетов и другие данные. Каждый день в таблицу вносится 10 000 новых записей, и пользователи часто обращаются к таблице с запросом на просмотр всех записей за последнюю неделю. Таким образом, к таблице происходит много параллельных запросов. Номер счета плохо подходит для кластеризованного индекса. Номер является индивидуальным признаком записи, а пользователи не слишком часто просматривают счета по номерам, поэтому физически располагать номера счетов на диске в последовательном порядке будет неудобно. Кроме того, значения номеров счетов увеличиваются монотонно (1001, 1002, 1003 и так далее). Если ассоциировать кластеризованный индекс с номером счета, то добавление новых строк в таблицу будет происходить в конце таблицы, за последним ранее внесенным номером, и приведет к образованию «горячей зоны».

Рассмотрим столбец с датами. Этот столбец хорошо подходит для того, чтобы по возможности увеличить последовательные операции ввода/вывода, поскольку пользователи часто выполняют просмотр таблицы по датам за последнюю неделю (то есть просматривают 70 000 строк). Однако, учитывая параллельный доступ к таблице, столбец с датой может плохо подходить для организации кластеризованного индекса. Если кластеризованный индекс ассоциировать с датой выписки счета, то все новые данные будут вставляться в конец таблицы, и на диске может возникнуть «горячая зона», совпадающая с концом таблицы. Вставленные в конец таблицы новые данные будут разделены 10 000 строк, появляющимися в таблице каждый день, поэтому даты выписки счетов с меньшей вероятностью, чем номера счетов, будут приводить к образованию «горячей зоны». Кроме того, аппаратный RAID-контроллер поможет распределить 10 000 строк по нескольким жестким дискам, что тоже будет способствовать минимизации вероятности возникновения «горячей зоны».

Идеального решения в данном примере не существует. Кластеризованный индекс можно помещать на столбец с датой счета, чтобы ускорить обработку запросов, требующих просмотра диапазона дат даже с риском возникновения «горячих зон». В этом случае следует внимательно следить за образованием очередей обращения к дискам, на которых размещена таблица. Можно рекомендовать организацию кластеризованного индекса на столбце с датой счета, поскольку тогда можно будет воспользоваться преимуществами, которые дают просмотры диапазонов дат, да и номера счетов в этом случае не будут физически располагаться на диске в непрерывной области.

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

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

В рассматриваемом примере рекомендуется создать кластеризованный индекс на столбце с номером социального страхования. Дело в том, что эти номера имеют случайный характер и никак не связаны с монотонным возрастанием порядковых номеров сотрудников. Можно говорить о том, что номера социального страхования распределены в таблице равномерно. Если кластеризованный индекс создать на столбце с этими равномерно распределенными данными, то записи о сотрудниках будут равномерно распределены по диску. Это распределение, а также параметры FILLFACTOR и PAD_INDEX, о которых речь пойдет ниже, позволяют создать на страницах данных во всей таблице открытые области, готовые для внесения новых данных. Предполагая, что вновь вносимые записи о сотрудниках будут иметь равномерное распределение номеров социального страхования, можно считать, что таблица будет заполняться равномерно, и таким образом можно будет избежать разбиения страниц. Если в таблице нет столбца с равномерным распределением данных, то целесообразно создать столбец с целочисленными данными и заполнить его равномерно распределенными значениями, а затем создать на таком столбце кластеризованный индекс. Такой «фиктивный», или вспомогательный, столбец с организованным на нем кластеризованным индексом не будет использоваться при запросах, но поможет равномерно распределить операции ввода/вывода по дискам и улучшить условия параллельного доступа к таблице, а значит, позволит повысить общее быстродействие системы. Такой метод хорошо работает в случае больших таблиц с интенсивным потоком обращений. Другое возможное решение в рассматриваемом примере – не создавать кластеризованного индекса. В

этом случае SQL Server 7.0 берет на себя все аспекты управления памятью. Сервер находит свободные места для помещения новых строк, заново использует места, освободившиеся при удалении строк из таблицы и в тех случаях, когда это целесообразно, автоматически реорганизовывает физическое расположение страниц данных на диске (чтобы обеспечить больший процент последовательных обращений к диску). Реорганизация страниц данных происходит в процессе выполнения операций автоматического сжатия файлов в базе данных. Более подробные сведения можно найти в разделах Managing Space Used by Objects и Space Allocation and Reuse в SQL Server 7.0 Books Online.

Параметры FILLFACTOR и PAD_INDEX

Если база данных SQL Server часто пополняется данными, следует позаботиться о создании и поддержании открытых областей на станицах индексов и страницах данных, чтобы предотвратить разбиение страниц. Разбиение страницы происходит, когда на ней уже не могут помещаться новые строки, а строка все же должна быть помещена на страницу по причине заданного порядка размещения данных. Когда это происходит, сервер должен разделить данные на заполненной странице и перенести примерно половину этих данных на новую страницу, чтобы на обоих страницах появилось некоторое количество свободного места. На эту операцию расходуются время и системные ресурсы.

При первоначальном создании индексов сервер помещает их В-деревья на физически смежные страницы. Это позволяет более оптимально организовать выполнение операций ввода/вывода за счет последовательного просмотра страниц индексов. Когда происходит разбиение страницы, и возникает необходимость вставить в В-дерево новые страницы, сервер должен найти место для расположения новых страниц индексов размером по 8 Кб каждая. Новые страницы располагаются уже в другом месте, и физическая непрерывность расположения страниц нарушается. Это приводит к тому, что операции ввода/вывода из последовательных превращаются в непоследовательные, что вдвое снижает быстродействие. Избыточное разбиение страниц можно устранить путем организации индекса заново и восстановления физической непрерывности размещения страниц индексов. Все сказанное справедливо и для концевого уровня кластеризованного индекса, с которым связано расположение страниц данных.

Ненулевые значения регистра SQL Server: Access Methods - Page Splits в утилите Performance Monitor указывают на разбиение страниц. Более подробный анализ разбиения страниц можно проводить с помощью оператора DBCC SHOWCONTIG. Дополнительные сведения об использовании этого оператора можно получить в разделе DBCC SHOWCONTIG в SQL Server 7.0 Books Online.

Оператор DBCC SHOWCONTIG помогает определить, не произошло ли избыточного разбиения страниц в таблице. Главным признаком этого служит параметр Scan Density. Его величина должна быть как можно ближе к 100 процентам. Если эта величина меньше 100 процентов, то на данной странице следует заново организовать кластеризованный индекс, воспользовавшись для ее дефрагментации опцией DROP_EXISTING. Опция DROP_EXISTING оператора CREATE INDEX позволяет заново организовывать индексы и делать это более эффективно, чем путем уничтожения и создания индексов вручную. Более подробные сведения об этом можно получить в разделах CREATE INDEX и Rebuilding an Index в SQL Server 7.0 Books Online.

Опция FILLFACTOR операторов CREATE INDEX и DBCC DBREINDEX позволяет указывать процент свободного места (открытых областей), которое нужно оставить на страницах индексов и страницах данных. Опция PAD_INDEX оператора CREATE INDEX относится к тем же объектам, которые были указаны в параметре FILLFACTOR для неконцевых уровней страниц индексов. Без указания параметра PAD_INDEX параметр FILLFACTOR главным образом влияет на страницы индексов концевого уровня кластеризованного индекса. Не следует пренебрегать опцией PAD_INDEX в FILLFACTOR. Более подробную информацию на эту тему можно найти в результате поиска по ключевым словам page split и PAD_INDEX в SQL Server 7.0 Books Online.

Оптимальное значение, которое следует задавать в параметре FILLFACTOR, зависит от того, насколько много новых данных заносится на 8-килобайтные страницы индексов и данных за данный отрезок времени. Следует иметь в виду, что страницы индексов в SQL Server содержат, как правило, гораздо больше строк, чем страницы данных, поскольку страницы индексов содержат только данные из столбцов, ассоциированных с этим индексом, тогда как страницы данных содержат данные, относящиеся ко всей строке. Следует также учитывать и периодичность технологических перерывов в работе базы данных, во время которых будет производиться создание индексов заново с целью устранения разбиения страниц. Следует стремиться к тому, чтобы создавать индексы заново только в тех случаях, когда заполнено уже большинство страниц индексов и данных, и следить за тем, чтобы кластеризованный индекс был организован на нужном столбце таблицы. Если кластеризованный индекс равномерно распределяет данные, и новые строки вносятся на все страницы данных таблицы, то страницы данных будут заполняться равномерно. Это увеличивает интервал времени, через который происходит разбиение страниц и возникает необходимость создания кластеризованного индекса заново. Величину параметра FILLFACTOR следует выбирать исходя как из предполагаемого количества строк, которые будут заноситься на страницу размером 8 Кб за данный отрезок времени, так и из того, насколько часто в системе будут производиться плановые технологические перерывы для воссоздания индексов.

Решение следует принимать с учетом компромисса между предоставлением большого количества свободного места на страницах и вероятностью возникновения разбиения страницы. Малая величина параметра FILLFACTOR оставляет на страницах индексов и данных большое количество свободного места, что препятствует возникновению разбиения страницы, но одновременно несколько снижает быстродействие, которое можно было бы повысить за счет более плотного размещения данных на странице. SQL Server работает быстрее, если информация на страницах индексов и данных размещена более плотно, так как при этом он может найти больше данных на меньшем количестве страниц и произвести меньшее количество операций ввода/вывода. Слишком большое значение параметра FILLFACTOR может оставить слишком мало свободного места на страницах, что приведет к их быстрому переполнению и разбиению страниц.

Задавая значения в опциях FILLFACTOR и PAD_INDEX, следует учитывать, что операций чтения, как правило, происходит больше, чем операций записи, и это справедливо даже для системы с оперативной обработкой транзакций (OLTP). Параметр FILLFACTOR замедляет все операции чтения, поскольку он распределяет данные по большей области диска (снижение степени компрессии данных). Прежде чем задавать значения опций FILLFACTOR и PAD_INDEX, следует использовать утилиту Performance Monitor и определить соотношение между количеством операций чтения и записи в системе. Использовать эти параметры имеет смысл в тех случаях, когда количество операций записи составляет значительный процент от операций чтения (более 30 процентов).

Если операции записи составляют значительный процент от операций чтения, то лучшим решением в интенсивно работающей системе OLTP будет указать большое значение в параметре FILLFACTOR: оно оставит минимальное количество свободного места на каждой 8-килобайтной странице, но все же будет препятствовать разбиению страниц и позволит серверу доработать до следующего планового перерыва для реорганизации индексов. Этот метод позволяет обеспечить компромисс между оптимальным быстродействием сервера (страницы заполнены данными в максимально возможной степени) и предотвращением разбиения страниц (опция FILLFACTOR не позволяет страницам переполняться). Можно поэкспериментировать с созданием индекса, изменяя значения FILLFACTOR и моделируя запросы к таблице. Таким способом можно подобрать оптимальное значение для параметра FILLFACTOR. После того как оно определено, можно автоматизировать процедуру плановой реорганизации индексов с помощью соответствующей задачи, выполняемой системой SQL Server. Более подробную информацию об этом можно найти в SQL Server 7.0 Books Online по ключевому слову creating a task.

Средства оптимизации SQL Server

Microsoft SQL Server 7.0 содержит несколько инструментов, которые облегчают администратору базы данных задачу оптимизации работы системы.

Пробные данные и тестовая нагрузка

Рассмотрим на примере работу средств оптимизации сервера. Первое действие – это создание таблицы:

CREATE TABLE testtable (nkey1 int IDENTITY, col2 char(300) DEFAULT 'abc', ckey1 char(1))

Затем в таблицу нужно занести 10 000 строк с пробными данными:

DECLARE @counter int

SET @counter = 1

WHILE (@counter <= 2000)

BEGIN

       INSERT testtable (ckey1) VALUES ('a')

       INSERT testtable (ckey1) VALUES ('b')

       INSERT testtable (ckey1) VALUES ('c')

       INSERT testtable (ckey1) VALUES ('d')

       INSERT testtable (ckey1) VALUES ('e')

       SET @counter = @counter + 1

END

Тестовую нагрузку на сервер можно обеспечить, выполнив следующие запросы:

SELECT ckey1,col2 FROM testtable WHERE ckey1 = 'a'

select nkey1,col2 FROM testtable WHERE nkey1 = 5000

Утилита SQL Server Profiler

Утилита SQL Server Profiler записывает подробную информацию о тех операциях, которые проводились на сервере. Эту утилиту можно настроить так, чтобы она следила и регистрировала действия либо одного, либо многих пользователей, посылающих запросы серверу, и выдавала любую комбинацию статистических данных из довольно широкого набора параметров, включая статистику операций ввода/вывода, уровень загрузки центрального процессора, запросы, приводящие к конфликтам, статистику процедур типа Transact-SQL и RPC, просмотр диапазонов индексов и таблиц, выданные предупреждения и появившиеся ошибки, создание и уничтожение объектов в базе данных, включение и выключение соединений, использование встроенных процедур, операции с использованием курсора и т. п. Более подробные сведения о том, какие параметры может регистрировать SQL Server Profiler, можно найти в SQL Server Books Online в результате поиска по ключевому слову SQL Server Profiler.

Использование SQL Server Profiler совместно с мастером Index Tuning Wizard

Утилиту SQL Server Profiler и мастер Index Tuning Wizard администратор базы данных может использовать совместно. Это облегчает создание индексов в таблицах. SQL Server Profiler записывает сведения о потреблении ресурсов при обработке запросов в файл формата .trc. Этот файл можно прочесть с помощью мастера Index Tuning Wizard, который читает информацию, записанную в файле, просматривает содержимое таблиц базы данных и выдает рекомендации для создания индексов. Мастер Index Tuning Wizard может либо автоматически создать необходимые индексы с помощью функции автоматической генерации индексов, либо выдать сценарий Transact-SQL, который администратор может просмотреть и выполнить позже.

Для анализа нагрузки от выполняемых запросов нужно проделать следующие операции:

Þ      Чтобы установить утилиту SQL Server Profiler (в Enterprise Manager)

1.   В меню Tools нажать SQL Server Profiler.

2.   В меню File подвести указатель к New и нажать Trace.

3.   Ввести имя файла трассировки.

4.   Выбрать Capture to file, затем выбрать тот файл формата .trc, в который утилита SQL Server Profiler должна заносить информацию.

Þ      Чтобы приложить тестовую нагрузку (в Enterprise Manager)

1.   В меню Tools нажать на SQL Server Query Analyzer.

2.   Соединиться с сервером и установить текущую базу данных на созданной таблице

3.   В окне запросов SQL Server Query Analyzer ввести следующие запросы:

       SELECT ckey1,col2 FROM testtable WHERE ckey1 = 'a'

       SELECT nkey1,col2 FROM testtable WHERE nkey1 = 5000

1.   В меню Query нажать Execute.

Þ      Чтобы прекратить работу SQL Server Profiler

1.   В меню File нажать Stop Traces.

2.   В диалоговом окне Stop Selected Traces выбрать те трассировки, которые нужно прекратить.

Þ      Чтобы загрузить файл формата .trc в мастер Index Tuning Wizard (в SQL Server Profiler)

1.   В меню Tools нажать Index Tuning Wizard и затем нажать Next.

2.   Выбрать базу данных для анализа и нажать Next.

3.   Убедиться, что строка I have a saved workload file, и затем нажать Next.

4.   Отметить строку My workload file, найти файл формата .trc, созданный утилитой SQL Server Profiler, нажать OK, а затем нажать Next.

5.   В окне Select Tables to Tune выбрать нужные таблицы и нажать Next.

6.   В окне Index Recommendations выбрать индексы, которые нужно создать, и нажать Next.

7.   Выбрать желаемую опцию и нажать Next.

8.   Нажать Finish.

Ниже приводится сценарий Transact-SQL, созданный мастером Index Tuning Wizard для пробных данных и тестовой нагрузки из рассматриваемого примера:

/* Created by: Index Tuning Wizard       */

/* Date: 9/7/98                   */

/* Time: 6:42:00 PM               */

/* Server: HENRYLNT2                     */

/* Database : test                */

/* Workload file : E:\Mssql7\Binn\Profiler_load.sql */

 

USE [test]

BEGIN TRANSACTION

CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1])

if (@@error <> 0) rollback transaction

CREATE NONCLUSTERED INDEX [testtable1] ON [dbo].[testtable] ([nkey1])

if (@@error <> 0) rollback transaction

COMMIT TRANSACTION

Предполагается, что администратор создаст рекомендуемые индексы для пробных данных и таблицы. В рассматриваемом примере имеется только пять различных значений ckey1, и каждое из них повторяется в 2 000 строк. Поскольку один из тестовых запросов (SELECT ckey1, col2 FROM testtable WHERE ckey1 = a) задает поиск в таблице по одному из значений, указанных в ckey1, целесообразно создать кластеризованный индекс на столбце ckey1. Второй запрос (SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000) задает поиск одной строки по значению, указанному в столбце nkey1. Значение nkey1 не повторяется среди всех 10 000 столбцов, поэтому на этом столбце уместно организовать некластеризованный индекс.

Утилита SQL Server Profiler и мастер Index Tuning Wizard особенно полезны в тех системах баз данных, где имеется много таблиц и к ним происходит много запросов. Выполнение утилиты SQL Server Profiler и запись данных в файл формата .trc целесообразно производить, когда сервер баз данных выполняет характерные запросы. После этого можно загрузить этот файл в мастер Index Tuning Wizard и с его помощью определить, какие индексы имеет смысл создать. Следуя подсказкам в Index Tuning Wizard, можно автоматически создать и запрограммировать выполнение задач по созданию индексов, которые система будет выполнять в периоды пониженной нагрузки. Если утилиту SQL Server Profiler и мастер Index Tuning Wizard запускать регулярно (например, раз в неделю), то можно контролировать изменение характера запросов, выполняемых сервером базы данных, и если эти изменения окажутся значительными, указанные средства помогут создать новые индексы. Регулярное использование SQL Server Profiler и Index Tuning Wizard поможет администратору постоянно поддерживать работу сервера в оптимальном режиме даже при изменении уровня нагрузки и размера базы данных.

Более подробные сведения можно найти в разделах Index Tuning Wizard и Index Tuning Recommendations в SQL Server 7.0 Books Online.

Анализ информации в SQL Server Profiler

Утилита SQL Server Profiler дает возможность сохранить статистическую информацию в специальной таблице. Когда создание такой таблицы будет завершено, ее можно будет проанализировать и определить, не расходуют ли какие-то определенные запросы слишком много ресурсов системы.

Þ      Чтобы занести информацию из SQL Server Profiler в специальную таблицу (в Enterprise Manager)

1.   В меню Tools нажать SQL Server Profiler.

2.   В меню File подвести указатель к New и нажать Trace.

3.   Ввести имя трассировки и выбрать строку Capture to Table.

4.   В диалоговом окне Capture to Table ввести имя таблицы, в которой должна быть сохранена информация, получаемая от SQL Server Profiler, и нажать OK.

5.   В меню File нажать Stop Traces.

6.   В диалоговом окне Stop Traces выбрать те трассировки, которые нужно прекратить.

Более подробные сведения можно получить, ознакомившись с разделами Viewing and Analyzing Traces, Troubleshooting SQL Server Profiler, Tips for Using SQL Server, Common SQL Server Profiler Scenarios, Starting SQL Server Profiler и Monitoring with SQL Server Profiler в SQL Server 7.0 Books Online.

Анализатор SQL Server Query Analyzer

После того, как информация записана в специальную таблицу сервера, можно прибегнуть к помощи анализатора запросов SQL Server Query Analyzer и определить, какие именно запросы потребовали наибольших ресурсов для их выполнения и, соответственно, требуют наибольшего внимания со стороны администратора базы данных. Например, при анализе статистической информации, записанной в таблицу с помощью SQL Server Profiler, очень часто может использоваться следующий запрос:

select top 3 TextData,CPU,Reads,Writes,Duration from profiler_out_table order by cpu desc

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

Статистика ввода/вывода

Анализатор запросов SQL Server Query Analyzer содержит опцию Show stats I/O, которая расположена на вкладке General диалогового окна Connections Options. Для того чтобы определить, какая часть ресурсов ввода/вывода затрачивается на только что выполненный запрос, следует отметить этот пункт в SQL Server Query Analyzer.

Например, если отметить эту опцию, то помимо обычного набора сведений, в результате запроса SELECT ckey1, col2 FROM testtable WHERE ckey1 = a будет выдана следующая информация:

Table 'testtable'. Scan count 1, logical reads 400, physical reads 382, read-ahead reads 400.

Аналогичным образом, если отметить эту опцию, то помимо обычного набора сведений, в результате запроса SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000 будет выдана следующая информация:

Table 'testtable'. Scan count 1, logical reads 400, physical reads 282, read-ahead reads 400.

Использование параметра STATISTICS I/O удобно при настройке процессов с целью минимизации очередей обращения к диску. Для иллюстрации этого создадим два индекса в нашей пробной таблице, как это рекомендует мастер Index Tuning Wizard, и затем повторно выполним запросы.

В запросе SELECT ckey1, col2 FROM testtable WHERE ckey1 = a кластеризованный индекс привел к повышению быстродействия (см. ниже). При этом запросе должно быть просмотрено 20 процентов таблицы, поэтому увеличение быстродействия будет достаточно заметным.

Table 'testtable'. Scan count 1, logical reads 91, physical reads 5, read-ahead reads 32.

В запросе SELECT nkey1, col2 FROM testtable WHERE nkey1 = 5000 создание некластеризованного индекса очень сильно повлияло на скорость обработки запроса. Поскольку при этом запросе ведется поиск только одной строки из 10 000 строк, увеличение быстродействия с помощью некластеризованного индекса также является довольно заметным.

Table 'testtable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.

Служба ShowPlan

Служба ShowPlan предназначена для отображения подробной информации о процессе работы оптимизатора запросов. SQL Server 7.0 содержит два варианта этой службы – текстовый и графический. Графическую информацию, которую предоставляет служба ShowPlan, воспроизводится в окне Results анализатора SQL Server Query Analyzer при выполнении запроса Transact-SQL нажатием клавиш Ctrl+L. При этом пиктограммы обозначают операции, которые анализатор будет выполнять при обработке запроса. Стрелки указывают направление, в котором перемещаются данные при выполнении запроса. Подводя указатель мыши к той или иной пиктограмме, можно получить более подробные сведения о данной операции. Та же самая информация может быть получена и в текстовом варианте ShowPlan при выборе параметра SHOWPLAN_ALL ON. Чтобы уменьшить количество подробностей, приводимых о работе оптимизатора запросов, следует выбрать опцию SET SHOWPLAN_TEXT ON.

Более подробные сведения об этой службе можно получить в разделе Understanding Nested Loops Joins в SQL Server 7.0 Books Online и в результате поиска по ключевым словам worktables и showplan.

Примеры информации, предоставляемой службой ShowPlan

Воспользуемся запросами из предыдущего примера и опцией showplan_text on в анализаторе SQL Server Query Analyzer:

При выполнении запроса

SELECT ckey1,col2 FROM testtable WHERE ckey1 = 'a'

служба ShowPlan выдаст следующую информацию:

|--Clustered Index Seek(OBJECT:([test].[dbo].[testtable].[testtable2]), SEEK:([testtable].[ckey1]='a') ORDERED)

При этом запросе используется кластеризованный индекс на столбце ckey1, как это и показано в Clustered Index Seek. Графический эквивалент этой информации будет выглядеть так:

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

|--Table Scan(OBJECT:([test].[dbo].[testtable]), WHERE:([testtable].[ckey1]='a'))

Та же самая информация, представленная графически, имеет следующий вид:

В таблицах небольшого размера просмотр всей таблицы оказывается наиболее эффективным способом поиска информации. Однако в больших таблицах просмотры таблиц, о которых сообщает ShowPlan, служат признаком того, что в таблице следует лучше организовать индексы или провести корректировку их параметров (с помощью оператора UPDATE STATISTICS). В SQL Server 7.0 обеспечивается автоматическая корректировка индексов. Лучше всего предоставить серверу автоматически корректировать параметры индексов, поскольку это помогает обеспечить такие условия, при которых статистика выполнения запросов всегда будет оставаться хорошей.

При выполнении запроса

SELECT nkey1,col2 FROM testtable WHERE nkey1 = 5000

служба showplan выдаст следующую информацию:

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([test].[dbo].[testtable]))

 |--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=5000) ORDERED)

Ее графический эквивалент будет иметь следующий вид:

При выполнении этого запроса используется некластеризованный индекс на столбце nkey1, о чем свидетельствует операция Index Seek на столбце nkey1. Операция Bookmark Lookup указывает, что при поиске запрошенных данных сервер должен провести поиск разбиений от страницы индексов до страницы данных рассматриваемой таблицы. Поиск разбиений потребовался потому, что запрос относился к столбцу col2, который не входил в некластеризованный индекс.

При выполнении запроса

SELECT nkey1 FROM testtable WHERE nkey1 = 5000

служба showplan выдаст следующие строки:

|--Index Seek(OBJECT:([test].[dbo].[testtable].[testtable1]), SEEK:([testtable].[nkey1]=[@1]) ORDERED)

Графический эквивалент этой информации будет выглядеть следующим образом:

Этот запрос использует некластеризованный индекс на столбце nkey1 в качестве группового индекса. Операции поиска разбиений в этом случае не потребовалось, поскольку вся информация, необходимая для выполнения запроса (условные операторы SELECT и WHERE), содержалась в некластеризованном индексе. Страницы некластеризованных индексов не требуют поиска разбиений на страницах данных. Количество операций ввода/вывода меньше по сравнению с ситуацией, когда требуется разбиение страниц.

Утилита Performance Monitor

Утилита Performance Monitor предоставляет информацию о тех операциях, которые производятся на сервере баз данных операционной системой Windows и SQL Server.

В графическом режиме представления данных следует обращать внимание на значения Max и Min. Значению среднего не следует придавать особого значения, поскольку на него могут повлиять крайние величины. Анализ формы графика и величин Min и Max поможет составить правильное представление об эффективности работы системы. Для выделения регистров следует использовать клавишу BACKSPACE.

Утилиту Performance Monitor можно использовать для фиксирования значений всех регистров и объектов Windows NT и SQL Server, за которыми проводится наблюдение. Запись этой информации осуществляется в файл регистрации, и ее (в виде соответствующих диаграмм и графиков) можно просматривать в интерактивном режиме. Величина задаваемого интервала выборки (sampling interval) определяет, насколько быстро увеличивается объем файла регистрации. Файлы регистрации могут расти довольно быстро: например, за один час объем файла может увеличиться на 100 Мб, если включены все регистры и интервал выборки составляет 15 секунд. Для размещения таких файлов тестируемый сервер должен иметь около двух гигабайт свободного дискового пространства. Однако если на диске важно сэкономить место, можно установить большой интервал выборки (30 или 60 секунд), чтобы утилита Performance Monitor производила выборку не столь часто, и значения регистров записывались реже – тогда размер файла регистрации окажется меньше.

Утилита Performance Monitor также потребляет некоторое количество ресурсов центрального процессора и требует выполнения операций обращения к диску. Если в системе недостаточно свободных ресурсов, то можно попробовать запустить эту утилиту с другого компьютера и следить за работой сервера по сети. Это возможно только при работе Performance Monitor в текстовом режиме. В режиме регистрации данных запись в файл регистрации осуществляется более эффективно, если утилита выполняется локально (то есть работает на том же компьютере, за работой которого она следит). Если все же необходимо включить режим регистрации с использованием сети, то следует задать только самые необходимые параметры слежения.

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

Þ      Чтобы начать регистрацию параметров (в Performance Monitor)

1.   В меню View нажать Log.

2.   Нажать кнопку (+).

3.   В диалоговом окне Add to Log выбрать регистры, значения которых должны быть записаны в файл регистрации.

4.   Нажать Add, затем нажать Done.

5.   В меню Options нажать Log.

6.   В строке File Name ввести имя файла, в который будет заноситься информация.

7.   Нажать Start Log.

Þ      Чтобы прекратить регистрацию параметров (в Performance Monitor)

1.   В меню Options нажать Log.

2.   Нажать Stop Log.

Þ      Чтобы занести записанную информацию в Performance Monitor (в Performance Monitor)

1.   В меню View нажать Log.

2.   В меню Options нажать Data From, затем выбрать Log File.

3.   Нажать кнопку просмотра (…), затем дважды нажать на имя файла.

4.   В диалоговом окне Data From нажать OK.

5.   В меню View нажать Chart, затем нажать кнопку (+).

6.   Нажать кнопку, на которой изображен знак +.

7.   В диалоговом окне указать названия наиболее интересных регистров для отображения информации о них в графическом виде. Для этого надо отметить сочетания объектов и регистров и нажать Add.

Þ      Чтобы соотнести события в файле регистрации со временем выполнения запросов

1.   Выполнить действия, описанные в разделе «Чтобы занести записанную информацию в Performance Monitor».

2.   В меню Edit нажать Time Window.

3.   В диалоговом окне Input Log File Timeframe можно установить время начала и окончания визуализации данных, нажав и удерживая кнопку мыши на соответствующих ползунковых регуляторах.

4.   Чтобы в графическом окне были показаны только те данные, которые относятся к заданному диапазону времени, нажать OK.

Основные регистры Performance Monitor

Утилита Performance Monitor содержит несколько регистров, характеризующих работу диска. Чтобы включить эти регистры, следует выполнить команду diskperf –y в командном окне Windows NT и перезапустить операционную систему. Команда diskperf -y расходует некоторое количество ресурсов сервера базы данных, но тем не менее выполнять ее на реально работающих серверах оказывается целесообразным, поскольку она позволяет сразу же обнаруживать возникновение очередей обращений к диску и использовать все имеющиеся регистры Performance Monitor для подробной диагностики сложившейся ситуации. Если требуются регистры обращений к диску, то команду diskperf -y следует выполнить, а систему Windows NT перезапустить до того, как регистры обращений к диску начнут передавать данные утилите Performance Monitor.

(Physical or Logical) Disk Queue > 2

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

Если в системе используется RAID-массивы, то администратор базы данных должен знать, сколько физических жестких дисков входит в состав каждого из массивов, поскольку система Windows NT распознает каждый RAID-массив как единый физический диск. Это необходимо для правильного расчета величины очереди обращений к физическому жесткому диску. Следует проконсультироваться у специалистов, отвечающих за аппаратную часть системы и узнать у них, как в системе распределены физические диски и SCSI-каналы. Это поможет администратору получить представление о том, как на дисках размещаются данные и как используются SCSI-каналы.

Утилита Performance Monitor предлагает несколько вариантов слежения за очередями обращений к диску. Регистры логических дисков ассоциированы с буквами латинского алфавита (обозначающими логические диски в системе), присвоенными дискам утилитой Disk Administrator, тогда как регистры физических жестких дисков ассоциированы с устройством, которое Disk Administrator распознает как единый физический диск. На самом деле это может быть либо действительно единый физический жесткий диск, либо RAID-массив, состоящий из нескольких жестких дисков. Регистр Current Disk Queue предоставляет мгновенные значения величины очередей, а регистр Average Disk Queue усредняет эти значения по интервалу выборки, заданному в Performance Monitor. Следует внимательно следить за тем, чтобы значения регистров Logical Disk: Average Disk Queue, Physical Disk: Average Disk Queue, Logical Disk: Current Disk Queue и Physical Disk: Average Disk Queue не превышали 2.

Приведенные выше рекомендованные значения указаны для физических жестких дисков. Если измерение величины очередей производится в RAID-массиве, полученные значения регистров следует разделить на количество физических дисков в массиве.

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

Более подробные сведения на эту тему можно получить в разделе Monitoring Disk Activity в SQL Server 7.0 Books Online.

System: Processor Queue Length > 2 (на каждый процессор)

Если значение этого регистра превышает 2, то это означает, что процессоры сервера получают больше запросов, чем они могут выполнить, и поэтому система Windows вынуждена поставить запросы в очередь.

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

Общим правилом для определения оптимальной величины очереди на процессорах может быть умножение на 2 количества процессоров в системе.

Если величина очередей на процессорах заметно превышает 2 в расчете на каждый процессор, то следует выяснить причину этого явления. Слишком большие очереди на процессоре снижают время выполнения запроса. Устранение аппаратного и программного разбиения памяти на страницы может высвободить ресурсы процессора. Среди других методов, которые также позволяют снизить размер очередей на процессоре, можно назвать такие как оптимизация запросов, выбор более подходящих индексов для снижения числа операций ввода/вывода (а, следовательно, и потребления ресурсов процессора) и увеличение количества процессоров в системе.

Аппаратное обращение к страничной памяти: Memory: Pages/Sec > 0 и Memory: Page Reads/Sec > 5

Значения регистра Memory: Pages/Sec больше 0 и регистра Memory: Page Reads/Sec больше 5 служат указанием на то, что система Windows обращается к диску, чтобы отработать ссылку на ячейку памяти (аппаратное обращение к страницам памяти), а при этом затрачиваются ресурсы процессора и увеличивается число обращений к диску. Регистр Memory: Pages/Sec служит хорошим показателем количества обращений к памяти, производимых системой Windows, а также того, насколько правильно настроена конфигурация сервера. Кроме того, Performance Monitor предоставляет и сведения о числе операций чтения (в секунду) страничного файла, к которому система Windows вынуждена была обратиться для того, чтобы отработать обращения к памяти. Эти сведения даются в регистре Memory: Page Reads/Sec. Значения этого регистра, превышающие 5, означают плохую настройку системы.

Автоматическая оптимизация памяти сервера позволяет динамически корректировать настройку памяти и избегать обращения к страницам памяти. Небольшое число обращений в секунду является признаком нормальной работы, но при слишком большом значении этого регистра следует предпринимать меры по настройке работы сервера.

Если сервер автоматически оптимизирует использование памяти, то для снижения величины в регистре Memory: Pages/Sec можно увеличить оперативную память сервера и/или прекратить выполнение других приложений на сервере баз данных.

Если использование памяти сервера баз данных настраивается вручную, то может потребоваться уменьшение количества памяти, отводимой SQL Server, прекращение выполнения других приложений или увеличение объема оперативной памяти сервера.

Сохранение значения регистра Memory: Pages/Sec равным или близким к 0 указывает на хорошее быстродействие сервера, поскольку такие значения говорят о том, что система Windows и все ее приложения, включая SQL Server, не обращаются к страничному файлу при отработке запросов к памяти, то есть объема оперативной памяти сервера оказывается достаточно. Значение регистра Pages/Sec, слегка превышающее 0, не является критическим, но при этом довольно заметно снижается быстродействие (операции обращения к диску), когда данные извлекаются не из оперативной памяти, а из страничного файла.

Следует понимать разницу между регистром Memory: Pages Input/sec и регистром Memory: Pages Reads/sec. Регистр Memory: Pages Input/sec указывает фактическое количество страниц памяти размером по 4 Кб, которые система Windows читает с диска, чтобы отработать обращение к памяти. Регистр Memory: Pages Reads/sec указывает, сколько запросов на обращение к диску при этом происходит за одну секунду, что позволяет осветить ситуацию под несколько иным углом зрения. При одной операции чтения страничного файла может происходить чтение сразу нескольких 4-килобайтных страниц. Эффективность выполнения операций обращения к диску увеличивается с увеличением размера пакетов обмена данными (64 Кб и более), поэтому имеет смысл следить за значениями обоих этих регистров. Следует также помнить и о том, что для жесткого диска одна операция чтения или записи 4 Кб информации занимает почти столько же времени, сколько требуется на чтение/запись 64 Кб. Рассмотрим следующую ситуацию: 200 операций чтения, в каждой из которых происходит чтение восьми 4-килобайтных страниц, могут быть выполнены быстрее, чем 300 операций чтения, в каждой из которых происходит чтение одной 4-килобайтной страницы. Возможны ситуации, в которых 1 600 операций чтения страниц памяти могут быть выполнены быстрее, чем 300 аналогичных операций чтения. При анализе статистики операций обращения к диску важно учитывать не только число байт, прочитанных или записанных на диск в секунду, но и число операций чтения/записи, производящихся за одну секунду. Более подробную информацию можно почерпнуть ниже в разделах «Регистры обращений к диску» и «Пример оптимизации обращений к дискам ЕМС» данной публикации.

Полезно сравнивать значение регистра Memory: Page Input/sec со значением регистра Logical Disk: Disk Reads/sec для всех дисков, ассоциированных со страничным файлом системы Windows NT, а значение регистра Memory: Page Output/sec со значением регистра Logical Disk: Disk Writes/sec также для всех дисков, ассоциированных со страничным файлом Windows NT. Эти данные позволяют определить, какая часть операций ввода/вывода относится непосредственно к обмену со страницами памяти, а какая – к работе других приложений, например, к работе самого SQL Server. Другой способ выделения той части операций обмена с диском, которые обусловлены обращениями к страничной памяти, заключается в размещении страничного файла на отдельном (от служебных файлов сервера) наборе дисков. Отделение страничного файла от служебных файлов сервера улучшает и быстродействие операций обмена с диском, так как в этом случае операции обмена, обусловленные обращениями к страничной памяти, будут выполняться параллельно с аналогичными операциями, обусловленными работой сервера.

Программное обращение к страничной памяти: Memory: Pages Faults/Sec > 0

Если значение регистра Memory: Pages Faults/Sec составляет величину больше 2, это говорит о том, что система Windows NT обращается к страничной памяти, но в регистре учитываются как аппаратные, так и программные обращения. Программное обращение означает, что на сервере базы данных имеется приложение (приложения), которое запрашивает страницы памяти, находящиеся в оперативной памяти, но за пределами рабочего множества данного приложения. Регистр Memory: Pages Faults/Sec полезен при определении числа программных обращений к страницам памяти. Отдельного регистра, показывающего число программных обращений, не предусмотрено. Эту величину можно определить следующим образом:

Memory: Pages Faults/sec - Memory: Pages Input/sec = Soft Page Faults per Second

Чтобы определить, не происходит ли при работе сервера излишне частого обращения к станичной памяти, следует наблюдать за значениями регистра Process: Page Faults/sec и следить за тем, не приближается ли число обращений к памяти в секунду для приложения SQLservr.exe к числу страниц в секунду.

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

До тех пор, пока сервер не обратится по одному разу к каждой странице кэша, каждое его обращение к странице будет приводить к операции программного обращения к памяти. Это не является признаком плохой работы сервера и не должно вызывать беспокойства. Более подробные сведения об этом содержатся в разделе «Контроль за работой процессоров» данной публикации.

Более подробную информацию об оптимизации памяти можно найти в разделе Monitoring Memory Usage в SQL Server Books Online.

Контроль за работой процессоров

Для достижения максимального быстродействия следует стремиться к тому, чтобы процессоры сервера работали с достаточной нагрузкой, но все же не входили бы в критический режим работы из-за перегрузки. Наиболее трудная задача при оптимизации быстродействия заключается в том, чтобы правильно определить причину критической ситуации. Если она не связана с работой центрального процессора, то наиболее вероятное место возникновения такой ситуации – дисковая подсистема, и тогда часть имеющихся ресурсов процессора оказывается невостребованной. В большинстве из современных систем ресурсы центрального процессора являются одним из тех параметров, увеличить которые в рамках данной конфигурации довольно сложно (обычно это либо 4, либо 8 процессоров в одной системе), поэтому признаком хорошей работы является использование этих ресурсов на уровне 95 процентов. Кроме того, следует обращать внимание и на время выполнения транзакций и следить за тем, чтобы оно оставалось в разумных пределах. Если это не так, то степень использования ресурсов процессора более 95 процентов может означать, что нагрузка на процессор слишком велика, и следует либо увеличить число процессоров, либо снизить или перераспределить нагрузку.

Регистр Processor: Processor Time % указывает степень использования ресурсов всех процессоров в системе; для каждого процессора эта величина должна постоянно быть менее 95 процентов. Регистр System:Processor Queue отражает величину очереди обращений ко всем процессорам в системе, работающей под управлением Windows NT. Если значение регистра System: Processor Queue превышает 2 на каждый процессор, то это означает, что процессоры работают в критическом режиме, и следует либо увеличить число процессоров в системе, либо снизить рабочую нагрузку. Снижение рабочей нагрузки можно обеспечить за счет оптимизации запросов или за счет более эффективного создания индексов, что должно привести к снижению числа операций ввода/вывода и, как следствие, к снижению потребности в ресурсах процессора.

Еще одним регистром, показания которого следует учитывать при вероятной перегрузке процессора, является регистр System: Context Switches/sec, который указывает число переключений (в секунду) с одного потока на другой, осуществляемых системой Windows NT и SQL Server. Переключение с одного контекста на другой в многопоточной и мультипроцессорной среде представляет собой нормальное явление, но избыточное количество таких переключений замедляет работу системы. О количестве переключений стоит беспокоиться только в тех случаях, когда возникают очереди обращений к процессорам. Если очередь возникла, то количество переключений может служить контрольным параметром при проведении оптимизации работы сервера. Можно использовать опцию lightweight pooling, чтобы сервер использовал послойную модель распределения памяти (fiber-based scheduling model), а не потоковую модель (thread-based scheduling model), которая используется по умолчанию. В этом смысле слои можно представить в виде облегченных (lightweight) потоков. Для того чтобы активизировать этот режим, следует выполнить команду sp_configure 'lightweight pooling', 1. Размер очередей обращений к процессору и число переключений с одного контекста на другой должны при этом измениться.

Дополнительную информацию о процессах ввода/вывода, использовании памяти и ресурсов центрального процессора с учетом идентификатора SPID можно получить с помощью оператора DBCC SQLPERF (THREADS).

Регистры обращений к диску

Регистры Disk Write Bytes/Sec и Disk Read Bytes/Sec содержат сведения о том, какой объем информации (в байтах) в секунду был передан логическому диску или получен от него. Нужно внимательно анализировать эти значения, учитывая при этом также значения регистров Disk Reads/Sec и Disk Writes/Sec. Из того, что регистры показывают малое число байт в секунду, еще не следует, что дисковая подсистема ввода/вывода не загружена работой. Один жесткий диск может поддерживать всего 75 непоследовательных или 150 последовательных операций чтения или записи в секунду.

Показания регистра Disk Queue Length указывают размер очереди обращений к диску для каждого диска, на котором размещены служебные файлы SQL Server. С его помощью можно определить, какие файлы вызывают увеличение размера очередей обращений к диску.

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

Возникновение очередей обращений к дискам может быть признаком того, что какой-либо из SCSI-каналов не в состоянии пропустить большее количество обращений. Утилита Performance Monitor не имеет средств непосредственной проверки такой ситуации. Поставщики аппаратных средств предлагают инструменты, позволяющие определить, какое количество операций ввода/вывода выполняет RAID-контроллер и проверить, не увеличивает ли он размер очереди. С наибольшей вероятностью это может происходить, если к SCSI-каналу подключено много (10 и более) жестких дисков, и все они с полной нагрузкой обрабатывают запросы на обращение к ним. Решить проблему можно, если половину этих дисков подключить к другому SCSI-каналу или к RAID-контроллеру и тем самым добиться более равномерного распределения операций ввода/вывода в системе. При перераспределении дисков по SCSI-каналам необходимо заново организовывать RAID-массивы, производить полное резервное копирование и восстановление файлов базы данных сервера.

Графическое представление результатов в Performance Monitor

На приведенном рисунке показаны значения регистров в утилите Performance Monitor, наиболее часто используемых при контроле за работой сервера. В данном случае наблюдение ведется за регистром Processor Queue Length. Нажатием клавиши BackSpace можно отметить значения этого регистра. Это позволяет выделить его значения на фоне прочих и облегчает анализ значений в ситуациях, когда с помощью Performance Monitor приходится контролировать много параметров.

Максимальное (Max) значение регистра Processor Queue Length (см. рисунок) составляет 22.000. Максимальное (Max), минимальное (Min) и среднее (Average) значения регистра Processor Queue Length в Performance Monitor относятся только к текущему временному интервалу, размер которого указан в окне Graph Time. По умолчанию длина временного интервала Graph Time составляет 100 секунд. Чтобы контролировать значения регистров на более длинном интервале времени и получать при этом значения величин Max, Min и Average, действительно отражающие работу системы за указанный интервал наблюдения, следует использовать режим регистрации параметров, предусмотренный в Performance Monitor.

Форма диаграммы Processor Queue Length на графике показывает, что максимальное значение 22 достигалось только на протяжении короткого отрезка времени. Однако перед этим был период, когда значение регистра превышало 5. Пояснить это можно следующим образом. В данном случае величина 22 составляет 100 процентов, а на диаграмме есть интервал времени, когда значения диаграммы превышают 25 процентов, то есть значение регистра превышает 5. В данном примере сервер базы данных HENRYLNT2 имеет только один процессор, и поэтому значение параметра Processor Queue Length не должно превышать 2. Поэтому утилита Performance Monitor показывает, что процессор данного компьютера работает с перегрузкой, и для обеспечения успешной работы системы в периоды с подобным уровнем нагрузки сервер HENRYLNT2 следует оснастить еще одним или несколькими процессорами.

Другие способы повышения быстродействия

В данном разделе приводится описание некоторых других факторов, кроме рассмотренных выше, которые могут повлиять на эффективность работы Microsoft SQL Server.

Снижение сетевого трафика и уменьшение количества потребляемых ресурсов

Создатели программ баз данных, которые при разработке своих продуктов используют относительно простые в эксплуатации интерфейсы прикладного программирования баз данных типа Microsoft ActiveX Data Objects (ADO), Remote Data Objects (RDO) и Data Access Objects (DAO), должны хорошо представлять, как будет работать созданный ими программный продукт. Интерфейсы ADO, RDO и DAO содержат в себе такие интерфейсы создания баз данных, которые позволяют создавать довольно развитые продукты даже при небольшом начальном опыте программирования. Создатели баз данных могут столкнуться с трудностями, выражающимися в низкой эффективности работы их продуктов, если будут создавать свои базы данных без учета того, какую информацию их приложение должно выдавать пользователю, где размещены индексы и как организовано размещение информации, хранящейся в базе данных. Для распознавания и устранения проблем, связанных с возникновением неполадок при обработке запросов, предусмотрены такие средства как утилита SQL Server Profiler, мастер Index Tuning Wizard и служба ShowPlan.

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

Более подробные сведения можно получить в разделах Optimizing Application Performance Using Efficient Data Retrieval, Understanding and Avoiding Blocking и Application Design в SQL Server 7.0 Books Online.

Взаимная блокировка

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

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

Более подробные сведения можно почерпнуть в разделах Avoiding Deadlocks, Troubleshooting Deadlocking, Detecting and Ending Deadlocks и Analogy to Nonserializable Transactions в SQL Server 7.0 Books Online.

Нежелательные выражения

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

Примерами могут служить такие запросы с использованием оператора WHERE и отрицания NOT:

WHERE <column_name> != some_value

WHERE <column_name> <> some_value

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

Например, вместо запроса

SELECT * FROM tableA WHERE col1 != 'value'

Можно использовать запрос

SELECT * FROM tableA WHERE col1 < 'value' or col1 > 'value'

SQL Server может использовать индекс (предпочтительно кластеризованный), если он организован на столбце col1, и в этом случае просмотра таблицы удастся избежать.

Грамотная нормализация

Если в таблице, к которой адресуется много запросов, имеются столбцы, которые не слишком часто запрашиваются приложениями, то такие столбцы можно перенести в другую таблицу. Уменьшив в основной таблице число столбцов до минимально необходимого, можно добиться уменьшения числа операций ввода/вывода и повышения быстродействия. Более подробно этот вопрос освещен в разделе Logical Database Design and Normalization в SQL Server 7.0 Books Online.

Представления с разбиением

SQL Server 7.0 поддерживает горизонтальное разбиение таблиц с помощью функции представления (view). Это позволяет повысить быстродействие системы в случаях, когда пользователи запрашивают не всю большую таблицу, а только ее часть. Например, если в большой таблице регистрируются все торговые операции во всех филиалах в течение года, а поиск в таблице ведется по какому-то одному филиалу, то можно использовать представление таблицы с разбиением. Для этого нужно создать таблицу регистрации торговых операций для каждого филиала, в каждой такой таблице на столбец с названием филиала наложить ограничивающее условие, и затем из всех этих таблиц создать представление, которое и будет называться представлением с разбиением. Ограничивающее условие, наложенное на столбец с названием филиала, используется оптимизатором запросов. Когда к представлению направляется запрос, все таблицы для филиалов, названия которых не совпадают с названием, указанном в запросе, игнорируются оптимизатором запросов, и в отношении этих таблиц операций ввода/вывода данных не производится. В результате уменьшается количество операций обмена данными, и скорость выполнения запроса повышается.

Более подробные сведения на эту тему можно получить в разделах Scenarios for Using Views, CREATE VIEW, Using Views with Partitioned Data, Modifying Data Through a View, Copying To or From a View и Partitioning в SQL Server 7.0 Books Online.

Быстродействие при репликации и создании резервных копий

Если администратору сервера баз данных удалось обеспечить такие условия, при которых подсистема ввода/вывода данных и центральные процессоры работают нормально, то можно говорить о том, что всем операциям SQL Server, включая репликацию данных и создание резервных копий, обеспечены условия хорошего быстродействия. При операциях репликации с помощью транзакций и создания резервных копий файла регистрации транзакций обязательно происходит чтение файлов регистрации транзакций. При репликации текущего состояния базы данных (snapshot replication) и создании резервных копий выполняется последовательное сканирование (просмотр) всех файлов баз данных. Организация памяти в SQL Server 7.0 позволяет быстро и эффективно выполнить эти операции при условии, что на центральных процессорах сервера и баз данных и на дисковой подсистеме не образуются очереди.

Более подробные сведения на эту тему можно получить, ознакомившись с разделами Optimizing Backup and Restore Performance, Creating and Restoring Differential Database Backups, Creating and Applying Transaction Log Backups, Using Multiple Media or Devices, Minimizing Backup and Recovery Times in Mission-Critical Environments, Backup/Restore Architecture, SQL Server 7.0 on Large Servers в SQL Server 7.0 Books Online и в результате поиска по ключевому слову replication performance.

Пример оптимизации обращений к дискам ЕМС

Для специалистов, развертывающих большие базы данных SQL Server на системах EMC Symmetrix Enterprise Storage Systems, могут представить интерес некоторые специальные методы распределения нагрузки от операций обращения к диску, которые предназначены для предотвращения критических ситуаций и повышения быстродействия системы.

Системы хранения информации Symmetrix могут иметь кэш оперативной памяти объемом до 16 Гб, а их дисковые массивы могут содержать встроенные процессоры, что позволяет повышать интенсивность операций ввода/вывода информации, не затрачивая ресурсов центрального процессора главного сервера. Чтобы эффективно распределять операции обращения к дискам в таких системах, следует иметь представление о том, из каких компонентов состоит система хранения данных Symmetrix. Таких основных компонентов четыре. Один из них – это кэш памяти объемом до 16 Гб. К нему по отдельным SA-каналам, снабженным усилителями считывания (SA channels), может быть подключено до 32 плат SCSI, установленных на основных серверах системы Symmetrix, работающих под управлением Windows NT. По всем этим каналам данные могут одновременно запрашиваться из кэша и передаваться на платы. Эта группа составляет второй элемент системы. Третьим элементом являются соединительные устройства, называемые DA-контроллерами (это внутренние SCSI-контроллеры), которые связывают дисководы системы Symmetrix с внутренним кэшем объемом 4 Гб. И, наконец, последний элемент системы – это собственно жесткие диски.

Жесткие диски стандарта ЕМС представляют собой диски с контроллерами SCSI и имеют такое же быстродействие, что и другие SCSI-диски, упоминавшиеся ранее в данной публикации. Одним из приемов, часто используемых в рамках технологии ЕМС, является организация гипер-томов. Гипер-томом называют логическую часть жесткого диска ЕМС, поэтому она распознается утилитой Windows NT Disk Administrator как отдельный жесткий диск, и эта утилита может управлять гипер-томом точно так же, как она управляет другими дисками. На каждом физическом жестком диске может быть создано несколько гипер-томов. Прежде чем приступать к оптимизации работы сервера баз данных, организованного с использованием технологии ЕМС, администратору следует подробно проконсультироваться со специалистами по эксплуатации, знакомыми с этой технологией, и получить верное представление о том, как в системе определены гипер-тома. Ошибочно считая один или несколько гипер-томов, организованных на одном жестком диске, отдельными физическими дисками, можно перегрузить реальный жесткий диск слишком большим количеством операций ввода/вывода данных и снизить быстродействие сервера.

Операции ввода/вывода, обусловленные работой сервера, должны быть равномерно распределены между отдельными DA-контроллерами, так как эти контроллеры связаны с определенным набором жестких дисков. Маловероятно, что сами эти контроллеры будут испытывать перегрузку при передаче данных, однако группа жестких дисков, подключенных к одному контроллеру, может оказаться более чувствительной к повышенной нагрузке. Равномерное распределение нагрузки ввода/вывода по DA-контроллерам и подключенным к ним дискам можно осуществить таким же способом, как и в случае дисков и контроллеров, созданных по другим технологиям.

Для контроля за операциями ввода/вывода в DA-канале или на отдельных физических жестких дисках администратору сервера баз данных понадобится помощь со стороны специалистов группы технической поддержки, знакомых с технологией ЕМС, поскольку операции ввода/вывода происходят на уровне глубже внутреннего кэша ЕМС и поэтому не определяются утилитой Performance Monitor. Устройства хранения, использующие технологию ЕМС, имеют встроенные средства контроля, которые позволяют специалистам группы технической поддержки следить за статистикой операций ввода/вывода в системе Symmetrix. Утилита Performance Monitor может определять только те операции ввода/вывода, которые передаются по каналу с усилителем считывания (SA-каналу). Этой информации может оказаться достаточно, чтобы определить, что в SA-канале образуется очередь из запросов на ввод/вывод данных, но недостаточно для того, чтобы определить, какой диск или диски служат источником проблемы. Если в SA-канале образуется очередь, то причина может крыться не в нем самом, а в связанных с ним жестких дисках. Одним из способов выявления истинного источника перегрузки диска по операциям ввода/вывода (либо это SA-каналы, либо DA-каналы и жесткие диски) является добавление новой SCSI-платы к главному серверу и подключение ее к другому SA-каналу. Если утилита Performance Monitor показывает, что количество операций ввода/вывода в обоих SA-каналах не изменилось, и очереди не исчезли, то причина их образования не связана с работой SA-канала. Другим возможным способом определения источника проблемы является участие специалиста по технологии ЕМС, который с помощью встроенных средств контроля может проанализировать работу системы ЕМС и выявить те DA-каналы и диски, которые приводят к образованию очередей.

Следует стремиться к равномерному распределению запросов от сервера по как можно большему числу дисков. Если перед администратором стоит задача управления базой данных небольшого размера, к которой происходит много обращений, то ему стоит задуматься о том, какого размера гипер-тома он должен попросить специалистов по технологии ЕМС организовать в системе. Предположим, что сервер состоит из базы данных объемом 30 Гб. Жесткие диски, поддерживающие технологию ЕМС, могут иметь объем до 23 Гб, так что всю базу данных можно разместить на двух дисках. С точки зрения стоимости и удобства управления системой такой вариант кажется привлекательным, однако если учесть интенсивность операций обращения к дискам, то оценка окажется иной. Устройство хранения данных, совместимое со стандартом ЕМС, может поддерживать до 100 внутренних дисков. Использование только двух дисков для размещения базы данных может привести к перегрузке какого-либо из элементов системы. Следует рассмотреть возможность организации гипер-томов меньшего размера, например, по 2 Гб каждый. Тогда примерно 12 гипер-томов можно будет разместить на одном жестком диске объемом 23 Гб. Если использовать гипер-тома объемом по 2 Гб, то для размещения базы данных потребуется 15 таких томов. Следует убедиться в том, что каждый гипер-том ассоциирован с отдельным физическим жестким диском. Не следует ассоциировать 12 гипер-томов с одним жестким диском и 3 гипер-тома – с другим, поскольку такой вариант будет эквивалентен использованию двух жестких дисков (что соответствует 150 операциям непоследовательного или 300 операциям последовательного обращения к двум дискам в секунду). В случае же, когда каждый гипер-том ассоциирован с отдельным жестким диском, сервер будет обращаться к системе из 15 жестких дисков, которая будет поддерживать 1125 операций непоследовательного или 2250 операций последовательного обращения к 15 дискам в секунду.

Следует также рассмотреть возможность использования нескольких SA-каналов на главном сервере, чтобы распределить нагрузку от операций ввода/вывода между контроллерами главных серверов, которые поддерживают более одной шины PCI. Целесообразно использовать один SA-канал на каждую шину PCI главного сервера, чтобы равномерно распределить нагрузку от операций ввода/вывода не только в SA-каналах, но и в шинах PCI. В системах хранения информации, поддерживающих технологию ЕМС, каждому SA-каналу соответствует отдельный DA-канал и вполне конкретный набор физических жестких дисков. Поскольку SA-каналы передают операции чтения и записи данных из внутреннего кэша ЕМС, то маловероятно, чтобы такие каналы оказались перегруженными операциями ввода/вывода. Так как перегрузка SCSI-контроллеров тоже маловероятна, целесообразно будет сосредоточить внимание на равномерном распределении нагрузки по обслуживанию запросов сервера между всеми физическими жесткими дисками и не слишком задумываться о том, сколько SA-каналов использовать в системе.

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

Документация по системе Microsoft SQL Server содержит сведения об архитектуре SQL Server и рекомендации по оптимизации работы баз данных, а также полный набор документов по синтаксису используемых команд и управлению сервером. Целесообразно установить интерактивную справку SQL Server Books Online на жесткие диски тех компьютеров, где постоянно используется SQL Server, поскольку она представляет собой чрезвычайно полезную справочную систему.

Наиболее свежую информацию о Microsoft SQL Server, а также другие технические публикации, можно получить, посетив веб-узел по адресу www.microsoft.com/SQL.

Компания Compaq выпустила новую версию своей публикации, посвященной RAID-массивам. Этот документ содержит 50 страниц чрезвычайно полезной информации, посвященной вопросам быстродействия серверов баз данных. Сведения в этой публикации, касающиеся Microsoft SQL Server, относятся к Microsoft SQL Server version 6.5 и не распространяются на SQL Server version 7.0. Эта публикация озаглавлена “Configuring Compaq RAID Technology for Database Servers” и находится на веб-узле компании Compaq по адресу http://www.compaq.com/support/techpubs/whitepapers/ecg0110598.html. Этот документ имеет номер ECG011/0598.

На веб-узле компании Compaq по адресу www.compaq.com/support/techpubs/whitepapers/ecg0250997.html имеется также 30-страничный документ, составленный группой интеграции систем Windows NT этой компании. Документ называется “Disk Subsystem Performance and Scalability”, содержит описание аппаратных характеристик работы жестких дисков Compaq и освещает технические вопросы, связанные с функционированием жесткого диска. Информация, опубликованная в этом документе, распространяется на жесткие диски с контроллерами SCSI, производимые компанией Compaq и другими поставщиками. Номер этого документа ECG025.0997.

Книга SQL for Smarties (автор Joe Celko), опубликованная в издательстве Morgan Kaufmann Publishers, ISBN 1-55860-323-9, содержит много полезных сведений и вариантов решений многих типичных проблем вроде представления иерархических данных и организации запросов к ним. Одна из глав книги посвящена вопросам оптимизации SQL-запросов.