Solved

checking inventory of indexes

Posted on 2014-10-23
4
75 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:Scott Pletcher
Scott Pletcher 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:
Scott Pletcher 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

815 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

11 Experts available now in Live!

Get 1:1 Help Now