SQLServer vs StateServer for ASP.NET Session State Performance

Question

I'm studying for a MS certification and one of the practice tests I'm doing has a question where the point of contention is the performance between storing the session in SQL Server as opposed to StateServer.

Given the app is running in a web farm, which solution for session state gives the best performance (SQL Server or StateServer) and most importantly, why?

Solution 1

A little, but important sidenote: InProc is not usable in a farm, as the name suggests, it runs in the current w3wp proces and cannot be shared across a farm. StateServer is a Windows service, so the speed of using StateServer is dependend on how fast the machine the stateserver service is running on, it is memory only. SQL of course needs to write the data and retrieve, which is probably slower than memory only.

From here:

• In process. In process will perform best because the session state memory is kept within the ASP.NET process. For Web applications hosted on a single server, applications in which the user is guaranteed to be re-directed to the correct server, or when session state data is not critical (in the sense that it can be re-constructed or re-populated), this is the mode to choose.
• Out of process. This mode is best used when performance is important but you can't guarantee which server a user will request an application from. With out-of-process mode, you get the performance of reading from memory and the reliability of a separate process that manages the state for all servers.
• SQL Server. This mode is best used when the reliability of the data is fundamental to the stability of the application, as the database can be clustered for failure scenarios. The performance isn't as fast as out of process, but the tradeoff is the higher level of reliability.

Solution 2

From this link: http://www.eggheadcafe.com/articles/20021016.asp

Performance

• InProc - Fastest, but the more session data, the more memory is consumed on the web server, and that can affect performance.

• StateServer - When storing data of basic types (e.g. string, integer, etc), in one test environment it's 15% slower than InProc. However, the cost of serialization/deserialization can affect performance if you're storing lots of objects. You have to do performance testing for your own scenario.

• SQLServer - When storing data of basic types (e.g. string, integer, etc), in one test environment it's 25% slower than InProc. Same warning about serialization as in StateServer.

So it would seem that StateServer is a little faster that SQL Server for storing session state.

In terms of the why, I'd suggest that the SQL Server is more multi-purpose and will likely be used for other things as well. Not only that but the storage mechanism is to disk, where as the StateServer is running in a separate process, yet it is simply storing the data in the memory space of the other process rather than having to write it to disk (virtual memory permitting)

Solution 3

SQL Server (In Memory) is the answer - available in SQL 2014

http://blogs.msdn.com/b/kenkilty/archive/2014/07/03/asp-net-session-state-using-sql-sever-in-memory.aspx

Comments

[CShark]

Also keep in mind that both SQL Server and Out-of-process involves serialisation and deserialisation of session data, which is an additional performance overhead inproc is not burdened with.

[CShark]

In addition to that, SQL server and out-of-process typically requires sending data over a network, so there are overheads wrt network communications also involved, which InProc is also not burdened with.

[cadull]

Link is no longer available. The following page is likely to be similar. cloudblogs.microsoft.com/sqlserver/2014/07/10/…

[TruthOf42]

great;knowing the real world performance impact is really helpful

[8vtwo]

SQL Server SessionState uses tempdb IN MEMORY already.