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.
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)