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
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.