Monday, July 31, 2017

Background

While working with Sitecore Support to troubleshoot a SQL session issue that we encountered on a high-traffic, scaled, Sitecore environment running Sitecore 8.1 Update 2, we discovered that the root cause of the issue was a connection leaking bug in the SessionStateStoreProvider that causes unnecessary load on SQL server making it unresponsive.

The purpose of this post is to arm you with the information that you need to implement a stable SQL Session State in your Sitecore deployment.

Symptoms

When the issue / outage occurred, the exceptions in the Sitecore logs where the following:

Our New Relic Application monitoring system reported that the GetExpiredItemExclusive SQL stored procedure that was running against the session database was the most time consuming and was responsible for the highest throughput between our Sitecore and SQL server.

We discovered that once the execution of the stored procedure got above the 10,000 calls per minute range, the application would start having trouble and would eventually stop responding.

Root Cause

It was determined that .NET was naturally creating a number of session state provider objects, and during high traffic periods, the number got so large that it caused too much load on the SQL server and eventually caused the application to stop responding.

Stabilizing Session State

The Patch

Sitecore Support issued us with a patch and noted that the issue was fixed in 8.2 Update 2 on.

From a high level, the change involved Sitecore using their own factory and creating session state objects manually.

The issue was registered as bug #98800. It's important to note that all prior versions will require a ticket to request the patch.

We implemented the patch by following these steps:

1) Put the attached 'Sitecore.Support.98800.dll' assembly to the /bin folder of the website.

Status

With the patch in place, and the final configuration updates, the application has been stable and has survived extremely high traffic days.

An example of one of these days: 40,000 requests per minute, 7500 simultaneous users and 142,000 page views per hour.

Takeaways

If you intend to use SQL Session State for your Sitecore implementation, and are running a version of Sitecore prior to 8.2 Update 2, you need to create a ticket with Sitecore support to request the patch.

After this, it's critical that you increase your polling interval configuration from the default 2 seconds to something higher like we did. 60 seconds seems to be the perfect number.

If you have any questions, feel free to submit a comment and I will help you out to the best of my knowledge about this issue.