Storing session state in SQL Server

I am currently running a web application with few users, but the sessions are data intensive (sometimes 90,000 rows returned from the database). I am presenting this data in gridviews (usually 50 rows at a time). To support sorting and paging, I store the datatable as a session variable.

Code:

Session("Query_gv_dataTbl") = tbl

As the sizes of the returned data sets get larger, users are being logged out. I have confirmed that at the same time the application is recycling because it has exceeded the memory limit.

It seems I have two choices: 1) re-query the database instead of storing the table as a session variable (which could be difficult, as some of these are complex queries) or 2) switch to SQLServer for storing session state.

Suggestions on pros and cons?

Also, while Discount ASP.NET recommends purchasing a new MS SQL database for this, my current database is only about 10% full, so the additional purchase does not make sense.

Finally, if I do have Discount ASP.NET create the tables needed to support SQLServer for storing session state in my current MS SQL db, will this be placed into the dbo schema by default, or can I specify which schema in my current database? For example, right now dbo hosts the membership tables.

Any advice most welcome.

Thanks.

 

I'm with your first thought - rather than storing the entire result set in session, opt to store just the search criteria, page parameters and sort order in session instead. It sounds like it might prove tricky for you to implement but if you do it, this will massively reduce the dependency on session.

Sql server session state is persistent across app pool recycles so that's a major advantage for some. Disadvantages include: it's slower than regular InProc and SessionService (about x10 slower). All session items need to be serializable (maybe not a huge problem but this can impact existing code).

You can use an existing SqlServer db - you just specify the right command line parameters when you execute aspnet_regsql to create the schema in an existing custom db. If you do this you also need to specify the custom option on the session provider.

 

Hi Joe,
Thanks again for your suggestions. Given your statement, I went back and found a way to store the query method as a session variable (instead of the datatable result) itself. Wasn't as painful as I thought. This improved things, but I still kept getting logged off. Then I tried monitoring memory with GC.GetTotalMemory(false), and forcing Garbage Collection after setting dataTable=nothing. Better, but still getting logged off. Then I turned off sorting on Gridviews where there where more than 10,000 rows. Better, but still getting logged off.

And not only am I getting logged off, but other users concurrently logged in are also getting logged off.

Does this mean I only have 200 MB of memory across all current users?

Can anyone tell me... if I do go the SqlServer db route, will this stop the sessions getting logged out? Or am I fundamentally misunderstanding something?

Help!

 

Thanks for the information Martino. I did have some specific questions though:

So if I'm using Forms authentications, the users would thrown back to their login page (because all session data was lost). Is that correct?

Does that mean in the above situation, that users would not be sent back to their login page?

That would be a strong motivation for me to use SQL Server sessions.

Thanks.