SQL Server with Mr. Denny

Jan 6 2011   2:00PM GMT

Lets get session state out of the database, and into memory where it belongs.

Denny Cherry Denny Cherry Profile: Denny Cherry

One of the all to common configuration mistakes that are made is to put the session state within the SQL Server when a website begins to scale to multiple web servers.

Some background

For those that don’t know what session state is, let me take a step back and fill you in.  Session state allows the web developer to store values in variables up on the web server, but in a shared location so that it doesn’t matter what web server the end user connects to the values are all there and the web servers understand the session information from each other.  This is very important for shopping websites, or pretty much any site with a login prompt and a load ballancer that is configured to send the users connection to the next available web server.

What is the problem?

The problem becomes that most people when they need to scale out session state opt to put the session state information into SQL Server because that is a nice easy repository that is probably already up and running, not to mention that the session state code in ASP.NET easily supports it.  The issue with putting the session state information into SQL Server is that you don’t give a crap about persisting the session state information to disk.  The information doesn’t get persisted more than an hour or so, and if the information within the database is lost, the only impact is that any values in session variables is lost.

So what are the options?

There are a few options besides using SQL Server for session state.

  1. Keep using the in-process option.
  2. Use the ASP.NET Session State Service

The easiest option is to keep using the in-process option in IIS for session information.  This means that you will need to configure what are called sticky sessions on the load balancer so that the user always goes to the same web server every time.  In the event that a web server fails, all the session information for those users would be lost, and they would need to start over by logging back into the site, or they would have an empty shopping cart, or whatever the site does.

The ASP.NET Session State Service is a Windows service that provides a memory only session state repository so that session information isn’t ever written to disk, and it isn’t kept on the web server.  You can either stand up a dedicated machine for this, or setup a couple and cluster the service manually so that you have an HA solution for your session state service.  If you have an existing SQL Server cluster you can even use this cluster for it, if you don’t have anywhere better to put it.  Just configure the cluster to have the SQL Server run on one node as the preferred node, and have the session state run on another node as its preferred node.  This way the services won’t ever run on the same node unless the other node is offline.  The session state service doesn’t take a lot of CPU power, and the amount of RAM it needs to be completely dependent on the amount of information that you are stuffing into session variables on the web servers.

In either case, either solution is better than putting the session state information into a SQL Server database.  The information doesn’t need to be written to disk, ever.  The information that is written into the session state database is in a blob binary form, not any sort of relational form so you can’t really do anything with it.

How do I know if sessions state is in SQL?

That is an easy one.  You’ll have some funky database, probably in simple recovery mode usually name aspnetdb (maybe with a prefix and/or a suffix).

What other problems can session state cause?

Well first it’ll take away buffer pool resources from your other databases.  Because the session state database is hit very hard the data from the database will always be in memory.  How ever much data is in the session state database, you are missing that much buffer pool space for your other databases.  Because of the way that session state works, every single time a page is clicked on the website, at least one call is made to the session state database pretty much forcing the database server to keep the data from the database in the buffer pool.

Another problem that you can see if ghost records.  Paul Randal describes ghost records perfectly on his blog post Ghost cleanup in depth.

When a record is deleted, apart from it being marked as a ghost record, the page that the record is on is also marked as having ghost records in one of the allocation maps – the PFS page (post coming soon!) – and in its page header. Marking a page as having ghost records in a PFS page also changes the database state to indicate that there are some ghost records to cleanup – somewhere. Nothing tells the ghost cleanup task to clean the specific page that the delete happened on - yet. That only happens when the next scan operation reads the page and notices that the page has ghost records.

He also talks about how to fix the problem in the same blog post, so I’ll leave you to click through for all that information.

Another problem goes back to the fact that the SQL Server will be persisting everything to disk, and with the information in this database changing all the time, as the website grows you’ll need faster and faster disks under the session state database just to keep up.  As the site grows more popular you will end up spending more and more money on faster and faster disks, just to keep the session state working, much less anything else on the SQL Server.  You may even get to the point where you actually need a dedicated SQL Server just to run the session state database.

Another problem is good old locking and blocking.  SQL Server likes to take page level locks when it does insert, update and delete operations.  Well, unless each page on disk only has a single row in it you are going to have processes being blocked for short periods of time as other users session state information is updated.  You can work around this to some extent by hacking the session state database’s stored procedures and forcing row locks, but now you are taking more locks (and more memory for locks), etc.

How do I change to the session state server?

First you need to install the service on the server that will be your session state server.  On Windows 2008 just install all the .NET components and that should do the trick.  You’ll probably want to start the service as well.  When you bring up the list of services it is the one called “ASP.NET State Service”.

Then on each web server you’ll want to change the session state information. You can either use IIS Manager to do this, or change the web.config. However you configured session state the first time, that’s how you’ll want to change it this time.

To use the IIS Manager select the website (or application) to configure and double click on the “Session State” icon on the right (shown below).

From there select “State Server” from the list and change “localhost” to the server you’ll be using for session state. If you have changed the TCP port number from 42424 to something else you can adjust that here as well.

To set the session state setting via the web.config file find the existing session state information and edit it, or add in the session state information. Set the “mode” to “StateServer” and set the “stateConnectionString” to the same value that goes in the IIS config setting.

<configuration>
  <system.web>
    <sessionState mode="StateServer"
      stateConnectionString="tcpip=SampleStateServer:42424"
      cookieless="false"
      timeout="20"/>
  </system.web>
</configuration>

Hopefully I’ve convinces you to move your session state information out of SQL Server and into a repository that it actually belongs in.

Denny

4  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • peschkaj
    An ever better place to put session is in a third party database like the AppFabric Cache, Redis, or Riak. Then you get a dedicated solution that can scale out on cheap servers as opposed to scaling up with more RAM. Then you can even load balance your session state.
    0 pointsBadges:
    report
  • Ozamora
    Great article Denny. We currently have a webfarm using F5 Load balancers. As the F5 sends traffic to any of the servers, what other option can we pursue to get the Session State DB outside SQL Server? Thanks
    0 pointsBadges:
    report
  • Denny Cherry
    Ozamora, You can also look at the AppFabric Cache, Redis, or Riak options which Peschkaj talked about. Denny
    66,130 pointsBadges:
    report
  • Inet1701
    Interesting that SharePoint stores the info in the DB.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: