Talk about a big herrm "mishap"

Question

Post reply

Talk about a big herrm "mishap"

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
October 1, 2010 at 9:11 am

#157234

I was running the performance dashboard to prepare for our rush period when I found this little gem :
CREATE INDEX missing_index_15196 ON [msdb].[dbo].[DTA_reports_table] ([DatabaseID]) INCLUDE ([TableID], [SchemaName], [TableName])
Talk about your "you seem to have forgotten something a tiny weeny bit important" in that sql release! :w00t:
Should I post this on connect?! :hehe:

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

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736478

October 1, 2010 at 10:01 am

#1230334

yes

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Done

https://connect.microsoft.com/SQLServer/feedback/details/608902/missing-index-es-in-msdb-dbo-dta

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

And here's another one.

DTA crashes even with a simplist query but with a DB with 7000 objects.

https://connect.microsoft.com/SQLServer/feedback/details/608926/dta-unsuable-with-7000-objects-in-db

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

I really hate the DTA. Piece of Marketing if you ask me.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Grant Fritchey (10/1/2010)
I really hate the DTA. Piece of Marketing if you ask me.

Agreed..manually cleaning out all the hypothetical indexes it creates and never cleans up is also a bucket of fun.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Hmm interesting info here.

Got any scripts that finds those indexes or is it just based on filtering on index names?

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

Ninja's_RGR'us (10/1/2010)
Hmm interesting info here.
Got any scripts that finds those indexes or is it just based on filtering on index names?

Yep..there's actually a field for it:

select object_name(object_id) as [Table],Name as [Index] from sys.indexes

where is_hypothetical = 1

This bug was supposedly fixed in SQL 2000 SP3 or so..but I just ran this on one of my 2005 databases and there's a good 14 or so. Eventually I'll run a cursor to go through all databases and just kill them.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I'm on 2008 standard sp 1and there's nothing left.

Thanks for the script.