OK, two in two days? What's wrong with me (is what Paul said) after he said – who are you and where is my wife?

But, this one is a short one, specifically a follow-on to my prior post about "Disk space is cheap…"

I did a bit of simple math on the internal overhead and costs of inefficient clustering keys and I wanted to share those as well. If a table is clustered then each and every nonclustered index must include ALL columns of the clustering key in [at least] the leaf level of the clustered index. When a nonclustered index is non-unique then it's even worse in that all columns of the clustering key must go all the way up the tree (into the non-leaf levels). I re-wrote sp_helpindex to give you better insight into what was happening in your nonclustered indexes here: A new and improved sp_helpindex (jokingly sp_helpindex8). I've actually re-written it again to include a column that shows whether or not an index is disabled but it still needs the base procedure [sp_SQLskills_ExposeColsInIndexLevels] from the other post – make sure you download that first before trying to use this sp_helpindex9: sp_SQLskills_SQL2008_helpindex (sp_helpindex9).sql (11.06 kb).

Having said that – what's the overhead in terms of some of your bigger tables?

If you have a 10 million row table with 8 nonclustered indexes then *just* the internal overhead is going to cost you the following with keys of the varying sizes:

 

 Description

 Width of CL key

 Rows

 NC Indexes

 MB 

 int

                            4

       10,000,000

               8

           305.18

 datetime

                            8

       10,000,000

               8

           610.35

 datetime, int

                          12

       10,000,000

               8

           915.53

 guid

                          16

       10,000,000

               8

        1,220.70

 composite

                          32

       10,000,000

               8

        2,441.41

 composite

                          64

       10,000,000

               8

        4,882.81

And, what if you have a bigger table – say a 100 million row table with 12 nonclustered indexes… the internal overhead is as follows:

 

 Description

 Width of CL key

 Rows

 NC Indexes

 MB 

 int

                            4

     100,000,000

             12

        4,577.64

 datetime

                            8

     100,000,000

             12

        9,155.27

 datetime, int

                          12

     100,000,000

             12

      13,732.91

 guid

                          16

     100,000,000

             12

      18,310.55

 composite

                          32

     100,000,000

             12

      36,621.09

 composite

                          64

     100,000,000

             12

      73,242.19

So, if you're talking about disk space, IOs, backups, etc. and you have a very inefficient clustering key (say a 5 column "natural key") that's 64 bytes then you have 71.53GB of OVERHEAD where it could have been only 4.47GB. That's a tad over 67GB of wasted disk space, memory (really – do you have 67GB+ of memory to waste??) and what about the long term costs of backing all of this up?? And, I haven't even started to talk about the inefficiencies with the FKs too. Or, the inefficiencies within the nonunique nonclustered index trees as well.

It's a COMPLETE NIGHTMARE!

Please… everyone repeat after me…………….. DESIGN MATTERS!!! :-)

Thanks for reading!
kt