Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.