SQL Server – Using sample_ms from sys.dm_io_virtual_file_stats

Background

When one queries the sys.dm_io_virtual_file_stats Dynamic Management View, there is a column, sample_ms, that is returned.

Let us see what it means and how we can use it.

 

Lab

sys.dm_io_virtual_file_stats

Query


SELECT  *
FROM    sys.dm_io_virtual_file_stats(1,1)

Output

sys.databases

As the tempdb date is re-created each time SQL Server is restarted, we will query sys.databases and fetch the create_date column.

Query


select 
      [dbname] = tblSD.[name]
    , [createDate] = tblSD.[create_date]

from   sys.databases tblSD

where  tblSD.[name] = 'tempdb'

Output

 

 

Review sample_ms

As the tempdb date is re-created each time SQL Server is restarted, we will query sys.databases and fetch the create_date column.

Query


; with cteSamplems
(
	  [sample_ms]
	, [@@TIMETICKS]
	, [days]
	, [hours]
	, [minutes]
	, [seconds]
	, [milliseconds]
)
as
(
	SELECT 
			  [sample_ms] = sample_ms
			, [@@TIMETICKS] = @@TIMETICKS
			, [Days] = sample_ms / (1000*60*60*24)
			, [Hours] = sample_ms / (1000*60*60) - sample_ms / (1000*60*60*24) * 24
			, [Minutes] = sample_ms / (1000*60) - sample_ms / (1000*60*60) * 60
			, [Seconds] = sample_ms / (1000) - sample_ms / (1000*60) * 60
			, [milliseconds] = sample_ms - sample_ms / (1000) * 1000

	FROM sys.dm_io_virtual_file_stats(1,1)
)
, cteDatabase
(
	  [name]
	, [createDate] 
)
as
(
	select 
			  [dbname] = tblSD.[name]
			, [createDate] = tblSD.[create_date]

	from   sys.databases tblSD

	where  tblSD.[name] = 'tempdb'
)
select 
		  cteS.*

		, [sqlServerInstanceStartDate]
			= cteDatabase.[createDate]

		, [lastSampleDate]
			=  dateadd(ms, cteS.[sample_ms], cteDatabase.[createDate])

		, [getdate()]
			= getdate()

		, [timeSinceLastIOSample]
			= datediff
				(
					  second
					, dateadd(ms, cteS.[sample_ms], cteDatabase.[createDate])
					, getdate()
				)

from   cteSamplems cteS

cross apply cteDatabase


Output

 

Explanation

Somehow ended up with a negative number when we tried computing current date from the date SQL Server re-created tempdb and sample_ms from sys.dm_io_virtual_file_stats table.

It is likely sourced to the fact that there is a lag between when sql server started and when temp_db was availed.

 

Summary

Nothing here, outside of the fact that curiosity killed the cat!

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