Для оптимального размещения файлов баз данных на дисках необходимо понимать какой объём операций ввода-вывода SQL Server организует для каждого из этих файлов. Для подобных оценок SQL Server располагает всеми необходимыми средствами, о которых и пойдёт речь в этой статье. Приводимые ниже примеры предназначены для использования в SQL Server 2005 и выше. Для применения функции прежних версий ознакомьтесь со статьёй “Соответствия между системными таблицами SQL Server 2000 и системными представлениями SQL Server 2005“.
В первом примере используются старые функции, но работоспособен он только начиная с SQL Server 2005 (в силу отличий в возможностях). Этот пример демонстрирует наиболее ресурсоёмкие по объёму ввода-вывода файлы баз данных, обслуживаемые текущим экземпляром SQL Server 2005. Анализируя результаты исполнения представленного ниже сценария можно понять какие файлы создают наиболее весомую нагрузку, какую нагрузку создают разные базы данных и какие именно операции преобладают для каждого из десяти файлов. В примере используется системная таблицаsysaltfiles и системная функция fn_virtualfilestats, которая возвращает статистику ввода-вывода для файлов базы данных, включая файлы журналов транзакций.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
