asp.net: checking session size in SQL Server ASPState DB...
by setting asp.net application to save session state into SQL Server database, not only can scale application out with multiple web servers, but also provided a way to investigate session usage.
Saving session state in SQL Server database will be slower than InProc session state, since it requires Serialization / DeSerialization of session data and there will be round trips between web server and database server, therefore, session size / usage in asp.net application becomes an important factor of optimizing and scaling out an application.
To create a session state database for using in asp.net application, refer to aspnet_reqsql.exe usage, using the command
aspnet_reqsql.exe -ssadd -sstype p -E
using -E if you are authenticating using windows credential (trusted connection), or using -U and -P to input sql server login and password. the default session state database name is "ASPState"
Inside ASPState db, there are 2 tables: "ASPStateTempApplications" and "ASPStateTempSessions". ASPStateTempApplications table have the mapping of self-made application-id and infomation of web virtual directory. the application-id is a decimal int value.
ASPStateTempSessions table is the place where the actualy session data is stored. the key field is SessionId, which is a 32 bytes string combined with asp.net session-id (the first 24 bytes) and application-id (the last 8 bytes). the application-id in SessionId field is represented in hex value, which is different than the id in ASPStateTempApplications table, which is in decimal value. thus the conversion become necessary if trying to mapping those 2 tables to create meaningful report in one query.
I just wrote a post with the function to convert HexString to VarBinary and to convert VarBinary to Int, so the conversion should be no problem if using that self-defined function. now here is the query for getting session size and mapping each session to its virtual directory:
select
SessionId,
substring(SessionId,25,8) as AppIDHex,
convert(int, dbo.HexStrToVarBinary(substring(SessionId,25,8))) as AppIDDec,
b.AppName,
datalength(SessionItemLong) as SessionSize
from dbo.ASPStateTempSessions as a
left outer join dbo.ASPStateTempApplications as b on
convert(int, dbo.HexStrToVarBinary(substring(a.SessionId,25,8))) = b.AppId
where datalength(SessionItemLong) > 0
order by SessionSize desc
Before running this query, be sure to first add the "HexStrToVarBinary" function to ASPState database, which stated in my last post.
[UPDATE 2008/01/13 07:50 Start]
since the AppId in ASPStateTempApplications might contain minus decimal values, the conversion from hex to decimal will have chances not match the decimal values of AppId.
to solve this, not converting to decimal value for the mapping, but using hex value for matching 2 tables:
SELECT
a.SessionId,
SUBSTRING(a.SessionId, 25, 8) AS AppIDHex,
b.AppId AS AppIDDec,
b.AppName,
DATALENGTH(a.SessionItemLong) AS SessionSize
FROM
dbo.ASPStateTempSessions AS a
LEFT OUTER JOIN
dbo.ASPStateTempApplications AS b
ON
SUBSTRING(a.SessionId, 25, 8) =
SUBSTRING(sys.fn_varbintohexstr(CONVERT(VarBinary,b.AppId)), 3, 8)
WHERE
(DATALENGTH(a.SessionItemLong) > 0)
ORDER BY SessionSize DESC
by using this query there is no necessary to use self-made function to convert hex string to varbinary value, but have to use the built-in sql function "sys.fn_varbintohexstr". this query guarantees the match of those 2 tables.
[UPDATE 2008/01/13 07:50 End]
the query result is like the following:
now you can see each session state with its size and where the virtual directory the session is belong to, to get an idea of where the problem is at...
by seeing the above result, I got a session with size around 18mb, which is not a good idea... imaging each page request of this session, the 18mb data transfer between sql server and web server, and serialize / deserialize of a 18mb data... well, I'll write further posts if I got it solved...
Technorati Tags: microsoft,asp.net,session,state,sqlserver,programming