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

 

QLogic – Determine IOPs (Using QConvergeConsole CLI [version 1.xxx])

QLogic – Determine IOPs (Using QConvergeConsole CLI)

1) Install QConvergeConsoleCLI

  • Make sure to note the Install Folder

2) Launch QConvergeConsole CLI

  • Available via Menu (QLogicManagementSuite \ QConvergeConsole CLI)
  • Command Line (Install Folder \ qaucli.exe)

3) The Console Application is displayed

  • The Application scans for the QLogic Adapters….
  • And, displays the Main Menu

4) Access “Adapter Statistics”

  • From the main menu, enter 5 to Access “5: Adapter Statistics”
  • From the “FC Statistics”, enter 1 to Access to “1: Display FC Port Statistics”
  • The HBA usually comes with dual ports, choose each port 1 or 2
  • From the “HBA Statistics Menu”, enter 4 to “Display HBA Statistics”

5) The Stats are displayed under the following headings

  • HBA
  • Port Errors
  • Device Errors
  • Reset
  • I/O Count
  • IOPS
  • BPS
  • Time