Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: composite index selectivity

    Assume we have a composite index on (colA,colB,colC) ,all columns have repeated values.

    Now How to find out average number pages read (index/data) when
    1. Using only colA in where clause
    2. Using only colA,colB in where clause
    3. Using only colA,colB,colC in where clause

    My aim is to remove colC from index.Running the query with 302/301 flag in production need too much bureaucracy.
    So I want to find out the statistics from data distribution and sp_spacesused to find number index page.


  2. #2
    Join Date
    Aug 2002
    Madrid, Spain
    If "update statistics <tablename>" has been run
    recently, you'll have those densities available.
    Just run "optdiag statistics <db.owner.tablename> ...".
    You'll find statistics for colA and also for the
    column groups colA,colB and colA,colB,colC.
    The figure "Total density" is what you need; it
    is, on average, the number of rows for each
    columns value (or group of columns' values)
    as a fraction of the total number of rows; ie,

    "avg # rows per column value" / "total # rows"

    Density 1 means all the rows have the same
    value. Density equal to 1/#rows means
    unique column values.

    The date statistics were computed is also shown.

    Mariano Corral

Posting Permissions

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