Sunday, 5 June 2016

(W)indexes

It’s no secret that I am a huge fan of MS SQL Server. I’m not in the 1% of users answering SQL Server questions on StackOverflow for nothing…

SQL Server has come a long way over the years… Back in the day, indexes were decided upon with a method somewhere between best guesses and reading tea leaves… It required a lot of intimate knowledge of the system and was largely just a mystery to most developers.

With the introduction of Dynamic Management Views (DVMs) like sys.dm_db_index_usage_stats, this process became a LOT more trivial. With the right amount of scripting and being fortunate enough to learn from the work done by people like Brent Ozar, it became trivial to essentially ‘ask’ SQL Server what indexes it felt would be useful.

For a lot of SQL developers (and even some DBAs), it can become easy to just rely on these scripts to tell you when an index is needed. But then we go right back to it being just some Dark Art that no one really understands…

So with that in mind, why not just make SQL Server capable of managing index creating/deletion on its own? I mean, it already has usage stats… Why can’t it just do it? And then if someone wants to get more hands-on with the indexes, fine… give an option to override the automatic (automagic?) indexing process. But I think a lot of users without a dedicated DBA would really appreciate that sort of thing.

And I’m not trying to point fingers, Microsoft, but I’m pretty sure MySQL is able to do that sort of thing already… Just sayin’…