Академия Специальных Курсов по Компьютерным Технологиям
    Главная страница Послать письмо
 
AskIt.ru  
   
   
   
   
   
   
 
 
  Главная / Заказные курсы / Microsoft SQL Server 2005 для администраторов
 
 

Получить учебные материалы по этому курсу


<-- Назад Читать дальше -->

11.4.11. Объекты Системного монитора для мониторинга работы SQL Server 2005

Анализ производительности SQL Server 2005 средствами Системного монитора, таблица sysperfinfo, Buffer cache hit ratio (Процент попаданий в кэш буфера), Percent Log Used (Процентов журнала использовано), Buffer Manager: Cache Size (pages) (Менеджер буфера: размер кэша в страницах), SQL Server Access Methods: Page Splits/sec (Методы доступа SQL Server: Разбиений страниц в секунду), SQL Server Locks: Average Wait Time (ms) (Блокировки SQL Server: Среднее время ожидания (миллисекунды)), SQL Server Access Methods: Full Scans/sec (Методы доступа SQL Server: Количество операций полного сканирования таблиц в секунду)

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

У счетчиков SQL Server есть интересная особенность: интерфейс для доступа к ним продублирован на уровне самого SQL Server. Просмотреть их можно из кода Transact-SQL, обратившись к специальному представлению sysperfinfo в базе данных master. Использование этого представления может оказаться очень удобным для прямого протоколирования информации о производительности, без необходимости обращения к Системному монитору. К сожалению, большая часть значений счетчиков представлена в sysperfinfo в кумулятивном виде (т. е. эта информация постоянно складывается с момента запуска сервера). Поэтому попытка посмотреть, например, значение счетчика Buffer cache hit ratio (Процент попаданий в кэш буфера) для объекта Buffer manager при помощи запроса вида:

SELECT * FROM sysperfinfo WHERE counter_name = 'Buffer cache hit ratio';

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

Предположим, что вы хотите запротоколировать информацию о количестве транзакций в секунду для базы данных Foodmart. При этом сбор информации должен производиться в течение 1 минуты, а интервал для сбора должен составлять 5 секунд. Код Transact-SQL для решения этой задачи может быть таким:

-- Создаем временную таблицу со столбцами времени и значений счетчика

CREATE TABLE #PerfTable(ts DATETIME, TranPerSec BIGINT);

DECLARE @iVar1 INT, @iVar2 INT, @dStartTime DATETIME

-- Получаем текущее время

SET @dStartTime = getdate();

-- Получаем текущее значение счетчика и ждем 5 секунд

SELECT @iVar2=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Transactions/sec' AND instance_name = 'Foodmart';

WAITFOR DELAY '00:00:05'

-- Запускаем цикл, который будет работать минуту

WHILE getdate() < dateadd(mi,1,@dStartTime)

BEGIN

-- Получаем еще раз значение счетчика (когда прошло 5 секунд)

SELECT @iVar1=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Transactions/sec' AND instance_name = 'Foodmart';

-- Вставляем разницу между двумя значениями в таблицу #PerfTable

INSERT INTO #PerfTable (ts,TranPerSec) VALUES (getdate(), @iVar1 - @iVar2);

-- ... и записываем текущее значение в первую переменную,

SET @iVar2 = @iVar1;

-- Опять ждем пять секунд

WAITFOR DELAY '00:00:05'

END

-- Когда цикл закончится, выводим записанные в таблицу значения

SELECT * FROM #PerfTable;

Далее перечислена информация об объектах SQL Server 2005 в Системном мониторе:

q      Access Methods (Методы доступа) — этот объект представляет большой набор счетчиков по статистике выполнения пользовательских операций (сколько было произведено обращений к индексам, сколько операций полного сканирования таблиц и т. п.);

q      Backup Device (Устройство резервного копирования) — для этого объекта предусмотрен единственный счетчик, который показывает скорость резервного копирования или восстановления;

q      Broker Activation (Активация брокера) — статистика по хранимым процедурам, которые активизируют очереди Service Broker для определенной базы данных;

q      Broker Statistics (Статистика брокера) — общая статистика по производительности Service Broker (количество сообщений в очереди, количество перенаправленных сообщений в секунду и т. п.);

q      Broker/DBM Transport Object (Объект транспорта брокера/зеркального отображения баз данных) — статистика по зеркальному отображению баз данных (DBM, DataBase Mirroring). В этом объекте содержатся счетчики для операций, в которых принимает участие Service Broker (количество сообщений, байт, передаваемых в секунду, и т. п.);

q      Buffer Manager (Менеджер буфера) — это очень важный объект, который предназначен для мониторинга статистики кэша буферов базы данных. Самый важный счетчик, про который уже говорилось, Buffer cache hit ratio (Процент попаданий в кэш буфера);

q      Buffer Partition (Раздел буфера) — статистика по пустым страницам кэша буферов базы данных;

q      CLR — в этом объекте представлена информация по обращениям к сборкам .NET в коде Transact-SQL. В нем предусмотрен единственный счетчик CLR Execution (Выполнение CLR) — время выполнения текущего обращения в микросекундах;

q      Cursor Manager by Type (Менеджер курсоров по типам) — этот объект предназначен для мониторинга производительности при работе с курсорами. В качестве экземпляров этого объекта представлены типы курсоров;

q      Cursor Manager Total (Общее по менеджеру курсоров) — это общая статистика по работе с курсорами, независимо от их типа;

q      Database Mirroring (Зеркальное отображение баз данных) — этот объект (вместе с объектом Broker/DBM Transport Object) предназначен для мониторинга зазеркалированных баз данных. При помощи его счетчика можно узнать скорость передачи данных и размер очереди;

q      Databases (Базы данных) — это, наверное, самый популярный объект Системного монитора для SQL Server. В нем предусмотрено большое количество счетчиков для всех параметров баз данных: размер файлов данных и журналов транзакций, количество активных транзакций и т. п. Очень удобен счетчик Percent Log Used (Процент журнала использован), который можно использовать для мониторинга оставшегося пространства в журнале транзакций;

q      ExecStatistics (Статистика выполнения) — этот объект предназначен для получения информации о выполнении распределенных запросов, вызовов распределенного координатора транзакций, расширенных хранимых процедур и вызовов OLE DB;

q      General Statistics (Общая статистика) — как понятно из названия, это общая статистика работы сервера SQL Server 2005. Например, при помощи счетчика User Connections (Подключения пользователей) можно узнать число пользователей, которые в настоящее время подключены к SQL Server;

q      Latches (Кратковременные блокировки) — статистика по этому специальному типу блокировок. Кратковременные блокировки — это аналоги блокировок, которые налагаются на ресурсы баз данных во избежание проблем. Но кратковременные блокировки налагаются не на страницы в базе данных, а на специальные области в оперативной памяти (например, которые используются для организации страниц в буфере). В основном используется значение счетчика Latch Waits/Sec (Количество ожиданий на установку кратковременных блокировок). Слишком большое значение этого счетчика может говорить о недостатке оперативной памяти для SQL Server;

q      Locks (Блокировки) — это информация о блокировках и взаимоблокировках SQL Server. Если время ожидания для блокировок слишком большое, или при работе пользователей регулярно возникают взаимоблокировки, то транзакции в приложении построены неправильно, и есть повод предъявить претензии разработчикам;

q      Memory manager (Менеджер памяти) — этот объект можно использовать для отслеживания информации о том, как SQL Server распределяет оперативную память между своими подсистемами;

q      Plan Cache (Кэш планов) — этот объект предназначен для получения информации о процедурном кэше, т. е. о кэше, в котором хранятся планы выполнения команд Transact-SQL, хранимых процедур и триггеров;

q      SQL Errors (Ошибки SQL) — для этого объекта предусмотрен единственный счетчик, который показывает количество ошибок в секунду, возникающих при выполнении команд SQL;

q      SQL Statistics (Статистика SQL) — это статистика по выполнению команд SQL на сервере;

q      Transactions (Транзакции) — это статистика по транзакциям на сервере, открытым в настоящий момент;

q      Wait Statistics (Статистика ожиданий) — этот важный объект показывает информацию об ожиданиях на SQL Server. Обычно такие ожидания возникают, когда системе не хватает какого-то ресурса (оперативной памяти, скорости сетевого ввода-вывода и т. п.). Однако в отличие от сервера Oracle, вмешаться и выделить вручную место для определенной области в оперативной памяти вы не можете;

q      User Settable (Настраиваемые пользователем) — это специальный объект, значения для 10 счетчиков которого устанавливаются из кода Transact-SQL при помощи хранимых процедур sp_user_counter1, sp_user_counter2 и т. п.

Свой набор счетчиков предусмотрен и для SQL Server Agent:

q      Alerts (Предупреждения) — этот объект представляет информацию о сработавших предупреждениях SQL Server Agent. В нем есть всего два счетчика: Activated Alerts (Сработавшие предупреждения) (общее количество предупреждений, которые сработали с момента запуска сервера) и Alerts Activated/Minute (Активированные предупреждения в минуту) (количество оповещений, которые сработали за последнюю минуту);

q      Jobs (Задания) — информация о заданиях SQL Server Agent (сколько было выполнено успешно, сколько завершилось с ошибками, сколько работает в настоящее время);

q      Job Steps (Этапы заданий) — статистика по этапам заданий SQL Server Agent;

q      Statistics (Статистика) — для этого объекта предусмотрен единственный счетчик, который показывает, сколько раз SQL Server был перезапущен в автоматическом режиме службой SQL Server Agent.

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

q      Buffer Manager: Buffer Cache Hit Ratio (Менеджер буфера: процент попаданий в кэш) — как уже говорилось, это главный счетчик, который позволяет определить, достаточно ли памяти отведено самому SQL Server. В системах OLTP значение этого счетчика в течение длительного времени работы сервера должно быть выше 90% (в некоторых источниках говорится, что выше 95%). Для хранилищ данных допускаются более низкие значения;

q      Buffer Manager: Cache Size (pages) (Менеджер буфера: размер кэша в страницах) — этот счетчик показывает, сколько памяти выделено под кэш SQL Server. Обратите внимание, что его значение определяется в страницах, поэтому, чтобы получить размер памяти в байтах, полученное значение нужно умножить на 8192. В идеале значение этого параметра должно быть близко к размеру физической оперативной памяти на сервере. Если оно существенно меньше, и на сервере остается много свободной памяти, то возможно SQL Server по каким-то причинам не может использовать оперативную память. Проблема может заключаться в том, что не включено использование AWE, или используется неверная редакция SQL Server, или на сервере настроены статически параметры работы с оперативной памятью;

q      SQL Server Access Methods: Page Splits/sec (Методы доступа SQL Server: Разбиений страниц в секунду) — большое значение этого показателя означает, что при выполнении операций вставки и изменения данных SQL Server приходится выполнять большое количество ресурсоемких операций по разбиению страниц и переносу части существующей страницы на новое место. Таких операций по возможности следует избегать. Проблему можно попытаться решить двумя способами:

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

·                перестроить индексы, увеличив значение параметра Fillfactor. Этот параметр позволяет зарезервировать в страницах индексов свободное место, которое будет использоваться для размещения новых данных, без необходимости производить операции разбиения страниц;

q      SQL Server Locks: Average Wait Time (ms) (Блокировки SQL Server: Среднее время ожидания (миллисекунды)) — этот счетчик показывает, сколько в среднем процессам пользователей приходится проводить в очереди, чтобы наложить на ресурс блокировку. Максимально допустимое значение этого счетчика полностью зависит от вашей задачи, какое-то среднее значение для всех приложений здесь определить сложно. Слишком высокое значение этого счетчика может означать проблемы с блокировками в вашей базе данных. Подробно про работу с блокировками будет рассказываться в разд. 11.5.7;

q      SQL Server Access Methods: Full Scans/sec (Методы доступа SQL Server: Количество операций полного сканирования таблиц в секунду) — этот счетчик определяет, сколько операций полного сканирования таблиц (без обращения к индексам) SQL Server выполняет в секунду. Для хранилищ данных такие операции совершенно нормальны и каких-то подозрений вызывать не должны. Однако если слишком большое количество операций полного сканирования проводится в базе данных, обслуживающей OLTP-систему, то вполне возможно, что индексов в этой базе данных недостаточно. Другая причина слишком высокого значения этого счетчика может заключаться в том, что разработчики предпочли фильтровать данные уже в приложении, а не на сервере. Про оптимизацию системы индексов будет рассказываться в разд. 11.5.5, а про анализ запросов — в разд. 11.5.8.

 

   
   
   
   
   
   
   
   
   
   
 
<-- Назад Читать дальше -->

Получить учебные материалы по этому курсу


 
© 2004-2016, Академия Специальных Курсов
по Информационным Технологиям
.
Все права защищены.

Разработка NevaStudio
г. Санкт-Петербург, Васильевский остров,
20-я линия, д. 7
Офис 101, 2-й этаж
Телефон: 8(812)922-47-60
E-mail: info@askit.ru