Probably one of the most common issues customers run into after completing the upgrade is performance degradation. This post covers how you can identify and solve SQL Server related issues after upgrading to a newer version.
SQL Server Performance & Tridion DB
I think we can all agree that SQL server does a pretty good job when it comes to automatic optimizations. However, this isn’t something you should just rely on. Almost all Tridon upgrade scripts contain database schema changes. And some include operations responsible for altering large amounts of data. Therefore an upgrade can potential impact database performance (fragmentation, outdated statistics).
Given that the database is offline during the upgrade, it might be the ideal opportunity for some Tridion DB housekeeping – Just keep the following in mind:
If you do have a DBA on staff, it would be best to ask if there are any maintenance plans in place that could be executed on demand. The most important options to include are:
- Reorganize and Rebuild Indexes (based on fragmentation and page count – http://gallery.technet.microsoft.com/scriptcenter/Rebuilding-Reorganizing-8d0b8748)
- Update the statistics (Only REBUILD updates the statistics with full scan)
- Recompile all stored procedures
Even if you don’t have a DBA, there are plenty of web sites out there that can help you setting up a basic maintenance plan. The following article covers the basics. MSDN: Use the Maintenance Plan Wizard – http://msdn.microsoft.com/en-us/library/ms191002.aspx
Note Just keep in mind that the Wizard does not tell you if you make poor choices therefore it might even be possible to hurt the performance of your SQL Server.