Which of these indexes should be deleted?

Question

Which of these indexes should be deleted?

sm_iransoftware

Ten Centuries

Points: 1228
More actions
January 26, 2015 at 4:41 am

#397929

Hi
I run a query on indxes . and this is 3 records of result :
TBL_1 --------- IX_1 user_seeks = 78 ------- User_Scans = 477 ------User_Lookup = 0 --------- User_Updates = 7707 -- Cost = (seek/update ) = 98
TBL_2 --------- IX_2 user_seeks = 6 ------- User_Scans = 2827 ------ User_Lookup = 0 --------- User_Updates = 314 -- Cost = (seek/update ) = 52
TBL_3 --------- IX_3 user_seeks = 296 --- User_Scans = 70329 ------ User_Lookup = 204635-- User_Updates = 13166 -- Cost = (seek/update ) = 44
TBL_4 --------- IX_4 user_seeks = 2443 --- User_Scans = 0 --- ------ User_Lookup = 0 --------- User_Updates =35000 -- Cost = (seek/update ) =14
part a : (nonclustered)
Ix_1 : Yes it should be deleted .
Ix_2 : has 2877 scan ? deleted ?
Ix_3 : huge lookup and update ? deleted?
part b : (clustered)
we have a record such as IX_3 on clustered index ? what do I do ?
and some times I have a clustered index with 0 seek and n.... update . deleted ? or change ?

Viewing 15 posts - 1 through 15 (of 21 total)

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

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

No way to answer that. Maybe none of them. All of them are getting used, so unless you're very, very, very certain that there will be no performance degradation as a result, leave them all

IX_3 is a clustered index (only get lookups to a clustered index)

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

sm_iransoftware Ten Centuries Points: 1228 More actions · Answer 2

sm_iransoftware

Ten Centuries

Points: 1228

January 26, 2015 at 5:33 am

#1773058

Then what is the best way to get Unused indexes?

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?

A brief search suggested this[/url].

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sm_iransoftware Ten Centuries Points: 1228 More actions · Answer 4

Which search? (select from dmv's or select from taht table)

Phil Parkin SSC Guru Points: 246988 More actions · Answer 5

sm_iransoftware (1/26/2015)
Which search? (select from dmv's or select from taht table)

I have no idea what you mean. Did you check the link?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sm_iransoftware Ten Centuries Points: 1228 More actions · Answer 6

sm_iransoftware

Ten Centuries

Points: 1228

January 26, 2015 at 6:12 am

#1773072

OK Thank you

I did not see link.

I use it Now.

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

Phil Parkin (1/26/2015)
sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?
A brief search suggested this[/url].

And the last sentence of that article is really, really important.

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

darn i missed the link too!, here it is expanded out instead of aliased:

Phil Parkin (1/26/2015)
sm_iransoftware (1/26/2015)
Then what is the best way to get Unused indexes?
A brief search suggested http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

umarrizwan Old Hand Points: 374 More actions · Answer 9

DECLARE @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),

INDEXNAME = I.NAME,

I.INDEX_ID

FROM SYS.INDEXES I

JOIN SYS.OBJECTS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID NOT IN (

SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = @dbid)

ORDER BY OBJECTNAME,

I.INDEX_ID,

INDEXNAME ASC

Phil Parkin SSC Guru Points: 246988 More actions · Answer 10

umarrizwan (1/28/2015)
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC

Some descriptive text would help.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

umarrizwan (1/28/2015)
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC

That will return only indexes which have never been used at all (select insert, update, delete) since the last server restart.

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

umarrizwan Old Hand Points: 374 More actions · Answer 12

indexes which are never used by any plan and Gail is right that by last restart of server.

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

umarrizwan (1/29/2015)
indexes which are never used by any plan and Gail is right that by last restart of server.

By 'never used', you mean never queried and the underlying table never had a data modification.

It's not usually what people mean by 'unused indexes', they're usually looking for indexes that seldom/never get read but get updated a lot

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

umarrizwan Old Hand Points: 374 More actions · Answer 14

if the data of underlying table changed and when we update statistics then plan should use that index.

Am I right?