4

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.

DOK
  • 32,337
  • 7
  • 60
  • 92
WhiskerBiscuit
  • 4,795
  • 8
  • 62
  • 100
  • Check out the comparisons in [this question](http://stackoverflow.com/questions/1447175/sqlserver-vs-stateserver-for-asp-net-session-state-performance) – DOK Dec 24 '12 at 16:53
  • can you post your web.config part where you placed the connectionstring for that? – Kundan Singh Chouhan Dec 24 '12 at 16:53
  • @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 Dec 24 '12 at 17:09
  • @WhiskerBiscuit, please be cautious in posting your real database connection string on a public site like this!! – DOK Dec 24 '12 at 17:11
  • The difference shouldn't be noticeable when not under load. Something (not sure what, or I would try to help) is definitely amiss. – Tim M. Dec 24 '12 at 17:22
  • How about doing a simple query for data in the same db? As the comments have stated, it shouldn't be noticeable so suspects range from load to network connectivity e.g. from your web server to db - am assuming they're all in separate boxes (aka 'web farm'). – EdSF Dec 24 '12 at 17:24
  • At the moment, this is a single site. I've narrowed this down to the session state. @DOK, don't worry, this is all test stuff I'm posting – WhiskerBiscuit Dec 24 '12 at 17:32
  • Can you use SQL Profiler to trace the queries being executed and their performance? Also what version of Sql Server? – RBarryYoung Dec 24 '12 at 18:32
  • 1
    I'll try the profiler next – WhiskerBiscuit Dec 24 '12 at 19:00
  • 1
    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 Dec 26 '12 at 05:08
  • Sounds like this other question can help: http://stackoverflow.com/questions/2156876/sql-server-requests-time-out-as-tempgetstateitemexclusive-is-getting-called-cont – Diego Jan 11 '13 at 22:34
  • How much data are you storing in the session? If you are storing more than a trivial amount of data in there, there could be significant serialization overhead going to and coming from the database. I would eliminate all unnecessary data from Session and see if performance improves. My guess is that you are putting some data in the Session that is very large. – Brady Holt Apr 04 '17 at 13:52

3 Answers3

0

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" />
Ben Collins
  • 455
  • 5
  • 7
0

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.

user5226582
  • 1,946
  • 1
  • 22
  • 37
captainprice
  • 11
  • 6
0

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

David
  • 31
  • 2