SQL Server – Storage – IO Pattern – Write%

Background

As we prepare to dig a bit deeper in Storage IOPs requirements, I found that I need an insight into Read and Write%.

 

Referenced Blogs

Here are the blogs that pre-meditated this post.

  1. SQL Server – Storage – IO Pattern – Seeks versus Scans
    https://danieladeniji.wordpress.com/2016/05/03/sql-server-storage-io-pattern-seeks-versus-scans/

 

Database \ File Aggregates

Guide

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.

Code



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 )

Output

FileIOStats-20160503-1029AM

 

Explanation

  1. File Type
    • Log
      • A lot more write transactions, 98%+, than reads
      • One to one ratio of Bytes written to Bytes read
    • Data
      • Much more read transactions compared to write transactions
      • Less than 5% bytes written compared to bytes read

 

One thought on “SQL Server – Storage – IO Pattern – Write%

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s