3

The system view sys.dm_db_index_usage_stats has several columns reflecting usage of the index by system queries. But what does that mean? Why does the system query my table?

The documentation is not particularly helpful:

Column name Data type Description
system_seeks bigint Number of seeks by system queries.
system_scans bigint Number of scans by system queries.
system_lookups bigint Number of lookups by system queries.
system_updates bigint Number of updates by system queries.

2 Answers 2

5

sys.dm_db_missing_index_group_stats documents this as

Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for. For more information, see Auto Stats Event Class.

One way of generating some system_seeks and system_scans is below.

I'm not sure if there are other scenarios (apart from filtered statistics) where you can end up with a system_seek on a user table.

CREATE TABLE dbo.Test(X INT PRIMARY KEY);

INSERT Test 
SELECT value 
FROM generate_series(1,10000)

--1 seek 
CREATE STATISTICS S_Filtered
ON Test(X) WHERE X > 100

--2 seeks 
UPDATE STATISTICS dbo.Test (S_Filtered)

-- 1 scans
CREATE STATISTICS S_UnFiltered ON dbo.Test(X);
-- 2 scans
UPDATE STATISTICS dbo.Test(S_UnFiltered);
-- 3 scans
UPDATE STATISTICS dbo.Test(S_UnFiltered);


SELECT  index_id, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update, system_seeks, 
        system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update
FROM         sys.dm_db_index_usage_stats
WHERE object_id = object_id('dbo.Test')
3

Why does the system query my table?

I can't think of any reason why that would be so, but someone smarter than me might be able to conjure up a use case...

...And someone smarter than me has prevailed. Statistics maintenance is a system process that reads from user tables. That would cause the system_seeks or system_scans to increase in this DMV for an index on a user table.

The system view sys.dm_db_index_usage_stats has several columns reflecting usage of the index by system queries. But what does that mean?

There are system tables that exist in the database instance. Some of those tables might be indexed as well. System processes that query those tables would likely result in an execution plan that leverages the indexes on them. That's what the point of the system_seeks, system_scans, etc columns of the sys.dm_db_index_usage_stats DMV are for.

1
  • 2
    One type of system query, statistics maintenance. Happens all the time. Commented Jan 3 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.