June 2008

You are currently browsing the monthly archive for June 2008.

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

В качестве бонуса – вариант сценария для SQL Server 2000.

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