Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.