As we prepare to dig a bit deeper in Storage IOPs requirements, I found that I need an insight into Read and Write%.
Here are the blogs that pre-meditated this post.
- SQL Server – Storage – IO Pattern – Seeks versus Scans
Database \ File Aggregates
We query the sys.dm_io_virtual_file_stats dmv for I/O statistics.
We grouped by database, logical disk, and file type ( rows and log ).
And, we skipped system databases and some of the less interesting databases.
set nocount on; declare @tblDatabaseSkip TABLE ( [name] sysname ) insert into @tblDatabaseSkip ([name]) select 'DBBackup' union select 'DBUtility' union select 'csSchoolContent' union select 'csLogins' union select 'nettraffic_db' union select 'repository' union select 'TestLogShipping' union select 'TSRDestribution' ;with cteBase ( [DB] , [type_desc] , [Drive] , [num_of_reads] , [num_of_bytes_read] , [num_of_writes] , [num_of_bytes_written] , [%writes] , [%byteswritten] ) as ( SELECT [DB] = DB_NAME ([vfs].[database_id]) , [mf].type_desc , [Drive] = LEFT ([mf].[physical_name], 2) , [num_of_reads] = sum([num_of_reads]) , [num_of_bytes_read] = sum([num_of_bytes_read]) , [num_of_writes] = sum([num_of_writes]) , [num_of_bytes_written] = sum( [num_of_bytes_written] ) , [%writes] = cast ( sum([num_of_writes]) * 100.00 / NULLIF( sum([num_of_reads] + [num_of_writes]) , 0 ) as decimal(10, 2) ) , [%byteswritten] = cast ( sum( [num_of_bytes_written] ) * 100.00 / NULLIF( sum( [num_of_bytes_read] + [num_of_bytes_written] ) , 0 ) as decimal(10, 2) ) FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] group by DB_NAME ([vfs].[database_id]) , [mf].type_desc , LEFT ([mf].[physical_name], 2) ) select [DB] , [type_desc] , [Drive] , [num_of_reads] , [num_of_writes] , [%writes] , [num_of_bytes_read] = cast ( ( [num_of_bytes_read] / 1E9 ) as decimal(30, 2) ) , [num_of_bytes_written] = cast ( ( [num_of_bytes_written] / 1E9 ) as decimal(30, 2) ) , [%byteswritten] from cteBase -- skip system databases where DB not in ( 'master', 'model', 'ReportServer', 'ReportServerTempDB' ) -- ignore some user databases and DB not in ( select [name] from @tblDatabaseSkip )
- File Type
- A lot more write transactions, 98%+, than reads
- One to one ratio of Bytes written to Bytes read
- Much more read transactions compared to write transactions
- Less than 5% bytes written compared to bytes read