Шпаргалка по 70-028 | Мониторинг SQL Server 7.0 | Дальше » |
Задачи и
цели мониторинга
Факторы производительности Средства и способы мониторинга Инструменты мониторинга Microsoft Event Viewer SQL Server Performance Monitor Окно Current Activity, SQL Server Enterprise Manager Мониторинг средствами Transact-SQL Мониторинг средствами SQL Server Profiler Информация из чёрного ящика MS SQL Server Мониторинг средствами SQL Server Query Analyzer Регламент администрирования SQL Server Вопросы для повторения Задачи и цели мониторинга Определить оптимальность работы сервера баз данных и выявить возможные неполадки или узкие места, можно с помощью средств мониторинга, предоставляемых SQL сервером. Грамотное использование этих средств позволит Вам вовремя определить и устранить причины низкой производительности сервера, которая определяется объёмом операций ввода-вывода, необходимых для исполнения транзакции, утилизацией процессоров и временем отклика системы. Для каждой конкретной реализации, уровень доступной производительности определяется составом аппаратно-программных средств и способов их использования. Наиболее наглядным фактором оценки производительности системы является её время отклика, которое представляет собой интервал времени, в течении которого сервер возвращает первую строку результата исполнения запроса, т.е. пользователь получает визуальное подтверждение того, что его запрос исполняется. Пропускная способность обслуживаемых сервером процессов и пользователей определяет сколько запросов возможно исполнить в фиксированный интервал времени, и сколько строк и какого размера возвращается клиенту. При увеличении числа активных процессов и/или пользователей, возрастает и их конкуренция за системные ресурсы. Результатом такой чрезмерной нагрузки может стать увеличение времени отклика и снижение общей пропускной способности. Большое влияние на производительности базы данных оказывает также и состояние/согласованность самих данных. Страницы данных и индексы могут оказаться повреждёнными, что приводит потере логической и физической согласованности. Для выявления таких повреждений используют специальное средство Database Consistency Checker (DBCC). Факторы производительности В числе факторов, которые способны повлиять на производительности системы, можно выделить, в первую очередь, аппаратную конфигурацию сервера. Количество процессоров и их быстродействие, количество и быстродействие дисков, объём оперативной памяти; всё это оказывает очень существенное влияние на уровень производительности. Не малую роль в общую производительность вносит и операционная система (ОС). Очевидно, что параллельно с процессами сервера баз данных будут выполняться процессы ОС и других, одновременно функционирующих на этом сервере программ. Также, на производительность могут повлиять файлы страничного обмена, их количество и местоположение. Использование RAID технологии также может в ту или иную сторону сказаться на общей производительности. Разумеется, нельзя исключать из систем, участвующих в оценке общей производительности, и сетевую среду, быстродействие сетевых соединений которой, а также её утилизация или уровень коллизий влияют на полосу пропускания сети, а следовательно, и на скорость передачи данных и запросов между клиентом и сервером. Сам SQL сервер, естественно, тоже оказывает влияние на производительность системы. Его конфигурация подразумевает динамическое распределение многих ресурсов и параметров, таких, как память, дисковое пространство подключения пользователей. Если у Вас нет веских на то оснований, лучше не вмешивайтесь в это динамическое распределение. Отрицательно сказываются на производительности сервера блокировки и большие объёмы журналируемых операций. Параллельное выполнение резервирования/восстановления, запуск DBCC или переиндексация могут также существенно сказаться на времени отклика сервера. Качество проектирования баз данных тоже имеет весомое значение. Производительность запросов может зависеть от уровня нормализации данных, их логической и физической структуры. Уровень контроля выполнения транзакций, как правило, влияет на количество и длительность блокировок. Часто повторяющиеся конфликты также замедляют работу. Зато оформление запросов в виде хранимых процедур может существенно поднять их «скорострельность», относительно незапланированных запросов, реализованных в логике клиента. От логики работы клиентского приложения также зависит очень многое. Само число одновременно обращающихся к серверу баз данных и выбранной для этого схемы сетевых соединений, оказывает существенное влияние на распределение памяти сервера. Уменьшение числа конфликтов при обслуживании транзакций в состоянии существенно повысить общую производительность. Реакция клиентского приложение на блокировки и его способность повторно выставлять запрос или операторы модификации данных, может существенно разгрузить и оптимизировать работу сети и SQL сервера. Время отклика можно значительно сократить за счёт использования правильных видов курсора, сокращения объёмов извлекаемых данных и оптимизации использования кэша. Средства и способы мониторинга Задача исследования производительности SQL сервера и
выявления узких мест решается намного проще, если Вы будете
использовать системный подход. Столкнувшись с тем, что Ваша
СУБД работает не так производительно, как Вы ожидали или
производительности неожиданно снизилась, без видимых на то
причин, необходимо последовательно локализовать проблему,
которая стала этому виной. Инструменты мониторинга На всех этапах мониторинга производительности системы в Вашем распоряжении будут шесть инструментальных средств, входящих в базовый комплект поставки Microsoft SQL Server. Для мониторинга на уровне системы используют Microsoft Event Viewer и SQL Server Performance Monitor. Для мониторинга операций Microsoft SQL Server и работы его компонент (блокировки, коллизии, подключения, утилизация tempdb, согласованность данных) используют такие средства, как: SQL Server Profiler, окно Enterprise Manager - Current Activity, системные хранимые процедуры, специальные операторы T-SQL, операторы DBCC. Для исследования производительности отдельных запросов (использование индексов, утилизация запросом процессора, объём I/O) применяют SQL Server Query Analyzer и Index Tuning Wizard. Microsoft Event Viewer Журналы операционной системы Microsoft Windows NT, доступ к
которым легко осуществляется с помощью программы Microsoft
Event Viewer, дают много полезной информации о событиях,
зафиксированных ОС. Анализируя эти события можно составить
представления о характере возможных «узких» мест в Вашей
системе. Программа Event Viewer предоставляет возможность
анализа трёх журналов событий операционной системы: Windows NT
Application Log, Windows NT System Log и Windows NT Security
Log. SQL Server Performance Monitor Стандартное средство диагностики работы операционной
системы Windows NT Performance Monitor может иметь вариант
настройки, позволяющий отслеживать операции, статистику
производительности в течении заданного временного интервала
или наблюдать операции сервера по мере их выполнения, и т.п.,
касающееся MS SQL Server, и всё это будет уже называться SQL
Server Performance Monitor. К стандартному набору счётчиков
сервера и операционной системы добавляются специфические
счётчики сервера баз данных, которые совместно со счётчиками
NT сохраняются в файле с расширением .pmc. Создание объектов
измерения производительности MS SQL Server происходит на
стадии инсталляции сервера БД, и эти, предварительно
определённые счётчики, сохраняются в файле Sqlctrs.pmc.
Разумеется, работа Performance Monitor не проходит незаметно
для общей производительности системы, поскольку отнимает
ресурсы на свои нужды. Если Вы используете NT аутентификацию,
то счётчики сервера баз данных будут доступны только для
пользователей, входящих роль sysadmin. - SQL Server: Buffer Manager\Buffer Cache Hit Ratio
- определяющий долю страниц в процентах, которые были
обнаружены в буферном кэше, что позволило обойтись без
дисковых операций; Следующие счётчики не являются предварительно определёнными, но очень часто бывают полезны: - SQL Server: Databases\Active Transactions -
показывает количество активных транзакций для БД; Часто, весьма полезными оказываются и счётчики Windows NT Performance Monitor, вот некоторые из них: - Memory/Pages per second - показывает количество
считанных или записанных на диск страниц из-за отсутствия
нужных страниц в памяти; Кроме стандартных счётчиков, у Вас есть возможность определить до десяти пользовательских счётчиков. Специальный объект SQL Server User-Settable Counters может содержать до 10-ти определяемых пользователем счётчиков - SQL User Counter. Такие счётчики Вы можете создавать с помощью хранимой процедуры sp_usercounter, параметром которой служит номер счётчика от 1 до 10. Вы можете сами определять значения для этих счётчиков, которые могут отображать данные, возвращаемые операторами SQL, а также любыми операциями SQL сервера, например, системными хранимыми процедурами. Окно Current Activity, SQL Server Enterprise Manager Одной из составных частей Enterprise Manager является окно
Current Activity (текущие операции), с помощью которого можно
получить данные о процессах, блокировках и текущих
подключениях SQL сервера. Эта информация представляется в
графическом виде и позволяет DBA получить информацию о текущих
соединениях пользователей с сервером, состоянии и блокировках
процессов и операторов и др. объектов, а также позволяет
управлять процессами и блокировками. Мониторинг средствами Transact-SQL Много полезной информации о работе сервера и состоянии его
объектом можно получить включив в запрос специализированные
хранимые процедуры, операторы, переменные или за счёт
использования флагов трассировки. Далее, рассмотрим некоторые глобальные переменные,
позволяющие получить, как конкретные данные, так и статистику
по запросам. Теперь, рассмотрим полезные для мониторинга операторы
T-SQL. Также, много полезного можно почерпнуть с помощью
разнообразных операторов команды DBCC. И в завершение, с помощью представленных ниже флагов
трассировки, можно устанавливать характеристики сервера,
выявлять проблемы в его работе, отлаживать запросы и
процедуры. Установить/снять флаги можно с помощью DBCC
TRACEON/TRACEOFF. Мониторинг средствами SQL Server Profiler Profiler является чрезвычайно мощным инструментом
мониторинга и позволяет следить (трассировать) за операциями
сервера и БД, подключениями и действиями пользователей и
приложений. Для удобства анализа больших потоков информации,
есть возможность использования гибких фильтров и сохранения
результатов трассировки в файл или таблицу. Информация из чёрного ящика MS SQL Server Как в самолёте, у SQL сервера есть "чёрный ящик" (BlackBox.trc). В этом BlackBox динамически хранится и обновляется информация о ста последних событиях сервера баз данных, которые записываются и хранятся в т.н. буфере истории запросов. Если ваш сервер сбойнёт (произойдёт исключительная ситуаций не ниже 17-го уровня), будет предпринята попытка записи содержимого буфера запросов в специальный файл трассировки BlackBox.trc, который, как и все аналогичные файлы, можно будет просмотреть в Profiler, даже если сервер на запущен или когда работоспособность сервера будет восстановлена. Хотя сбор данных для истории запросов практически не вносит дополнительной нагрузки в работу сервера, эта опция, по умолчанию, отключена. Включение/отключение её можно осуществить с помощью расширенной хранимой процедуры xp_trace_setqueryhistory, которая имеет следующий синтаксис: EXECUTE xp_trace_setqueryhistory (0|1) Другая расширенная процедура поможет Вам вручную записать последние 100 запросов в указанный файл. Вот её синтаксис: EXECUTE xp_trace_flushqueryhistory ИМЯ_ФАЙЛА Есть ещё одна специальная программа sqldiag, запуск которой
при включённой истории запросов записывает буфер в файл
\MSSQL7\LOG\Sqldiag.trc Мониторинг средствами SQL Server Query Analyzer Query Analyzer часто используется для детального, поэтапного анализа исполнения запроса, что позволяет составить представление об эффективности его построения. Для этого, существует опция Show Estimated Execution Plan, которая позволяет без реального исполнения запроса показать подробный план его исполнения сервером. В этом плане становятся доступны для анализа эффективности такие его делали, как использование индексов, типы методов доступа (сканирование таблиц), объём работы средств ввода/вывода, временные затраты на выполнение каждого этапа запроса, а также условная стоимость каждого этапа. Кроме того, с помощью Perform Index Analysis можно сгенерировать скрипт T-SQL, который достроит необходимые для оптимальной работы Вашего запроса индексы. Регламент администрирования SQL Server Сопровождение баз данных и непосредственно SQL сервера
требует системного подхода, который должен учитывать все
особенности конкретной реализации информационной системы и её
эволюцию во времени. Большинство баз данных не являются
статичными, и, как это присуще OLTP системам, могут
претерпевать значительные изменения в процессе их
эксплуатации. Рост объёмов данных в таблицах приводит к
увеличению отвлекаемых на поддержание индексов ресурсов и к
существенной фрагментации данных. Это требует периодического
обновления сведений об оптимизации данных и регулярной
реорганизации размещения самих страниц данных. Также, возможно
потребуется изменять коэффициент заполнения - fillfactor, если
автоматический выбор оптимального его значения не приносит
желаемого результата, или первоначальная установка требует
корректировки. Кроме того, существенные размеры базы данных
могут затруднить автоматическое обновление статистики
распределения значения ключа индексов таблиц, используемое
оптимизатором запросов, что, в свою очередь может потребовать
дополнительного использования оператора UPDATE STATISTIC. Если
логика работы системы требует массового удаления данных или
данные удаляются достаточно часто, а также, при переносе
больших кусков базы (например, относящихся к прошлым временным
периодам) в другие местоположения (например, долговременный,
юридический архив), в базе может образоваться не заполненное
место, которое возможно усечь с помощью оператора DBCC
SHRINKDATABASE. Это бывает необходимо, если не задействована
опция автоматического усечения базы - autoshrink. Не мало
важную роль играет также отслеживание целостности данных, в
процессе которого выявляется несогласованность содержимого
базы, появление которой возможно в результате аппаратных или
программных сбоев, а также в следствии сетевых проблем. Для
внутренней проверки целостности данных исследуют распределение
страниц индексов и данных каждой таблицы, образующих целостную
структуру экстентов, используя при этом оператор DBCC
CHACKALLOC. Целостность объектов базы проверяют с помощью DBCC
CHECKDB, а проверку целостности страниц данных (включая тип
text), индексов или страниц индексов заданной таблицы,
выполняют с помощью DBCC CHECKTABLE. Возможно задать
автоматическое исправление обнаруженных нарушений целостности.
Святой обязанностью каждого DBA является создание и
поддержание в актуальном состоянии резервных копий, иначе,
случаев потери данных не избежать. Резервирование журналов
транзакций позволяет, сохранять внесённые в базу данных между
полными копиями изменения. Стандартное руководство рекомендует
также вести подробную историю администрирования СУБД,
включающую информацию о всех выполненных на сервере операциях
и результаты мониторинга. Вопросы для повторения. ВОПРОС | |
Шпаргалка по 70-028 | Мониторинг SQL Server 7.0 | Дальше » |