mdw_purge_data_ long runtime I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW.

When the management data warehouse is created it creates a number of agent jobs one of which is mdw_purge_data_[DB NAME] that goes through and purges data that exceeds the retention period, as far as I am aware. This job is currently running and its duration has increased form a few hours to days. This was initially an issue in RTM but was supposedly fixed in Service Pack 1 (we are running SP2). I have tried following an amendment of the stored procedure it calls as per this link:

http://www.toddbaker.org/blog/2010/12/17/sql-2008-mdw-fixing-long-running-purges/

This does not seem to have helped. I then read another blog post suggesting creating two indexes on the sql_handles columns of two tables in the MDW, snapshots.query_stats and snapshots.notable_query_text. When I try and create these I end up with a timeout and the index fails to create. This suggests that there is far too much information in there.

Has anyone got any pointers about reigning in the mdw_purge_data_ job as I think it may well be impacting the performance of the server as a whole.
So finally sorted this out.  There was an orphaned instance within the MDW.  I removed this, which took at least a day to kill off.  Trying to create the indexes failed through the gui due to a time out.  Using activity monitor I could see that there were still huge waits on the server.  I stopped all data collection for the MDW and disabled all the jobs associated, including the purge job.  I then stopped this job as it was still running.  Created the indexes using t-sql.  Re ran the jobs and I am now back with the baseline for this server.  I have gone from over a day to purge data to about 15 minutes so all in all much happier.