Tags
As a developer, I always less focus on the database. But the database configuration is the key to the performance of SharePoint.
Here is the some key points:
DBCC
run the DBCC CHECKTABLE on the tables that you find having some sort of fragmentation in all the the SharePoint Databases. You can schedule an SQL Agent Job to run the command
The DBCC CHECKDB command should be run on SharePoint DBs once a week.
Here is a good blog http://programming4.us/database/2924.aspx and http://www.sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/
Initial Size for primary data file,transaction log
There are a few performance concerns with these Files settings. The Initial Size value is small; the Autogrowth value is small; and the Path of the files is pointing to the default directory on drive C.
For instance, if you were to upload a 10 MB file into this database using the default Initial Size and Autogrowth settings, SQL Server would have to lock the database 8 to 10 times to grow the data file in 1-MB increments until there was enough room to accept the 10-MB file you wanted to upload. Furthermore, because the log file Initial Size is small and its Autogrowth setting is at 10 percent increments, this file would also have to grow to accept the file being uploaded. Also, each time these files are enlarged in 1-MB increments, it causes fragmentation of your hard drive. As you can imagine, this can have an enormous impact on your SharePoint performance.
This is why it is important that you carefully consider how much information will be contained within most of your SharePoint databases, as well as how much information will be added, modified, or deleted, before you modify the Initial Size setting in the Model database. After you make the change, all new databases created using the Model database will begin with that Initial Size value, which will eliminate—or at least reduce—the need for Autogrowth to occur. There is no magic number that is best for the Initial Size setting of the content databases; you must perform a careful analysis to make that determination yourself. However, the best practice is that the size of your content databases should not exceed 100 GB. This is a soft limit that will increase the chances of performing a recovery in less than four hours.
Note:
Your database transaction log initial size is normally 25 percent of the size of the associated data file.
SharePoint 2010 (recommended max) |
|
Items per view | 5000 |
Documents per library | 10 million |
Database size |
200GB (up to 1TB for workloads) |
Simultaneous Doc Editors | 10 (max at 99) |
Column | New Row Wrapping (8,000 bytes) |
Content Databases per Web App | 300 |
App Pools per web server | 10 |
Indexed (Crawl Count) | 100 Million per search Application |
Site Collections per Web App |
500,000 |
Microsoft has an good article on SQL Capacity planning and configuration. and Joe has a good one for SharePoint 2010 Capacity Planning