Index Usage Statistics report

Question

Index Usage Statistics report

as_1234

SSCrazy

Points: 2865
More actions
August 24, 2018 at 7:07 am

#408305

All,
I've just looked at this report for the first time so I may be missing something obvious.
It's telling me that there are frequent 'user scans' on a primary key index. The only way I know to try and identify the queries causing this is to use query profiler and then check the query plan for all the queries that use that table. Is there any easier way?
Thanks
Reason for edit: Correct to 'query profiler' from 'query analyser'

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 1

First, on 2016, don't use profiler, use extended events. They are much more light weight than profiler and safer for your system.

I don't know of a way to capture index usage, but I do know a way to capture statistics usage, which is almost as good. Read this blog post. That will show you which stats are in use, and the stats are associated with an index, so it'll get you there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 2

Another possibility is to query the plan cache to see which indexes are in execution plans there. That approach is dependent on the plan being in cache though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

as1981 - Friday, August 24, 2018 7:07 AM
All,
I've just looked at this report for the first time so I may be missing something obvious.
It's telling me that there are frequent 'user scans' on a primary key index. The only way I know to try and identify the queries causing this is to use query profiler and then check the query plan for all the queries that use that table. Is there any easier way?
Thanks
Reason for edit: Correct to 'query profiler' from 'query analyser'

IMHO, It's a total waste of time to look for such things because index scans are sometimes much better than index seeks. What you should care about is the insitu performance of code. In the absence of all other tools and with the understanding that they only remain in cache if nothing bumps them out, you can right click on the instance in the Explorer Window (press the {f8} key to get there if it's not already open), select {reports} and then follow your nose to the top 10 IO and top 10 CPU reports. Those will usually tell you what the top 10 worst queries there are. I wrote my own code to do similar with more interesting output years ago. Adam Machanic also wrote sp_WhoIsActive and provides it for free and will provide you with similar information.

Heh... stop tilting at windmills that look like scans or wait stats or... or... or... and get to the real problems, 99.9% of which are in the code identified by the builtin reports or tools like what Adam (and myself) wrote.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2865 More actions · Answer 4

All,

Thank you for your help and sorry for my late reply.

Thanks