Перевод статьи: Top 5 Misleading SQL Server Performance Counters
Счётчики производительность превосходный инструмент для мониторинга и выявления проблем Microsoft SQL Server. Но некоторые счётчики могут ввести вас в заблуждение, потому что они означают не то, что многие думают. Предлагаю рассмотреть некоторые из них.
SQLServer: Buffer Manager\Buffer cache hit ratio
Идея: Процент чтений данных из памяти (вместо диска)
Проблема: Этот счётчик не учитывает «упреждающее чтение». Упреждающее чтение — важный тип физического чтение, который используется для поднятия данных с диска в память. У вас может быть множество чтений с диска и этот счётчик не покажет вам их.
Решение: Собирайте LogicalDisk: Avg Disk Bytes/Read and Write и Avg Disk sec/Read and Write. Они помогут вам понять задержки доступа к диску. Avg Disk bytes поможет определить ситуации очень больших и очень малых операций. Так же для понимания задержек чтений/записи по файлам БД, вы можете иногда собирать информацию из DMV sys.dm_io_virtual_file_stats.
LogicalDisk\Avg. Disk Queue Length
Идея: Оповещение о проблемах диска (появление очередей)
Проблема: Современные хранилища могут работать с большими очередями и в то же время с маленьким временем отклика.
Решение: Собирайте всё те же LogicalDisk: Avg Disk Bytes/Read and Write и Avg Disk sec/Read and Write и sys.dm_io_virtual_file_stats.
SQL Server: Access Methods\Page Splits/sec
Есть несколько видов разрывов страниц:
- Необходимо добавить большую запись в середину страницы
- Нужна новая страница, чтобы добавить запись в конец индекса
Идея: Мониторить первый тип разрывов страниц
Проблема: Счётчик производительности показывает оба типа
Решение: Вы можете собирать определённое событие Extended Events (англ). Так же вы можете собирать информацию о скорости фрагментации ваших индексов и с помощью FILLFACTOR достигнуть компромисса между занимаемым пространством и скоростью образования фрагментации.
SQL Server: Access Methods\Full Scans/sec
Идея: Оповещение о большом количестве полного сканирования таблицы
Проблема: Не все сканирования являются полными. Например, оператор TOP может просканировать только то количество строк, которое указано после него. И не важно сколько просканировал запрос, 15 строк или 15 000 000, счётчик отразит его как «полное сканирование»
Решение: С помощью следующего скрипта вы можете получить информацию о запросах, которые выполняют множество логических чтений
SELECT TOP 20
(SELECT CAST(SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
qs.execution_count AS [#],
qs.total_logical_reads as [logical reads],
CASE WHEN execution_count = 0 THEN 0 ELSE
CAST(qs.total_logical_reads / execution_count AS numeric(30,1))
END AS [avg logical reads],
CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
CASE WHEN execution_count = 0 THEN 0 ELSE
CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,1))
END AS [avg cpu sec],
CAST(qs.total_elapsed_time/1000./1000. AS numeric(30,1)) AS [elapsed sec],
CASE WHEN execution_count = 0 THEN 0 ELSE
CAST(qs.total_elapsed_time / execution_count / 1000. / 1000. AS numeric(30,1))
END AS [avg elapsed sec],
qp.query_plan AS [query execution plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC
OPTION (RECOMPILE);
SQLServer: Locks – Average Wait Time (ms); Lock Wait Time (ms); Lock Waits/sec
Идея: Оповещения о высоком времени ожиданий блокировок
Проблема: Эти счётчики обновляются только тогда, когда ожидание блокировки уже закончилось, не во время самого ожидания. Предложим что вы ожидали 5 минут и вы сможете увидеть эти проблемы только по окончанию ожидания.
Эти счётчики могут привести в замешательство:
Мы видим, что 4 сессии испытывают блокировки, но счётчики показывают нулевые значения
Решение: Собирайте счётчик SQL Server: General Statistics \ Processes Blocked. Он покажет количество сессий, который заблокированы в данный момент.
Если вы хотите узнать больше о блокировках, то можете посмотреть следующий курс Troubleshooting Blocking & Deadlocks for Beginners.
