Professional Web Applications Themes

What's best: Heap or Clustered Idx on combined key? - Microsoft SQL / MS SQL Server

Hi Eric. Generally speaking, the smaller and more selective the clustered index key, the better. You are correct - NCIXs refer to CIX keys which impacts NCIX sizing. Because SQL Server uses bookmark lookups from NCIXs to CIXs, bookmark operations are more efficient during query runtime if CIX keys are smaller. Heaps have a similar but often overlooked behaviour to CIX page splitting termed "row forwarding". Where a heap is updated so that rows no longer fit on their current storage page, they are "forwarded" to another page, and a forwarding pointer is left in the original page. Many forwarding ...

  1. #1

    Default What's best: Heap or Clustered Idx on combined key?

    Hi Eric.

    Generally speaking, the smaller and more selective the
    clustered index key, the better. You are correct - NCIXs
    refer to CIX keys which impacts NCIX sizing. Because SQL
    Server uses bookmark lookups from NCIXs to CIXs, bookmark
    operations are more efficient during query runtime if CIX
    keys are smaller.

    Heaps have a similar but often overlooked behaviour to CIX
    page splitting termed "row forwarding". Where a heap is
    updated so that rows no longer fit on their current
    storage page, they are "forwarded" to another page, and a
    forwarding pointer is left in the original page. Many
    forwarding records can cause a heap to perform poorly and
    tend to hurt select query performance as opposed to CIX
    page splits which impact update / insert performance.
    Whether your application performs more updates or selects
    will drive whether it is going to be impacted more by row
    forwarding (on a heap) or page splitting on a clustered
    index. Page splitting also occurs on indexes too, btw but
    row forwarding doesn't impact indexes.

    In my experience I've observed that the Optimizer tends to
    generally favour unique index for most queries, so you
    might even find that adding a surrogate identifying
    (unique) single column key can provide the optimizer an
    even better alternative. Keep in mind that this is a
    physical database optimization technique only and
    generally not good for logical database design purposes.

    If I could impress only one thing upon you though, it
    would be to TEST different approaches, with real data (as
    possible) and real queries, in as much as possible a real
    environment. Observe the different query plans and be
    guided heavily by the logical io counts produced by your
    different indexing strategies. Become friends with the
    Index Tuning Wizard - it can help suggest indexing
    strategies you might not have thought of and has the added
    benefit of being able to assess indexing options for
    multiple queries. Pay particular attention to the Logical
    IO rates produced by different indexing strategies (eg
    which columns to NCIX / CIX etc) because reducing Logical
    IO is probably the single most important objective of
    tuning queries. There are certainly other important
    aspects too, but if you're on the learning curve with
    query tuning (& we all are b/c the optimizer keeps
    changing) Logical IO is a good starting benchmark for
    comparing the effectiveness of your indexing plans.

    That's enough from me - I'm sure others will chip in here.

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP
    >-----Original Message-----
    >Let say I have a table where Field1 is "almost" unique
    (in 90% of
    >cases)
    >
    >(Field1, Field2, Field3) is unique
    >
    >If I create a unique clustered index on the three fields,
    I understand
    >that all other indexes will point to the three values in
    the clustered
    >index.
    >Does not this increase significantly the size of these
    non-clustered
    >indexes?
    >
    >Would I be better off creating a Non Unique Clustered
    Index on Field1
    >only?
    >
    >Or would it be better to leave my table as a heap because
    the Row
    >Pointers are probably quite small? Although this may have
    nasty
    >implications if we add/update many rows in the table due
    to page
    >splits, hence pointer changes?
    >
    >It's a bit academic but I'd like to have gurus opinions,
    please!
    >
    >Thanks
    >
    >PS: in the past, I have created clustered indexes on many
    fields
    >because I wanted to retrieve my data in this order, but I
    suspect this
    >may have been a bad idea...
    >.
    >
    Greg Linwood Guest

  2. #2

    Default Re: What's best: Heap or Clustered Idx on combined key?

    Very interesting

    Thanks
    Eric Mamet Guest

Similar Threads

  1. clustered CF services
    By johnnyTarr in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 2nd, 01:21 AM
  2. Clustered web farm
    By sudha in forum ASP.NET General
    Replies: 3
    Last Post: July 10th, 12:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139