Software Boundary Guidance for SharePoint 2013 Gotcha’s (Don't do this)

 

Recently published on Microsoft TechNet (30 October) were the Software boundaries and limits for SharePoint 2013 .  Some noticeable adds are in this guidance so please take the time and familiarize yourself with the changes.  Some of the guidance however did not change and a couple in particular I want to reference due to the fact on pretty much every engagement I go on with a customer these areas are not adhered to and in some cases blown completely out of the water.

List view lookup  threshold	8 join operations per  query	Threshold	Specifies the maximum  number of joins allowed per query, such as those based on lookup,  person/group, or workflow status columns. If the query uses more than eight  joins, the operation is blocked. This does not apply to single item  operations. When using the maximal view via the object model (by not  specifying any view fields), SharePoint will return up to the first eight  lookups.
List view threshold	5,000	Threshold	Specifies the maximum  number of list or library items that a database operation, such as a query,  can process at the same time outside the daily time window set by the  administrator during which queries are unrestricted.

 

To give an example of what I see customers do:

 
  
 Notice where I highlighted. 

These two setting changes will provide you with the opportunity one day to bring your farm to its knees and in most cases will also cause you to pick up the phone and contact Microsoft to open a case.  This is a case that can easily be avoided.. How?   By reading the Software Boundaries and Limits for SharePoint 2013.   We put this guidance out for a reason and the teams aren’t just randomly selecting numbers
out of the air and filling in the charts. We do extensive testing on our products with many different hardware configurations that we expect our customers to run under.  These boundary numbers are areas where we
will see performance degradation occur.  Also notice that some of these are soft boundaries and some are hard.   The Hard boundaries are supportable stop points and should not be exceeded.  The Soft boundaries fall in line with the example above.  This is a throttling limit we set.  You can adjust it if you like but you will face issues down the line.  Depending on your hardware you may not run into it for some time past
the limit.  But I can guarantee you that in some time you will run into an issue here. 

So let’s go through an example of what this customer faced.  The background here is that I was there for a week performing a Microsoft RAP (Risk Assessment Program) and this is one of the test cases that we
run. 

Scenario

I run through the test cases and I am starting my analysis of the FARM’s Health and Risks… I come across a case that states that the “List View Threshold is set to a value greater than 5,000”  At this point I look into the details and what I’m looking for are the number of list items shown.  In this case one list had 60K items.  Remember we are not concerned about the number of list items… were concerned with the View.    I then have the customer open up Central  Admin and I look at the Resource Throttling section for the Web Apps in question and low and behold I find this was changed.  Customer stated that they made this change
early on in the deployment but didn’t know if anything would be affected.   These types of findings allow us to amplify the importance of RAPs where we provide proactive side by side discussions with
customers.   Many times these discussions open interesting findings that we can hopefully correct so as not to have the customer face issues in the future.   During this conversation the customer mentioned they had an outage a few months ago and they were about to open a Premier Support Ticket but it suddenly normalized.  This was all I needed.  This little snippet of info allowed me to prove that this issue is real and was experienced (though the customer never found out root cause) 

Changing these settings will cause an issue known as SQL Escalation Lock.  Basically what happens is that you can have a very large list view in place.  You’re a member of a team site and you open
up that large list.  You’re freely viewing the items in this list and notice no issue.  Other users however are not so lucky.  You’re opening up that list in an exclusive mode where others will receive an error when trying to view.  But this is just one list.  Who cares right?   This gets better… follow along.   You are done viewing this list and close it.  This is where the real problem occurs.  What happens now is that your SQL Processors are probably spiked and remain spiked.  Performance is now tanked.  Why?  SQL is trying to remove the lock and depending on how large that list really is... it can take some time for it to heal itself.   This is a SQL thing… not so much a SharePoint thing.  SQL Queries are handled much better with the numbers we state in the Boundaries list.  In time however the CPU’s will normalize but in many cases the customers panic and call an outage (which this is) but we can avoid this from happening… read on.

What to do about this?   

Two methods are recommended   

1.
Create a hierarchal view of folders (Folder
View) and bucket the list items into these folders (Best resulting Performance) 2.
Create an Indexed View (easier to implement)

In MOSS the first option was the preferred choice.  However in SharePoint 2010 and 2013 what we see is you still start off with better performance with option 1 but once you start to hit a list size of 100K it starts to level off to same performance results.  When I work with my customers depending on how many lists are affected I generally will tell them to go with option 2 because its pretty simple to implement.  Option 1 will take quite a bit of work moving items into their desired folders and there will need to be some planning with regards to naming conventions.

Don’t believe me? Have a look at the info in this guidance https://technet.microsoft.com/en-us/library/cc262813.aspx#Throttling    (for those running SharePoint 2013 this guidance is still relevant)

 

Hope this avoids issues for someone in the future