patternsqlMinor

What are system_seeks in sys.dm_db_index_usage_stats?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

system_seekswhataredm_db_index_usage_statssys

Problem

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.

Solution

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')

Code Snippets

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')

Context

StackExchange Database Administrators Q#334511, answer score: 5

Revisions (0)

No revisions yet.