If you’re a “reluctant DBA” supporting Microsoft SQL Server in an environment that uses SharePoint Server, you may find yourself wondering how you can get better SQL Server-SharePoint performance.
SQL Server is the back end for SharePoint, so better SQL Server performance equals better SharePoint performance. Of course, you don’t have full control over things like SharePoint’s database design, but you can have a positive impact on SQL Server’s ability to support SharePoint’s needs.
- Get the blobs out of the database. SharePoint likes to store binary large objects, or blobs, in SQL Server. (Those are things like Word and Excel documents to you and me.) Problem is, SQL Server isn’t a terribly efficient file system, and a ton of blobs in the database can slow things down. Remote BLOB Store to the rescue! This SQL Server feature, which SharePoint can use, moves blobs off to the ordinary file system, leaving only a pointer in the database. The result? Better database performance and smaller database files.
- Put a maintenance plan in place. SQL Server maintenance plans are designed to keep your database running smoothly. It’s not enough to just back it up (although that’s obviously a key task). You also have to reorganize and rebuild indexes periodically, make sure database statistics are being updated and so forth. A nightly or weekly maintenance plan can take care of those tasks automatically, leaving you free to worry about other stuff. Keeping your database in the best condition possible from a performance perspective should be high on your list of priorities.
- Treat Search special. SharePoint’s Search database is a busy customer, and the more dedicated disk spindles, memory and so forth that you can provide, the better. Rearranging the location of the search database and using SQL Server’s File Groups to position the database on its own partition (enabling you to give it dedicated disk spindles) is the way to go. Consider moving the database -- it’s a nondestructive operation -- to get a nice boost in SharePoint performance.
One more thing you can do goes a bit outside the “tip” category and more into the “rethinking your architecture” category: storage. SQL Server needs a lot of storage for SharePoint, and it has to be fast storage. Faster-spinning drives, and more drives, is the key. In fact, having more small drives is better than a few large drives, because more drives give you much better throughput. Boost the random-access memory in your SQL Server machines to give SQL Server more room to cache data in-memory, helping it rely just a wee bit less on those disks.
Follow these guidelines and do regular maintenance and your SQL Server database should be in prime condition to handle whatever SharePoint throws at it.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at www.ConcentratedTech.com.
This was first published in August 2011