DTA - want to delete dta indexes

Question

DTA - want to delete dta indexes

krypto69

SSChampion

Points: 13604
More actions
October 27, 2016 at 10:39 am

#314986

Hi,
Previous DBA used DTA to create allot of indexes.
I though DTA indexes always skipped (not used) by queries?
But when I run this:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND S.database_id = DB_ID()
I see values for 'user_updates, scans, etc'
I was hoping to remove this mess - but it appears the dat indexes are being used? Or am I missing something?

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

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

krypto69 (10/27/2016)
Hi,
Previous DBA used DTA to create allot of indexes.
I though DTA indexes always skipped (not used) by queries?
But when I run this:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND S.database_id = DB_ID()
I see values for 'user_updates, scans, etc'
I was hoping to remove this mess - but it appears the dat indexes are being used? Or am I missing something?

You're not missing anything. DTA indexes aren't necessarily skipped and they're not necessarily bad.

--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)

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

All indexes suggested by DTA are useful within the context of sample query that was supplied to DTA. The problem is that often times these index suggestions are for niche query that's not commonly used (like for a single use month end report) or for queries that are obsolete and no longer used by the application.

To determine if an index is worth the cost of maintenance, you'll want to compare total seeks + scans + lookups versus total updates. Also look at the datetime related columns to determine how recently the index has been read.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I think you're confusing the indexes created using DTA, with the indexes created during the DTA analysis but usually removed after the process completes. These later indexes are hypothetical and are marked that way in a sys.indexes column.

The following should return an empty set.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID

WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

AND S.database_id = DB_ID()

AND I.is_hypothetical = 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

krypto69 SSChampion Points: 13604 More actions · Answer 4

krypto69

SSChampion

Points: 13604

October 27, 2016 at 12:16 pm

#1909206

Yes.

Thanks everyone most helpful.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Definitely don't want to just drop them all!!

DTA is just HORRIBLY misused in my experience! I once spent almost 200 man hours cleaning up rampant DTA use by a client that when they scaled up their data size and concurrent use essentially made there system unusable. Once I was done average read workload speed was down about 5% but writes, tlog activity and concurrency and some other metrics were all one to two orders of magnitude better.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service