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)
Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
Make each data file the same size; this allows for optimal proportional-fill performance.
Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
Put the tempdb database on disks that differ from those that are used by user databases.
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.
