SQLServer vs StateServer for ASP.NET Session State Performance

State Server is faster because it stores session data in an in-memory dictionary. SQL Server is slower because it's stored in a database which persists data to disk.

SQL server is also slower because everything is stored in one table which leads to contention as more and more clients access/update the session data.

SQL server is more reliable because it is persisted to disk and can be set up as a cluster with failover capability.

See the preamble in this article for an indepth explanation.

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.

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)