ASP.NET SQL Server Session State extremely slow

4
votes

I'm having problems with an ASP.NET application that I need to web-farm, however just logging into my application takes at about 15 seconds

I did a little experiment where I created a non-web farmed version of my application and then used INPROC session state instead, and the login time is immediate. In this test instance, I'm running SQL Express on the same machine.

I know session state on SQL server is slower, but there is no way it should be THIS slower. Any suggestions on how to track down the issue?

This is my session state:

<sessionState mode="SQLServer" timeout="60" sqlConnectionString="Data Source=localhost;Integrated Security=SSPI;" sqlCommandTimeout="30" cookieless="false" useHostingIdentity="False" regenerateExpiredSessionId="True" />

I've tried both using a username password as well as integrated security.

3
asp.netsql-serversql-server-expresssql-session-state
Check out the comparisons in this question – DOK
can you post your web.config part where you placed the connectionstring for that? – Kundan Singh Chouhan
@DOK I've looked at that. Yes I know SQL server is supposed to be slower, but we are talking orders of magnitude slower. – WhiskerBiscuit
I'll try the profiler next – WhiskerBiscuit
Your connection string points to the default SQL Server instance, while SQL Server Express by default installs a named instance (SQLEXPRESS). Not sure if this is related to your issue, but something worth looking into. A simple operation taking 15 seconds definitely points to a timeout of some kind, maybe your ASP.NET cannot connect to your SQL Server Express at all... I don't know what the expected behavior is in that case. – Krzysztof Kozielczyk

3 Answers

0
votes

Relational databases are Atomic (ACID), so give pretty poor performance when everyone is hitting the same table (ASPStateTempSessions).

We had the same problem as used the SessionState mode instead, we started the ASPNet State Service on the load balancer and used that. Much better throughput.

<sessionState mode="StateServer" stateConnectionString="tcpip=localhost:42424" cookieless="false" timeout="60" />
0
votes

Your question spans a wide area -

  1. Code: What things you are pushing into the session matters a lot. If you are storing large blobs etc, serialization/deserialization happens every time session data is stored and read from the database. Can you parse the data being stored and try to find out average size? I have seen business applications storing 1mb to 2mb data on sql server with thousands of users connected but then underlying hardware was capable enough to support the throughput.
  2. Infrastructure: How much RAM do you have on your machine? Can you try running sql trace on your aspstate database to see how many connections are coming in and how long it takes to execute the query? The problem may not be on the database end, it might be your application trying to do something when storing session information.

SQL Server session storage is not this slow, on my machine with 8gig ram and asp.net application running through visual studio (IIS Express) it takes merely a second to connect and launch the application.

0
votes

The best thing you can do (if your application permits) is to completely avoid a session storage backend. If the session data fits in the HTTP headers themselves (which usally does) you can have it encoded and move back and forth with the requests themselves as cookies.

Plus most implementations of session state provider are "blocking" so you cannot have concurrent requests for the same session, turning into a huge bottleneck.

This article points to a JWT based drop-in session state provider:

http://www.drupalonwindows.com/en/content/aspnet-session-state-scaling-and-performance-issues