Showing posts with label thresholds. Show all posts
Showing posts with label thresholds. Show all posts

Wednesday 27 June 2012

SP2010 List View Throttling

Overview:  IT pro's and developers in SP generally understand the properties that can be used in setting list views in SP.  Users of SharePoint and department site collection administrator types seem to find it confussing.


How Lists Throttling Works - Simplified:
SharePoint's default list view threshold is 5000 items.  This does not mean the list is up to 5000 items simply that your list view will querry the list and work with a maximum of 5000.  Now the point to understand is that if you are filtering on 6000 rows event though you are displaying 100, you have passed the threshold as you need to look at all 6000 that you are filtering/sorting.  If you add an index to the property that you are filtering/sorting on, it will work. 

"For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold. " from MS.

Other:
The smallest the list view threshold can be set to is 2000 results.
List thresholds can be increase for general users, this can have performance issues.

Glyn Clough explains list view throttling clearly in more detail.

Steve Peschka explains working with large lists.
Tutorial from MS.  This blog and movies explains view limit thresholds perfectly.

Thursday 5 May 2011

File Upload Size Limits

Problem: Need to store files in excess of 2GB in SharePoint.
Initial Hypothesis:
  • 50MB is the default upload limit set by SharePoint OOTB.  You can change this on the farm as described here by Dave Coleman up to 2GB or 2047MB. 
  • A common misconception is that by using RBS and not you content database to store the blob you can overcome this 2GB limit.  We it's partly true...  The maximum file size for a file in SQL Server is 2GB however, the next restraint is SharePoint 2010 Server Object Model and this has a hard limit of 2GB for an upload so moving to RBS won't overcome the problem.
  • I believe SharePoint limit's the upload to 2GB due to IIS's worker process w3wp.exe, to upload a file you need to use all the IIS available memory to upload the full stream.  Each w3wp.exe worker process runs well with 2-4GB of memory, this is not a boundary just a good idea (on x64), therefore this makes sense to me that the SP2010 team have limited any file upload to 2GB.
  • Also be aware that increasing you upload file size to 2 GB has performance ramifications so it a user uploads a file and there is no memory available no new requests can be handled until the memory is available again.
Resolution:  Store large files outside SharePoint and surface them in SharePoint.  I believe there is another solution available using a Telerik Silverlight upload control but I haven't tried it.
More Info:
http://blah.winsmarts.com/2010-3-Large_File_Upload_in_SharePoint_2010.aspx

Update 24/07/2013: SharePoint 2013 has the same hard limit of 2 GB for the maximum upload size.  Technet states 50 MB is the default limit for SP2013, the default from an OOTB install is 250MB which is the same value you get with SharePoint Online/Office 365.

Wednesday 15 December 2010

SharePoint 2010 boundries and thresholds

I attended a suguk.org event in London about a week ago.  John Timney did the 1st presentation session and asked a couple of questions on SharePoint limits.  I didn't know the answers, tried to think back to MOSS and what I'd seen previously.  The simplest question that I should know the answer to:

Qu: What is the maximum content database size supported by SharePoint 2010?
Ans: Microsoft supports Content databases up to 200GB in size.  In MOSS it was 100GB.  It is fairly common to see content databases considerably bigger than 100GB in MOSS that work.   The issue is how long does it take to perform operations on these content DB's such as backups moving content db's.  If you have a dedicated SAN, there is no reason not to go to much larger content databases however, they are not supported by MS.

More info on SharePoint's boundaries and thresholds from MS

Qu: What I/O speed does MS recommend for your SharePoint 2010 SQL database?
Ans:  I/O operations per second (IOPS).  The faster that SQL can handle request, results in faster return time and reduced que requests, so pretty important and a fairly common bottleneck.  This is often a reason why people choose not to virtualise SQL Server, it I/O intensive in SharePoint and really important to be fast.  Tip: Ensure VM's are thick provisioned for SQL Server. 

To determine you IOPS  use SQLIO Disk Subsystem Benchmark Tool (http://go.microsoft.com/fwlink/?LinkID=105586).

I guest the answer is as fast as possible but you can determine your IOPS requirement using the tool and you usage.  I go with ldf files on the fastest disk on the TempDB followed by ldf files for the content dbs on spinning disks.

Update 09/06/2011
Qu: Should I using seperate disks for mdf (data files) & ldf (transaction logs)?
Ans:  On small SQL server farms ensure that the transaction logs are stored on a different physical drive to the content databases as this will reduce contention and increate performance signigicantly.  Larger SQL instances like SANS have multiple disks so there is no need to seperate the files as this is already done by the nuber of disk readers.  You can also check the performance of a drive by watching the "disk seconds per read/write counters" which should be less than 20ms.  If the disk seconde per read/write is approachiing 20ms consider improving the disk speed or increasing the number of read points.
Update 22/08/2012 - Bigger architectures may use SSD/Flash memory as opposed to disks.  The IOPS are hugely improve as the is no disk search time.  http://technet.microsoft.com/en-us/library/cc298801.aspx#Section1_5a

Qu: What is the default SQL Server database growth setting sizes?
Ans: SQL Server 2008 will grow data files by 1MB and transaction logs by 10% increments.  I would start with an initial content database size of 100MB(adjust according to your anticipated demand) and autogrowth to be 50MB (adjust according to your system).  This general prinipal will result in the growth to the db's being infrequent so the associated performance hit is reduced, unused space being optimised as the percentage growth in the transaction log has huge incremental hit that are generally never reached after initial growth and less fragmented databases results in faster performance. 

More Info:
Summary of limits and thresholds
http://blah.winsmarts.com/2010-5-How_big_can_my_SharePoint_2010_installation_be.aspx
SQL Checklist for SharePoint 2013