SP2010 : Large List Throttling over 5000 items - Query to Detect

  • Article

Hi all-

If you are planning to upgrade from SharePoint 2007 to SharePoint 2010 - you might want to consider your users that have list view lookups of over 5000 items and/or data column lookups of over 8 lookup columns. In SharePoint 2007, it was recommended not to go over 2000 list items to prevent poor performance on your farm.

 In SharePoint 2010 throttling is enabled by default (This is configurable however in Central Administration). Throttlng is set to not display list view lookups of 5000 items or lists that have over 8 lookup columns. The reason for this out of the box throttle is that if you are doing a lookup in SQL on less than 5000 rows, SQL executes a row level lock. If you execute a lookup with over 5000 items SQL executes a table lock which can potential impact all your site collections in that one ContentDB.

Here is a clever script written by a SQL guru on my team to help audit your farms for these large lists and over 8 lookup columns - so you can warn your customers if they have any lists that will be throttled after you deploy SharePoint 2010.

SELECT

      t3.fullurl [WEBURL],

      t1.tp_title [LISTNAME],

      COUNT(t1.tp_id) [ITEMCOUNT],

      ISNULL(MAX(t4.lookupCount),0) [lookupCount]

FROM DBO.alllists t1 with (nolock)

      JOIN DBO.alluserdata t2 ON t2.tp_listID = t1.tp_id

      JOIN DBO.webs t3 ON t3.id = t1.tp_webid

            LEFT JOIN

            (

            SELECT siteID,listID,COUNT(listID) [lookupCount] FROM dbo.AllLookupRelationships

            GROUP BY siteID,listID HAVING COUNT(listID) >= 8

            ) t4 ON t4.listID = t1.tp_id

WHERE t1.tp_title != 'User Information List'

GROUP BY t1.tp_id,t3.siteid,t3.fullurl,t1.tp_title

      HAVING (COUNT(t1.tp_id) >= 5000 OR MAX(t4.lookupCount)>= 8)

ORDER BY 1;

3/27 Update: Added no lock thanks to RonG for pointing this out. Make sure to run this query either in your Pre-Production farm or off-production hours as well. Thanks all