Density column in SHOW_STATISTICS

Question

Density column in SHOW_STATISTICS

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
October 23, 2009 at 1:16 pm

#135882

Is the Density column in SHOW_STATISTICS stored in any DMV, so I can see the values for all the index in one shot.
If not, is there any other suggestions how this can be done.
Thanks
Dan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

repent_kog_is_near (10/23/2009)
Is the Density column in SHOW_STATISTICS stored in any DMV, so I can see the values for all the index in one shot.
If not, is there any other suggestions how this can be done.
Thanks
Dan

You can try the table valued function provided by MS and look at the Avg_page_space_used_in_percent column

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'Detailed')

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

No. The statistics-related info is only available via DBCC Show_Statistics. You can see the number of stats in the system (and whether they are recomputed) from sys.stats, but not the density or histogram

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

CirquedeSQLeil (10/23/2009)
repent_kog_is_near (10/23/2009)
Is the Density column in SHOW_STATISTICS stored in any DMV, so I can see the values for all the index in one shot.
You can try the table valued function provided by MS and look at the Avg_page_space_used_in_percent column

Avg_page_space_used has absolutely nothing to do with a statistic's density.

Avg_page_space_used is shows, on average, what percentage of an index page (leaf or non-leaf) is used up by the rows on the page.

Density (as shown in DBCC SHOW_STATISTICS) is defined as 1/<number of unique values> and is given, for a statistic, on all of the left-based subsets of that statistic.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 4

then, is there a way to see which indexes are selective and which are not, from elsewhere in SQL Server, in one shot, instead of traversing each index, one at a time?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

GilaMonster (10/23/2009)
CirquedeSQLeil (10/23/2009)
repent_kog_is_near (10/23/2009)
Is the Density column in SHOW_STATISTICS stored in any DMV, so I can see the values for all the index in one shot.
You can try the table valued function provided by MS and look at the Avg_page_space_used_in_percent column
Avg_page_space_used has absolutely nothing to do with a statistic's density.
Avg_page_space_used is shows, on average, what percentage of an index page (leaf or non-leaf) is used up by the rows on the page.
Density (as shown in DBCC SHOW_STATISTICS) is defined as 1/<number of unique values> and is given, for a statistic, on all of the left-based subsets of that statistic.

Thanks for setting that straight Gail.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

repent_kog_is_near (10/23/2009)
then, is there a way to see which indexes are selective and which are not, from elsewhere in SQL Server, in one shot, instead of traversing each index, one at a time?

No.

What is it that you're trying to do here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 7

trying to determine which indexes are not going to be used often by execution plan, and thus lessen the overhead of unnecessary indexes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Then you need to look at the sys.dm_db_index_usage_stats DMV, look for indexes that aren't used, and start going through the queries on your system and see what indexes they use. Bear in mind that sys.dm_db_index_usage_stats is cleared when SQL is started so you're going to have to watch it for a long time to ensure that an index that looks unused really isn't used.

It's not as simple as saying that an index with a selectivity > x% will never be used. That is not the case. The histogram comes into effect as well (there are cases where an index on a bit column is very useful), the index may be covering, it may be used in scans to support order by or group by operations.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass