SQL Server Performance

Contrary to what many people think, SQL Server is seldom the bottleneck on a runtime CMS site. One SQL Server can easily handle a significant Web farm of CMS servers. Make sure that you have investigated other performance tuning options before you start to spend a great deal of effort trying to optimize your database. You may find that your time is better spent investigating your caching plan or the efficiency of your templates.

There is one scenario where SQL Server could be stressed by a CMS site. This is when a lot of authoring is occurring at the same time as a lot of traffic. With this in mind, you need to remember a few key points about optimizing your CMS SQL Server database. The most important point is that you will improve your performance if you dedicate a SQL Server machine to your CMS database. SQL Server is able to handle a tremendous load, so this may not be required, but it is certainly the first consideration when you are contemplating this topic.

If you are using your SQL Server for many databases, then you should consider how you will prevent resource contention issues. For best performance, the SQL Server databases should be separated from their transaction logs. This separation should occur across different physical drives.

SQL Server performance is a broad topic and will not be discussed in depth in this book. Refer to the SQL Server documentation for a more thorough discussion of this topic at http://go.microsoft.com/fwlink/?LinkId=9512.