Context

One of my favorite quotations from a fictional character comes from “Atlas Shrugged” by Ayn Rand: There is no substitute for competence.

One of the most important things that we need to do as IT professionals is to use our own experience and competence even when taking advice from peers, or those you deem as experts. Even my own advice is sometimes not correct, when taken out of context (You will note if you follow these blog entries that I will attempt a lot of “tongue-in-cheek” humor, please take it that way).

As a public speaker (Users Groups, conventions, SQL Saturdays, etc.) I continually try to challenge those attendees at my talks to rethink some of the decisions they’ve made, based upon facts I give them based upon my own experiences. Frequently, they challenge me back (for me, this is part of the fun).

At a recent talk I gave on indexes, I said something along these lines:
“You may be able to come with some theoretical exceptions to this rule, but from a practical perspective, you should have a clustered index on every table. Without a clustered index, you are creating a hot spot at the tail end of your table’s page chain, (meaning contention when you have multiple insertions, and/or updates which cause rows to remove). In addition, updates which cause rows to move will cause data to be unbalanced towards the tail end of the table’s page chain (as well).”

A point that has come up (on at least two recent occasions) from an audience member was along the following lines:
“ I was reading a paper by {no idea who wrote the paper, if anybody reading this does, please let me know so that I can credit the author), and in it he/she stated that benchmarking has shown that insertions are fastest with NO clustered index on the table because of some benefits of page allocations …”

The first time I heard that, I responded that I’d have to read the article to see what it had to say before responding, but that my recommendations from own extensive practical experience stood. The second time I heard that, something clicked, and I responded with, “Was that a single process performing a data load of some sort, or was that a thousand concurrent processes trying to gain access to a table?”

Context.

If you are trying to load data into a table, your issue isn’t necessarily contention, it is the speed of your disk & corresponding page allocations. If you are trying to give many users access to the table, your issue is going to be both page and index contention, at update time, page split time, etc.

Over time, I’m going to be offering a variety of recommendations, every one of which will be based upon my personal experiences in 20 years of tuning SQL Servers.

Yet, I caution you: everything needs to be taken in context. Test my assertions; make sure that the environments I’m describing actually match your own.