x

What causes `system_scans` in sys.dm_db_index_usage_stats to count up?

As usual [BOL][1] only states the obvious here.

I have a few non clustered indexes in a database that have zero user_scans, user_seeks and user_lookups. These same indexes have high number of system_scans and user_updates.
Taking an educated guess here, but would the system_scans be from something like auto updating of statistics?

Is my guess correct? any idea on a simple way of testing out the theory? What other processes cause system usage stats to count up on user tables/indexes?

[1]: http://technet.microsoft.com/en-us/library/ms188755.aspx
more ▼

asked Aug 03, 2010 at 12:27 AM in Default

Nick Kavadias gravatar image

Nick Kavadias
476 3 3 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

I understand the system scans/lookups columns are to do with statistics updates & index maintenance. I asked Brent Ozar when he was presenting over in Germany about a month ago and he confirmed this for me.

I asked him about this with regard to identifying unused indexes on a system. He said that you only have to be concerned with User Lookups and User Scans, the system ones are really only system side operations. So an index that only has "system" activity has not been used by queries since the index usage statistics were last reset.
more ▼

answered Aug 03, 2010 at 01:25 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x13

asked: Aug 03, 2010 at 12:27 AM

Seen: 2664 times

Last Updated: Aug 03, 2010 at 12:34 AM