Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors

Hello All,

After many performance issue investigations,  we have released at 10th of October 2015  following kb article for about “Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors”

In this article scope we make availability and  some flexiblity for database maintenance operations about  preventing “outdated update statistics” for DBAs , and now you are not depending just only SharePoint Daily Timer job which responsible update database statistics by using the proc_updatestatistics SQL procedure anymore.

Our TechNet article “Best practices for SQL Server in a SharePoint Server farm” has now been updated with the same guidance and cross referencing the new KB article.

Do not enable auto-create statistics on SharePoint content databases. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. We recommend updating the SharePoint content database statistics daily using the FULLSCAN option from SQL Server. Although SharePoint does have a timer job to update statistics by calling proc_updatestatistics, we strongly recommend implementing a scheduled maintenance plan from SQL Server to ensure database statistics are reliably updated on a daily basis. For more information, see Outdated database statistics.

Best practices for SQL Server in a SharePoint Server farm

Now ; to prevent  potential service outages, SQL Server maintenance plans can be implemented to keep SharePoint content database statistics updated by using the FULLSCAN option and it can be done manually by DBAs

When implementing the SQL Server maintenance plan to update the statistics on your SharePoint databases, it is not required to disable the job from SharePoint. However, because these maintenance tasks perform similar functions from both locations, it is permissible to disable the timer job from the SharePoint farm.


About SharePoint 2013 Virtualization and Best Practices

Best Practices

  • For the highest level of performance, configure a VP:LP ratio of 1:1 for any virtual machine that is used in a SharePoint 2013 farm. Remember that oversubscribing the CPU on the physical host used for virtualization can reduce performance.
  • For optimal performance of demanding workloads, run Windows Server 2012 Hyper-V on SLAT-capable processors/hardware. This offers the additional benefits of improved performance, greater virtual machine density per host machine, and reduced overhead as compared to non-SLAT systems.
  • When you are planning how to use the host server’s memory, it is important to consider the virtualization-related overhead. Whether you choose to use NUMA or Dynamic Memory, both have some overhead related to memory management in the virtualized environment. In the case of SharePoint environments, Microsoft does not support the use of Dynamic Memory, or technologies similar to Dynamic Memory found on alternative hypervisor platforms. This is because certain features of SharePoint can suffer from performance degradation when Dynamic Memory is enabled. For example, the cache size for the Search and Distributed Cache features are not resized when the memory allocated to the virtual machine is dynamically adjusted.
  • In most production SharePoint Server deployments, we recommend that you have at least 8 GB of RAM on each web server. Capacity should be increased to 16 GB on servers that have greater traffic or deployments with multiple application pools set up for isolation.

In Summary  : I am always sharing following rule with our customers ;

“The Golden Rule for SharePoint 2013 Virtualization” : Configure your virtual machines like a Physical Machine with all dedicated resources ( CPU,RAM,HDD etc.)  for any hypervisor platform and avoid shared Resources.

SharePoint Workflow Configuration Common Issues

  • Unable to connect to the remote service

PS C:\Users\mossadm> Register-SPWorkflowService  -SPSite “http://www.contoso.com” -W
orkflowHostUri http://wfm.contoso.com:12291 -AllowOAuthHttp
Register-SPWorkflowService : Unable to connect to the remote service at
http://wfm.contoso.com:12291/SharePoint/. See InnerException for more details. Client
ActivityId : e592f590-80d3-4f43-9118-39e526e3c5ff.
At line:1 char:1
+ Register-SPWorkflowService  -SPSite “http://www.contoso.com” -WorkflowHostUri
http:/ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (Microsoft.Share…WorkflowService:
RegisterSPWorkflowService) [Register-SPWorkflowService], WorkflowEndpointN
+ FullyQualifiedErrorId : Microsoft.SharePoint.WorkflowServices.PowerShell

In here ; SharePoint is telling you that it cannot find the Workflow Manager service endpoint at this address
– Check for the Firewall and possible networking issues.
– Make a browser test that you can browse the workflow host uri
-Check the WFM IIS for the bindings of the Workflow Manager Site
-Check that the workflow manager  IIS to make sure that the Workflow Manager Front End is running on correct port !.

  • When you try to publish a workflow you may face following issues.

Microsoft.SharePoint.SPException: App Management Shared Service Proxy is not installed.
at Microsoft.SharePoint.AppRegistration.GetProxy(SPServiceContext serviceContext)
at Microsoft.SharePoint.AppRegistration.AddOrUpdateAppNoPermissionCheck(SPAppPrincipalInfo appInfo)
at Microsoft.SharePoint.SPAppPrincipalManager.RegisterWithInternalDirectory(SPAppPrincipalIdentityProvider identityProvider, String nameIdentifier, String displayName, List`1 appEndpointAuthorities, List`1 redirectAddr

You can face this  because App Management Service application is not provisioned or the App Management Service is not running or the App Management Service Proxy is not added to the default proxy group.
-Check the app management service from CA -> Application Management -> Manage Service Application . If it is not provisioned , provision it.

Then if you face this ;
Microsoft.SharePoint.SPEndpointAddressNotFoundException: There are no addresses available for this application.
at Microsoft.SharePoint.SPRoundRobinServiceLoadBalancer.BeginOperation()
at Microsoft.SharePoint.Administration.SPServiceApplicationProxyBase`1.ExecuteOnChannel(Boolean requireDelegation, Action`1 codeBlock)
at Microsoft.SharePoint.AppManagement.AppManagementServiceApplicationProxy.GetScaleOutDatabaseMap()
at Microsoft.SharePoint.SPScaleOutDatabaseMap.GetMapCacheEntries

-Dont forget to start App Management Service from CA-> Services on Server -> App Management Service
Make an IISReset

  • When you try to run a SP 2013 workflow, you get a ‘suspended’ error message, and the error states;
    RequestorId: <Guid>. Details: RequestorId: <Guid>. Details: An unhandled exception occurred during the execution of the workflow instance. Exception details: System.ApplicationException: HTTP 401 {“error_description”:”The server was unable to process the request due to an internal error.

The reason may the security service application is unable to identify the user id from the user claim

-Open IIS Manager, navigatred to Application Pools > Click on the app pool named “Security Token Serice Application Pool”
-Click Advanced settings
-Modified the value for the property named “Load User Profile” from FALSE to TRUE
-Perform an IISRESET /noforce

About UserNotFoundException when SharePoint AD LDS (LDIF) sync operation

You are trying to Configure profile synchronization using a Lightweight Directory Interchange Format (LDIF) file in SharePoint 2013 Using the following article : http://technet.microsoft.com/en-us/library/ff959234.aspx. You have successfully used this method in your SharePoint 2010 farm, however when you try to configure it in SharePoint 2013 and attempt a synchronization, you an ma-extension-error.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.AggregateException: One or more errors occurred. —> Microsoft.Office.Server.UserProfiles.UserNotFoundException: A user with the specified SID could not be found in the domain.  Check the spelling of the account name ‘johnd@contoso.com’ and try again. —> System.ComponentModel.Win32Exception: No mapping between account names and security IDs was done
at Microsoft.Office.Server.Utilities.Win32.AdvApi.LookupAccountName(String lpSystemName, String lpAccountName, IntPtr Sid, Int32& cbSid, StringBuilder ReferencedDomainName, Int32& cchReferencedDomainName, SID_NAME_USE& peUse)

Reason for this error

The objectSid attribute was not included in the LDIF file.  The objectSid is required in SharePoint 2013 to process the accounts listed in the LDIF file.

For resolution :

1. Go to the LDIF MA, right click and select properties
2. Select Configure Attributes
3. Select New
a. Name: objectSid
b. Type: Binary
c. Select Ok
4. Go to the LDIF MA, right click and select properties
5. Now Select “Define Object Type”
6. From the Object types: select user and click Edit
7. Select objectSid and put it into the May have attributes:
8. Select OK
9. Select Configure Attribute Flow
10. Expand the user object
11. From the Data source attribute, select objectSid
12. From the Metaverse attribute, select objectSid
13. Mapping Type is Direct
14. Flow Direction is Import
15. Select New
16. objectSid displays in the Configure Attribute Flow
17. Select OK
18. Right click the MOSS MA and select properties
19. Select Configure Attribute Flow
20. Verify that the SID to objectSid attribute flow exists
21. Select OK
22. Open your LDIF file for edit
23. Add the objectSid to your accounts
24. Save the file
25. Run a Full Sync

An example from my test LDIF file

dn: CN=John Doe,CN=Roles,CN=Partition,DC=Contoso,DC=COM
changetype: add
displayName: John Doe
userPrincipalName: johnd@contoso.com
sn: Doe
mail: johnd@contoso.com
givenName: John
objectClass: user
objectSid:: AQUAABTfkXMrX0BU0ChCzd4FhEeWw8XrYl1T+Q==

-How you find the correct sid ? You need to extract correct sid from AD LDS.
ldifde -f “c:\import.ldif” -s “localhost:389″ -d “CN=partition,dc=contoso,dc=com” -r “(objectClass=user)” -l “dn,changetype,displayName,userPrincipalName,mail,givenName,sn,objectSid

Technet Distributed Cache Articles are updated !

Heads up , Technet Distributed Cache Articles are updated !

Plan for feeds and the Distributed Cache service in SharePoint Server 2013

Microsoft has updated the following sections:

• Capacity planning for the Distributed Cache service
• Memory allocation

The memory issues have been noted here. Now we are requiring at least 34GB of memory for 16Gb Cache Servers. If more than 16GB’s are required, we now require at least 2 cache servers

Manage the Distributed Cache service in SharePoint Server 2013

Microsoft has updated the following section:

*Fine-tune the Distributed Cache service by using a Windows PowerShell script
*Changed the Graceful Shutdown procedure

In the “Fine-tune the Distributed Cache” section we called out the MaxConnectionsToServer issue and have a script to tune these settings. We are also tuning the “DistributedLogonTokenCache” and “DistributedViewStateCache” as the defaults are too small.

This should eventually eliminate many support cases that occur after customers follow bad advice from random Blogs and utilize default settings.

Do we really need a Load Balancer between Workflow Manager Farm and SharePoint ?

Well , It  depends .

If you have a Workflow Manager farm (more than one server) and one of the WFM EndPoints recieve a request without any load balancer ,it balances the load across the WFM servers .Yes thats correct . WFM farm have an internal load balancing mechanizm to do that.


But if you don’t use a load balancer ,WFM only balance the load , not switch between endpoints if active endpoint dies . There is only one endpoint be active at a time . And if something happens on that EndPoint or its host machine then you may face an outage even your other servers are alive. Because SharePoint knows only one endpoint url and it is not reachable.

So it depends that how much you want a high availability . Actually in real , load balancer is resposible not to share load , just keeps high availability if an endpoint dies  between your SharePoint farm and WFM farm .

Can we configure Exchange Autodiscover with My Site Host URL for SharePoint 2013 ?

it is only possible for Exchange 2013 and SharePoint 2013

You can find the details and scripts in below article
Configure Exchange Autodiscover with a My Site Host URL in SharePoint Server 2013

What about if we use Exchange 2010 ?

No. It is not possible for Exchange 2010

It will not work for Exchange 2010 because the script tries to update the property named msExchServiceEndPointURL which is new in Exchange 2013.

[string]$propertyName = “msExchServiceEndPointURL”
Write-Host “`nProperty name: $propertyName”

Therefore, it is expected that you receive error “Error setting property” when you have Exchange 2010 server with Exchange 2010 schema. http://technet.microsoft.com/en-us/library/bb738144(v=exchg.150).aspx