SQL Server – Database File IO Stats

 

Background

A few days ago we experienced an Incident on of our SQL Server Instances.

It is a set of SQL Instances that we have being watching for a while.

There is a nightly batch job that runs and in this post we will cover one of the tools that we can use to measure IO performance.

 

Lineage

Here are some past posts along those same lines.

  1. SQL Server – List Database Size
    Use sys.master_files
    Published On :- 2017-March-29th
    Link :- Link
  2. SQL Server – Database Size Aggregated By File Type
    Use sys.database-files
    Published On :- 2017-April-7th

    Link :- Link

 

Forward

The posts aforementioned looked at the database size, but as we revised the script we noticed we did not notice material differences in the database size.

And, so we want to see whether there are differences between the amount of IO being digested by the database files as we tuned the code.

 

Instrumentation

Traditionally IO Stats are measured through fn_virtualfilestats.

In v2005, sys.dm_io_virtual_file_stats was introduced.

Lab

Outline

In our Lab exercise we will do the following:

  1. Create Database Objects
  2. Add some baseline data to a couple of tables
  3. Create invocation scripts for calling the original and revised code
    • The invoking script will get the current IO Usage metrics
    • Call the user or revised code
    • Post invocation, read the IO Usage metrics
  4. Compare the Usage Metrics across the System and User Databases touched

 

Database Objects

  1. Table
    • iostat.task
    • iostat.taskstepIOFileStat
  2. View
    • Expose File Stats
      • iostat.vw_IOVirtualFileStats
    • Reporting – Internal
      • iostat.vw_taskstepIOFileStatFirst
      • iostat.vw_taskstepIOFileStatLast
    • Reporting – End User
      • iostat.vw_taskstepIOFileStatSummaryFile
      • iostat.vw_taskstepIOFileStatSummaryDataSpace
  3. Function
    • Reporting
      • iostat.IUDF_taskstepIOFileStatSummaryFile
      • iostat.IUDF_taskstepIOFileStatSummaryDataSpace
  4. Stored Procedure
    • iostat.usp_TraceFileStatsIO
  5. Add Data
    • iostat.task.DML.sql

 

Data Model

ETL Data Mapping

 

iostat.taskstepIOFileStat iostat.vw_IOVirtualFileStats
 Packaging
 taskID  @taskID
 seriesID  @seriesID
 sequence  @sequence
 Database
 dbid  sys.dm_io_virtual_file_stats::database_id
 dbname db_name(sys.dm_io_virtual_file_stats::database_id)
 Data Space ID/ File Group
 dataspaceID  dataspaceID
 File
 fileID   sys.dm_io_virtual_file_stats:fileID
 filename   sys.master_files::name (sys.dm_io_virtual_file_stats::file_id)
 filePhysicalname   sys.master_files::physical_name (sys.dm_io_virtual_file_stats::file_id)
 File Type
 fileTypeID  sys.dm_io_virtual_file_stats::file_id
 fileTypeDesc  sys.master_files::type_desc (sys.dm_io_virtual_file_stats::file_id)
 TimeStamp
 sampleMS  sys.dm_io_virtual_file_stats:: sample_MS
timestamp  getdate()
 Read
 numberReads  sys.dm_io_virtual_file_stats::num_of_reads
bytesRead  sys.dm_io_virtual_file_stats::num_of_bytes_read
 ioStallReadMS sys.dm_io_virtual_file_stats::io_stall_read_ms
 Write
 numberWrites  sys.dm_io_virtual_file_stats::num_of_writes
bytesWritten  sys.dm_io_virtual_file_stats::num_of_bytes_written
 ioStallWriteMS sys.dm_io_virtual_file_stats::io_stall_write_ms
 Aggregates
 ioStallMS  sys.dm_io_virtual_file_stats::io_stall
bytesOnDisk sys.dm_io_virtual_file_stats::size_on_disk_bytes

 

 

Table

Table – iostat.task



if schema_id('iostat') is null
begin

	print 'Create schema [iostat] '

	exec('create schema [iostat] authorization [dbo] ');

end
go


if object_id('[iostat].[task]') is null
begin

	create table [iostat].[task]
	(

		  [taskID] int not null
		, [taskName] nvarchar(120) not null

		, constraint [iostat.PK_Task] 
		  primary key
		  (
			[taskID]
		  )

		, constraint [iostat.uniquePKTaskName] 
		  unique 
		  (
			[taskName]
		  )
		  

	)
end
go


Table – iostat.taskstepIOFileStat


if schema_id('iostat') is null
begin

	print 'Create schema [iostat] '

	exec('create schema [iostat] authorization [dbo] ');

end
go


/*

	drop table [AdminDB].[iostat].[taskstepIOFileStat]

	truncate table [AdminDB].[iostat].[taskstepIOFileStat]

*/

if object_id('[iostat].[taskstepIOFileStat]') is null
begin

	create table [iostat].[taskstepIOFileStat]
	(
  	
		  [taskID] int not null

		, [seriesID] int not null
		, [sequence] tinyint not null
		--, [spid]     int     not null

		, [dbid]   int not null
		, [dbname] sysname not null

		, [dataspaceID]			int not null
		, [fileID]				int  not null
		, [filename]			varchar(600) not null
		, [filePhysicalName]	nvarchar(600) not null


                 , [fileTypeID]			int not null
		 , [fileTypeDesc]		sysname not null

		, [sampleMS]  bigint not null

		, [timestamp] datetime not null
			constraint [defaultTaskstepIOFileStatTimeStamp]
			default getdate()

		, [numberReads] bigint not null
		, [bytesRead]   bigint not null
		, [ioStallReadMS] bigint not null

		, [numberWrites] bigint  not null
		, [bytesWritten] bigint not null
		, [ioStallWriteMS] bigint not null

		, [ioStallMS]		 bigint not null
	
		, [bytesOnDisk]		 bigint not null
	
		, constraint [iostat.PK_TaskStepIOFileStat] 
		  primary key
		  (
			  [taskID]
			, [seriesID]
			, [sequence]
			--, [spid]  
			, [dbid]
			, [dataspaceID]
			, [fileID]
			, [timestamp] desc 
		  )

		, constraint [iostat.FK_TaskStepIOFileStat_Task]
			foreign key
			(
				[taskID]
			)
			references [iostat].[task]
			(
				[taskID]
			)
	)
end
go

/*
  [readLatency]

	alter table [iostat].[taskstepIOFileStat]
		drop column [readLatency];
*/
if not exists
(

	select tblSC.[name]
	from   sys.columns tblSC
	where  tblSC.object_id = object_id('[iostat].[taskstepIOFileStat]')
	and    tblSC.[name] ='readLatency'
)
begin

	print 'Adding Column [iostat].[taskstepIOFileStat].[readLatency]'

	alter table [iostat].[taskstepIOFileStat]
		add [readLatency]
			as (
					         [ioStallReadMS]
					/ NULLIF([numberReads], 0)
			   )	 
		PERSISTED

end
go


/*
  [averageBytesPerRead]

	alter table [iostat].[taskstepIOFileStat]
		drop column [averageBytesPerRead];
*/
if not exists
(

	select tblSC.[name]
	from   sys.columns tblSC
	where  tblSC.object_id = object_id('[iostat].[taskstepIOFileStat]')
	and    tblSC.[name] ='averageBytesPerRead'
)
begin

	print 'Adding Column [iostat].[taskstepIOFileStat].[averageBytesPerRead]'

	alter table [iostat].[taskstepIOFileStat]
		add [averageBytesPerRead]
			as (
					         [bytesRead]
					/ NULLIF([numberReads], 0)
			   )	 
		PERSISTED

end
go

/*
  writeLatency
  	alter table [iostat].[taskstepIOFileStat]
		drop column [writeLatency];
*/
if not exists
(

	select tblSC.[name]
	from   sys.columns tblSC
	where  tblSC.object_id = object_id('[iostat].[taskstepIOFileStat]')
	and    tblSC.[name] ='writeLatency'
)
begin

	print 'Adding Column [iostat].[taskstepIOFileStat].[writeLatency]'

	alter table [iostat].[taskstepIOFileStat]
		add [writeLatency]
			as (
					         [ioStallWriteMS]
					/ NULLIF([numberWrites], 0)
			   )
			 PERSISTED  	 

end
go



/*
  [averageBytesPerRead]

	alter table [iostat].[taskstepIOFileStat]
		drop column [averageBytesPerRead];
*/
if not exists
(

	select tblSC.[name]
	from   sys.columns tblSC
	where  tblSC.object_id = object_id('[iostat].[taskstepIOFileStat]')
	and    tblSC.[name] ='averageBytesPerRead'
)
begin

	print 'Adding Column [iostat].[taskstepIOFileStat].[averageBytesPerRead]'

	alter table [iostat].[taskstepIOFileStat]
		add [averageBytesPerRead]
			as (
					         [bytesRead]
					/ NULLIF([numberReads], 0)
			   )	 
		PERSISTED

end
go

/*
  [averageBytesPerWrite]

	alter table [iostat].[taskstepIOFileStat]
		drop column [averageBytesPerWrite];
*/
if not exists
(

	select tblSC.[name]
	from   sys.columns tblSC
	where  tblSC.object_id = object_id('[iostat].[taskstepIOFileStat]')
	and    tblSC.[name] ='averageBytesPerWrite'
)
begin

	print 'Adding Column [iostat].[taskstepIOFileStat].[averageBytesPerWrite]'

	alter table [iostat].[taskstepIOFileStat]
		add [averageBytesPerWrite]
			as (
					         [bytesWritten]
					/ NULLIF([numberWrites], 0)
			   )	 
		PERSISTED

end
go


View

View – iostat.vw_IOVirtualFileStats


if schema_id('iostat') is null
begin

	print 'Create schema [iostat] '

	exec('create schema [iostat] authorization [dbo] ');

end
go

if object_id('[iostat].[vw_IOVirtualFileStats]') is null
begin

	exec('create view [iostat].[vw_IOVirtualFileStats] as select [shell] = 1/0')

end
go

alter view [iostat].[vw_IOVirtualFileStats] 
as

	select 
			
			  [dbid] = tblSDMIOVFS.[database_id]
			, [dbname] = tblSD.[name]

			, [dataspaceID] = tblSMF.data_space_id
			, [fileID] = tblSDMIOVFS.[file_id]
			, [filename] = tblSMF.[name]
			, [filePhysicalName] = tblSMF.[physical_name]

			, [fileTypeID] = tblSMF.[type]
			, [fileTypeDesc] = tblSMF.[type_desc]

	
			, [sampleMS]
				=tblSDMIOVFS.[sample_ms]

			-- Reads Begin
			, [numberofReads]
				=tblSDMIOVFS.[num_of_reads]

			, [numberofBytesRead]
				=tblSDMIOVFS.[num_of_bytes_read]

			, [ioStallReadMS] 
				= tblSDMIOVFS.[io_stall_read_ms]

			-- Reads End

			-- Writes Begin
			, [numberofWrites]
				=tblSDMIOVFS.[num_of_writes]

			, [numberofBytesWritten]
				=tblSDMIOVFS.[num_of_bytes_written]

			, [ioStallWriteMS] 
				= tblSDMIOVFS.[io_stall_write_ms]

			-- Writes End

			--IOStall
			, [ioStallMS]	
				= tblSDMIOVFS.[io_stall]

			, [bytesOnDisk]	
				=tblSDMIOVFS.size_on_disk_bytes	

	from   sys.dm_io_virtual_file_stats(NULL,NULL) tblSDMIOVFS
	
	inner join sys.master_files tblSMF

			on  tblSDMIOVFS.[database_id] = tblSMF.[database_id]
			and tblSDMIOVFS.[file_id] = tblSMF.[file_id]

	inner join sys.databases tblSD

			on  tblSDMIOVFS.[database_id] = tblSD.[database_id]

go



 

View – iostat.vw_taskstepIOFileStatFirst


if object_id('[iostat].[vw_taskstepIOFileStatFirst]') is null
begin

	exec('create view [iostat].[vw_taskstepIOFileStatFirst] as select [shell] = 1/0')
end
go


alter view [iostat].[vw_taskstepIOFileStatFirst]
as

	select
  	
		  tblIOFS.[taskID] 

        , tblIOFS.[seriesID]
		, tblIOFS.[sequence]

		, tblIOFS.[dbid]   
		, tblIOFS.[dbname] 

		, tblIOFS.[dataspaceID] 
		, tblIOFS.[fileID] 
		, tblIOFS.[filename] 

		, tblIOFS.[sampleMS]  
		, tblIOFS.[timestamp]

		, [numberReads]
		, [bytesRead]   
		, [ioStallReadMS] 

		, [numberWrites] 
		, [bytesWritten] 
		, [ioStallWriteMS]

		, [ioStallMS]		
	
		, [bytesOnDisk]			

	from  [iostat].[taskstepIOFileStat]	tblIOFS

	where  tblIOFS.[sequence] = 1
			  

go

View – iostat.vw_taskstepIOFileStatLast


if object_id('[iostat].[vw_taskstepIOFileStatLast]') is null
begin

	exec('create view [iostat].[vw_taskstepIOFileStatLast] as select [shell] = 1/0')
end
go



alter view [iostat].[vw_taskstepIOFileStatLast]
as

	with cteIOFSMax
	as
	(
		select
			  [taskID] 
			, [seriesID]
			, [dbid]   
			, [dbname] 
			, [dataspaceID]
			, [fileID] 
			, [filename] 

			, [sampleMS]  
				= max([sampleMS])

			, [timestamp]  
				= max([timestamp])

			, [sequence]
				= max(tblIOFS.[sequence])

		from  [iostat].[taskstepIOFileStat]	tblIOFS

		group by
			  [taskID] 
			, [seriesID]
			, [dbid]   
			, [dbname] 
			, tblIOFS.[dataspaceID]
			, [fileID] 
			, [filename] 
		
	)
	select
  	
		  tblIOFS.[taskID] 

        , tblIOFS.[seriesID]
		, tblIOFS.[sequence]

		, tblIOFS.[dbid]   
		, tblIOFS.[dbname] 

		, tblIOFS.[dataspaceID]
		, tblIOFS.[fileID] 
		, tblIOFS.[filename] 

		, tblIOFS.[sampleMS]  
		, tblIOFS.[timestamp]

		, [numberReads]
		, [bytesRead]   
		, [ioStallReadMS] 

		, [numberWrites] 
		, [bytesWritten] 
		, [ioStallWriteMS]

		, [ioStallMS]		
	
		, [bytesOnDisk]			

	from  [iostat].[taskstepIOFileStat]	tblIOFS

	inner join cteIOFSMax cte

			on  tblIOFS.[taskID] = cte.[taskID]  

			and tblIOFS.[dbid] = cte.[dbid]   
			and tblIOFS.[dbname] = cte.[dbname] 

			and tblIOFS.[dataspaceID] = cte.[dataspaceID] 
			and tblIOFS.[fileID] = cte.[fileID] 
			and tblIOFS.[filename] = cte.[filename]  

			and tblIOFS.[sampleMS] = cte.[sampleMS]
			and tblIOFS.[sequence] = cte.[sequence]

go


View – iostat.vw_taskstepIOFileStatSummaryFile

/*
	drop view [iostat].[vw_taskstepIOFileStatSummaryFile]
*/


if object_id('[iostat].[vw_taskstepIOFileStatSummaryFile]') is null
begin

	exec('create view [iostat].[vw_taskstepIOFileStatSummaryFile] as select [shell] = 1/0')
end
go

alter view [iostat].[vw_taskstepIOFileStatSummaryFile]
as


	select
  	
		  tblIOFS.[taskID] 
		, tblTask.taskName
		, tblIOFS.[seriesID]
		, tblIOFS.[dbid]   
		, tblIOFS.[dbname] 
		, tblIOFS.[dataspaceID]

		, tblIOFS.[fileID] 
		, tblIOFS.[filename] 
		, tblIOFS.[filePhysicalName]

		, tblIOFS.[sampleMS]  
		, tblIOFS.[timestamp]
		
		, [timestampDelta]
			= datediff
				(
					  second
					, cteIOFSMin.[timestamp]
					, cteIOFSMax.[timestamp]
				)
		
		, tblIOFS.[sequence]

		, cteIOFSMax.[numberReads]

		, [numberReadsGap]
				= (
					 cteIOFSMax.[numberReads]
					 - cteIOFSMin.[numberReads]
				  )		
					
		, tblIOFS.[bytesRead]   

		, [bytesReadMB]
			= ( tblIOFS.[bytesRead] 
				/ ( 1024 * 1024 ) 
			  )   
			
		, [bytesReadGap]
				= (
					 cteIOFSMax.[bytesRead]
					 - cteIOFSMin.[bytesRead]
				  )			

		, [bytesReadGapMB]
			= (  cteIOFSMax.[bytesRead]
					 - cteIOFSMin.[bytesRead]
			  )	/ ( 1024 * 1024 ) 
			     
				  	
		, tblIOFS.[ioStallReadMS] 

		, tblIOFS.[readLatency]

		, [averageBytesPerRead]
			= (tblIOFS.[averageBytesPerRead])

		, [averageBytesPerReadKB]
			= (tblIOFS.[averageBytesPerRead] / 1000)

		, tblIOFS.[numberWrites] 

		, [numberWritesGap]
				= (
					 cteIOFSMax.[numberWrites]
					 - cteIOFSMin.[numberWrites]
				  )		

		, tblIOFS.[bytesWritten]

		, [bytesWrittenMB]
			= ( tblIOFS.[bytesWritten] 
				/ ( 1024 * 1024 ) 
			  )
			   
		, [bytesWrittenGap]
				= (
					 cteIOFSMax.[bytesWritten]
					 - cteIOFSMin.[bytesWritten]
				  )	

		, [bytesWrittenGapMB]
				= (
					 cteIOFSMax.[bytesWritten]
					 - cteIOFSMin.[bytesWritten]
				  )	
				  / ( 1024 * 1024 ) 
				  			 
		, tblIOFS.[ioStallWriteMS]

		, tblIOFS.[writeLatency]

		, [averageBytesPerWrite]
			= (tblIOFS.[averageBytesPerWrite])

		, [averageBytesPerWriteKB]
			= (tblIOFS.[averageBytesPerWrite] / 1000)

		, tblIOFS.[ioStallMS]		
	
		, tblIOFS.[bytesOnDisk]			

	from  [iostat].[taskstepIOFileStat]	tblIOFS


	inner join [iostat].[vw_taskstepIOFileStatLast] cteIOFSMax 

			on  tblIOFS.[taskID] = cteIOFSMax.[taskID]  
			and tblIOFS.[seriesID] = cteIOFSMax.[seriesID]  
			and tblIOFS.[dbid] = cteIOFSMax.[dbid]   
			and tblIOFS.[dbname] = cteIOFSMax.[dbname] 
			and tblIOFS.[dataspaceID] = cteIOFSMax.[dataspaceID]
			and tblIOFS.[fileID] = cteIOFSMax.[fileID] 
			and tblIOFS.[filename] = cteIOFSMax.[filename]  

			and tblIOFS.[timestamp] = cteIOFSMax.[timestamp]
			and tblIOFS.[sequence] = cteIOFSMax.[sequence]


	inner join [iostat].[vw_taskstepIOFileStatFirst] cteIOFSMin

			on  tblIOFS.[taskID] = cteIOFSMin.[taskID]  
			and tblIOFS.[seriesID] = cteIOFSMin.[seriesID]  

			and tblIOFS.[dbid] = cteIOFSMin.[dbid]   
			and tblIOFS.[dbname] = cteIOFSMin.[dbname] 
			and tblIOFS.[dataspaceID] = cteIOFSMin.[dataspaceID]

			and tblIOFS.[fileID] = cteIOFSMin.[fileID] 
			and tblIOFS.[filename] = cteIOFSMin.[filename]  


	inner join [iostat].[task] tblTask
			on  tblIOFS.[taskID] = tblTask.[taskID]  
	
go



View – iostat.vw_taskstepIOFileStatSummaryDataSpace



if object_id('[iostat].[vw_taskstepIOFileStatSummaryDataSpace]') is null
begin

	exec('create view [iostat].[vw_taskstepIOFileStatSummaryDataSpace] as select [shell] = 1/0')
end
go

alter view [iostat].[vw_taskstepIOFileStatSummaryDataSpace]
as


	select
  	
		  tblIOFS.[taskID] 
		, tblTask.[taskName] 
			
		, tblIOFS.[seriesID]
		, tblIOFS.[dbid]   
		, tblIOFS.[dbname] 
		, tblIOFS.[dataspaceID]

		--, tblIOFS.[fileID] 
	 	--, tblIOFS.[filename] 
		 
		, tblIOFS.[sampleMS]  
		, tblIOFS.[timestamp]
		
		, [timestampDelta]
			= datediff
				(
					  second
					, max(cteIOFSMin.[timestamp])
					, max(cteIOFSMax.[timestamp])
				)
		
		, [sequence]
			= max(tblIOFS.[sequence])

		, [numberReads]
			= max(cteIOFSMax.[numberReads])

		, [numberReadsGap]
				= (
					 sum(cteIOFSMax.[numberReads])
					 - sum(cteIOFSMin.[numberReads])
				  )		
					
		, [bytesRead]
			= sum(tblIOFS.[bytesRead])   

		, [bytesReadMB]
			= ( sum(tblIOFS.[bytesRead]) 
				/ ( 1024 * 1024 ) 
			  )   
			
		, [bytesReadGap]
				= (
					 sum(cteIOFSMax.[bytesRead])
					 - sum(cteIOFSMin.[bytesRead])
				  )			

		, [bytesReadGapMB]
			= (  sum(cteIOFSMax.[bytesRead])
					 - sum(cteIOFSMin.[bytesRead])
			  )	/ ( 1024 * 1024 ) 
			     
				  	
		, [ioStallReadMS] 
			= sum(tblIOFS.[ioStallReadMS])


		, [readLatency]
			= avg(tblIOFS.[readLatency])

		, [averageBytesPerRead]
			= avg(tblIOFS.[averageBytesPerRead])

		, [numberWrites]
			= sum(tblIOFS.[numberWrites]) 

		, [numberWritesGap]
				= (
					 sum(cteIOFSMax.[numberWrites])
					 - sum(cteIOFSMin.[numberWrites])
				  )		

		, [bytesWritten]
			=sum(tblIOFS.[bytesWritten])

		, [bytesWrittenMB]
			= ( sum(tblIOFS.[bytesWritten]) 
				/ ( 1024 * 1024 ) 
			  )
			   
		, [bytesWrittenGap]
				= (
					 sum(cteIOFSMax.[bytesWritten])
					 - sum(cteIOFSMin.[bytesWritten])
				  )	

		, [bytesWrittenGapMB]
				= sum
					(
					 cteIOFSMax.[bytesWritten]
					 - cteIOFSMin.[bytesWritten]
				   )	
				  / ( 1024 * 1024 ) 
				  			 
		, [ioStallWriteMS]
			= sum
			  (
				tblIOFS.[ioStallWriteMS]
			  )


		, [writeLatency]
			= avg(tblIOFS.[writeLatency])

		, [averageBytesPerWrite]
			= avg(tblIOFS.[averageBytesPerWrite])

		, [averageBytesPerWriteKB]
			= avg(tblIOFS.[averageBytesPerWrite]/1000)

		, [ioStallMS]		
			= sum
			  (
				tblIOFS.[ioStallMS]
			  )	

		, [bytesOnDisk]
			= sum
			  (
				tblIOFS.[bytesOnDisk]			
			  )

	from  [iostat].[taskstepIOFileStat]	tblIOFS


	inner join [iostat].[vw_taskstepIOFileStatLast] cteIOFSMax --cteIOFSMax

			on  tblIOFS.[taskID] = cteIOFSMax.[taskID]  
			and tblIOFS.[seriesID] = cteIOFSMax.[seriesID]  
			and tblIOFS.[dbid] = cteIOFSMax.[dbid]   
			and tblIOFS.[dbname] = cteIOFSMax.[dbname] 
			and tblIOFS.[dataspaceID] = cteIOFSMax.[dataspaceID]
			and tblIOFS.[fileID] = cteIOFSMax.[fileID] 
			and tblIOFS.[filename] = cteIOFSMax.[filename]  

			and tblIOFS.[timestamp] = cteIOFSMax.[timestamp]
			and tblIOFS.[sequence] = cteIOFSMax.[sequence]


	inner join [iostat].[vw_taskstepIOFileStatFirst] cteIOFSMin

			on  tblIOFS.[taskID] = cteIOFSMin.[taskID]  
			and tblIOFS.[seriesID] = cteIOFSMin.[seriesID]  

			and tblIOFS.[dbid] = cteIOFSMin.[dbid]   
			and tblIOFS.[dbname] = cteIOFSMin.[dbname] 
			and tblIOFS.[dataspaceID] = cteIOFSMin.[dataspaceID]

			and tblIOFS.[fileID] = cteIOFSMin.[fileID] 
			and tblIOFS.[filename] = cteIOFSMin.[filename]  

	inner join [iostat].[task] tblTask
			on  tblIOFS.[taskID] = tblTask.[taskID]  
	
	group by			
		  tblIOFS.[taskID] 
		, tblTask.[taskName] 
		, tblIOFS.[seriesID]
		, tblIOFS.[dbid]   
		, tblIOFS.[dbname] 
		, tblIOFS.[dataspaceID]

		, tblIOFS.[sampleMS]  
		, tblIOFS.[timestamp]

go


Function

iostat.IUDF_taskstepIOFileStatSummaryFile



if object_id('[iostat].[IUDF_taskstepIOFileStatSummaryFile]') is null
begin

	exec('create function [iostat].[IUDF_taskstepIOFileStatSummaryFile]() returns table as return ( select [shell] = 1/0)')
end
go


alter function [iostat].[IUDF_taskstepIOFileStatSummaryFile]
(
	  @dbname sysname
	, @lastRunOnly	bit
)
RETURNS TABLE
as
RETURN
(

	select itvfSF.*

  	from   [iostat].[vw_taskstepIOFileStatSummaryFile] itvfSF with (nolock)
	
	where  itvfSF.[dbname] = isNull(@dbname, itvfSF.[dbname])

	and    (
	
				    ( @lastRunOnly = 0)

				or (
							( @lastRunOnly = 1)

						and (
								itvfSF.[seriesID] =
									(
										select max(itvfSF_Inner.[seriesID])

  										from   [iostat].[vw_taskstepIOFileStatSummaryFile] itvfSF_Inner

										where  itvfSF.[taskID] = itvfSF_Inner.[taskID]

									)	
							)

				   )


			)

)	
go


iostat.IUDF_taskstepIOFileStatSummaryDataSpace



if object_id('[iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]') is null
begin

	exec('create function [iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]() returns table as return ( select [shell] = 1/0)')
end
go


alter function [iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]
(
	  @dbname sysname
	, @lastRunOnly	bit
)
RETURNS TABLE
as
RETURN
(

	select itvfSF.*

  	from   [iostat].[vw_taskstepIOFileStatSummaryDataSpace] itvfSF with (nolock)
	
	where  itvfSF.[dbname] = isNull(@dbname, itvfSF.[dbname])

	and    (
	
				    ( @lastRunOnly = 0)

				or (
							( @lastRunOnly = 1)

						and (
								itvfSF.[seriesID] =
									(
										select max(itvfSF_Inner.[seriesID])

  										from   [iostat].[vw_taskstepIOFileStatSummaryFile] itvfSF_Inner

										where  itvfSF.[taskID] = itvfSF_Inner.[taskID]

									)	
							)

				   )


			)

)	
go

 

Add Data

Table – iostat.task


set nocount on
go

insert into [iostat].[task]
( [taskID], [taskName])
values (1, 'Daily Load - Uses Common Table Expressions (CTE)')
go

insert into [iostat].[task]
( [taskID], [taskName])
values (2, 'Daily Load - Uses Temp Tables')
go

Stored Procedure

iostat.usp_TraceFileStatsIO


if schema_id('iostat') is null
begin

	print 'Create schema [iostat] '

	exec('create schema [iostat] authorization [dbo] ');

end
go

if object_id('[iostat].[usp_TraceFileStatsIO]') is null
begin

	exec('create procedure [iostat].[usp_TraceFileStatsIO] as select [shell] = 1/0')

end
go

alter procedure [iostat].[usp_TraceFileStatsIO]
(
	  @taskID	    smallint
	, @isFirstEvent bit = 0
)
as
begin

	set XACT_ABORT on;
	set nocount on;

	declare @sequence smallint;
	
	declare @seriesIDLastAssigned bigint;
	declare @seriesID			  bigint;

	declare @timestamp			  datetime
	
	set @timestamp = getdate()		

	if (
			    (@isFirstEvent != 0)
			and (@isFirstEvent != 1)
		)
	begin

		raiserror('@isFirstException is a bit field', 16, 1)

	end

	if (@isFirstEvent = 1)
	begin

		select 
				  @sequence = 1
				, @seriesIDLastAssigned = max(tblIOFS.[seriesID])

		from [iostat].[taskstepIOFileStat] tblIOFS
		
		where tblIOFS.[taskID] = @taskID

		set @seriesID = isNull(@seriesIDLastAssigned, 0) + 1

	end
	else
	begin

		select 
				    @seriesID = [seriesID]
				  , @sequence = isNull([sequence], 1) + 1


		from [iostat].[taskstepIOFileStat]

		where 
			-- get the last event from this Task and SPID
			[seriesID] = 
						(
							select max([seriesID]) 
							from   [iostat].[taskstepIOFileStat] 
							where  [taskID] = @taskID
						)

	end

	insert into  [iostat].[taskstepIOFileStat]
	(
	   [taskID]
	  ,[seriesID]
      ,[sequence]

      ,[dbid]
      ,[dbname]
	  
	  ,[dataspaceID]
      ,[fileID]
      ,[filename]
	  ,[filePhysicalName]
      
	  , [fileTypeID]
	  , [fileTypeDesc]

	  , [sampleMS]
      , [timestamp]
      
	  ,[numberReads]
      ,[bytesRead]
      ,[ioStallReadMS]
      
	  ,[numberWrites]
      ,[bytesWritten]
      ,[ioStallWriteMS]
     
	  ,[ioStallMS]
      ,[bytesOnDisk]
	
	)

	select 

	    @taskID
	  , @seriesID
      , @sequence
      
	  , [dbid]
      , [dbname]
	  
	  , [dataspaceID]
      
	  , [fileID]
      , [filename]
	  , [filePhysicalName]
     

	  , [fileTypeID]
	  , [fileTypeDesc]
	  
	  , [sampleMS]
      , @timestamp
      
	  , [numberofReads]
      , [numberofBytesRead]
      , [ioStallReadMS]
      
	  , [numberofWrites]
      , [numberofBytesWritten]
      , [ioStallWriteMS]
      
	  , [ioStallMS]
      , [bytesOnDisk]


	from    [iostat].[vw_IOVirtualFileStats] vwIOFS

end

go

Sample Payloads

Issue Queries related to Task 1

declare @taskID int
declare @isFirstEvent bit 

set @taskID = 1
set @isFirstEvent = 1

exec [AdminDB].[iostat].[usp_TraceFileStatsIO]
          @taskID = @taskID
	, @isFirstEvent = 1


-- execute SQL task
-- Issue queries related to Task1

exec [AdminDB].[iostat].[usp_TraceFileStatsIO]
	  @taskID = @taskID
	, @isFirstEvent = 0


 

Issue Queries related to Task 2

declare @taskID int
declare @isFirstEvent bit 

set @taskID = 2
set @isFirstEvent = 1

exec [AdminDB].[iostat].[usp_TraceFileStatsIO]
          @taskID = @taskID
	, @isFirstEvent = 1


-- execute SQL task
-- Issue queries related to Task1

exec [AdminDB].[iostat].[usp_TraceFileStatsIO]
	  @taskID = @taskID
	, @isFirstEvent = 0


Sample Report Queries

Granularity – File


declare @dbname sysname
declare @lastRunOnly bit

set @dbname = 'tempdb'
set @lastRunOnly =1

select

		  itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[filePhysicalName]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryFile] 
(
	  @dbname
	, @lastRunOnly 
) itvf 


set @dbname = 'Assist'

select

		  itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[filePhysicalName]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryFile]
(
	  @dbname 
	, @lastRunOnly 
) itvf

set @dbname = 'AssistDW_20170405'

select

			itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[filePhysicalName]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryFile]
(
	  @dbname 
	, @lastRunOnly 
) itvf



Granularity – DataSpace


use [AdminDB]
go

set transaction isolation level read uncommitted
go

declare @dbname sysname
declare @lastRunOnly bit

set @dbname = 'tempdb'
set @lastRunOnly =1

select

		  itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]
(
	  @dbname 
	, @lastRunOnly 
) itvf  

set @dbname = 'Assist'

select

			itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]
(
	  @dbname 
	, @lastRunOnly 
) itvf  


set @dbname = 'AssistDW_20170405'

select

			itvf.taskName
		, itvf.seriesID
		, itvf.[dbname]
		, itvf.[dataspaceID]
		, itvf.[timestamp]
		, itvf.[timestampDelta]
		, itvf.[bytesReadGapMB]
		, itvf.[bytesWrittenGapMB]

from   [iostat].[IUDF_taskstepIOFileStatSummaryDataSpace]
(
	  @dbname 
	, @lastRunOnly 
) itvf  




Report

Granularity – File

Granularity – DataSpace

 

Explanation
  1. Tempdb
    • Granularity – File
      • Log ( dataspaceID=0 )
        • Uses CTE
          • 0 MB
        • Uses TempDB Temp Tables
          • 1 MB
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • Averages 18 MB just about evenly over 4 files
          • Data Written and read are same
        • Uses TempDB Temp Tables
          • Averages 15 MB evenly across 4 files
          • Data Written and read are same
    • Granularity – Grouped By DataSpaceID
      • Log ( dataspace=0)
        • Uses CTE
          • 0 MB
        • Uses TempDB Temp Tables
          • 1 MB
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • 72 MB
          • Data Written and read are same
        • Uses TempDB Temp Tables
          • 62 MB read & 60 Mb written
  2. Assist
    • Granularity – File
      • Log ( dataspaceID=0 )
        • Uses CTE
          • 0 MB
        • Uses TempDB Temp Tables
          • 0 MB
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • 171 MB
        • Uses TempDB Temp Tables
          • 255 MB
    • Granularity – Grouped By DataSpaceID
      • Log ( dataspace=0)
        • Uses CTE
          • 0 MB
        • Uses TempDB Temp Tables
          • 0 MB
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • 171 MB Written, None Read
        • Uses TempDB Temp Tables
          • 255 MB Written, None Read
  3. AssistDW
    • Granularity – File
      • Log ( dataspaceID=0 )
        • Uses CTE
          • 0 MB Read, 376MB read
        • Uses TempDB Temp Tables
          • 2 MB Read, 177MB read
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • 0 MB Read, 120 MB
        • Uses TempDB Temp Tables
          • 3 MB, 109 MB written
    • Granularity – Grouped By DataSpaceID
      • Log ( dataspace=0)
        • Uses CTE
          • 0 MB Read, 376MB Written
        • Uses TempDB Temp Tables
          • 2 MB Read, 177MB Written
      • TempDB Data File  ( dataspaceID = 1 )
        • Uses CTE
          • 0 MB Read, 120 MB Written
        • Uses TempDB Temp Tables
          • 3 MB Read, 109 MB Written

Source Code

GitHub

Link

 

Summary

I am so sorry that I have so much SQL Code in this post.

Please feel free to skip and just access the GitHub repository.

When we manage temp table explicitly rather than have Common Table Expression (CTE) do so implicitly, this is what we observe when we look at IO

  1. TempDB
    • Reads & Writes are about the same
    • We use just about 0 log storage
    • We use slightly less TempDB with our explicit temp tables
  2. Source DB
    • No Reads on the Log files
    • For Data Files about 65% less IO on CTE queries
  3. Destination DB
    • No Reads on Log files
    • About 300% more IO on Log writes
    • About 160% more IO For data writes

Timing wise cte took 220 seconds and temp tables took 47 seconds.

The timing comparison means cte took about 4 times as long.

Once again, sorry for the cluttered post!

 

References

  1. Dynamic Management Views
    • sys.fn_virtualfilestats (Transact-SQL)
    • sys.dm_io_virtual_file_stats

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