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.
Here are the blogs that pre-meditated this post.
- SQL Server – Storage – IO Pattern – Seeks versus Scans
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.
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
- 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