{"id":516,"date":"2012-01-02T06:46:00","date_gmt":"2012-01-02T06:46:00","guid":{"rendered":"\/blogs\/paul\/post\/Survey-results-How-cluster-key-size-can-lead-to-GBs-of-wasted-space.aspx"},"modified":"2017-04-13T09:50:47","modified_gmt":"2017-04-13T16:50:47","slug":"survey-results-how-cluster-key-size-can-lead-to-gbs-of-wasted-space","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-results-how-cluster-key-size-can-lead-to-gbs-of-wasted-space\/","title":{"rendered":"How cluster key size can lead to GBs of wasted space"},"content":{"rendered":"

Back in November I kicked off a <\/span>survey<\/span><\/a> that had you run some code to get some details about your cluster keys, nonclustered indexes, and table size. I got results from more than 500 systems across the world, resulting in 97565 lines of data – thanks! <\/span><\/p>\n

The purpose of the survey is to highlight one of the side-effects of not adhering to the general guidelines (i.e. there are exceptions to these) for choosing a clustered index key. It should be, if possible: <\/span><\/p>\n

    \n
  1. \n
    Narrow <\/span><\/div>\n<\/li>\n
  2. \n
    Static <\/span><\/div>\n<\/li>\n
  3. \n
    Ever-increasing <\/span><\/div>\n<\/li>\n
  4. \n
    Unique <\/span><\/div>\n<\/li>\n<\/ol>\n

    The survey and this post are intended to show how not adhering to Rule #1 can lead to performance problems. <\/span><\/p>\n

    Both Kimberly and I have explained in the past the architecture of nonclustered indexes – where every nonclustered index row has to have a link back to the matching heap or clustered index record. The link must be a unique value as it must definitively match a single record in the heap or clustered index. For nonclustered indexes on a table with a clustered index, this link is the cluster key (or keys) as these are guaranteed to be unique. Ah, you say, but what about when the clustered index is NOT defined as unique? That’s where Rule #4 comes in. For a non-unique clustered index, there will be a hidden 4-byte column (called the uniquifier) added when necessary as a tie-breaker when multiple clustered index records have the same key values. This increases the clustered index key size by 4 bytes (the uniquifier is an integer)\u00a0when needed. <\/span><\/p>\n

    But I digress. The crux of the matter is that every nonclustered index record will include the cluster keys. The wider the cluster key size is (e.g. a few natural keys), the more overhead there is in each nonclustered index record, compared to using, for instance, an integer (4-byte) or bigint (8-byte)\u00a0surrogate cluster key. This can mean you’ve got the potential for saving huge amounts of space by moving to smaller clustered index keys – as we’ll see from the data I collected.<\/span><\/p>\n

    The survey code I got you to run returned: <\/span><\/p>\n

      \n
    1. \n
      Number of nonclustered indexes <\/span><\/div>\n<\/li>\n
    2. \n
      Number of cluster keys <\/span><\/div>\n<\/li>\n
    3. \n
      Total cluster key size <\/span><\/div>\n<\/li>\n
    4. \n
      Number of table rows <\/span><\/div>\n<\/li>\n
    5. \n
      Calculation of bytes used in all the nonclustered indexes to store the cluster keys in each row <\/span><\/div>\n<\/li>\n<\/ol>\n

      I did not take into account filtered indexes in 2008, or variable length cluster key columns, as to be honest although these will make a difference, for the purposes of my discussion here (making you aware of the problem), they’re irrelevant. It also would have made the survey code much more complex for me to figure out :-) <\/span><\/p>\n

      Now let’s look at some of the results I received. To make things a little simpler, I discarded results from tables with less then ten thousand rows, and with clustered index key sizes less than 9. This dropped the number of data points from 97565 down to 22425. <\/span><\/p>\n

      The graphs below show the estimated amount off savings that could be had in GB from moving to an 8-byte bigint, plotted against the first four factors in the list above. <\/span><\/p>\n

       <\/p>\n

      \u00a0\"\"<\/span><\/p>\n

      \u00a0\"\"<\/span><\/p>\n

      \u00a0\"\"<\/span><\/p>\n

      \u00a0\"\"<\/span><\/p>\n

      And here are the top 20 in terms of potential savings so you can see how the rough table schema:<\/span><\/p>\n

      NCIndexes\u00a0 ClusterKeys\u00a0 KeyWidth\u00a0 TableRows\u00a0\u00a0\u00a0\u00a0\u00a0 KeySpaceGB\u00a0 SavingsGB
      \n———\u00a0 ———–\u00a0 ——–\u00a0 ————-\u00a0 ———-\u00a0 ———
      \n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 891,751,171\u00a0\u00a0\u00a0 358.8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 352.1
      \n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3,189,075,035\u00a0 285.1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 261.4
      \n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 45\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4,479,327,954\u00a0 187.7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 154.4
      \n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 453,251,463\u00a0\u00a0\u00a0 182.4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 179.0
      \n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2,766,814,206\u00a0 164.9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 144.3
      \n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 89\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 371,745,035\u00a0\u00a0\u00a0 123.3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 120.5
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 774\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 76,337,053\u00a0\u00a0\u00a0\u00a0 110.1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 109.5
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 774\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 76,331,676\u00a0\u00a0\u00a0\u00a0 110.0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 109.5
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 774\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 75,924,837\u00a0\u00a0\u00a0\u00a0 109.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 108.9
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 774\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 75,533,539\u00a0\u00a0\u00a0\u00a0 108.9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 108.3
      \n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 318,217,628\u00a0\u00a0\u00a0 106.7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 104.3
      \n7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 60\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 269,590,810\u00a0\u00a0\u00a0 105.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 103.4
      \n22\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 389,203,725\u00a0\u00a0\u00a0 103.7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 100.8
      \n22\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 329,772,049\u00a0\u00a0\u00a0 87.8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 85.4
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 509\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 90,311,271\u00a0\u00a0\u00a0\u00a0 85.6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 85.0
      \n17\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 510\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9,334,362\u00a0\u00a0\u00a0\u00a0\u00a0 75.4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 75.3
      \n22\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 267,380,864\u00a0\u00a0\u00a0 71.2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 69.2
      \n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 172\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 219,929,560\u00a0\u00a0\u00a0 70.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 68.8
      \n22\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 261,967,851\u00a0\u00a0\u00a0 69.8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 67.8
      \n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 31\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 395,800,250\u00a0\u00a0\u00a0 68.6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 65.6<\/span><\/p><\/blockquote>\n

      Wow – that’s some pretty amazing stuff – and that doesn’t even account for the space taken up by page headers etc.<\/span><\/p>\n

      You might be thinking – why do I care? There are plenty of reasons:<\/span><\/p>\n