I’ve been doing some work with a client lately around performance issues on their shiny new SharePoint 2010 farm – essentially we were getting poor SQL Query response times according to the SharePoint Developer Dashboard, but great response times from SQL Profiler. As an example:
|Query from SharePoint||Milliseconds||Milliseconds in SQL Profiler|
|SQL Query 1||24||20|
|SQL Query 2||36||35|
|SQL Query 3||450||32|
|SQL Query 4||688||41|
|SQL Query 5||707||63|
|SQL Query 6||684||55|
|SQL Query 7||870||34|
|SQL Query 8||45||42|
|SQL Query 9||60||57|
You can see that some of the SharePoint times for queries were roughly the same as the SQL times (plus a couple of milliseconds for network latency and clock tick rounding) and then others were dramatically different. Generally, standard SharePoint pages which only resulted in 2 or 3 queries were not noticeably slow from the user’s perspective but we had a custom page with 25-odd SQL queries in it, and the page load speed would vary between 3 seconds and 100 seconds (yes, over a minute and a half to load a web page). Also back-end processes like provisioning a new site collection would take forever to complete.
The one thing that you can say with some certainty based on the chart above is that there was a problem between the 2 applications (SharePoint and SQL) causing a slowdown in communications… After eliminating DNS and Authentication issues we eventually narrowed it down to a network issue, and then to this KB Article: http://support.microsoft.com/kb/2477730 – The TCP Chimney Offload feature fails on all network adapters in Windows Server 2008 R2 or in Windows 7 if you disable or change the properties of a network adapter.
What was happening is that a new Network Communications management feature (called the TCP Chimney Offload feature) in Windows 7 and Windows 2008 R2 was slowing down the communications at random intervals – this issue is particularly relevant to users of VM’s as it is easy to add and remove virtual NIC’s in a Virtual environment.
If you believe you have this problem and you are not on a network running Network Access Protection and you are not running a VPN or Firewall from this server, you can test whether the patch will solve your problem by disabling the feature:
- From the command prompt, run
netsh int tcp set global chimney=disabled
- Verify that it is now disabled by running
netsh int show global
- You should see this returned:
- Now run
net stop bfe
This will shut down the “IPSec Policy Agent”, the “IKE and AuthIP IPsec Keying Modules” and the “Base Filtering Agent” services.
Now test the page loads again and see if the performance has improved and remains consistent throughout the page load. If it has, then the hotfix will fix your problem and it may be worth implementing it. If the performance has not improved (have you also disabled the TCP Chimney Offload feature on your SQL Server / Cluster as well?) then you can roll back the changes above without permanently impacting the environment.
This only affects systems running on Windows 7 or Windows 2008 R2, so if both your SharePoint servers and your SQL servers are on 2008, this will not be your problem.