I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and SCAN:Stopped events.
I can understand that a lot of these scans are probably insignificant, but I can't help but think that there are missing indexes that would help performance a lot.
I am trying to connect these scans back to the actual queries, and ultimately to the missing indexes and am trying to figure out the best way.
So far I have linked the SCAN:Started rows back to the actual objectid, so that shows me the table that was in the query.
On the other hand I am looking at the sys.dm_db_missing_index_group_stats table and seeing that all of the rows have a 'user_scans' of 0.
If I run a SQL statement like this,
SELECT migs .group_handle, user_seeks, user_scans, avg_user_impact, statement, mid.*
FROM sys .dm_db_missing_index_group_stats AS migs
INNER JOIN sys. dm_db_missing_index_groups AS mig
ON ( migs.group_handle = mig.index_group_handle )
INNER JOIN sys. dm_db_missing_index_details AS mid
ON ( mig.index_handle = mid.index_handle )
WHERE migs .group_handle in ( SELECT group_handle
FROM sys .dm_db_missing_index_group_stats)
Again it only shows me rows with 'user_scan' of 0.
