The Database Tuning Advisor – Schroedinger\’s Sledgehammer
Database performance tuning is bit of a dark art to most people. Hell, even the best of use gets it wrong sometimes, but that’s usually mitigated by experience. Experience of several late nights and busy days trying to bring the production database back to life, but that’s a whole other story.
We were talking the other day about index fragmentation, and the impact of clustered index inserts on non-clustered indexes and database snapshots. (I know, it doesn’t get much more rock’n’roll than that, right ?)
Trawling through a vendor’s database, we happened to take a look at the indexes on certain tables and we could see that most of them are prefixed ‘_dta_’. Oh, the humanity ! The dread hand of the Database Tuning Advisor (DTA) was witnessed, and it sent a shudder though all those present. Either that, or the air-con went dappy again.
The DTA is a VERY useful tool when developing and tuning your databases. You get a database load, run it through the DTA, and it’ll recommend some indexes and perhaps some statistics that may help when you go live. Hoopy, right ? I mean, you’d be pretty excited if there was a tool that can solve all your performance problems (real or imagined) BEFORE you unleash it on your estate, right ?
The answer, as always, is… (altogether now) ‘It Depends’. Not all recommendations are to be taken as gospel, and the DTA output should be taken as a starting point for your eventual implementation. The DTA doesn’t know how your data will change, how many users will be hitting it, and, most of all, it doesn’t know about those last minute ‘enhancements’ requested by the business at the last minute.
In this way, the DTA could possibly be described as Schroedinger’s Sledgehammer.
We’re all familiar with the famous cat that was used in the theoretical experiment. Wait, you’re not ? Well, lookee here : http://en.wikipedia.org/wiki/Schr%C3%B6dinger\’s_cat
TL:DR – You can’t tell what’s happened to the cat without opening the box. Sure, you can guess, but you can’t actually know. Theorise all you like, the cat’s state remains undefined until you observe the system (comprised of the box, the radioactive material, the vial, the hammer, and that poor kitty), and you can’t observe the system until you alter it (ironically, by taking a look to see what’s happened).
It is my contention that the DTA could be seen as a quantum thought experiment, in that you have some knowns (the data / the workload / the design), some suppositions (IO performance / CPU speed / constant workload), but you never really know what will happen until you observe those details in the live system.
(The Copenhagen Interpretation (q.v.) would seem to suggest that your database will be both performant and non-performant at the same time, but we know that not to be the case.)
What we DO know is that there is a performance impact on updating multiple non-clustered indexes for every clustered index, and that there are ways of identifying indexes that haven’t been used at all and are just sitting there taking up your storage and IO (sys.dm_db_index_usage_stats and a plethora of others)
So, there is a risk, as often happens, that the DTA will recommend indexes and stats that turn out to be sub-optimal when implemented in production. Kind of like using a sledgehammer to crack a nut.
By all means, use the DTA to guide you in the right direction. But use it more like a divining rod that points the way than a big stick with which to beat your database into performance.
And if you ever see indexes in a production database that start with ‘_dta_’ spare a thought for the poor developer – they were probably only trying their best, after all !
Back soon…
The Modern Dataist 