Debunking myths about clustered indexes - part 4 (CIXs, TPC-C & Oracle clusters)
This is the fourth blog post in a series I am currently writing in an attempt to de-bunk the myth that "clustered indexes (CIXs) should be created on every table". I'm going to take a moment here to clarify that I'm NOT saying CIXs should NEVER be used - they certainly can be a useful tool in certain circumstances and I definitely use them regularly. To state that everyone should run around suddenly removing them would be equally as thoughtless as the argument I'm trying to counter - that every table should have one mindlessly installed.
My main argument is that, like all tools, CIXs should be used thoughfully & strategically rather than blindly installed everywhere. CIXs do have an associated cost, which often out-weighs their "benefits" & therefore, they shouldn't necessarily be used on all tables.
If you choose to write to me re this discussion (as some already have), please bear this in mind first. I'd also encourage anyone who wants to discuss this with me to do so here in public - no-body's going to bite & the public discussion might actually benefit others.
In case you've missed my earlier blog posts on this topic, here they are:
Debunking myths about clustered indexes
Part 1 (CIXs worsen bookmark lookups)
Part 2 (CIXs are NOT better for range scans)
Part 3 (example script)
In this post, I'm going to discuss how Microsoft have recently used ClXs on their largest TPC-C benchmark to date, comparing the indexing strategy used to that used by Oracle in their largest TPC-C benchmark & drawing a conclusion that MS might not have used their indexing features as effectively as they could have to achieve the best TPC-C result possible.
Before reading on, I'd encourage you to visit each of these benchmark results to review the outcomes - Oracle 10g achieved ~1.6M tpmC whilst SQL Server 2005 achieved ~ 1.23M tpmC.. Interestingly, Oracle achieved their larger result with only 16 physical CPUs (32 cores) vs SQL Server's 64 physical CPUs (64 cores). 1Tb RAM was used in each benchmark. It should probably also be mentioned that presently, these are the 3rd & 4th highest TPC-C benchmarks on record to date, both behind two IBM DB2 benchmarks of ~4.0M & ~3.2M tpmC respectively.
I've noticed that Microsoft reps sometimes tend to discuss TPC results inconsistently. When SQL Server is on top of the TPC results (as it was when SQL 2000 was launched) this fact is gratuitously leveraged in marketing & promotion initiatives. When SQL Server isn't on top (as is the case now), you tend to hear a different message from some (not all) MS reps - eg, "TPC-C isn't important - it's only temporary bragging rights", "all that matters is that you have a result somewhere in the top 10" etc, etc. In my opinion, TPC is an extremely important and valuable way of measuring the respective capabilities of database technologies & should always be taken seriously, regardless of who's on top at any point in time.
On the other hand, it's important to also understand that TPC-C benchmarks involve configuration decisions which can adversely affect the overall performance of the benchmark, leading to results which can potentially describe poorly chosen configurations rather than inefficiencies in the database product itself. Indexing is by far the most important configuration to get right in performance tuning a database system & indexing in TPC-C benchmarks is no different to regular performance tuning in this regard.
This might seem like a wild claim, but I fear that the indexing choices made in SQL Server's largest TPC-C benchmark to date might have been poorly made, having been based on the established convential "wisdom" (that all tables must have CIXs) rather than carefully measured rationales.
Here's my case:
Take a careful look at the SQL Server TPC-C benchmark's full disclosure report & note that all tables have CIXs, some multi-columns wide. You'll find this detailed in appendix B2 - Table definitions, page 212.
Next, take a careful look at the Oracle TPC-C benchmark's full disclosure report & note that they have NOT used their equivalent technology (Index Organised Tables) at all, opting instead for another more efficient indexing technique available in Oracle, confusingly similarly named "clusters".
Be very careful to understand that Oracle clusters are totally different to SQL Server CIXs. Whilst there is some similarity in the names of these two features, their functionality is worlds apart. Oracle clusters can be used in various ways but in this benchmark, they have been used in conjunction with a hashing lookup technique that allows Oracle to lookup primary keys without index lookups at all. Instead, the key values are hashed in a way that allows Oracle to directly access the block (page) storing the row containing the key value, avoiding b-tree traversals entirely.
This reduction in b-tree processing overhead would significantly reduce CPU utilisation because index traversal is generally an extremely intensive CPU activity. Index b-tree pages tend to reside in memory (due to re-use algorithms) & are used extremely heavily by SQL Server, especially where CIXs are used heavily, leading to more expensive NCIX > CIX bookmark lookup activity. The high re-use of index b-tree pages, combined with memory residency easily leads to extreme CPU saturation, something avoided by Oracle hash clusters by avoiding the b-tree traversals in the first place.
SQL Server doesn't have an equivalent technology to Oracle clusters (at least not yet) but it is still possible to reduce b-tree processing overhead by reducing the use of CIXs, which consequently reduces the incidence of bookmark lookups (as discussed in post 1 in this series).
Hence, I am wondering whether those who configured SQL Server's largest TPC-C benchmark ever even considered using a non CIX indexing strategy, which might have yielded significantly less CPU utilisation (& therefore improved Price / tpmC), or whether they simply followed the established conventional "wisdom" blindly?
I don't have access to the necessary performance analysis data to make any sound assessment of whether trying a non CIX indexing strategy might have actually produced a better result, but it stands to reason that Oracle's more successful TPC-C benchmark was actually designed to eliminate b-tree travesal as much as possible & that SQL Server's might have benefitted from a similar strategy based on the limits of it's own features..
Finally, in case anyone mis-reads into this that I might be trying to highlight superior Oracle features or SQL Server deficiencies, please think again. I am an ardent proponent of SQL Server technology for my own good (but un-blinkered) reasons and I always want to see SQL Server succeeding in its market. My intention here is to point out that SQL Server's TPC-C performance might have been hampered by technicians blindly following established, conventional "wisdom" & that we need to re-evaluate the age old "every table should have a clustered index" dogma.
I'll also mention here that I have recently been discussing my thoughts on CIXs with other SQL MVP colleagues in our private discussion forum & so far I have not been supported by any of these colleagues to date (although not everyone has actually disagreed with me). I've had many arguments returned (though none compelling in my opinion), so please read my thoughts with this in mind - no one seems to be agreeing with me here, so perhaps I'm totally up the creek with this. On the other hand, I run a business that provides a dedicated indexing management service to many companies every day & I spend more time than most embroilled in detailed index analysis so I'm quite confident in the observations I've made to date. (c:
I'd also like to thank one of my business partners Jeff Plumb, who spent some time describing Oracle's cluster technology and analysing & discussing the Oracle TPC-C benchmark with me tonight. Jeff's an OCP & an MCP and has significant expertise on both platforms so it's great to have your knowledge & perspectives handy Jeff. The opinions (& any mistakes!) I'm expressing here are definitely mine though.