Bad Idea Jeans Week: Prevent the Use of the Database Engine Tuning Advisor

Every now and then, we put on our bad idea jeans in the company chat room and come up with something really ill-advised. We should probably bury these in the back yard, but…what fun would that be? Bad ideas are the most fun when they’re shared.

One day, after seeing enough horrific but well-meaning indexes created in production by the Database Engine Tuning Advisor, we got to brainstorming. Could we set something up that would stop the DETA on a production box, but let it keep running in a development environment? Meaning, not break the contents of a database, but somehow disable DETA at the server level?

Sí, se puede – and the first step is to understand that the DETA creates these tables in msdb when it first fires up:

I see what you did there
I see what you did there

So all we have to do is set up a database trigger inside msdb on the production server. Trigger adopted from an idea from Richie:

This database trigger in MSDB stops the prevention of any tables with DTA in the name. Now, whenever you try to point the DETA at the production server:


Gosh, I can't imagine what the problem might be
Gosh, I can’t imagine what the problem might be

In the immortal words of a timeless philosopher, no no, no no no.

(People who liked this post also liked how I proposed to stop changes to tables.)

Previous Post
Query Tuning Week: How to Run sp_BlitzCache on a Single Query
Next Post
Bad Idea Jeans Week: Building a Fork Bomb in SQL Server

12 Comments. Leave new

  • Nice article …I’ll be grabbing a copy of that code 🙂
    …and I hate to be the picky person who spots the mistakes instead of just enjoying the article as a whole but …

    “This database trigger in MSDB stops the prevention of any tables with DTA in the name”

  • And why not to use policies for the same result ?

  • Michal Piatek
    August 23, 2016 11:57 am

    What’s so bad about using DETA in the first place?

    • There are NUMEROUS reasons why one should not be using the DTA!!

      1) It will cover EVERYTHING, meaning LOTS of INCLUDED columns. All of those come with significant overhead, including IO, fragmentation, locks, latches, stats updates, backup overhead, etc., etc.

      2) It does not check existing indexes. You can get a ton of overlapping and nearly identical indexes.

      A, B
      A Include B
      A, B, C
      A include B, C

      3) It doesn’t care at all about locking, blocking, deadlock potential, the cost of any DML activity performed by the app, etc.

      I have had clients absolutely DESTROY their applications with rampant use of DTA! One client essentially shut down their application at scale when they added a new client to the existing database after years of DTA use. “The Table” in their app had 67 nonclustered indexes that averaged almost 50% of all columns on the table – each! It took me over 200 manhours(!!!) to clean up the mess they created. When I was done total workload read performance was down by about 5% but concurrency and DML activity improvement was through the roof.

  • Kenneth AMBROSE
    August 23, 2016 12:54 pm

    I have found the ability to dynamically create agent jobs very useful when I want to execute TSQL asyncronously.

    I haven’t yet tried to bring down a server using that approach. I will keep it in mind though in case we ever need to do that.

  • For those outside of USA (not wanting to get around Hulu’s restrictions), other official Bad Idea Jeans link is:

  • Some days, I wouldn’t mind this on the dev servers either. Maybe change the PRINT statement to recommend various index tuning resources and training courses 🙂

    Triggers can be fun and nefarious, I once wrote a logon rollback trigger to prevent connections that came from Microsoft Access (QA people clogging it up with huge, full table queries in Access).

  • michael edwards
    July 28, 2022 10:56 am

    Can I just confirm, being a relative newby, that DTA will only ever create *hypothetical* indexes and stats? I believe it is supposed to clean them up after completion, but sometimes this does not seem to occur. So although that can cause a lot of junk in a database, it won’t affect performance / space use as these indexes / stats will never get used. Unless of course someone makes them hyperthetical (or whatever the opposite of hypothetical is!). Thanks

  • michael edwards
    July 29, 2022 4:48 pm

    Thanks Brent. But just to confirm, whatever options you use in the UI version of DTA, it will not actually create permanent stats, indexes or anything else automatically? With the exception that these ‘IsHypothetical’ ones can get left behind if it terminates abnormally? They are after all, “recommendations” Thanks!

  • michael edwards
    July 29, 2022 5:28 pm

    Apologies, I am very lazy and it was some way down! I see that the GUI will never implement changes automagically(!) but the command line utility can with the -a option. Thanks and apologies again!

    “If you want to examine the tuning results first before implementing them, use the Database Engine Tuning Advisor GUI. You can then use SQL Server Management Studio to manually run the Transact-SQL scripts that Database Engine Tuning Advisor generates as a result of analyzing a workload to implement the recommendations. If you do not need to examine the results before implementing them, you can use the -a option with the dta command prompt utility.”


Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.