SEEK is better then SCAN August 17, 2007
Posted 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
Is this Index useful ? August 17, 2007
Posted by furrukhbaig in DMV's, dm_db_index_usage_stats, Index tuning, Index usefulness, Indexes, Optimize, Performance, Performance Tuning, SQL Server 2005, SQL Server 2005 features, TSQL.Tags: dmv, dm_db_index_usage_stats, index usage, indexes, Performance Tuning, sql 2012 indexes, sql server 2005, SQL server 2008 R2, sql server 2012, sql tips
1 comment so far
Creating indexes on tables always been a tricky question. Creating a index does not mean that optimizer will use that index to solve queries. SQL Server 2005 introduced a dynamic management view sys.dm_db_index_usage_stats to capture the use of indexes by SELECT, INSERT, UPDATE and DELETE. Thus any index or table which does not exists in this view is overhead to system and not useful.
Following query will return all the indexes and tables which never get used by SQL Server since the service started. The best way to check is to create index and run the data work load or regression test that should make use of all indexes and run following query.see full article on SQL Server Customer Advisory Team (CAT) How can SQL Server 2005 help me evaluate and manage indexes
SELECT ObjectName = OBJECT_NAME(ind.object_id), IndexName = ind.name FROM Sys.Indexes ind INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id WHERE OBJECTPROPERTY(obj.object_id,‘IsUserTable’) = 1 AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id ) ORDER BY ObjectName, IndexName