Solved

SQL Indices

Posted on 2012-03-26
7
408 Views
Last Modified: 2012-03-27
We have a large table that is heavily-indexed and I am convinced some of the indices are no longer in use.

Does SQL offer any way to find out when the last time an index was accessed or other means to see how often it is used?

Thanks.
0
Comment
Question by:dthansen
7 Comments
 
LVL 25

Accepted Solution

by:
TempDBA earned 100 total points
ID: 37769738
The following link will help you. If the number of seeks + lookups are very few, you shouldn't bother about keeping and maintaining it. You can easily drop it.
http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/
0
 
LVL 9

Assisted Solution

by:keyu
keyu earned 100 total points
ID: 37769955
0
 
LVL 15

Assisted Solution

by:deepakChauhan
deepakChauhan earned 100 total points
ID: 37770241
Hi,
Use this script to find out index usage.. hope so it is usable for u...


SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('DatabaseName') AND sis.OBJECT_ID = OBJECT_ID('TableName');
GO
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 25

Assisted Solution

by:jogos
jogos earned 100 total points
ID: 37771014
Be aware that the a information of the DMV's (such as  sys.dm_db_index_usage_stats) is initialized by a restart.  So it's important to know if their content spans a fair period.

Even if an index is not used for 2,5 months it can be crusial for a quarterly or yearly report to run in a normal time-frame.  If so it could be worth to see how that index can be less a pain
- only create it in the period you need it
- less included columns that are updated a regularly
- filtered index (2008) so it's smaller but fit's the query it has to speed up
0
 

Author Comment

by:dthansen
ID: 37771362
Looks good.

I will try these out in our next maintenance window in 2 days.

Thanks,
Dean
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 37771549
As noted by others, sys.dm_db_index_usage_stats is the key view.


Btw, I would never maintain an index(es) solely for quarterly or *yearly* report.  That seems wrong to me.  

If it only runs that infrequently (ONCE a YEAR!), let it scan the whole table -- 3 times even if it needs to -- rather than maintain all the index rows for a full year.

Or build the index(es) immediately prior to the qtrly / yearly reporting, then *drop them* when done that period rpting.
0
 

Author Closing Comment

by:dthansen
ID: 37773637
We got a chance to review index usage stats during idle time today. Unfortunately, it did not reveal any indices we could get rid of.

Thank you  all for your feedback!

Dean
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now