Finding out UnUsed/sleeping indexes by srihari nandamuri

Question

Post reply

Finding out UnUsed/sleeping indexes by srihari nandamuri

srihari nandamuri

Say Hey Kid

Points: 661
More actions
January 26, 2009 at 7:29 am

#126948

Finding out UnUsed/sleeping indexes in your database since the SQL Server service started.
Guys have a look at these indexes sleeping in your database with no use.Take corrective action
SELECT OBJECT_NAME(ind.object_id) 'Object Name', ind.name 'Index Name'
FROM Sys.Indexes ind INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJ.type='U' AND ind.name IS NOT NULL AND obj.object_id NOT IN (SELECT object_id FROM Sys.dm_db_index_usage_stats usg
WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id )
ORDER BY 1,2
Comments please....
Srihari Nandamuri

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

srihari nandamuri (1/26/2009)
Take corrective action

What kind of corrective action are you proposing?

Dropping indexes that haven't been used since the last server start is not recommended, unless you're very sure that they're never used. Consider that there may be indexes that support month-end jobs and hence are only needed once a month.

If you want to do this, keep a permanent records of unused indexes (put the contents of the DMV into a real table) and watch them over a period of time and evaluate the indexes over a period of time before dropping them,

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

I agree with Gail... be very careful about removing indexes just because they haven't been used in a week or two.

Also, will that snippet of code tell you if the index was used as a SEEK or SCAN?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Jeff Moden (1/26/2009)
I agree with Gail... be very careful about removing indexes just because they haven't been used in a week or two.
Also, will that snippet of code tell you if the index was used as a SEEK or SCAN?

Never mind... I've been stuck in the 2k world too long... I see where the Sys.dm_db_index_usage_stats view has all sorts of goodies in it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

srihari nandamuri Say Hey Kid Points: 661 More actions · Answer 4

I agree for not just simply removing the unused indexes...

But we can have sql code scan of these index/columns usage and decide why they are and how useful they are.

I just worried on huge number of unnecesary Indexes and also duplicate indexes getting created on a table which finally results in performance delay.

Comments please....

Srihari Nandamuri

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

Oh, one other thing.

This script is only going to find indexes that are never used at all (no seeks, no scans, no lookups, no updates), because it's doing a not in on the index usage stats DMV.

It's not going to show indexes that have not been used for a seek, scan or update, but have been frequently updated as the base table changes. Indexes that are never read or updated are just a waste of disk space. It's ones that aren't used to speed up queries, but are frequently updated that can cause performance problems on inserts, update, deletes.

Jeff Moden (1/26/2009)
Also, will that snippet of code tell you if the index was used as a SEEK or SCAN?

The query, as written won't. The DMV includes that info (as well as info on the number of lookups and the number of updates), but the query as written doesn't check any of that.

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

GilaMonster (1/27/2009)
Jeff Moden (1/26/2009)
Also, will that snippet of code tell you if the index was used as a SEEK or SCAN?
The query, as written won't. The DMV includes that info (as well as info on the number of lookups and the number of updates), but the query as written doesn't check any of that.
Never mind... I've been stuck in the 2k world too long... I see where the Sys.dm_db_index_usage_stats view has all sorts of goodies in it.

Yep... I posted way to quickly... thanks though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)