Solved

checking inventory of indexes

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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 contention 16 49
Help Required 3 107
(sql serv16)ssis 2016 question/check 1 67
SQL Server Shrink hurting performance? 4 16
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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 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