Large List Throttling over 5000 items in SharePoint 2010

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 a SQL script 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

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;