SQL Server – Storage – IO Pattern – Seeks versus Scans

Background

One of the areas that needs to be considered when preparing IOPs requirements, is the mix of Seeks versus Scans.

Let us see how we compare amount of seeks versus scans on an existing system.

 

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/

Code

Guide

We query  the sys.dm_db_index_usage_stats dmv for index usage statistics.

The table exposes seeks and scans for each index.

By aggregating the data we can get an index of how much seeks and scans we are experiencing.

Code

set nocount on;

declare @tblDatabaseSkip TABLE
(
	  [name] sysname
	, [databaseID]
		as isNull
				(
					  db_id([name])
					, -1
				)
)


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 cteObject
 (
	  [database_id]
	, [object]
	, [seek]
	, [scan]
 )
 as
 (

	 select 

			  [database_id]

			, [object]
				= object_schema_name
					(
						tblIUS.object_id
					)
					+ '.'
					+ object_name
					(
						tblIUS.object_id
					)

			, [random]
				= (
						   tblIUS.user_seeks
					--	+  tblIUS.user_lookups
				  )	

			, [sequential]
				= tblIUS.user_scans


	 from   sys.dm_db_index_usage_stats tblIUS

	 where  tblIUS.database_id not in
				(
					select [databaseID] from @tblDatabaseSkip
				)

)
select 
		  [database]
			= db_name([database_id])

	    , [seek] 
			= sum([seek])

		, [scan] 
			= sum([scan])

		, [%seek] 
			= sum([seek]) * 100
				/ sum
					(
						[seek] + [scan]
					)
		, [%scan] 
			= sum([scan]) * 100
				/ sum
					(
						[seek] + [scan]
					)

from   cteObject

group by
		[database_id]

order by
		(
			sum([seek])
		  + sum([scan])
		) desc


 

Output

SeeksVersusScans-20160503-0305PM

 

Explanation

  1. We can see that when we add user_seeks to user_lookups ( bookmark lookups )  and compare the summed total to user_scans, the seeks percentiles are high
    • We likely have high Index Utilizations, and very little Table Scans

 

One thought on “SQL Server – Storage – IO Pattern – Seeks versus Scans

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