List all indexes on an instance

Question

List all indexes on an instance

kingrudedog

Mr or Mrs. 500

Points: 529
More actions
July 6, 2011 at 12:25 pm

#243700

This has been killing me...
I have been searching for a good script to show all indexes on an instance (not a specific database).
using:
"sys.dm_db_index_physical_stats (null,null,null,null)" and "sys.dm_db_index_usage_stats"
These are great for pulling data...but the problem is, is that they don't pull index names or associated table names.
Right now I use these in scripts to run reports, but I have to run scritps against each database (editing the id for each database) which is time consuming.
My scripting skills are amateurish at best, and I have been searching extensively for several days and my brain hurts.
Fellow dba's...come to my rescue (and make me look like a hero in the process!).
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

One way is to define a temp table, then use sp_MSforeachDB to run a query in each database on the instance to insert DB_ID, Object-ID, Index_ID and Index_name into the temp table, then you can query from that.

Be careful of index usage stats, it only shows indexes that have been used in some way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kingrudedog Mr or Mrs. 500 Points: 529 More actions · Answer 2

"Be careful of index usage stats, it only shows indexes that have been used in some way. "

Thanks for the tip...this would explain an anomoly I was looking at earlier.

"One way is to define a temp table, then use sp_MSforeachDB to run a query in each database on the instance to insert DB_ID, Object-ID, Index_ID and Index_name into the temp table, then you can query from that."

This is what I figured. Unfortunately my t-sql skills have only progress to standard selects, joins and filters. But I am kicking my own [insert word here] learning (actually spending hours every day).

Does anyone have a standardized script for this (for entire instance):

-Database name

-Table name

-Index name

-Fragmentation level

-Standard exclusions apply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's not hard.

1) Decide what the temp table must look like and write a CREATE TABLE.

2) Write a query that, for one database, gets the database_id, object_id, index_id, index name for indexes in one database (use the DB_ID() function for the Database name, then it's a single table query

3) Write that select as an insert statement, inserting into the temp table that you defined in step 1

4) Use sp_MSforeachDB to run that statement across all user databases. Form will be this:

EXEC sp_MSforeachDB 'USE ?; IF DB_ID()>4 <insert statement goes here>'

Then you can join that temp table to sys.dm_db_index_physical_stats (which is instance-wide) to get the fragmentation

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

kingrudedog (7/6/2011)
"Be careful of index usage stats, it only shows indexes that have been used in some way since the last time the instance was restarted, the database closed/re-opened or the usage stats were explicitly cleared (or some other stuff Gail probably knows about). "
Thanks for the tip...this would explain an anomoly I was looking at earlier.

Gail knows this, and normally I would not nitpick, but I added the bolded text above to what Gail said in case it plays into the "anomoly" you were seeing.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

kingrudedog Mr or Mrs. 500 Points: 529 More actions · Answer 5

I love this forum...

The level of awesomeness is rediculous...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The usage stats can't be explicitly cleared (it's the wait stats and one other only that can)

But yes, the usage stats show indexes used since that database last started up. That'll be when SQL opened it on startup, or if the DB was attached, restored or brought online. If autoclose is on, that could be very often (that the DB is opened)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

kingrudedog (7/6/2011)
The level of awesomeness is rediculous...

Apologies. We'll try to tone the awesome level down to crazy or below for you. :hehe: 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

kingrudedog (7/6/2011)
I love this forum...
The level of awesomeness is rediculous...

Indeed it is 😀

GilaMonster (7/6/2011)
The usage stats can't be explicitly cleared

Boooo 😛 I thought you could clear those but you're right I was thinking of wait stats...learned something new there. Thanks as always Gail.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

kingrudedog Mr or Mrs. 500 Points: 529 More actions · Answer 9

I will attempt the scripting on my own - I have really been pushing myself improve.

Forgive me if I post updates asking for opinions and corrections.

Thanks!