1.Upgrading the server to SP1 or SP2: will it be worth the hassle?
I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM
Yes very much and believe me its not a hassle it will save you from lot of hassles. As already pointed you are running on unsupported version of SQL Server. Let me tell you personal experience, suppose you face any issue which comes eventually as a MS bug and you plan to ask MS to pay you for your loss you cannot because you are running unsupported version. The legal agrrements are little more complicated and I will not talk about it. Also even if you raise a case with MS for some support related activities MS engineere will straight away say its unsupported version first please apply SP2, YES not even SP1 SP1 is also not supported, then only they will proceed further, after you have applied SP2.
There was a critical bug fixed in SQL Server 2012 SP1.
- Memory leak for SQLOPTIMIZER
- Unexpected start for SQL Server.
List of bugs fixed in SQL Server 2008 R2 Sp1
List of bugs fixed in SQL Server 2008 R2 SP2
I guess above two reasons and links are enough. Are you waiting to face the issue and then apply SP2 ? Please dont. Also the option recompile bug was fixed in later CU and eventually in SP1.
2.Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?
As already pointed SQL Server 2008 R2 SP2 is not fully supported. As a general rule after launch of new service pack within one year previous SP becomes unsupported. Following is difference between mainstarem support and extended support taken from here
Think of Mainstream support as “normal”. In other words, mainstream support means Microsoft supports a product with its full offerings including paid incident support, hotfix support, security updates, etc.
When a product enters the Extended Support “phase”, the game changes:
1) We still provide security updates at no charge to all customers
2) You can still call CSS or create a case online per the normal support offerings (pay per incident, Premier, etc)
But…you cannot obtain a non-security hotfix from Microsoft for no charge. In order to obtain a non-security hotfix, you must purchase an Extended Hotfix Support Agreement. Extended Hotfix Support Agreements are available for Premier customers. Contact your Technical Account Manager for more information.
The extended support might involves more cost than normal support and is not available to everyone. If your firm has premier support tie up with MS then you can get extended support eaisly otherwise its costly and bit difficult. This is as per my region MS support/licensing is complex and you should speak to licesning expert to get more information about extended support.
MS says that CU should be only applied if user is facing bug which is fixed in that CU. If not Microsoft recommends not to apply it. SP's are more throughly tested and more reliable as compared to CU. So I would also say unless you face issue dont go for CU.If you want help in deploying SP please refer to This Link(its for SS 2012 but process/steps remains same)
Best Answer
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.