1

My team has proposed to store session state data for each open client session in a table in the database. The application server creates a new table for each client session as the user logs in via web browser. The data that needs to be kept for the session is created along with the table, and is not modified (except in rare circumstances). When the user logs out, the table is dropped. Orphan tables are purged regularly.

The application server manages these short-term tables. There won't be any database code that manages the tables or the data.

The data in these tables has relational links to other tables in the database. The tables will be joined in queries to other tables in the database.

I'd like to find out the pro's and con's of this design, in contrast to others. For example, all of the same functionality could be implemented in a single table that stored state data for all sessions. If done this way, there would be one clustered index on the session ID. Each access to the state data would read all of the data for exactly one session ID.

What are the scalability concerns for creating/dropping multiple tables rapidly? Assume this occurs on the order of 1K-10K time per hour.

  • Has your team already considered and rejected #temp tables? – Jon of All Trades Jan 30 '15 at 23:01
  • Using global temp tables might be practicable. Let's assume for now that these short-term tables are in a user database. The POC for this project uses a user database. – Jacob Jan 30 '15 at 23:21
  • Web apps do not maintain connection to the DB, hence temp tables, local or global, are not an option. Is this IIS / ASP.NET? So far, I would lean towards a single SessionState table. Dropping and creating lots of tables will all hit the system catalog table(s) that manage the object meta-data. Adding and deleting data is meant to happen thousands of times per second (or more). – Solomon Rutzky Jan 30 '15 at 23:27
  • This is a Java application server. It has a pool of threads that all need to have access to the table(s). I thought a global temp table would support this. – Jacob Jan 30 '15 at 23:44
  • Just use a single, permanent table with a key to each "pool." If a pool can't clean up its own threads leave some kind of trail behind so that a background job can help with cleanup. – Aaron Bertrand Jan 31 '15 at 1:03
2

I don't see much benefit of storing Web App Sessions per table:

  • Creating and dropping tables is a little more expensive than inserting and deleting rows

  • Depending on how many sessions can exist (current + inactive that have not been cleaned up yet), 10,000 tables is a bit more to deal with than 100,000 rows in a table

  • Temp tables are not an option. http is a connectionless / stateless protocol, hence the need for sessions ;-). Since temp tables -- local and global -- are automatically dropped when the session in which they were created ends, they wouldn't persist between calls.

  • Any app-based mechanism that can clean up obsolete tables can just as easily delete obsolete rows (i.e. an OnSessionEnd event and/or job that runs periodically).

IF, you are using ASP.NET, there is a somewhat built-in option to store Session State in SQL Server:

That might work with JAVA / JSP, but probably not?

If not using ASP.NET, then you can:

  • use a single table to store the serialized Session object. (this is one of the options mentioned in the question).

  • OR, possibly use a cache server, such as memcached, redis, or even Windows Server AppFabric Caching. They might be typically intended for longer-term caching, but I see some benefits to them, such as:

    • they are based in memory, not on disk (i.e. faster, which is why they exist in the first place)
    • they have built-in mechanisms for key expiration so you probably won't need app-based clean-up logic. Something to at least consider / look into.
  • Shouldn't be hard to make Java use the session state database. The asp.net support is just a bunch of wrappers around stored procedure calls, no? Also it was one of the poster children for In-Memory OLTP ("Hekaton"), because durability is not important you could make the best use of that technology and benefit similarly to other in-memory technologies. I realize the OP is using 2005, but hopefully won't be forever, and other readers might find that angle useful. – Aaron Bertrand Feb 1 '15 at 4:16
  • @AaronBertrand The main benefit of the ASP.NET SQL Server option for Session handling is that it is built into the ASP.NET app server and completely transparent. The value of those objects is greatly diminished if one needs to update their app code to manage it. This is why I left it as a question as I haven't look into the various JAVA app servers to see if they can repoint their session handling. And In-Memory OLTP would be more viable if it weren't Enterprise-only. Session-handling alone wouldn't justify the cost. When that feature shows up in Express, then it will be an attractive option. – Solomon Rutzky Feb 1 '15 at 16:23
  • Well, I'm not suggesting go to Enterprise for that feature, especially if you'll only use it for session state. But if you're already on Enterprise (the OP hasn't said they're using Express, after all). Anyway, I'm just suggesting that in the right scenario you don't necessarily have to add layers to achieve the same types of efficiencies. – Aaron Bertrand Feb 1 '15 at 16:27
  • (As an aside, I've also blogged about a series of enhancements to help ASPState scale better, long before Hekaton details were made public.) – Aaron Bertrand Feb 1 '15 at 16:29
  • @AaronBertrand Yes, agreed that if they already have Enterprise, then using it in addition to making some/all of the changes you noted in that blog is definitely a viable option. But for non-ASP.NET I still think memcached and/or redis might be more appropriate. I did see a comment on your blog regarding someone using MS AppFabric Caching, which is the same concept, which I didn't mention because they were already using JAVA, but maybe worth a mention. I will update with that and the link to your blog with those tips. – Solomon Rutzky Feb 1 '15 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.