This article can also be found in the Premium Editorial Download "SharePoint Insider: Designing a Sharepoint governance plan."
Download it now to read this article plus other related content.
SharePoint lives and breathes by its SQL Server databases. With the introduction of Windows SharePoint Server, even a small company can end up with a database that is quite large. And a large company can end up with a database that quickly approaches a ridiculous size.
Every page that the SharePoint portal displays is saved into a SQL database. So is every document uploaded into the document repository. SharePoint doesn't necessarily follow the SQL Server table design best practices.
That, in addition to having these large databases, can mean that designing a highly scalable SQL Server to run your SharePoint environment can be a challenge.
Smaller systems can work nicely on your existing SQL Server hardware This doesn't change if your current environment is running on the x86 hardware platform. (Note: In this article, "x64" refers to both x64 and Itanium platforms.) Larger systems will want to move into the x64 hardware platform. The x64 platforms give SQL Server access to more memory and allow you to install more CPUs than an x86 server. Moving into a 64-bit platform changes the way SQL Server allocates memory internally. On large SharePoint installations, the different allocation can be important.
With SQL Server, it's easy to figure out memory requirements -- get as much as you can afford. If you don't think that's enough, get the budget increased. These days a small SQL Server will have 4 GB in it. SQL Servers with 16+ GB of RAM are no longer an oddity. Your limiting factor is going to be that Windows Server 2003 Enterprise and Windows Server 2008 Enterprise support only up to 64 GB of RAM. Beyond that, you need to get Windows Server 2003 Datacenter edition or Windows Server 2008 Datacenter edition.
Selecting storage for SharePoint
Storage is another place to avoid cutting corners because it's the key to a well performing SQL database server. It is highly recommended that you use several different RAID arrays when configuring your SQL Server.
You'll want to place your data files and log files on separate RAID arrays and put your tempdb database on a separate RAID. These RAID arrays can each be RAID 10, but if your environment has a large amount of write cache, you may be able to use RAID 5 for the database files and RAID 10 for the log files and tempdb database. Each of these three arrays should be a separate RAID array, not just a logical partition of the same RAID.
When configuring your system to ensure that you are getting the maximum performance out of your storage, be sure to correctly align your partitions when you create them. When working in a SAN environment, all volumes on the RAID group must have the correct alignment to get the most benefit.
More data files for better optimization
On higher-end systems, adding data files can increase your database performance. It is recommended that you have one data file per four physical cores. So if you have a quad core, quad chip machine -- 16 total cores -- you will want to have four physical database files. For optimum performance, each database file should be on its own RAID array.
For your tempdb database, you should have even more physical files. The recommended configuration for the tempdb database is to have one tempdb database file per physical core on the server. On the same 16-core server, you will want to configure 16 tempdb database files, ideally with each one on its own RAID array. In reality, you aren't going to have 16 RAID arrays to dedicate to the tempdb database, so configure as many as you can and balance the physical files across the RAID arrays.
When configuring each of these additional database files, start each of the additional files at the same size as the current file and make the growth settings the same. This forces SQL Server to use the most optimum method to stripe the data across the database files, which will force SQL Server to keep the data balanced across all of the database files.
Maintenance is key to peak SharePoint performance
Contrary to popular belief, Microsoft SQL Server is not a self-healing system. Proper and regular database maintenance is key to keeping it running at peak performance. Larger shops will probably have a SQL Server DBA who already has his or her own custom scripts that will handle that. Smaller Windows shops or larger Oracle shops that do not have these scripts ready-made -- do not fear. Microsoft has not forgotten about you. SQL Server 2005 and SQL Server 2008 have an easy-to-use GUI that can be used to set up your database maintenance plans with no coding required.
In order to create your SharePoint maintenance plan, connect to the SQL Server using the SQL Server Management Studio and bring up the object explorer. Under the Management Folder you will see Maintenance Plans. Right-clicking on Maintenance Plans and selecting New Maintenance Plan will bring up the editor.
When setting up a basic maintenance plan, two objects will give you the most bang for the buck: the Reorganize Index Task and Update Statistics Task. Add those into your SharePoint maintenance plan and set up the Reorganize Index Task to run first by dragging the green arrow from that object to the Update Statistics Task object. Fix the settings within the objects to your server and instance, as well as your SharePoint database or databases. It's easier to simply select All User Databases and schedule the maintenance plan to run weekly or monthly as you see fit.
Don't be afraid to grow your SharePoint environment beyond a single server and scale it over several servers. As with any high-end system, proper design is the key to any successful deployment and will keep it stable for several years.
Denny Cherry has more than a decade of experience managing SQL Server
installations, including one of the largest in the world at MySpace.com with 175 million users.
Cherry is a senior database administrator and architect at Awareness Technologies, where his areas
of expertise include systems architecture, performance tuning, replication and troubleshooting. He
holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Read his IT
Knowledge Exchange blog at SQL Server with Mr. Denny.
This was first published in November 2008