I wanted to share a little bit more from my deck I delivered at Teched with Paul Learning.  Had a great session, great attendance.  What we got as a result was the need for more crossover information on SQL and SharePoint.  I do recommend you start with this post on “Understanding the SharePoint SQL Databases” on SharePointForAll which I put together as post to better explain how the data is spread out and to help better understand the differences between the different SharePoint databases.

Performance and the various bottlenecks introduced by SharePoint leveraging SQL need to be better understood for us to optimize SQL for SharePoint.  Beyond RAM which is often the first bottleneck for SQL systems to hit when running with SharePoint databases is disk I/O.  It’s not the typical content database that has these challenges, but the temp and search db.  The SharePoint databases are most often disk bound, meaning the bottleneck is Disk IO.  You can easily optimize your databases for disk throughput requirements by understanding their read and write patterns and demands.

Above I’ve tried to break down the database disk demands into three tiers.  High, medium, and low.  Obviously there are operations which affect the performance of these databases at various times. 

The search database is extremely write intensive.  During a crawl of a collaboration environment you will find higher disk IO in a large SharePoint farm than even Exchange!  As well, the temp db is extremely volatile.  It is often the bottleneck to write performance for your databases.  I recently stated that after RAM, the temp db is most often the bottleckneck and can do more for your large scale performance and planning than anything else (Of course there are other performance considerations like the pipes between the servers and NIC).  Recently I explained in a post titled “SharePoint Performance and File Groups for Temp db, Search db, and Content Dbs” recommending for large environments looking for better peformance to split out the temp and search dbs with multiple NDF files (files and file groups) to optimize the write performance by allowing for better write throughput.    SharePoint admins may not realize, but everything is written to the temp db first, and then written into the transaction logs and then into the content database.

Tempdb optimization MSDN Article on Temp db optimization outlines this quite nicely (section quoted)

As well, in the hot/high importance category, the transaction logs for those who aren’t database admins, are often misunderstood.  The transaction logs need to be optimized despite whether your environment is in simple or full logging mode.  I highly highly encourage that your transaction logs be on optimized RAID configurations such as 0+1. Stripped and mirrored.  You need write performance for your transaction logs.

SharePoint Top Performance Killers!

1. Indexing/Crawling – watch out for the crawler threads and external as well as crawling within the farm.  Offloading this to Index as a FE will seriously help.  The better you can understand the ins and outs of search and crawling, the better off you’ll be in actually tuning the indexing to lessen the impact to your SharePoint farm.  SharePoint does pay attention to robots.txt if you need to block a crawler or disallow.
2. Backup (SQL & Tape) – serious write disk I/O performance hit and serious CPU hit.  This stuff hurts.  STSADM backup is one of the most inefficient commands you can run.  Using SQL backup is a good start, and DPM or other snapshots technology and even SQL Litespeed or SQL 2008 backup with compression all help to lessen the performance hit. (Tips for improving SharePoint backup performance)
3. Profile Import – watch your SQL CPU spike and it hold onto the cycles for hours.  You can attempt to be smarter about the Query in your LDAP query to your AD.  That’s a good start.
4. Misc Timer Jobs – User Sync for large #s of Users – the more users the longer the timer jobs will run.  Profile Synchronization - This job runs once every Hour and there is one per Web Application Quick Profile Synchronization - This job runs once every minute as performance permits and there is one per Web Application (MSDN.)  More information on miscellaneous timer jobs…
5. STSADM Backup/Restore – Note the updated information on 12GB as a size where they recommend putting site collections in their own database?  This is a recommendation around performance and scale. There is an article on monitoring for SQL blocking.
6. Large List Operations – I refer to the recent SharePoint MS IT Performance whitepaper where the IT Team monitors for lists over 3000 to watch and prevent SQL blocking. Also check out Revisiting SharePoint List Scalability
7. Heavy User Operation List Import/Write – Another scenario of users having power they don’t realize they have.  In SQL there’s little difference between this and bulk user operations.  Not a lot on the performance impact, but here’s an example of how to create a list based on a spreadsheet.  You can imagine an underground project to import a ton of data from file shares or from Access databases.  Totally under the radar.  Not much you can do other than plan for peak, and encourage people to plan with you for these type of long running operations.

Resources and related posts

“Understanding the SharePoint SQL Databases” on SharePointForAll

SharePoint Performance and File Groups for Temp db, Search db, and Content Dbs (blog post)

Performance recommendations for storage planning and monitoring (SharePoint CAT team “Storage” paper)

Technet article: Physical storage recommendations (Office SharePoint Server) TechNet Article

Using Microsoft Office SharePoint Server to implement a large-scale content storage scenario with rapid search availability Paul Learning & Knowledge Lake

Top SharePoint Storage (SQL) Resources (Links)

Key capacity planning information and links (Links)