Как узнать статистику чтения / записи таблицы SQL Server?
Есть ли способ найти статистику по чтению и записи таблиц в SQL Server 2005/2008?
Я специально ищу DMVs/DMFs без использования триггеров или проверок.
Цель здесь состоит в том, чтобы выяснить подходящий коэффициент заполнения для индексов - идея из этой статьи ( Fill Factor Defined).
[ОБНОВЛЕНИЕ] Существует следующий вопрос по ServerFault
Как определить интенсивное чтение / запись таблицы из статистики DMV/DMF
4 ответа
- sys.dm_db_index_physical_stats (размер и фрагментация)
- sys.dm_db_index_usage_stats (использование, количество сканирований / поисков / обновлений и т. д.)
- sys.dm_db_index_operational_stats (текущая активность по индексу)
Помните, что "таблица" означает кластеризованный индекс или "кучу".
Следующий запрос может быть использован для определения количества операций чтения и записи по всем таблицам в базе данных. Этот результат запроса может быть экспортирован в файл CSV, а затем с помощью формул Excel вы можете легко рассчитать соотношение чтения / записи. Очень полезно при планировании индексов на столе
DECLARE @dbid int
SELECT @dbid = db_id('database_name')
SELECT TableName = object_name(s.object_id),
Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
GROUP BY object_name(s.object_id)
ORDER BY writes DESC
Чтобы определить подходящий коэффициент заполнения для индексов таблицы, вам нужно посмотреть на количество происходящих разбиений страницы. Это показано в sys.dm_db_index_operational_stats:
Количество выделенных листьев: общее количество разбиений страниц на уровне листа индекса.
Количество нелистовых выделений: общее количество разбиений страниц выше конечного уровня индекса.
Количество слияний конечных страниц: общее количество слияний страниц на уровне листьев индекса.
После нескольких копаний я увидел несколько постов, в которых говорится, что числа разделенных страниц из DMV не очень полезны (я лично не подтвердил это), но есть также счетчик производительности "число разделений в секунду" (но это только на уровне экземпляра SQL Server).
Я использую эмпирическое правило, согласно которому обычные таблицы используют коэффициент заполнения по умолчанию 90%, высокие таблицы вставки где-то между 70 - 85% (в зависимости от размера строки). Таблицы только для чтения могут использовать коэффициент заполнения 100%
Если у вас есть хороший кластеризованный индекс (т. Е. Постоянно увеличивающийся, уникальный, узкий), то реальными определяющими факторами для фактора заполнения являются то, как обновляется таблица, и типы данных столбцов. Если все столбцы имеют фиксированный размер (например, целое число, десятичное число, число с плавающей запятой, символ) и не могут иметь значения NULL, то обновление не может увеличить объем памяти, необходимый для строки. Учитывая хороший кластеризованный индекс, вы должны выбрать коэффициент заполнения 90+, даже 100, так как разделение страниц не произойдет. Если у вас есть несколько столбцов переменной длины (например, Varchar для хранения имени пользователя), и столбцы редко обновляются после вставки, вы все равно можете сохранить относительно высокий коэффициент заполнения. Если у вас есть данные, которые сильно изменяются по длине (например, пути UNC, поля комментариев, XML), то коэффициент заполнения следует уменьшить. Особенно, если столбцы часто обновляются и растут (как столбцы комментариев). Некластеризованные индексы обычно одинаковы, за исключением того, что ключ индекса может быть более проблематичным (не уникальным, возможно, никогда не увеличивающимся). Я думаю, что sys.dm_db_index_physical_stats дает лучшие показатели для этого, но это после факта. Посмотрите на размер записи avg/min/max, размер фрагмента avg, место на странице avg, использованное, чтобы получить представление о том, как используется индексное пространство. НТН.