From the answer to the question What causes system_scans in sys.dm_db_index_usage_stats to count up?
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.
I confirmed this partially by running some queries on AdventureWorks2012:
/* Get the statistics from Sales.SalesOrderHeader */
EXEC sp_helpstats 'Sales.SalesOrderHeader'
-- Results:
/*
statistics_name statistics_keys
-------------------------------------------
_WA_Sys_00000004_4B7734FF DueDate
_WA_Sys_00000008_4B7734FF SalesOrderNumber
_WA_Sys_0000000D_4B7734FF TerritoryID
_WA_Sys_0000000E_4B7734FF BillToAddressID
_WA_Sys_0000000F_4B7734FF ShipToAddressID
_WA_Sys_00000010_4B7734FF ShipMethodID
_WA_Sys_00000011_4B7734FF CreditCardID
_WA_Sys_00000013_4B7734FF CurrencyRateID
*/
/* Update the statistics for the SalesOrderNumber stats */
UPDATE STATISTICS Sales.SalesOrderHeader _WA_Sys_00000008_4B7734FF WITH FULLSCAN
/* Get the index usage stats for that index */
SELECT
DB_NAME(iu.database_id),
OBJECT_NAME(iu.object_id),
i.name,
iu.user_seeks,
iu.user_scans,
iu.system_seeks,
iu.system_scans
FROM sys.dm_db_index_usage_stats iu
JOIN sys.indexes i
ON iu.object_id = i.object_id
AND iu.index_id = i.index_id
WHERE i.name = 'AK_SalesOrderHeader_SalesOrderNumber'
/*
DatabaseName TableName IndexName
AdventureWorks2012 SalesOrderHeader AK_SalesOrderHeader_SalesOrderNumber
user_seeks user_scans system_seeks system_scans
0 0 0 1
*/
/* Seek and scan the index */
SELECT SalesOrderNumber
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = N'A'
UNION ALL
SELECT TOP 100 SalesOrderNumber
FROM Sales.SalesOrderHeader WITH (INDEX(AK_SalesOrderHeader_SalesOrderNumber))
/*
Running the index usage query from above returns the following:
DatabaseName TableName IndexName
AdventureWorks2012 SalesOrderHeader AK_SalesOrderHeader_SalesOrderNumber
user_seeks user_scans system_seeks system_scans
1 1 0 1
*/
/* Rebuild the index to remove all usage stats */
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON Sales.SalesOrderHeader REBUILD
So user seeks and scans appearing the usage management view seem appropriate, and the system scan from updating the statistics matches the answer given above.
But back to the original question, I would say you don't need to worry about system seeks and system scans on sys.dm_db_missing_index_group_stats. It seems like they would signify that SQL Server was attempting to look up some type of its own meta data and not having the correct internal indexes to satisfy the queries.
