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