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.
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.