How Can You Find Out If An Index Is Being Used Or Not In SQL Server?

Is there a way to detect and or gather information that show what indexes are being used and how often the are being used in a SQL Server database?
Omega002Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
if you haven't restarted your system for a while the following script will list out the indexes, you have to monitor them for few days bbefore you delete

 SELECT   * FROM      sys.indexes idx          JOIN      sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id                                                       AND idx.index_id = ius.index_id          WHERE     idx.type_desc = 'nonclustered'                    AND ius.user_seeks = ( SELECT   MAX(user_seeks)                                           FROM     sys.dm_db_index_usage_stats                                           WHERE    object_id = ius.object_id                                                    AND type_desc = 'nonclustered' )      
0
 
Forefront_Data_SolutionsCommented:
If you are using SQL Server 2005 or later, there are some useful queries you can use.  Check these out:
http://www.mssqltips.com/tip.asp?tip=1239
0
 
SharathData EngineerCommented:
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Omega002Author Commented:
can I also see this over a period of time like for the last 3 months?
0
 
Omega002Author Commented:
Is there a way to capture Index usage in SQL Server Over a daily, weekly, monthly and quarterly period?
0
 
SharathData EngineerCommented:
No, you cannot. On a regular basis, if you can save the result of the query mentioned in the URL  to a temp table, you can have the history.
0
All Courses

From novice to tech pro — start learning today.