Alright, if you have been tracking the posts on the upcoming SharePoint release, you may very well say, “Yes, I know…I know. SQL Server needs to be 64-bit.” But I ask you to keep reading because I think there is more to the story.
The Basics
Let me very quickly review the 64-bit story for those who may not seen the posts. In order to install SharePoint 2010, your SQL Server will need to be version 2005 or 2008 and it will need to be 64-bit. You won’t be able to use a 32-bit SQL Server or a SQL Server 2000 instance.
So What Else Is There?
SQL Server is the heart of your SharePoint farm. I continue to repeat this phrase every opportunity I get to discuss SharePoint infrastructure. With the improved functionality of SharePoint 2010 or your current SharePoint 2007 platform, your usage and needs from SharePoint will continue to grow. SharePoint 2010 providing more functionality means you will need more SQL resources. The first place to start to ensure the infrastructure is ready for your needs is SQL Server.
Does your current SharePoint environment’s response time meet your needs? Do you have enough capacity to handle your database growth for the next six months to year? These are important questions to evaluate now. If your answer to these questions is no or I don’t know, I want to give you some steps to begin finding the root of the problems.
Performance
In evaluating the performance of a SQL Server, review these four components: processor, memory, disk and network.
Disk is normally the primary bottleneck I find when evaluating client deployments. The disk subsystem needs to have enough disk throughput (or IOPs) so that it can stay responsive enough to your needs. For small to mid-size businesses, your disks should probably respond to requests within 20 milliseconds (ms). For most enterprise environments, a 10 ms responsive time is ideal. For high performance needs, you will design for 5 ms. Look at performance counters \Logical Disk\Avg. Disk sec/Read and \Logical Disk\Avg. Disk sec/Write.
Memory bottlenecks on a SQL server are harder to identify so I want to provide some very high level guidelines: small servers need 8GB, medium servers need 16GB, and large servers need 32GB. I will do a future post digging into these sizing recommendations in more detail.
Processor and network on your SQL server are pretty easy to evaluate. Processor should stay below 80% average utilization and not have any long sustained peeks at 100% utilization. Network utilization should stay below 70% average utilization.
When evaluating bottlenecks, you want to evaluate and eliminate bottlenecks in this order: memory, processor, disk and network. There are reviewed in this order because the previous item could be effecting the item after it.
Capacity
As your use of SharePoint grows, you are going to want to manage the growth of your content databases. For collaboration environments, you want to make sure your content databases do not grow above 100GB. If they start to approach this size, you need to break up the content in different site collections and move those site collections to other content databases.
Ensure you check the free space on each of your drive letters (LUNS). NTFS performance drops after the free space is below 25%. Therefore, no drive letter should not go beyond 75% utilization.
You want to monitor how quickly your databases grow over time. This allows you to predict future storage needs and plan accordingly.
Putting It All Together
These are a series of check you should do in your environment to make sure today that your SQL Server is ready for the SharePoint workload tomorrow.
