SQL Server – Configuring UCP and MDW on a Single Server

If you look carefully at the waits graph, the coloured bars actually indicate lock waits, not I/O waits.

Also, the I/O graph shows increased system disk usage, not SQL Server disk usage.

Given that the memory graph is approaching your system memory size as the system utilization increases over time, I believe your instance is overcommitted for the current max server memory setting (or the max server memory setting isn't set at all), and one or more of the SQL Server memory pools are swapping to disk.

The solution is to lower the max server memory setting (or set it to an appropriate value if it isn't set). Please see my answer here for a more in-depth answer from a similar scenario, and my blog post:

Is my SQL Server's memory over-committed?

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.