Like This Blog 0

Added by Don Jones April 25, 2013

If you’re a “reluctant DBA” supporting SQL Server, in an environment that uses SharePoint Server, you may sometimes find yourself wondering how you can get better performance out of the combination. 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 definitely 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. That’s 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 definitely slow things down. Remote BLOB Storage to the rescue! This SQL Server feature, usable by SharePoint, moves BLOBs off to the ordinary file system, leaving only a pointer in the database. The result? Better database performance and smaller database files. Two for one!
   
• 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 and keeping your database in the best condition possible from a performance perspective. Microsoft has a great paper on database maintenance from the SharePoint perspective – read it. Database Maintenance for Microsoft SharePoint Products and Technologies
   
• 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. SQL File groups and Search offers some great tips for rearranging the location of the search database, using SQL Server’s File Groups to position the database on its own partition (enabling you to give it dedicated disk spindles). Consider moving the database – it’s a non-destructive operation – to get a nice boost in performance.

One other thing you can do goes a bit outside the “tip” category and more into the “rethinking your architecture” category: Storage. SQL Server will need a lot of storage for SharePoint, and it will need to be fast storage. Faster-spinning drives, and more drives, is the key. In fact, more small drives is better than fewer large drives, because more drives will give you much better throughput. Boost the RAM 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.

With these tips and regular maintenance, your SQL Server database should be in prime condition to handle whatever SharePoint throws at it.

Enjoy,

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ