Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server ASP Session State Impact Expand / Collapse
Author
Message
Posted Friday, April 16, 2010 9:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 3:09 AM
Points: 95, Visits: 292
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
Post #904998
Posted Friday, April 16, 2010 11:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #905090
Posted Tuesday, July 13, 2010 12:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:06 AM
Points: 262, Visits: 128
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



Post #951714
Posted Tuesday, July 13, 2010 11:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:07 PM
Points: 32, Visits: 367
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.
Post #952020
Posted Wednesday, July 14, 2010 4:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:06 AM
Points: 262, Visits: 128

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



Post #952169
Posted Wednesday, July 14, 2010 6:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:50 AM
Points: 922, Visits: 1,444
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.




Post #952270
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse