Sitecore high CPU usage – is the SQL Session State Provider the villain?

My massive Sitecore 9.1 installation started having CPU spikes, even when the servers did not seem to receive more requests. When CPU spiked, I could see that the requests queue would build up, until it crashed with the following error:

System.Web.HttpException (0x80004005): The request queue limit of the session is exceeded.
at System.Web.SessionState.SessionStateModule.QueueRef()
at System.Web.SessionState.SessionStateModule.PollLockedSession()
at System.Web.SessionState.SessionStateModule.GetSessionStateItem()
at System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData)
at System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.<>c__DisplayClass285_0.b__0()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

My internet search let me to believe that my SQL Session State Provider could be the issue.

Sitecore have some settings regarding session management. One is the pollingInterval:

PollingInterval – Specifies the time interval in seconds that the session-state provider uses to check if any sessions have expired.

The default polling interval is 2 seconds, which can lead to high load on the SQL server, and therefore a high CPU load, leading to longer response times, leading to the request queue not being processed.

Changing the pollingInterval is done 2 places, the SQL Session State Provider itself in the web.config, and the \App_Config\Sitecore\Marketing.Tracking\Sitecore.Analytics.Tracking.config config file

I changed my values to 60 seconds like this in the web.config:

<sessionState mode="Custom" cookieless="false" timeout="20" customProvider="mssql">
  <providers>
    <add name="mssql" type="Sitecore.SessionProvider.Sql.SqlSessionStateProvider, Sitecore.SessionProvider.Sql" 
	sessionType="private" 
	connectionStringName="session" 
	pollingInterval="60" 
	compression="true" />
  </providers>
</sessionState>

And in Sitecore.Analytics.Tracking.config:

<sharedSessionState defaultProvider="mssql">
  <providers>
    <add name="mssql" type="Sitecore.SessionProvider.Sql.SqlSessionStateProvider,Sitecore.SessionProvider.Sql" 
     connectionStringName="sharedsession" 
	 pollingInterval="60" 
	 compression="true" 
	 sessionType="shared" />
  </providers>
</sharedSessionState>

After this change, CPU usage have dropped, response times have dropped, the request queue does not build up, and I have not received any more request queue limit errors.

MORE TO READ:

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in Sitecore 7, Sitecore 8, Sitecore 9 and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.