February 14, 2013 at 9:26 am
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
February 14, 2013 at 9:39 am
Take a look at this post. It gives some good information and a starting place for further investigation/reading.
______________________________
AJ Mendo | @SQLAJ
February 14, 2013 at 9:49 am
SQLAJ (2/14/2013)
Hi, thanks for this post, but the info there is poor, unfortunately.
# User Scans The number of scan operations on the index caused by user activity - only this.
Igor Micev,
My blog: www.igormicev.com
February 14, 2013 at 10:00 am
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
February 14, 2013 at 10:11 am
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
February 14, 2013 at 10:13 am
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
February 14, 2013 at 10:59 am
GilaMonster (2/14/2013)
A scan of a portion (range) of an index. Anything more than a single row is considered a rangeLow 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
February 14, 2013 at 1:00 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply