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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE msdb; GO CREATE TRIGGER NO_DTA_BAD_DTA ON DATABASE FOR CREATE_TABLE AS DECLARE @indexname NVARCHAR(4000); SELECT @indexname = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(4000)') IF (CHARINDEX('DTA_', @indexname) > 0) BEGIN PRINT 'DTA Tables are not permitted to exist.'; ROLLBACK; END GO |
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:
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.)
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 ?
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
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.
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:
https://www.youtube.com/watch?v=mGfBEnBw01A
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).
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
The DTA can indeed implement its recommendations based on your commands. Here’s the documentation on how it works: https://docs.microsoft.com/en-us/sql/relational-databases/performance/view-and-work-with-the-output-from-the-database-engine-tuning-advisor?view=sql-server-ver16
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!
You’re welcome. Just to confirm, did you read the documentation page that I took the time to find for you?
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.”