SQL Server - Identify Unused Tables & Indexes

Who is online?  292 guests and 0 members
home  »  blogs  »  Ritesh Medhe  »  SQL Server - Identify Unused Tables & Indexes
  Rate This Blog Entry:  register  or  login

Author

riteshmedhe Ritesh Medhe (Member since: 3/31/2011 11:34:25 AM)
Ritesh Medhe is a Microsoft Certified DBA & a BI professional with over 8 years of experience. Expertise: Performance optimization & SQL Server Administration

View Ritesh Medhe 's profile

Comments (8)

shannon lowder
1/10/2012 12:53:35 PM shannon lowder said:
I believe this query wouldn't be able to tell if your heaps are used or not, since they wouldn't have a clustered index in the sys.indexes table. I think that was the piece I was missing when I went searching for the same thing. Can you verify it reports correctly on tables without a clustered index?
by
Kyle
1/10/2012 5:10:08 PM Kyle said:

Just add i.type_desc as one of the selected fields and it shows it works on heap and nonclusterd indexes. You'll need to test it to determine if its "reporting correctly". Tongue out

by
riteshmedhe
1/11/2012 1:05:12 PM Ritesh Medhe said:

Hi Shannon, The script does show unused heaps, Script would return <unused table> under column index name no matter whether the table is heap/have CI or NCI  

by
riteshmedhe
1/11/2012 1:05:59 PM Ritesh Medhe said:

Thanks Kyle 

by
user622069
1/11/2012 4:11:34 PM user622069  said:

Just one problem:  sys.dm_db_index_usage_stats is initialized to empty whenever the SQL Server service starts, whenever a database is detached or is shut down.  This means index utilization stats are only valid since the last time the SQL Server service was started.

Is is very possible to have indexes that support reports which are only run periodically (monthly, quarterly or annually).  These could be mistakenly dropped because they appear to be unused.

I agree completely that such objects can be dropped ONLY after consulting with the team members who support the application

by
Grant McAllister
1/11/2012 7:42:49 PM Grant McAllister said:

I have just been studying the DMV sys.dm_db_index_usage_statsused here this week for just this purpose as well as the DMV's for possible missing indexes.

I don't think you can really just use the existance or not of the index within the DMV as it may have been used for a system scan to update the statistics! I was looking to use the columns within the DMV to assess how much the index is used as a candidate for deletion could be used seldomly and infrequently (after ignoring the system accesses).

by
AmitBansal
1/17/2012 12:00:07 AM Amit Bansal said:
Good reading; the post and comments
by
A
1/19/2012 2:24:43 AM A said:

Amit,

How would you get all unused indexes in a single table instead of all databases?

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Ritesh Medhe's latest blog posts

  • We have heard people talking about big data especially DWH folks. Do we really know the definition of BIG Data? Majority of us would attribute big data solely to the size of data. Well to know the rea...
  • This white paper will navigate you through the process of exposing SSAS cubes over port 80 Prequisites : SSAS server must have IIS installed (if both the servers are different you might need to setup ...
  • It's a known fact that an ideal Production DBA is like a parent to 100's of children. By children I mean database servers, and yes, they are like babies to DBAs. No matter how unpleasant your child is...
  • Hello Guys, We all are aware that monitoring a database server for that matter any server is of prime importance in order to ensure business continuity. I will be talking only from database perspectiv...
  • It often happens that DBA’s/Developers end up creating database objects(mostly tables) during development phase for various reasons like intermediate testing or backup . Such objects often end u...
  • I always say that database sizing is one of the prime tasks and concern for any DBA. As a DBA you should ensure that there is no single record claiming space in the databasefor no reason. DBA's should...
  • It is always a big challenge to maintain application’s performance optimum. Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report th...
  • Scenario: Database sizing is one of the most critical tasks for any DBA. One of the DBA’s tasks is to also ensure optimum performance. Database/table size is one of the key factors impacting the...
Blogs RSS Feed

Latest community blog posts

  • Hi Friends, In this blog post, let’s have an insight on how Auditing can be done in SSIS packages? The basic auditing in package includes measures like How many rows were inserted, updated or de...
  • Hi SQL Geeks, Here are the blog posts by Piyush Bajaj for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at ht...
  • Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at htt...
  • Hi Friends, Are you facing any issue on SQL Server 2014 while running the same query which is running fine on previous version of SQL Server? If answer is yes then this may be due to the change in SQL...
  • I have written a SQL Server Change Data Capture GUI interface to facilitate for CDC operations. The project is hosted here https://sqlcdcapp.codeplex.com/ . Feel free to download, review and suggest c...
  • Recently I came across an interesting deadlock scenario I wasn’t aware off. I didn’t have idea that foreign key constraint can also result in deadlocks. The detailed steps to replicate the...
  • Hi Friends, In my earlier blog, I just explain about the importance of filtered indexes. Link for that blog is mention below: http://sqlservergeeks.com/blogs/princerastogi/personal/599/filtered-indexe...
  • A few words about the technology of our site. It's easy to take technology for granted, to focus on content and pay no attention to how it is being delivered. Typically we would encourage this focus. ...