Solved

Low use Indexes

Posted on 2011-02-25
3
238 Views
Last Modified: 2012-05-11
I need to compile statictics info on useage of all indexes to see which are not being used and then delete them from the system. How can I do that.

I need to monitor index usage for 2 weeks

Using SQL Server 2008 R2
0
Comment
Question by:tantradude
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 34984030
select * from sys.dm_db_index_usage_stats
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34984120
To find unused indexes:
DECLARE  @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT   OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
            INDEXNAME = I.NAME,
            I.INDEX_ID
    FROM     SYS.INDEXES I
            JOIN SYS.OBJECTS O
            ON I.OBJECT_ID = O.OBJECT_ID
    WHERE    OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
        AND I.INDEX_ID NOT IN (
    SELECT S.INDEX_ID
        FROM   SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE  S.OBJECT_ID = I.OBJECT_ID
            AND I.INDEX_ID = S.INDEX_ID
            AND DATABASE_ID = @dbid)
    ORDER BY OBJECTNAME,
            I.INDEX_ID,
            INDEXNAME ASC

Open in new window

0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 34986167
this will list unused indexes:

the condition  (user_seeks + user_scans + user_lookups ) = 0
will retrive indexes with no seeks scans or lookups

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
      , indexname=i.name, i.index_id
      , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
      sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
and  (user_seeks + user_scans + user_lookups) = 0
order by (user_seeks + user_scans + user_lookups) asc
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Powershell v3 - SQLCMD 3 27
SQL Query 2 33
SQL Server Configuration Manager WMI Error 11 19
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question