When using Lookup Column facing exceed list view threshold

Recently faced following issue ; To clarify that i want to share how i reproduce it ;
This issue can be reproducable SPO, SharePoint 2013 and 2010

+Create a list named “LargeOne” with 2100 item
+Set the list view threshold limit 2000 for this list
+Create another list for lookup named “LookupOne”
+Add some data in LookUp List
+Add a Lookup column to “LargeOne” list as pointing the “LookupOne” List (For example title)
+Index that column in LargeOne List
+Create a view named “testview” by filtering related LookupColumn and set row limit 100

Occurence :
View not show items and facing following error
“The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator”

Expected :
View shows items

What you will see in ULS Logs
01.06.2015 14:15:07.70 w3wp.exe (0x3978) 0x1694 SharePoint Foundation Health 46ri High
Throttled:Big list slow query. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070024 List internal name, flags, and URL: {976E4B58-476A-4B5F-A4D5-7AB44279BD71}, flags=0x0000000020801080, URL=”https://blog.bugrapostaci.com/Lists/Largeone/testview.aspx&#8221; Current User: 2110 Query XML: “<Query><Where><Eq><FieldRef Name=”SiteID”/><Value Type=”Text”>999</Value></Eq></Where></Query>” SQL Query: “N/A”

01.06.2015 14:15:07.70 w3wp.exe (0x3978) 0x1694 SharePoint Foundation General xxpm High
Unable to execute query: Error 0x80070024

01.06.2015 14:15:07.74 w3wp.exe (0x3978) 0x1694 SharePoint Foundation Web Parts 89a1 High
Error while executing web part: Microsoft.SharePoint.SPQueryThrottledException:
The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator
—> System.Runtime.InteropServices.COMException (0x80070024):
The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator.

Well this issue is “By Design” . The throttling limit is enforced in order to protect the health of the SharePoint server. Looking up the value of the lookup list causes the throttling limit to be enforced.  When making a query to SQL, we limit our queries to less than 5000 items.   There is a hard coded functionality on SQL that will lock the entire table if the query exceeds 5000 items.  In order to offset this we set the list threshold to a value equal or less than this to avoid such actions.

And documented following article.
https://support.office.com/en-us/article/Manage-lists-and-libraries-with-many-items-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US#_Toc346115857
“Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index.”

And more extra info: Indexing the column in the other list or library does not improve performance of the lookup operation

For a workaround you think that you may be  disable throttling ,  but this time for a big possibility you will face performance issues.

$weburl=”http://<URL>&#8221;
$listname=”NameOftheList”
$web=Get-SPWeb $weburl
$list=$web.lists[$listname]
$list.enablethrottling = $false
$list.update()

On this manner we suggest make a good planing for handle large lists when you are using SharePoint otherwise that could create a real pain and lots of jobs to do.

Advertisement

SharePoint Slow Warm-Up Problem

It is a well known issue that  if you have a SharePoint and after you recycle the application pool of site or make an iisreset or any movement that force to recycle (web.config modifications etc.),When the first request send to the server , the response of the server is very slow for the first time like 2 minutes. After this first slow reponse ,your server begins responding very quickly. We are calling this duration as warm up state.
So what’s happening in warm up ? In warm up state basically your worker process are getting up, while a worker process is getting up, the necessary assemblies are compiled and loaded , and needed configuration files are read and prepare the process for serving  the requests. If your web site has integrated with other sites in same server also may need to warm up sequence for other worker process and the process of our site can wait other process’s warmup to create a reliable response to you. And expected that all this operations could take some time that is depended on your assembly count, configuration, hardware etc.

But what if it is taking more than usual ? For example assume  that you have identical sharepoint site hosting in identical two farm and the warm up times are different like first instace of the site hosted server A have 20-30sec warm up and second instance of the site hosted in Server B it takes 180seconds.. So there should be a problem here.

This problem occurs because a .NET Framework 2.0 managed assembly that has an authenticode signature takes longer than usual to load.when loading signed assemblies the .net Framework 2.0 checks the Internet based Certificate Revocation list for verifing these signatures and the signatures is always verified when the .NET Framework 2.0 managed assembly that has an authenticode signature is loaded .And SharePoint has lots of signed assemblies.For some environment if does not have any Access to internet it causes a delay like 2 minutes to respond .

If you collect a process monitor log from your worker process , you may face the time gap bettween fallowing context.

14:57:39,6298324 w3wp.exe 3648 TCP Reconnect SERVERB.postman.local:63017 -> 80-239-149-10.customer.teliacarrier.com:http SUCCESS Length: 0, seqnum: 0, connid: 0 14:57:46,6170930 w3wp.exe 3648 RegCloseKey HKU\S-1-5-21-220523389-842925246-682003330-29313\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Connections SUCCESS

0 ntoskrnl.exe CmpCallCallBacks + 0x1c0 0xfffff80001a25fc0 C:\Windows\system32\ntoskrnl.exe
1 ntoskrnl.exe  ?? ::NNGAKEGL::`string’ + 0x364df 0xfffff800019c28f6 C:\Windows\system32\ntoskrnl.exe
2 ntoskrnl.exe ObfDereferenceObject + 0xd4 0xfffff8000168a3d4 C:\Windows\system32\ntoskrnl.exe
3 ntoskrnl.exe ObpCloseHandleTableEntry + 0xc4 0xfffff80001973b94 C:\Windows\system32\ntoskrnl.exe
4 ntoskrnl.exe ObpCloseHandle + 0x94 0xfffff80001974144 C:\Windows\system32\ntoskrnl.exe
5 ntoskrnl.exe KiSystemServiceCopyEnd + 0x13 0xfffff80001680453 C:\Windows\system32\ntoskrnl.exe
6 ntdll.dll ZwClose + 0xa 0x7748140a C:\Windows\System32\ntdll.dll
7 kernel32.dll RegCloseKey + 0xbc 0x77324cd0 C:\Windows\System32\kernel32.dll
8 winhttp.dll CRegBlob::~CRegBlob + 0x1c 0x7fef98fb768 C:\Windows\System32\winhttp.dll
9 winhttp.dll ReadWinInetProxySettings + 0x1fb 0x7fef990a9f7 C:\Windows\System32\winhttp.dll
10 winhttp.dll WinHttpGetIEProxyConfigForCurrentUser + 0x28a 0x7fef990a674 C:\Windows\System32\winhttp.dll
11 cryptnet.dll InetGetProxy + 0x11e 0x7fef69eae42 C:\Windows\System32\cryptnet.dll
12 cryptnet.dll InetSendAuthenticatedRequestAndReceiveResponse + 0x190 0x7fef69e9237 C:\Windows\System32\cryptnet.dll
13 cryptnet.dll InetSendReceiveUrlRequest + 0x57e 0x7fef69e983d C:\Windows\System32\cryptnet.dll
14 cryptnet.dll CInetSynchronousRetriever::RetrieveObjectByUrl + 0xc4 0x7fef69e9d9c C:\Windows\System32\cryptnet.dll
15 cryptnet.dll InetRetrieveEncodedObject + 0xbf 0x7fef69e29c5 C:\Windows\System32\cryptnet.dll
16 cryptnet.dll CObjectRetrievalManager::RetrieveObjectByUrl + 0x15c 0x7fef69e2ef0 C:\Windows\System32\cryptnet.dll
17 cryptnet.dll CryptRetrieveObjectByUrlWithTimeoutThreadProc + 0xc1 0x7fef69e9acc C:\Windows\System32\cryptnet.dll
18 kernel32.dll BaseThreadInitThunk + 0xd 0x7732652d C:\Windows\System32\kernel32.dll
19 ntdll.dll RtlUserThreadStart + 0x1d 0x7745c521 C:\Windows\System32\ntdll.dll

 

This proves that the CRL check causes delay. For a resolution you can disable CRL check on .net level

You can find more detail and how to fix it this problem in following KB Article. http://support.microsoft.com/kb/936707

This hotfix adds the generatePublisherEvidence configuration setting to the .NET Framework 2.0. After you apply this hotfix, you can use this configuration setting to disable signature verification in a .NET Framework 2.0 managed application.  You can use this configuration setting in an application configuration file.  To do this, add the following code to the <ApplicationName>.exe.config file for the .NET Framework 2.0 managed application:

<configuration>
	<runtime>
		<generatePublisherEvidence enabled="false"/>
	</runtime>
</configuration>

If your application is hosted in IIS, change one of the following:

  • C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet.config
  • C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config

NoteOn x64 machines, you must also change one of the following:

  • C:\Windows\Microsoft.NET\Framework64\v2.0.50727\aspnet.config
  • C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG\machine.config

Grouped list views don’t expand items are missing in document library for SharePoint 2010 after SP1 install

After installing SP1  and modify a view as grouped view you may have noticed that grouped list views don’t expand and under the group,items are missing.To verify this error you have to open your page in SharePoint server’s browser. Becuase by default if you brows a sharepoint page in SharePoint server the debug js has been shown and you can see fallowing error.The problem has happening for other language except English.
Message: ‘g_ExpGroupXSLTQueue’ is undefined
Line: 1
Char: 31898
Code: 0
For resolving this problem you may upgrade your SharePoint with minimum patch level of August CU 2011 or latest installing latest cumulative update.
Please check fallowing link to find latest cumulative update for SharePoint.
http://technet.microsoft.com/en-us/windowsserver/sharepoint/bb735839.aspx

Getting null reference exception when try to open list settings (Listedit.aspx) for Sharepoint 2010

This is a typical  error scenario that you have if you move or copy some sharepoint core dll’s which is referenced fr0m another folder.There is common mistake when developing custom applications that moving and copying sharepoint core dll’s to where is not in original folder (for example coping Microsoft.Sharepoint.dll to BIN folder of web application)  or unnecesserly register in GAC (for example registering Microsoft.Sharepoint.ApplicationPages.dll to GAC).

Why is this dangerous ? i can simply say when you upgrade or install any patch on sharepoint , the copied dlls form original location  can not be upgraded  by default .So this cause a huge anomality in your system. DLL version integrity is very important issue for every Microsoft product. So be careful for this operations , after all if you still need to copy or move operation, that upgrading copied dlls is your responsibility.

So lets keep going with our error :

when you click the one of list’s list settings (listedit.aspx) you can probably getting fallowing error in application event logs:

Exception information:
Exception type: NullReferenceException
Exception message: Object reference not set to an instance of an object.

Stack trace:    at Microsoft.SharePoint.ApplicationPages.ListEditPage.SetSectionLinks()
at Microsoft.SharePoint.ApplicationPages.ListEditPage.OnLoad(EventArgs e)
at ASP._layouts_listedit_aspx.OnLoad(EventArgs e) in c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\listedit.aspx:line 25
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

And if you enable custom errors from  in your web.config which is in Layouts folder.You can see the error in browser:

NullReferenceException: Object reference not set to an instance of an object.]
Microsoft.SharePoint.ApplicationPages.ListEditPage.SetSectionLinks() +426
Microsoft.SharePoint.ApplicationPages.ListEditPage.OnLoad(EventArgs e) +896
ASP._layouts_listedit_aspx.OnLoad(EventArgs e) +290
System.Web.UI.Control.LoadRecursive() +65
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2427

For resolving this issue you should find the related copied assembly files which according this scenario is Microsoft.Sharepoint.ApplicationPages.dll in use for unfamiliar locations.

the orginal location for this dll is  Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\BIN

1) First Check the GAC this dll should not be added to GAC if it is upgrade latest correct version or delete the dll.
2) Check other possible existance may it be used in BIN folders of web applications. For getting all list of any dll location you can use this script:
https://blog.bugrapostaci.com/2011/12/26/powershell-find-files-with-versions/ this powershell command gives you a list that defined dll locations and versions.

For best practise if you need any reference to Sharepoint DLL’s use and reference them in original locations .if not you always remember to upgrade latest versions where it have been that is your resposibility.

See you next time.

 

 

 

 

 

 

Sharepoint 2010 List all imported profile users by PowerShell

#PowerShell Script - List All User Profiles - SharePoint 2010
#The scripts is distributet "as-is." Use it on your own risk. The author give no warranties, guarantees or conditions.

 #Add SharePoint PowerShell SnapIn if not already added
 if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
#Get ServiceContext from associated site
$site = new-object Microsoft.SharePoint.SPSite("http://blog.bugrapostaci.com:8090");  
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);  

#Get UserProfileManager from the My Site Host Site context
$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)  
$AllProfiles = $ProfileManager.GetEnumerator()  

foreach($profile in $AllProfiles)  
{  
    $DisplayName = $profile.DisplayName  
    $AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value  

    #Do not delete setup (admin) account from user profiles.
    if($AccountName -ne "BLOG\Mossadmin")
    {
        write-host "Profile: ", $AccountName
    }

}  
write-host "Finished." 
$site.Dispose()

note:this code is not belong to me thanks for that . I changed it a little .)