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

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.