SQL Server ASP Session State Impact

Question

Post reply

SQL Server ASP Session State Impact

Paul A

SSC Eights!

Points: 985
More actions
April 16, 2010 at 9:43 am

#156879

Hi,
Can someone help me with a question I have been asked about storing ASP Session State in a SQL Server Database.
Having had no experience of using this at all, I need to assess what the impact of installing this will have on the server I choose to use.
So in summary really can anyone shed any light on the following points, or perhaps provide a link to some detailed information:-
•Real requirements (hardware/software) for SQL session state
•What are the IOPS considerations
•What are the network considerations
•What are the utilisation considerations
Any advice however small would be appreciated.
Thanks in advance.
Paul 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's a link to an ms article that goes over ASP vs ASP.NET session state;

http://msdn.microsoft.com/en-us/library/ms972429.aspx

usually the session is heald in memory, so it's a little slower with the roundtrip to a sql server to get the session:

Performance and Reliability Considerations
It's worth mentioning, briefly, some of the performance and reliability issues you should consider when using ASP.NET session state modes.
In process. In process will perform best because the session state memory is kept within the ASP.NET process. For Web applications hosted on a single server, applications in which the user is guaranteed to be re-directed to the correct server, or when session state data is not critical (in the sense that it can be re-constructed or re-populated), this is the mode to choose.
Out of process. This mode is best used when performance is important but you can't guarantee which server a user will request an application from. With out-of-process mode, you get the performance of reading from memory and the reliability of a separate process that manages the state for all servers.
SQL Server. This mode is best used when the reliability of the data is fundamental to the stability of the application, as the database can be clustered for failure scenarios. The performance isn't as fast as out of process, but the tradeoff is the higher level of reliability.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 2

Piggy backing off of the original note...

Are there capacity planning guides available for the ASP.Net Session database?

I'm specifically looking at how to size the data and transaction log files for a State Session database (in simple recovery mode) for XXX number of users. Setting the files to auto-grow won't be an option for this particular environment.

I know the right answer is that it probably depends, but I was hoping that there was some kind of reference material available to help guide the estimates.

Thanks in advance.

~Rusty

sqlblogi SSC-Addicted Points: 420 More actions · Answer 3

What kind of traffic are you looking at? I let mine autogrow at 10% and I am getting millions of unique visitors everyday. The session job created by .net is just 2 tables and a sql job to clear out the expired sessions. I have about 1100 other active db's on this server too with no hit. I just use a das, 16 gigs of ram and dual quad core's.

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 4

Traffic to the session database has been averaging 300 simultaneous connections, but has gotten as high as 1500.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 5

There really aren't any issues with regards to size of the ASPState database, nor the performance. The size stays relatively small. There are "cleanup" jobs that run on a scheduled basis. Just make sure to do your FULL and T-log backups and the size should be fine.

Our ASPState db is 5GB and it never comes close to using the space. As far as performance, we've never had any issues with this. I suppose it really depends on what the application is saving as session data. If the application is storing large amounts of data in session then you may have issues.

Read the article that lowell linked to.