{"id":491,"date":"2010-11-12T08:27:00","date_gmt":"2010-11-12T08:27:00","guid":{"rendered":"\/blogs\/kimberly\/post\/How-much-does-that-key-cost.aspx"},"modified":"2013-01-11T23:14:12","modified_gmt":"2013-01-12T07:14:12","slug":"how-much-does-that-key-cost-plus-sp_helpindex9","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/how-much-does-that-key-cost-plus-sp_helpindex9\/","title":{"rendered":"How much does that key cost? (plus sp_helpindex9)"},"content":{"rendered":"

\nOK, two in two days? What's wrong with me (is what Paul<\/a> said) after he said – who are you and where is my wife?\n<\/p>\n

\nBut, this one is a short one, specifically a follow-on to my prior post about "Disk space is cheap…<\/a>"\n<\/p>\n

\nI 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)<\/a>. 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)<\/a>.\n<\/p>\n

\nHaving said that – what's the overhead in terms of some of your bigger tables?\n<\/p>\n

\nIf 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:\n<\/p>\n

  <\/p>\n\n\t<\/p>\n\n\n\n\n\n\n\n\n
\n

\n\t\t\t <\/span>Description <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>Width of CL key <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>Rows <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>NC Indexes <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>MB  <\/span><\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span><\/span>int<\/span> <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                            <\/span>4 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t           <\/span>305.18 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>datetime <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                            <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t           <\/span>610.35 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>datetime, int <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t           <\/span>915.53 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>guid <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>16 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t        <\/span>1,220.70 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>composite <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>32 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t        <\/span>2,441.41 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>composite <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>64 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t       <\/span>10,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t               <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t        <\/span>4,882.81 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\nAnd, what if you have a bigger table – say a 100 million row table with 12 nonclustered indexes… the internal overhead is as follows:\n<\/p>\n

  <\/p>\n\n\t<\/p>\n\n\n\n\n\n\n\n\n
\n

\n\t\t\t <\/span>Description <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>Width of CL key <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>Rows <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>NC Indexes <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t <\/span>MB  <\/span><\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span><\/span>int<\/span> <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                            <\/span>4 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t        <\/span>4,577.64 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>datetime <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                            <\/span>8 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t        <\/span>9,155.27 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span><\/span>datetime<\/span>, <\/span>int<\/span> <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t      <\/span>13,732.91 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>guid <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>16 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t      <\/span>18,310.55 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>composite <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>32 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t      <\/span>36,621.09 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n

\n

\n\t\t\t <\/span>composite <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t                          <\/span>64 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t     <\/span>100,000,000 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t             <\/span>12 <\/span>\n\t\t\t<\/p>\n<\/td>\n

\n

\n\t\t\t      <\/span>73,242.19 <\/span>\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\nSo, 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.\n<\/p>\n

\nIt's a COMPLETE NIGHTMARE!<\/strong>\n<\/p>\n

\nPlease… everyone repeat after me…………….. DESIGN MATTERS!!! <\/strong>:-)\n<\/p>\n

\nThanks for reading!
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,16,17,23,25,31,63],"tags":[],"class_list":["post-491","post","type-post","status-publish","format-standard","hentry","category-are-you-kidding-me","category-clustered-index","category-clustering-key","category-database-maintenance","category-design","category-foreign-keys","category-sp_helpindex-rewrites"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/491","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=491"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/491\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}