SQL Server. Статистика использования диска базами данных

SQL Server. Статистика использования диска базами данныхДля оптимального размещения файлов баз данных на дисках необходимо понимать какой объём операций ввода-вывода SQL Server организует для каждого из этих файлов. Для подобных оценок SQL Server располагает всеми необходимыми средствами, о которых и пойдёт речь в этой статье. Приводимые ниже примеры предназначены для использования в SQL Server 2005 и выше. Для применения функции прежних версий ознакомьтесь со статьёй “Соответствия между системными таблицами SQL Server 2000 и системными представлениями SQL Server 2005“.
В первом примере используются старые функции, но работоспособен он только начиная с SQL Server 2005 (в силу отличий в возможностях). Этот пример демонстрирует наиболее ресурсоёмкие по объёму ввода-вывода файлы баз данных, обслуживаемые текущим экземпляром SQL Server 2005. Анализируя результаты исполнения представленного ниже сценария можно понять какие файлы создают наиболее весомую нагрузку, какую нагрузку создают разные базы данных и какие именно операции преобладают для каждого из десяти файлов. В примере используется системная таблицаsysaltfiles и системная функция fn_virtualfilestats, которая возвращает статистику ввода-вывода для файлов базы данных, включая файлы журналов транзакций.

USE master
GO
SELECT TOP 10	DB_NAME(saf.dbid)			AS [База данных]
	,	saf.name				AS [Логическое имя]
	,	vfs.BytesRead/1048576			AS [Прочитано (Мб)]
	,	vfs.BytesWritten/1048576		AS [Записано (Мб)]
	,	saf.filename 				AS [Путь к файлу]
FROM		sysaltfiles				AS saf
JOIN	::	fn_virtualfilestats(NULL,NULL)		AS vfs
ON		vfs.dbid = saf.dbid 
AND		vfs.fileid = saf.fileid
AND		saf.dbid NOT IN (1,3,4)
ORDER BY	vfs.BytesRead/1048576 + BytesWritten/1048576 DESC
GO

Второй пример демонстрирует суммарную нагрузку ввода-вывода всех файлов баз данных экземпляра на каждый диск,  используемый для размещения баз данных. Здесь принято допущение, что каждый логический диск обозначен буквой, т.е. если физические диски смонтированы на каталоги или не обозначены вовсе, представленные в примере сценарий нужно соответствующим образом исправить. Для простоты демонстрации мы ограничимся наиболее типичным случаем монтирования дисков на одну букву. Если на диске несколько разделов, обозначенных каждый своей буквой, это нужно учитывать при анализе результатов, суммируя нагрузку по буквам разделов одного диска. В примере используются новое динамическое административное представление sys.dm_io_virtual_file_stats, которое заменяет функцию fn_virtualfilestats. Кроме того, старую системную таблицуsysaltfiles в примере заменяет общесистемное представление sys.master_files.

SELECT   SUBSTRING(saf.physical_name, 1, 1)		AS [Диск]
       , SUM(vfs.num_of_bytes_read/1048576)		AS [Прочитано (Мб)]
       , SUM(vfs.num_of_bytes_written/1048576)		AS [Записано (Мб)]
FROM	 sys.master_files				AS saf
JOIN	 sys.dm_io_virtual_file_stats(NULL,NULL)	AS vfs
ON	 vfs.database_id = saf.database_id 
AND	 vfs.file_id = saf.file_id
AND	 saf.database_id NOT IN (1,3,4)
AND	 saf.type < 2
GROUP BY SUBSTRING(saf.physical_name, 1, 1)
ORDER BY [Диск]
GO

Автор: Александр Гладченко

Запись опубликована в рубрике В помощь администратору с метками . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: