here's a link to an ms article that goes over ASP vs ASP.NET session state;
http://msdn.microsoft.com/en-us/library/ms972429.aspx
usually the session is heald in memory, so it's a little slower with the roundtrip to a sql server to get the session:
Performance and Reliability Considerations
It's worth mentioning, briefly, some of the performance and reliability issues you should consider when using ASP.NET session state modes.
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.
Lowell