Solved

checking inventory of indexes

Posted on 2014-10-23
4
77 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 45
Are triggers slow? 7 25
Parsing this XML works but the other one doesn't 9 30
Can a Trigger trigger a Trigger? 4 24
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 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