Optimize query executions in sharepoint

Question

I was recently asked this question. Would like to know some opininon on what is the best strategy.

The SharePoint databases are configured to run on a dedicated instance of Microsoft SQL Server 2008 R2 Service Pack 1. You need to optimize query executions. What should you do?

A. Move the SharePoint databases to a RAID 10 volume.

B. Move the SharePoint databases to a RAID 5 volume.

C. Set the maximum degree of parallelism for SQL Server instances to 4.

D. Set the maximum degree of parallelism for SQL Server instances to 0.

Answer 1

Microsoft best practice is to set max degree of parallelism to 1 for instances of SQL Server hosting SharePoint databases - so that rules out answers C and D.

As far as I'm aware RAID 10 offers the highest availability and performance but you will double your storage costs due to the extra disks required for parity and mirroring. Microsoft best practice says to use RAID 10 array for the drive that stores tempdb data files with the number of tempdb data files equal to the number of CPU cores and each set to the same size.

See the below for more details:

Best Practices for SQL Server in a SharePoint Server Farm

SharePoint Performance Optimization with SQL Server

Answer 2

The decision whether to follow the Parallel Exceution Plan or Serial Execution Plan depends upon scenario to scenario. In case of OLTP systems, where queries are relatively smaller and they return less number of records, it is best to follow Serial Plan with Maximum Degree of Parallelism set to 1. However, in case of OLAP systems, where the queries are quite complex, it is preferable to go for Parallel Execution Plan with Degree of Parallelism set to 0.

In SharePoint 2010, setting Maximum Degree of Parallelism option was recommended to be set as 1 , but is was optional and not mandatory. However, in case of SharePoint 2013, it is required to set this to 1 otherwise the configuration wizard will fail.