- For a deep-dive on ASP.NET Session State Providers, click here
- ScottGu's brief post has some great links
- For the source code, click here.
In Part II I discuss a session state monitoring tool I quickly threw together yesterday and have continued to work on today. I mentioned yesterday that I wanted to monitor session creation and deletion. That is in the tool now. The image below is the latest screen shot, showing the sessions in the tree along with the session items below each session node. The list on the right shows the values of the session value over time (in the sample below SQLSessionState2Text1's value has changed from 1 to 2). Only primitives and string session items are monitored at this time.
Let's take a step back from the tool and look deeper into how session state is persisted and managed in Microsoft SQL Server (MSSQL). In Part I, I show you how to set a state database and point your web.config to it--pretty simple stuff. Now let's take a look at the database itself.
The State Tables
ASPStateTempApplications has two attributes: AppId and AppName. AppId is the primary key and is a hash of the AppName. The hash function used is T-SQL's version of this C# hash function:
The AppName is the first request from a new application full virtual path: [/LM/W3SVC/<Web site number> /Root /<virtual directory name>]. Note that if you have two applications that have identical virtual paths, a hash collision occurs and you may have to rename one of the application's virtual directory. If you have a farm of front-end servers and a single MSSQL state server, this is actually what you would expect. For more information on using MSSQL state server in a farm, read this ttp://support.microsoft.com/default.aspx?scid=kb;en-us;325056.
ASPStateTempSessions is where session state is persisted. You may have noticed but ASPStateTempApplications and ASPStateTempSessions are not naturally joinable. Some work is required to ask questions like "how many active sessions in a particular application. Jonus Bush provided some useful information. First, you need to create a hex to int function, like the one below:
Now you can build queries such as the one below that returns the number of sessions per application:
The rest of ASPTempStateSession's attributes are described below:
|SessionId||As described above|
|Created||Date/time when the session was created|
|Expires||The expiration date of the session (Created + Time-Out)|
|LockDate||UTC date when session was locked. This is used to determine lock age in MSSQL Server version 2000+. For version 7.0, the LockDateLocal|
|LockDateLocal||Local (to the web server) date when session was locked.|
|Timeout||Session time out in minutes|
|Locked||1 = Session Locked, 0=Session not locked|
|SessionItemShort||Serialized session data <= 7000 bytes|
|SessionItemLong||Serialized session data > 7000 bytes|
|Flags||Session state flags (1=Uninitialized session)|
Both SessionItemShort and SessionItemLong have a binary header with additional information, including the session time-out, a boolean value indicating whether the byte stream includes session items, and another boolean value indicating whether the byte stream contains static objects. The unpacking of this information looks like this:
The Stored Procedures
Reference is here.
A variety of stored procedures are present in the state database. The following table describes each and what it is used for (at least as I understand it). I am only going to touch on the stored procedures used by .Net 2.0 and MSSQLServer 2000+.
|CreateTempTables||Creates the tables. Keep in mind that aspnet_regsql.exe (the system tool that sets up state in a particular server) has an option to create the tables in the TempDB, the default ASPState database, or a custom database.|
|DeleteExpiredSessions||Used by the SQL Server Agent to purge expired sessions. I will discuss expiration further down the post.|
|GetHashCode||Used TempGetAppId to create the hash of the application's virtual directory, which in turn is used as the AppId in ASPStateTempApplications.|
|GetMajorVersion||Returns the major version of MSSQLServer and is used in branching dependant upon version (for example, the lock-age is calculated differently in 7.0 versus how it is calculated in 2000 and 2005. This particular example is interesting because in 7.0, the lock-age is calculated using the local date of the SQL Server and the web server, which means the two (or more) servers must be in the same time zone. Furthermore, the lock information in 7.0 is not stored in UTC which means there are problems with day light savings.|
|TempGetAppID||Converts an application name into an application ID; queries the ASPStateTempApplications table and inserts a new record if necessary.|
|TempGetStateItem3||Retrieves read-only session state from the database (ASP.NET 2.0). In the underlying implementation, this stored procedure is called in DoGet. The method signature looks like this:
|TempGetStateItemExclusive3||Retrieves read/write session state from the database (ASP.NET 2.0). If getExclusive = True in DoGet, this stored procedure is called.|
|TempGetVersion||Marker whose presence indicates to ASP.NET 2.0 that the session state database is ASP.NET 2.0-compatible. During initialization, the method GetServerSupportOptions is called, which look like this:
|TempInsertStateItemLong||Adds a new session, whose size is > 7,000 bytes, to the database.|
|TempInsertStateItemShort||Adds a new session, whose size is <= 7,000 bytes, to the database.|
|TempInsertUninitializedItem||Adds a new uninitialized session to the database in support of cookieless sessions.|
|TempReleaseStateItemExclusive||Releases a lock on a session; called when ASP.NET determines that a request has timed out and calls the provider's ReleaseItemExclusive method.|
|TempResetTimeout||Resets a session's timeout by writing the current date and time to the corresponding record's Expires field.|
|TempUpdateStateItemLong||Updates a session whose size is > 7,000 bytes.|
|TempUpdateStateItemLongNullShort||Updates a session whose old size is <= 7,000 bytes, but whose new size is > 7,000 bytes.|
|TempUpdateStateItemShort||Updates a session whose size is <= 7,000 bytes.|
|TempUpdateStateItemShortNullLong||Updates a session whose old size is > 7,000 bytes, but whose new size is <= 7,000 bytes.|
"SqlSessionStateStore doesn't actively monitor the Expires field. Instead, it relies on an external agent to scavenge the database and delete expired sessions—sessions whose Expires field holds a date and time less than the current date and time. The ASPState database includes a SQL Server Agent job that periodically (by default, every 60 seconds) calls the stored procedure DeleteExpiredSessions to remove expired sessions. DeleteExpiredSessions uses the following simple DELETE statement to delete all qualifying rows from the ASPStateTempSessions table."
Reference is here.
Okay, this is it for now, but I want to part with the actual implementation!