Hi,
This article is about assessing the index, regarding which one is needed and which one is not. By the way it willbe helpful for intermediate level guys on SQL Server
SQL Server has no magic to improve the performance of your query, it is the correct utilization of indexes which will do the magic for you. We are going to do our assessment based on Dynamic Management View(DMV) which is the out of box feature from SQL Server 2005 on wards.
We should know one common fact regarding DMVs, the informations obtained on a DMV will last upto the restart of that instance. So it is the best practice to store those information on a table periodically.
Here we go, we are going to do our assessment based on below DMV
sys.dm_db_index_usage_stats
SELECT db.name AS [database] , |
ob.name [table], |
ix.name [index], |
dm_ix.user_seeks , |
dm_ix.user_scans , |
dm_ix.user_lookups |
FROM sys.dm_db_index_usage_stats dm_ix |
INNER JOIN sys.databases db ON db.database_id = dm_ix.database_id |
INNER JOIN sys.indexes ix ON ix.object_id = dm_ix.object_id |
INNER JOIN sys.objects ob ON ob.object_id = dm_ix.object_id |
WHERE ob.type = 'U' |
Now we are done, the above SQL will show you the database name, table name, and what are all the index exist etc. Now we should concentrate on below threee columns
- user_seeks
- user_scans
- user_lookups
user_seeks :- A seek is when the index is used, as an index, to retrieve data. The key values and the b-tree that make up the index are referenced to find exact values and locations within the index.
user_scans:- A scan is when the index is used, well, like a stack of papers. They’re gone through one at a time to find the interesting values. Needless to say, in most situations, seeks are preferable to scans.
user_lookups:- Number of bookmark lookups by user queries.
You can filter the results of one database by using database id on the where class.
Feel free to pass your comments and feedback.
