Joel Oleson's Blog - SharePoint Land

Site collections per database – It is recommended to limit the number of sites in a database so it doesn’t grow to a less manageable size.    Out of the box you have 9,000 notification and 15,000.  If you leave this setting you’ll end up with all your sites in a single database.  Databases from a pure SQL standpoint can exist into the Terabytes, but if you haven’t managed databases of that size you’ll find it is more difficult to manage one large database verses a few large but manageable databases.  In MS IT the preferred limits are around 250 site collections per database.  The average size of a site in MS IT from farm to farm is around 60 to 120 MB in size.  With simple math if we round this to 100MB.  If I want a 50 GB database or thereabouts then I would cap my IIS Virtual server/Web application at two hundred and fifty average 100MB  sites (with room for outliers).  Here’s the math… #sites * avg size = estimated database size

Database size per database – This is totally up to your SQL folks.  We’ve found 50GB to fit our SLAs the best.  It keeps the number of databases to a minimum, without having a single huge  database.  I don’t recommend going much beyond 100GB as it becomes more difficult to move across the wire, less likely to fit on a single tape, less likely to come online in a reasonable period of time.  This isn’t a question of how well SQL server works, its about time, consistency, and bits traveling across the wire without interruption.  There are more options now in SQL 2005 for splitting databases into multiple files, but why do this when they can be independent themselves? 

Databases per SQL instance – The bottlenecks we saw on 32 bit systems were memory, specifically the MTL or Memory to Leave space.  Specific memory errors in the SQL log files which resulted in failed uploads and ultimately failed page loads would occur if the available MTL space became lower than page size.  With 64 bit systems we have not experience these same issues.  Recommendations based on our 32 bit systems were not to go more than 100 databases on an instance to avoid having memory issues, and with special tweaking we were able to do host 300 databases, specifically SPS 2003 databases which had a lot of not frequently accessed databases like the PROF and SERV databases. 

Total Database size on a given SQL Instance – How much data can you put on databases on a node?  Our experience said 2 to 3 Terabytes.  That was with tape backup and some fancy failover to provide a longer backup window and a third party database compression backup product which ultimately increased our backup speeds in addition to our multi threaded backup scripts.  With snapshots, SQL 2005, and log shipping type solutions, this number could vary.  The main considerations here are SQL connections (Memory) and network bandwidth and the contentions introduced with your backup solution.  You’d think that Disk I/O would be a huge consideration, but our experience on a large intranet was still showing that Disk I/O was not becoming a bottleneck.  With larger disks or SATA drives you should watch your disk I/O.  RAID 5 is a reasonable solution for data drives on SharePoint.  I do recommend 0+1 on the transaction log drives.

Portals per Farm, Extended IIS Virtual Servers, Web Applications – The natural route is to go with 1.  In most environments 1 is the right answer.  If you are going for a departmental web application solution, 2-10 is very reasonable, but you will need to start watching the application pools.  At 30 you are pushing the limits of the 32 bit system which can leverage 2GB of memory for the application pools.  You will have memory issues that will require special tweaking even before you get to 30.  Consolidating the application pools and isolating poor performing application pools is the right answer for hosting multiple web applications.  With 64 bit systems and 8GB of memory, you’ll have less of an issue hosting 30 web applications.  I do still recommend going the consolidation route for your application pools.  In a sense in 2007 the max number of SSPs you should ever need is likely around 3, but I don't recommend more than 10 in a farm.  Not sure why you'd ever need that many, but just in case....

App pools per server – SharePoint application pool worker processes under heavy stress can reach 800MB to 1GB of RAM.  These worker processes can be poor performing and slow if not cycled on a recurring basis such as nightly or if they are not set to cycle if they do end up poor performing.  My recommendation is to set them to not reach beyond 800MB of physical memory.  It is my experience that worker processes that go much beyond this have slow cycle times, slow stopping times and can hang in the request to recycle.  With 32 bit systems with 4GB of RAM (2GB available to the app), I don’t recommend more than 4 application pools of 1 worker process.  On 64 bit systems with 8GB of RAM or more it is reasonable to have 1 per 1GB+ of physical RAM.  When counting app pools, I do usually ignore the central admin app pool and SSP app pool they really aren't used that much unless you're using them to store content.  You could even setup the central admin app pool to stop after so many minutes of unuse.  I don't recommend doing that on any of the content app pools.

Worker processes per server & app pool – The best performing systems I’ve come across are those that limit the number of web applications to 1 (beyond the central admin/SSP) of course.  On those systems the application pool that serves the key content web application  I recommend adding additional worker processes building a web garden.  This web garden can provide extra CPU or horsepower and essentially speed up page load times and retrieval.  On 32 bit systems with web applications where there are more than three I don’t recommend having additional worker processes.  On 64 bit systems with 8GB of RAM or more, these options are more flexible.  With the rule of thumb of 1 app pool per 500-800GB of RAM, you can reverse these numbers to essentially break this down into worker processes where you don’t need additional app pools.  This gives you the ability to add the available memory into worker processes that can be leveraged to take advantage of the additional memory.  For example, lets say you have 5 content based web applications and they are all hit equally hard, and need to be isolated, and you have 8GB of memory.  Reserve 2GB for your OS, then divide the remaining 6 GB of RAM into 12 500MB groups then take out 2 for overhead and give each application pool 2 worker processes.  This could be divided a number of ways, such as giving 4 worker processes to the highest performing app pool, consolidating the others and isolating any poor performing.  I do highly recommend determining at what level of memory a worker process becomes poor performing or slow and limiting the max physical memory prior to this point, then using these limits for your math.  The math is very fuzzy here, but I hope you can see how these can be determined for your environment.

Site Collection Max Quota – prior to SP2 my max limits were 5GB for a site collection.  The main things to watch out here are the deletes and site collection backups for renames and moves or archiving.  A long running STSAdm backup in WSS v2 and SPS 2003 may cause locking (more likely pre SP2).  A lot of these issues have been fixed, but I am still cautious.  On a high performing system, you don’t want your site collection backups to cause blocking spids or blocking requests on the databases.  With careful planning of site collection backups and moving operational tasks to off hours on high performance systems, you can be more flexible on your site collection backups.  For database capacity planning and growth I do always recommend having quotas.  Always, always, always use quotas.  It encourages good practices of the site administrators to manage their storage, usage, etc…  By having a quota site collection administrators will be smarter about how they divide up their teams, projects, etc…  There is no hard limit of the size of a site collection.  I do recommend putting a site collection into its own content database if it plans to grow much larger than 5GB.  If a site because of business reasons and to take advantage of specific workflow or aggregation features is required to be large, then plan ahead and keep it in its own database.  What this does is provide a space where the blocking will not affect other sites whether it’s a large delete action, backup action, or other intensive operation.  I don’t recommend a ton of tiny 5MB databases as is meant to be understood with the number of databases per server.

File Upload Size – Out of the box is 50MB.  This upload size will provide the best user experience and provide assistance with planning the appropriate usage of the SharePoint system with the understanding that the data is stored in SQL database storage.  If you increase the size to 100MB you may have some users that are pleased and some that begin to have time outs when uploading over slow bandwidth.  If storage of files up to 100MB is a business requirement, then you’re good to go with some potential support issues.  With SP1 of WSS the upload limits in the product were able to support 2GB.  Basically this is the max that SQL supported and WSS simply stepped out the way allowing administrators to set this to whatever is needed.  It is possible to simply set your max limit at 2GB and put the burden on the user to determine the best storage for the files and deal with whatever latency or timeout issues they experience.  There are ways of tweaking the server timeouts as is mentioned in the WSS SP1 documentation.