Alexander Gladchenko

Для оптимального размещения файлов баз данных на дисках необходимо понимать какой объём операций ввода-вывода 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

USE master
GO
DECLARE   @dbid int, @dbname sysname, @fileid int
	, @name sysname, @BytesRead int
	, @BytesWritten int, @filename nvarchar(255)
CREATE TABLE ##IOfilestat 
	( [База данных] sysname
	, [№ файла] int
	, [Логическое имя] sysname
	, [Прочитано (Мб)] int
	, [Записано (Мб)] int
	, [Путь к файлу] nvarchar(255)
	)
DECLARE	  filestats CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT	  saf.dbid
	, saf.fileid
	, saf.name
	, saf.filename
FROM	  sysaltfiles AS saf
WHERE	  saf.dbid NOT IN (1,3,4)
ORDER BY  saf.name

OPEN GLOBAL filestats
WHILE 1 = 1
BEGIN
	FETCH filestats INTO @dbid, @fileid, @name, @filename
	IF @@fetch_status <> 0 BREAK
	INSERT INTO ##IOfilestat 
		(   [База данных]
		,   [№ файла]
		,   [Логическое имя]
		,   [Прочитано (Мб)]
		,   [Записано (Мб)]
		,   [Путь к файлу] 
		)
	SELECT	DB_NAME(@dbid)
		,  @fileid
		,  @name
		, (SELECT BytesRead/1048576 FROM :: fn_virtualfilestats(@dbid, @fileid))
		, (SELECT BytesWritten/1048576 FROM :: fn_virtualfilestats(@dbid, @fileid))
		,  @filename
END
CLOSE GLOBAL filestats
DEALLOCATE filestats
SELECT [База данных],[№ файла],[Логическое имя],[Прочитано (Мб)],[Записано (Мб)],[Путь к файлу]
FROM ##IOfilestat
ORDER BY [Прочитано (Мб)] + [Записано (Мб)] DESC
DROP TABLE ##IOfilestat
GO