SEEK is better then SCAN August 17, 2007Posted by furrukhbaig in DMV's, dm_db_index_physical_stats, dm_db_index_usage_stats, Execution Plan, Index tuning, Index usefulness, Indexes, Optimize, Performance, Performance Tuning, Query hint, SEEK vs Scan, SQL Server 2005, SQL Server 2005 features, TSQL, XML Execution Plan.
add a comment
I have been involved in performance tuning for several databases and usually come across a situation where indexes has been created but due to poorly written SQL Code those indexes are not utilized or badly used by optimizer. To find out how optimizer using your index use query below. This will provide you the frequency optimizer is uses SEEK, SCAN and LOOKUP operation on indexes.
As a rule of thumb SEEK is better then SCAN. But there are few scenarios where SCAN operator perform better then SEEK specially when large number of rows are expected to return by operation.SELECT Table_Name = OBJECT_NAME(usg.object_id), ind.name, usg.user_seeks, user_scans, user_lookups, last_user_seek, last_user_scan, last_user_lookup FROM sys.dm_db_index_usage_stats usg INNER JOIN sys.indexes ind ON usg.object_id = ind.object_id AND usg.index_id = ind.index_id WHERE — Your table list goes here OBJECT_NAME(usg.object_id) IN ( ‘Table_Name1’, ‘Table_Name2’ ) ORDER BY Table_Name