Index usage statistics

Question

Index usage statistics

Igor Micev

SSC-Dedicated

Points: 33110
More actions
February 14, 2013 at 9:26 am

#270218

Hi All,
Right click on a databse and then
Reports->Standard reports->Index usage statistics
The output is:
a) Index Usage statistics - show how users and system use the indexes;
b) Index Operational statistics - show details of the no. of operations performed on the indexes.
For some indexes in a) #user scans is 0 or very low, and in b) the #range scans is big.
How do you describe this?
Regards,
IgorMi.
Igor Micev,My blog: www.igormicev.com

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

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

SQLAJ Ten Centuries Points: 1359 More actions · Answer 1

Take a look at this post. It gives some good information and a starting place for further investigation/reading.

http://blogs.msdn.com/b/buckwoody/archive/2008/02/06/sql-server-management-studio-standard-reports-index-usage-statistics-databasename.aspx

______________________________
AJ Mendo | @SQLAJ

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

SQLAJ (2/14/2013)

Take a look at this post. It gives some good information and a starting place for further investigation/reading.

http://blogs.msdn.com/b/buckwoody/archive/2008/02/06/sql-server-management-studio-standard-reports-index-usage-statistics-databasename.aspx%5B/quote%5D

Hi, thanks for this post, but the info there is poor, unfortunately.

# User ScansThe number of scan operations on the index caused by user activity - only this.

Igor Micev,My blog: www.igormicev.com

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

But that's exactly what user scans are, the number of scans of the index caused by user queries.

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 4

GilaMonster (2/14/2013)
But that's exactly what user scans are, the number of scans of the index caused by user queries.

Okey, and what is #range_scans? How do you describe #user_scans=0 and #range_scans = 13195642 for e.g.

Thank you

IgorMi

Igor Micev,My blog: www.igormicev.com

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

A scan of a portion (range) of an index. Anything more than a single row is considered a range

Low scans, high range scans, I'd say that index is well used.

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 6

GilaMonster (2/14/2013)
A scan of a portion (range) of an index. Anything more than a single row is considered a range
Low scans, high range scans, I'd say that index is well used.

Thank you Gail!

What do you think of an index with #user_scans=0, #user_seeks= 47, #range_scans = 13195642 and fragmentation of 54%. Its fill factor is 100. Do you think decreasing it to 95% will be useful? Page allocation is high.

Thank you in advance,

IgorMi.

Igor Micev,My blog: www.igormicev.com

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

Not enough information.

Decreasing fill factor is a decision you need to make based on how fast the index fragments and whether the increased size is worth the decreased fragmentation.

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

Index usage statistics

Cookies on SQLServerCentral