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

К вопросу исследования SQL сервера с помощью Performance Monitor
Настройка конфигурационного параметра SQL Server 7.0: "Max Async I/O"
Мониторинг производительности сервера баз данных с помощью SQL Server Profiler
Снижение интенсивности операций ввода-вывода
“Тюнинг” – для Windows NT, обеспечивающий более эффективную работу MS SQL Server

К вопросу исследования SQL сервера с помощью Performance Monitor

Процессор: "% Processor Time" - показывает загрузку CPU. В многопроцессорной системе возможна диагностика загрузки, как каждого процессора в отдельности, так и всех вместе. Также, одноимённый параметр можно использовать для определения утилизации процессора каждым потоком. Если "% Processor Time" показывает среднее значение в диапазоне 80 % - 100 %, это говорит о проблеме с производительностью Вашей системы. Необходимо принимать меры по масштабированию системы или изменения конфигурации. В то же время, кратковременное достижение  "% Processor Time" - 80 % уровня или даже пики до 100 % ничто опасного или нежелательного не представляют. Поскольку ресурсы процессора использует не только сервер баз данных, Вы должны определить является ли SQL Server причиной высокой утилизации процессорного времени. Используйте SQLServer: CPUtime для определения доли SQL сервера в "% Processor Time". Выяснив, что причина повышенной загрузки процессоров является SQL сервер, а также какой процесс её провоцирует, Вы должны проанализировать проект исполняемого в это время запроса. Убедитесь, что в запросе индексы используются оптимальным образом. Возможны случаи, когда неумело построенный запрос не использует или не оптимально использует существующий индекс.
Запрос может хорошо кэшироваться но, в то же время, перегружать систему ввода - вывода (I/O), что отвлекает большое количество циклов CPU.
Это может говорить о том, что при проектировании таблицы индекс был задуман не оптимально. Если проект запроса оптимален,  можно решить проблему за счёт масштабирования, например, добавить процессоры или установить более производительный процессор. Естественно, добавлять процессоры следует только в том случае, если Ваш объe:м ОЗУ достаточно велик, чтобы удовлетворить запросы SQL сервера.
Напротив, если "% Processor Time" постоянно показывает очень низкую утилизацию CPU, это, как правило, говорит о наличии проблем. Вариант, когда сервер чрезвычайно избыточен для решения задач СУБД, в данной статье не рассматривается. Низкая утилизация возможна из-за ограничений в конфигурации SQL сервера (например, используются значения по умолчанию, когда требуется их увеличение) или проблема кроется в Вашем приложении прикладной задачи.

Процессор: "% Privileged Time" - удобен для определения чрезмерной загрузки I/O. Если средне значение превышает 20%, а "% Processor Time"- существенно ниже 80 %, это говорит о том, что SQL Server чересчур сильно утилизирует систему I/O. Вам необходимо проанализировать проект базы данных, загрузку RAID контроллера и сетевой платы. Существенное влияние на "% Privileged Time" могут оказывать и работающие параллельно с SQL сервером процессы или сервисы, в том случае, когда сервер используется не только для обслуживания СУБД. Одним из распространe:нных вариантов решения роблемы высокой утилизации I/O является размещение tempdb в ОЗУ.

Система: "Processor Queue" - предназначен для диагностики очередей процессоров. Если его значение больше чем 2 значит, что CPU работает с перегрузкой. Очевидно, что для решения этой проблемы необходимы дополнительные процессорные мощности.

Система: "Context Switches/sec" - переключение контекста, когда NT или SQL Server переключают обслуживание процессором с одного потока на другой, что вызывает всплеск утилизации CPU. Если при этом "Processor Queue" > 2-х, постарайтесь изменить число потоков, используемых SQL сервером.

Процесс: "Thread Count" - число активных потоков. Значение этого счётчика совместно с "Context Switches/sec" можно использовать для оптимального конфигурирования SQL сервера, чтобы снизить чрезмерную утилизацию CPU.

Процесс: "Virtual Bytes" - позволяет определить, сколько памяти использует SQL сервер и какие приложения используют её недостаточно эффективно; процесс: "Working Set" - объe:м памяти используемый процессом. Изменение конфигурационных настроек SQL сервера после анализа этих счe:тчиков позволит оптимизировать распределение памяти между сервером баз данных, операционной системой и другими приложениями сервера.

SQLServer: "Cache Hit Ratio" - для хорошо сбалансированных приложений число попаданий в кэш должно стремиться к 100%. Часто, достижение высокого уровня попадания в кэш достигают просто увеличением ОЗУ. Боле тонко регулировать кэширование можно контролируя 1081 trace flag, добиваясь, что бы страницы индексов оставались в кэше данных дольше, чем страницы данных.

Настройка конфигурационного параметра SQL Server 7.0: "Max Async I/O"

Как правило, значение по умолчанию параметра  Max Async I/O достаточно только для дисковых подсистем нижнего класса. Для более продвинутых RAID - контроллеров с очень высокой пропускной способностью и обслуживающих большое количество дисков этого может оказаться недостаточно или просто возможности системы будут сдерживаться.
Исключение составляет случай использования в качестве операционной системы Windows 95/98, которая просто не поддерживает асинхронный ввод - вывод. Оптимальный выбор значения Max Async I/O позволит серверу полностью отработать Checkpoint, до его следующего цикла и, при этом, не помешает выполнению параллельно исполняемых процессов/потоков.
Microsoft предлагает следующее эмпирическое правило для установки
максимального значения Max Async I/O, если Вы используете RAIDE с большим количеством дисков: "Умножьте число физических дисков, доступных для одновременного ввода/вывода на 2 или на 3, и полученное значение присвойте параметру Max Async I/O".
После этого, наблюдайте средствами Performance monitor или Microsoft Management Console поведением дисковой подсистемы и очередей.
Следите, что бы Checkpoint не монополизировал всю ширину, которая обслуживает дисковую подсистему.

Мониторинг производительности сервера баз данных с помощью SQL Server Profiler
(По материалам статьи Maxim Smirnov на swynk.com «Monitoring Performance With SQL Server Profiler»)

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

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

Одним из эффективных инструментов анализа работы сервера баз данных является SQL PROFILER. Основным его преимуществом является возможность сохранения собранной в процессе трассировки информации в специально созданных администраторам таблицах базы данных. Кроме того, этот инструмент DBA имеет простой, дружественный интерфейс.

Установка трассировки в SQL PROFILER

Запустите SQL PROFILER, и в пункте меню FILE выберите NEW, а затем TRACE. Введите информацию о SQL сервере, который Вы хотите исследовать. Введите имя сервера, логин и пароль. В появившейся форме введите название новому заданию для трассировки, например TRACE1. Для опции "Save to table" - Вы должны указать базу данных и таблицу в которую Вы хотите сохранять собранную информацию. Очень хорошая идея, использовать отдельный SQL сервер (например, настольную версию), чтобы избегать конкуренции за ресурсы между последовательной записью данных трассировки в таблицу и другими процессами сервера баз данных.

В настройках для таблицы результатов трассировки (EVENTS) лучше удалить все заданные по умолчанию счётчики и затем выбирать всё, что относится к хранимым процедурам и запросам TSQL и любым другим дополнительным событиям, которые Вас интересуют.

В настройках фильтров задают параметр OBJECTID для дерева и флаг проверки "Exclude system objects".  После этого можно вернуться в основную форму и нажать кнопку RUN. С этого времени SQL PROFILER начнёт собирать информацию обо всех процедурах, исполняемых сервером баз данных. Процесс трассировки можно оставить запущенным на несколько часов, в течение обычной активности пользователей. После этого, Вы готовы к анализу данных трассировки.

Анализ Данных

Михаил предлагает, в первую очередь, обратить внимание на два счётчика: READS и DURATIOIN. В листинге трассировщика будет содержаться информация по каждой завершённой процедуре или запросу наряду с id пользователей и другими параметрами.

Перед составлением запросов к таблице данных трассировки (TRACE1), Михаил предлагает создать два индекса для полей DURATION и READS. Это существенно ускорит анализ.

CREATE NONCLUSTERED INDEX IND_TRACE_1 ON dbo.TRACE1(Duration)
GO

CREATE NONCLUSTERED INDEX IND_TRACE_2 ON dbo.TRACE1(Reads)
GO

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

SELECT * FROM TRACE WHERE Duration IN
(SELECT DISTINCT TOP 20 Duration
FROM TRACE ORDER BY Duration DESC)

Чтобы понять, почему процедура исполняется так долго, нужно анализировать параметры для READS. Когда текущая величина READS не высока, вероятно предположить, что требуемые ресурсы (например таблицы или представления) были заблокированы другим процессом. Это указывает на возможные проблемы блокировок, и Вы можете искать процедуры, которые в это время используют те же самые объекты.

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

Перетащите долго выполняемый запрос в Query Analyzer и с помощью Плана Выполнения запроса определите, какие таблицы сканируются или какие индексы используются. Простого сканирования можно избежать, если применить надлежащий индекс к полям, участвующим в предложении WHERE или объединении. Просмотр индекса исполняется намного быстрее чем сканирование таблицы (для этого их и придумали). Часто, для таблиц создаётся составной индекс, и, если поля, используемые в WHERE или объединении, не входят ни в один из существующих индексов, можно создать дополнительные, не составные индексы для этих столбцов, что может существенно снизить READS и повысить эффективность исполнения запроса.

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

SELECT * FROM TRACE WHERE Reads IN
(SELECT DISTINCT TOP 20 Reads
FROM TRACE ORDER BY Duration DESC)

В случае прямого сканирования таблиц, также можно использовать аналогичные запросы к таблице результатов трассировки.

Снижение интенсивности операций ввода-вывода
По материалам сообщения John Savill на:
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14921

Если Ваш сервер баз данных чересчур интенсивно использует I/O, можно изменить значение параметра операционной системы I/O Page Lock Limit, который может увеличить эффективную норму чтения/записи данных операционной системой на жесткий диски.
Сначала, выполните эталонный тест I/O для вашей обычной загрузки сервера. Затем, в regedit.exe откройте ключ:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement\IoPageLockLimit

Смысл Ваших действий состоит в пошаговом подборе значений этого ключа до наиболее оптимального, с точки зрения изменений результатов эталонного тестирования, значения.
В этом ключе операционная система считывает максимальное число байт, которые она можете использовать для операций I/O. По умолчанию установлено значение 0, которому соответствует 512КБ. Увеличивайте это значение по шагам, каждый раз прибавляя по 512КБ (например: "512", "1024", и т.д.), и выполняйте после каждого изменения эталонное тестирование вашей системы. Увеличивать этот параметр есть смысл только до тех пор, пока вы наблюдаете увеличение пропускной способности операций ввода – вывода, которое может проявляться в снижении временных затрат на стандартные дисковые операции. Когда Вы перестанете наблюдать существенное улучшение, возвратитесь в редактор реестра и уничтожьте последнее приращение.

Предостережение: Есть ограничение на максимальный размер значения этого ключа. Если Вы имеете 16 МБ ОЗУ, не устанавливайте IoPageLockLimit более 2048 байт; для 32МБ ОЗУ, не превышайте 4096 байт, и так далее.

“Тюнинг” – для Windows NT, обеспечивающий более эффективную работу MS SQL Server
По материалам статьи Sergey A. Vartanyan на swink.com “Tips on tuning the Windows NT server”.

Как DBA или разработчик, Вы должны знать некоторые общие аспекты конфигурирования системы Windows NT для повышения эффективности работы SQL сервера. В этой статье, Сергей предлагает некоторые настройки Windows NT, на котором предполагается запускать MS SQL Server:
1.) Если у Вас ОЗУ большого размера, Вы можете запретить Windows NT сбрасывать страницы в pagefile. Для этого, с помощью regedit.exe, установите значение ключа

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement

в единицу. (Замечание Автора: лично я этот ключ трогать боюсь…)
2.) Создать на каждом отдельном физическом дисковом массиве свой page file. Исключение может составить только тот диск, где расположен системный каталог Windows NT.
3.) Установите для сервера опцию "Maximize Throughput for Network Applications":
Start => Settings => Control Panel => Network => Services
Выберите Server и потом Properties.
Из списка выберите последний пункт "Maximize Throughput for Network Applications".
4.) Вы можете повысить производительность отключив учёт даты последнего доступа к файлам. Для этого нужно установить в единицу ключ:
HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
5.) Используйте минимальный набор сетевых протоколов. Например, только TCP/IP. Если Вы используете несколько протоколов, определите наиболее часто используемый, и поместите его на первое место в Bindings листе.
6.) Используйте как можно меньшее количество счётчиков в Performance Monitor.
7.) Не используйте Open GL хранители экрана, потому что они используют очень много системных ресурсов.
8.) При настройке Audit Policy, добавляйте новые проверки очень осторожно. Не проверяйте "File and Objects Access" и "Process Tracking", т.к. это может привести к почти полной потере доступности сервера.
9.) Укажите серверу запускать приложения с консоли с тем же приоритетом, что и background applications
Start => Settings => Control Panel => System => "Performance"
Далее с помощью клавиши табуляции перейдите к нужному пункту и установите "Application Performance" в "None".

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