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

ASP.NET state is persisted in MSSQL in two tables, ASPStateTempApplications and ASPStateTempSessions. Let's take a closer look at the first table.

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:

int GetHashCode(string s)

{

int hash = 5381;

int len = s.Length;

for (int i = 0; i < len; i++) {

int c = Convert.ToInt32(s[i]);

hash = ((hash << 5) + hash) ^ c;

}

return hash;

}

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:

CREATEFUNCTION [dbo].[HexToInt]

(

@HexValue nvarchar ( 8 )

) RETURNSint

AS

BEGIN

DECLARE @tempResult bigint

SET

@tempResult = 0

DECLARE @resultint

SET

@result = 0

DECLARE @tempInt bigint

SET

@tempInt = 1

DECLARE @HexSet nvarchar ( 16 )

SET

@HexSet = '0123456789abcdef'

/* Do actual conversion */

WHILE len

(

@HexValue

) > 0

BEGIN

SET

@tempResult = @tempResult +

(

@tempInt *

(

CHARINDEX

(

RIGHT

(

@HexValue,

1

),

@HexSet

) - 1

)

)

SET

@tempInt = @tempInt * 16

SET

@hexValue =

LEFT

(

@HexValue,

LEN

(

@HexValue

) - 1

)

END

/* Handle overflow forint */

IF @tempResult <= 2147483647

SET

@result = @tempResult

ELSE

BEGIN

DECLARE @offset int

SET

@offset = @tempResult - 2147483648

SET

@result =

(

2147483648 - @offset

) * - 1

ENDRETURN @result

END

Now you can build queries such as the one below that returns the number of sessions per application:

SELECT

A.AppName,

COUNT ( A.AppName ) AS NumSessions

FROM

ASPStateTempSessions S JOIN

ASPStateTempApplications A

ON dbo.fnHexToInt

(

RIGHT

(

SessionId,

8

)

) = A.AppId

WHERE

S.Expires > getdate ( )

GROUPBY

A.AppName

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

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

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:

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.

TempRemoveStateItem

TempRemoveStateItem

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.

Session Expiration

"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."

I own a post suggestion... Toddler clutter... How can you keep your house from giving the impression of a daycare everyday? It always looks so organized, without looking overwhelmed by primary colors.... And precisely what are some of Clara's favorite toys? My daughter will be one soon and I have been previously doing numerous research in keeping the toy clutter to a minimal... Some of the baby toys is going to be getting set aside soon and I'm curious about some toddler favorites, especially from none other than the famous Clara P :) Anywho, that's my suggestion :)

Ι do not know іf it's just me or if everyone else encountering problems with your blog. It appears as though some of the written text in your content are running off the screen. Can someone else please comment and let me know if this is happening to them as well? This may be a problem with my web browser because I've had thіs hapρen previouѕly. Kudos

I am really loving the theme/design of your website. Do you ever run into any browser compatibility problems? A few of my blog audience have complained about my site not working correctly in Explorer but looks great in Chrome. Do you have any recommendations to help fix this problem?

Well thats the initial time ive heared that there is a reset button on your cartridge, you might save money by just utilizing this tip, does this mean i could replenish the cartridge myself and push the rest button and my printer will certainly accept that the cartridge is complete.

Disclaimer

Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.