Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server ASP Session State Impact


SQL Server ASP Session State Impact

Author
Message
Paul A
Paul A
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 305
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 :-)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38962
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
rustroot1
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 153
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
sqlblogi
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 383
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
rustroot1
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 153
Traffic to the session database has been averaging 300 simultaneous connections, but has gotten as high as 1500.



Steve-3_5_7_9
Steve-3_5_7_9
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1584
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search