Our site runs ASP.NET 4.5 using NLB across 4 web servers. We run about 2,000 to 3,000 concurrent sessions during a typical business day. We are currently storing sessions in SQL Server 2008 R2. We have used the InstallSqlStatePersist.sql script and we
have the updated DeleteExpiredSessions stored proc in place that has been around for some time. Our application pools use Integrated Pipeline. We find that when an error occurs on a page the page can get hung in the AcquireRequestState, which then contines
to hit the ASPState database every 500 miliseconds. After a while, perhaps 5 to 10 minutes, the page ends and stops hitting the SQL Server. Sometimes a few errors stack up and we see 10 or 20 requests that are stuck in this state. At that point the SQL
Server that is handling the ASPState database is pummelled with 150 to 200 SQL batches per second, most of which are trying to hit the TempGetStateItemExclusive3 stored procedure. It seems that the Locked field in the ASPStateTempSessions table is set to
1 for these session records and there is contention going on. If we go ahead and execute this query:
Update ASPStateTempSessions Set Locked = 0 Where Locked = 1
A few times in rapid succession, the pages that are stuck in AcquireRequestState suddenly move along their happy way, the load on the SQL Server goes back to normal, and everything is fine until some other errors kick off the problem again.
I know that we could enhance our application to try to tune it for ReadOnly access to the session object on a page-by-page basis. We do a lot of ajax, and that is just not a great solution either in the short term or long term. We really just want Session
to use a last-in-wins blocking philosophy and let a few chips fly if concurrent page requests from some impatient user cause conflicting values to be stored.
Lots of people posting in forums and blogs seem to hit this wall and immediately blame SQL Server. They say that SQL Server should not be used for session data, or that it inherently is a bad choice due to page level locking, etc. But I think the real
issue is just the design of the ASPState database and the over-aggressive usage of locking in this particular provider. What do you think about the following approaches:
1) We could modify the stored procedures that are used to Insert and Update to the ASPStateTempSessions table so that they always leave the Locked field set to zero. This would eliminate the problem I believe, but would there be other consequences? Because
our application would really prefer the last-in-win behavior, I cannot foresee any problems with this approach.
2) We could switch to using the ASP State Service, rather than SQL Server. But I do not know if the same locking problem would be exhibited because ultimately it should be the same implementation of the ASP.NET state provider, except that it uses RAM instead
of SQL Server for persistence. Can anyone tell me if the State Service implements Locking? Also, can anyone point me to some documentation as to what is new in the ASP State Service from .NET version to .NET version? For instance, there is a different version
of aspnet_state.exe in each Windows\Microsoft.NET\Framework\version directory. I cannot find any information as to why one should use the latest version, if it has to match the version of ASP.NET they are using, etc. Ideally we will want it to match, but
it would be nice to know more about this executable if we are going to rely on it for session storage. With SQL Server, we can see the data, stored procedures, monitor transactions, etc., but we won't be able to do that very well with State Service.
3) We could investigate AppFabric or some other third-party state provider. This increases the risk and possibly the cost, however, so we would rather not go with this approach until we must.
Here are a few general questions:
. Can anyone give their experience with using State Service under heavy load, and how many concurrent users is "heavy" to you?
. Can anyone give their experience with getting the SQL Server method to scale beyond this locking issue?
. Can anyone give their experience with AppFabric or any other third-party products?
By simply changing the "Locked = 1" to "Locked = 0" and this has completely resolved our issues. We do not need locking sessions for our app. I suspect a great majority of sites that rely on sessions also do not need locking. For us, the last-in-wins
philosophy, well, wins. You, too, can make these simple changes to these stored procedures and delay (possibly completely avoid) the need to go with AppFabric or some other third party session store. As for performance, with 3,000 concurrent sessions we
are seeing about 12 SQL transactions per second on the database server that serves the ASPState database. There are no spikes. There are ZERO pages stuck in AcquireRequestState, and of course there are zero sessions that are locked and hence zero sessions
that are stuck in the locked state. I suspect this post will be greeted with some measure of hostility by vendors who make third party session stores and charge top dollar for them. They know who they are. They will likely say that our solution is bad because
it does not have locking, that it is bad because it relies on disk I/O, and that it is bad because it is not distributed. Well, our replies are that we don't need locking, our SQL Server has 48 GB of memory and the disks are almost not even being touched,
and if our database server is down then it doesn't matter if the session server is up anyway. So there you have it, for the price of three carefully placed bytes (the "0" character) we have solved our problems and now have the ability to scale our session
storage to probably 100 times our current traffic load.
Thanks for reading, and please enjoy.
Marked as answer by offshorewahoo on Dec 28, 2012 03:31 PM
offshorewaho...
0 Points
2 Posts
Having SQL Server State issues, Considering Moving to State Service
Dec 27, 2012 09:29 PM|LINK
Our site runs ASP.NET 4.5 using NLB across 4 web servers. We run about 2,000 to 3,000 concurrent sessions during a typical business day. We are currently storing sessions in SQL Server 2008 R2. We have used the InstallSqlStatePersist.sql script and we have the updated DeleteExpiredSessions stored proc in place that has been around for some time. Our application pools use Integrated Pipeline. We find that when an error occurs on a page the page can get hung in the AcquireRequestState, which then contines to hit the ASPState database every 500 miliseconds. After a while, perhaps 5 to 10 minutes, the page ends and stops hitting the SQL Server. Sometimes a few errors stack up and we see 10 or 20 requests that are stuck in this state. At that point the SQL Server that is handling the ASPState database is pummelled with 150 to 200 SQL batches per second, most of which are trying to hit the TempGetStateItemExclusive3 stored procedure. It seems that the Locked field in the ASPStateTempSessions table is set to 1 for these session records and there is contention going on. If we go ahead and execute this query:
Update ASPStateTempSessions Set Locked = 0 Where Locked = 1
A few times in rapid succession, the pages that are stuck in AcquireRequestState suddenly move along their happy way, the load on the SQL Server goes back to normal, and everything is fine until some other errors kick off the problem again.
I know that we could enhance our application to try to tune it for ReadOnly access to the session object on a page-by-page basis. We do a lot of ajax, and that is just not a great solution either in the short term or long term. We really just want Session to use a last-in-wins blocking philosophy and let a few chips fly if concurrent page requests from some impatient user cause conflicting values to be stored.
Lots of people posting in forums and blogs seem to hit this wall and immediately blame SQL Server. They say that SQL Server should not be used for session data, or that it inherently is a bad choice due to page level locking, etc. But I think the real issue is just the design of the ASPState database and the over-aggressive usage of locking in this particular provider. What do you think about the following approaches:
1) We could modify the stored procedures that are used to Insert and Update to the ASPStateTempSessions table so that they always leave the Locked field set to zero. This would eliminate the problem I believe, but would there be other consequences? Because our application would really prefer the last-in-win behavior, I cannot foresee any problems with this approach.
2) We could switch to using the ASP State Service, rather than SQL Server. But I do not know if the same locking problem would be exhibited because ultimately it should be the same implementation of the ASP.NET state provider, except that it uses RAM instead of SQL Server for persistence. Can anyone tell me if the State Service implements Locking? Also, can anyone point me to some documentation as to what is new in the ASP State Service from .NET version to .NET version? For instance, there is a different version of aspnet_state.exe in each Windows\Microsoft.NET\Framework\version directory. I cannot find any information as to why one should use the latest version, if it has to match the version of ASP.NET they are using, etc. Ideally we will want it to match, but it would be nice to know more about this executable if we are going to rely on it for session storage. With SQL Server, we can see the data, stored procedures, monitor transactions, etc., but we won't be able to do that very well with State Service.
3) We could investigate AppFabric or some other third-party state provider. This increases the risk and possibly the cost, however, so we would rather not go with this approach until we must.
Here are a few general questions:
. Can anyone give their experience with using State Service under heavy load, and how many concurrent users is "heavy" to you?
. Can anyone give their experience with getting the SQL Server method to scale beyond this locking issue?
. Can anyone give their experience with AppFabric or any other third-party products?
Many thanks in advance!
offshorewaho...
0 Points
2 Posts
Re: Having SQL Server State issues, Considering Moving to State Service
Dec 28, 2012 03:31 PM|LINK
Update... We modified these three stored procedures:
TempGetStateItemExclusive
TempGetStateItemExclusive2
TempGetStateItemExclusive3
By simply changing the "Locked = 1" to "Locked = 0" and this has completely resolved our issues. We do not need locking sessions for our app. I suspect a great majority of sites that rely on sessions also do not need locking. For us, the last-in-wins philosophy, well, wins. You, too, can make these simple changes to these stored procedures and delay (possibly completely avoid) the need to go with AppFabric or some other third party session store. As for performance, with 3,000 concurrent sessions we are seeing about 12 SQL transactions per second on the database server that serves the ASPState database. There are no spikes. There are ZERO pages stuck in AcquireRequestState, and of course there are zero sessions that are locked and hence zero sessions that are stuck in the locked state. I suspect this post will be greeted with some measure of hostility by vendors who make third party session stores and charge top dollar for them. They know who they are. They will likely say that our solution is bad because it does not have locking, that it is bad because it relies on disk I/O, and that it is bad because it is not distributed. Well, our replies are that we don't need locking, our SQL Server has 48 GB of memory and the disks are almost not even being touched, and if our database server is down then it doesn't matter if the session server is up anyway. So there you have it, for the price of three carefully placed bytes (the "0" character) we have solved our problems and now have the ability to scale our session storage to probably 100 times our current traffic load.
Thanks for reading, and please enjoy.
Angie xu - M...
All-Star
18491 Points
1570 Posts
Microsoft
Re: Having SQL Server State issues, Considering Moving to State Service
Dec 31, 2012 07:19 AM|LINK
Hi offshorewahoo
Thank you for sharing your solutions and experience here. It will be very beneficial for other community members
who have similar questions, and welcome to post your question on our ASP.NET forums in your future programming.
kind regards
Feedback to us
Develop and promote your apps in Windows Store