16

I am investigating a problem with our website that uses SQL server to manage sessions. The website is asp.net webforms based around the sitecore CMS. We have the same code in various environments e.g. QA, staging, and production.

In production, what we are seeing is, periodically, we get a rapidly rising CPU usage that does not correlate in any way to traffic to the server. Along with this cpu spike, we are seeing a corresponding spike in network I/O.

Our monitoring software does not differentiate between traffic out to the internet and traffic to the DB server; however, what we are seeing on the DB server is literally hundreds of calls per second to dbo.TempGetStateItemExclusive3 in the asp session database, all for the same session id, and no corresponding amount of page requests coming into the web servers.

With the same code and config, we simply do not see this behaviour for other environments. We also do not see it for other session ids, just this one specific one.

Deleting the row from the database simply results in it being recreated with the same session id.

UPDATE

I have found this error in the event log:

Violation of PRIMARY KEY constraint 'PK__ASPState__C9F49290145C0A3F'. Cannot insert duplicate key in object 'dbo.ASPStateTempSessions'. The duplicate key value is (sessionidwiththeproblem). The statement has been terminated.
Stack trace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean\ breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand\ cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,\ TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,\ RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult\ result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.SessionState.SqlSessionStateStore.SqlExecuteNonQueryWithRetry(SqlCommand\ cmd, Boolean ignoreInsertPKException, String id)

Anyone any ideas how a duplicate session id could possibly be attempted to be created?

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
Ben Robinson
  • 21,601
  • 5
  • 62
  • 79
  • Have you been able to see a pattern with how long these spikes normally last, how frequently they occur, and / or what time of day they occur? – Josh Darnell Mar 22 '13 at 15:46
  • This is just kind of a shot in the dark, but are your app pools configured (significantly) differently between the different environments? I'm wondering if the mass of DB calls lines up with some kind of scheduled / regular cycling of the production app pool (app pool resets, and the ASP.NET runtime is restoring all the non-expired Sessions from SQL Server at once). – Josh Darnell Mar 22 '13 at 16:15
  • The spikes last until the app pool is recycled. This usually clears the problem down for a little while. There is no real pattern they seems to happen at random. We previously had app pool recycling at a scheduled time every day, we have had to change this to every 2 hours to try and mitigate this issue. If it helps we have 2 web front end servers load balanced using sticky sessions. Our ISP techs tells us there is no corresponding traffic coming in over the LB so it's like some runaway thread is making these calls. – Ben Robinson Mar 22 '13 at 16:32
  • Currently we're facing the same issue on our production server and still didn't find a real explanation of that behaviour. Only app pool restart helps. – John Smith Nov 03 '13 at 09:20
  • Which does ***monitoring software*** you use ? – Kiquenet Oct 28 '16 at 06:01

3 Answers3

10

We've faced a similar problem having the following configuration:

  • IIS 7.5
  • .NET Framework 4.0
  • Windows 2008 (on both the IIS and the DB server)
  • sessions managed by the ASPState database

The problem was that sometimes some of the sessions remained locked into the ASPState database, resulting in hundreds of calls per second to dbo.TempGetStateItemExclusive3 for each of the locked sessions.

The CPU on the IIS server would eventually go up with the number of locked sessions. A temporary solution was to recycle the application pool.

Going further and enabling the Tracing on the IIS server(s) and then analyzing the traces we noticed that whenever there was a problem (i.e. network connectivity problem that caused a 500 Internal server error) in the EXECUTE_REQUEST_HANDLER module, the next module which is RELEASE_REQUEST_STATE (and should unlock the session) was not be executed. Thus the session remained locked.

It turned up to be a bug from IIS and we fixed it by changing the value of the uploadReadAheadSize to 0 in the web.config:

<system.webServer>
  <serverRuntime uploadReadAheadSize="0" />
</system.webServer>

The UploadReadAheadSize property establishes the number of bytes a Web server will read into a buffer and pass to an ISAPI extension. This occurs once per client request.

See also: ManagedPipelineHandler for an AJAX POST crashes if an IE9 user navigates away from a page while that call was in progress

Community
  • 1
  • 1
Adrian M
  • 101
  • 1
  • 3
  • We are seeing this same issue in production and are considering using the solution you found. I am not familiar with that property, though, and I am having trouble finding anything about the effects of changing it on any other part of the site. Would you be able to provide more detail about what other effects setting this to 0 would have? – Geneb Nov 12 '15 at 22:28
  • Which is `SQL statement` for view **sessions remained locked** into the _ASPState database_? HOW **monitoring** _hundreds of calls per second_: SQL Profiler or another software? – Kiquenet Oct 28 '16 at 06:02
  • Which are modules that IIS executes and the ***order*** ? first EXECUTE_REQUEST_HANDLER module, second RELEASE_REQUEST_STATE, ... – Kiquenet Oct 28 '16 at 06:07
0

Looks like a SQL issue rather than a Sitecore one, possibly related to sessions not being cleared up. I'm not a DBA but is the the SQL Agent enabled ? Is your production SQL server at a different service pack/patch level to the other environments (this article mentions some old hotfixes for a similar issue)?

Some links for investigation, until maybe someone can answer this more specifically ! You might want to include some info on what versions of SQL you are using.

http://jerschneid.blogspot.co.uk/2010/01/aspnet-sql-server-requests-timing-out.html

https://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/

Community
  • 1
  • 1
Stephen Pope
  • 3,551
  • 1
  • 17
  • 21
  • Thanks for your input, yes SQL agent is running and the cleanup job is running correctly. There are maybe 400 rows in the sessionstate table and the DB is around 15 MB. – Ben Robinson Mar 22 '13 at 16:27
0

To explore the notion that is could be something in the application setup within IIS, you could dump out the config of your site using web deploy (msdeploy). Then run a compare on the output between the box exhibiting the problem and another that doesn't.

Something like this will output to console

msdeploy –verb:dump –source:appHostConfig="Default Web Site"

or as XML 

msdeploy –verb:dump –source:appHostConfig="Default Web Site" -xml

See http://technet.microsoft.com/en-us/library/dd569101(v=ws.10).aspx

ErikE
  • 48,881
  • 23
  • 151
  • 196
Paul George
  • 1,788
  • 16
  • 33