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

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

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

    Reply
  • Michal Piatek
    August 23, 2016 11:57 am

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

    Reply
    • 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
      A, B
      A Include B
      A, B, C
      A include B, C
      etc
      etc

      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.

      Reply
  • 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.

    Reply
  • For those outside of USA (not wanting to get around Hulu’s restrictions), other official Bad Idea Jeans link is:
    https://www.youtube.com/watch?v=mGfBEnBw01A

    Reply
  • 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).

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Menu
{"cart_token":"","hash":"","cart_data":""}