List View Lookup Threshold

I was recently delivering a SharePoint Risk Assessment on a customers SharePoint 2010 environment, one of the issues identified by the assessment was that the List View Lookup Threshold for all Web Applications had been increased from its default setting of 8 to a huge 15! This is a really bad idea. Setting this to a value greater than 8 can cause some serious performance issues due to the potential increased load that this can place on SQL, this is described in Designing large lists and maximizing list performance - https://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx#Throttling.

The customer I was working with wasn't actually sure why this had been changed and also which (if any) lists relied on the setting being increased from it's default value. I put together a PowerShell script that iterates through all Lists within all Web Applications within the local farm and outputs the URL of all lists that have 8 or more custom Lookup Fields added (this doesn't include any of the out of the box Lookup Fields). The idea being that they could then work with the list owners to re-architect the list, with a view to reducing the List View Lookup Threshold back down to 8.

All you need to do is specify a suitable location to output this information to (highlighted) and run the script.

"Adding Sharepoint snapin..." | Write-Host -ForegroundColor Yellow
asnp *SharePoint* -ErrorAction SilentlyContinue
$Log = "C:\Log.txt"
"Lists with greater than 8 lookup fields: " + (Get-Date) > $Log
"------------------------------------------------------------" >> $Log
Foreach ($WebApp in (Get-SPWebApplication))
{
"Checking Web Application " + $WebApp.Name | Write-Host -ForegroundColor Green;
Foreach ($Site in $WebApp.Sites)
{
"-Checking Site " + $Site.Url | Write-Host -ForegroundColor Green;
Foreach ($Web in $Site.AllWebs)
{
"--Checking Web " + $Web.Url | Write-Host -ForegroundColor Green;
Foreach ($List In $Web.Lists)
{$Fields = $List.Fields | Where {$_.Type -eq "Lookup" -and $_.SourceID -ne "https://schemas.microsoft.com/sharepoint/v3"};
if ($Fields.Count -gt 7) {"List found with " + $Fields.Count + " lookup fields, Name:" + $List.Title + ", Web URL:" + $Web.Url + ", List URL:" + $List.DefaultViewUrl >> $Log}
}
}
$Site.Dispose()
}
}
"!Please check the log file for further details!" | Write-Host -ForegroundColor Yellow

Brendan Griffin

Comments

• Anonymous
  December 04, 2013
  Who doesn't have sharepoint lists with more than 8 lookups? Sure it hurts performance, but it still works. Instead of MS re-architecting SP to work as the customers prefer, MS is forcing customers to re-architect their libraries/lists. That's loyalty, for sure!!