Solved

checking inventory of indexes

Posted on 2014-10-23
4
72 Views
Last Modified: 2014-10-26
on a big database, and if 3-4 DBAs have worked on it on and off, how do you best track of all indexes deployed so far. are DBAs to document which index was created for what purpose, when etc so others can infer later why an index exists? (instead of going to DMV to check if an index is used or not.. some indexes may be seasonal.. only run on month ends or quarterly etc).

any other thoughts to make this efficient?
0
Comment
Question by:25112
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
ID: 40400191
SELECT * FROM sys.indexes, but this doesn't allow a DBA to store a description value that would help document the index, so they would have had to leave some other documentation such as email, Excel, Word on the context of why the index exists.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 total points
ID: 40400377
I use a table for such documentation.

If you don't want to do that, add extended properties to contain that type of info.  But be sure to do a separate back up of those properties in case the index is dropped.
0
 
LVL 5

Author Comment

by:25112
ID: 40402415
good ideas. thanks.


>> separate back up of those properties
how can you do backup of extended properties?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 333 total points
ID: 40402681
>> how can you do backup of extended properties? <<

I use view sys.extended_properties, backing it up, but be sure to first decode the ids -- major_id, minor_id -- to names in case the id changes.  That may not be an "official" answer, but it works for me :-).
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

18 Experts available now in Live!

Get 1:1 Help Now