We use SQL session state on our hosted environment for OnTime 2007, and we wanted to see what sessions were currently active, and how many sessions were active for a given application. Our first glance at the table structures provided didn't yield any immediate clues. There is an ASPStateTempApplications table that looks like this:
In this table, 'AppName' refers to the IIS path of the application; for example '/lm/w3vc/1/root/MyWebSite'.
Then there is an ASPStateTempSessions table that looks like this:
As you can see, there's no AppId column in the sessions table. :) We knew there must be a way that a session entry was tied to an application, it just took some digging into Reflector to find it. It turns out that the SessionId column in the sessions table is a concatenation of an arbitrary identifier from asp.net and the AppId's value in hexadecimal. So all we needed now was a way in T-SQL to convert a hex value to decimal:
CREATE
FUNCTION dbo.fnHexToInt
(
@HexValue nvarchar(8)
)
RETURNS
int
AS
BEGIN
DECLARE
@tempResult bigint
SET @tempResult = 0
DECLARE
@result int
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 for int */
IF @tempResult <= 2147483647
SET @result = @tempResult
ELSE
BEGIN
DECLARE @offset int
SET @offset = @tempResult - 2147483648
SET @result = (2147483648 - @offset) * -1
END
RETURN
@result
END
GO
Now that we have that function, we can use this T-SQL command to get the number of current sessions for every 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()
GROUP by A.AppName
This will be very useful to us for monitoring and tuning our hosted servers. Hopefully this code can be of use to you also. :)