For those of you who are unfamiliar, the ASP Session object is used to store information about, or change the settings for an individual user.  This information is available to every single page in a web application, and is not available across different users (a similar object exists for this, referred to as the server cache).  This is usually a great way to keep information about a user, such as their permissions or preferred city without the need to retrieve the information from a database on every single page.

There are five different methods for storing the session information.  Those are as follows:

In this post, we will be covering the state server and SQL server options in a web farm environment where the requests are split over a number of servers.

State Server:

This method is very similar to the inproc method, except that the session information is extracted from the IIS worker process.  By extracting this information, it allows it to be moved to a separate server and utilized remotely from the web farm as shown below:

This has the benefit that it does not matter which web server the user hits, their session information will always be readily available through the state server, which is definitely a benefit.  Also, if Web server 2 where to go offline and all traffic where directed to web server 1, no one loses their session information which is critical in some applications.  This is not the ideal solution though, as information stored in memory is only temporary and can easily be over written if space needs to be freed up.  Let’s say that the ASP State server uses a FIFO strategy as it simplifies the process for understanding:

As more users join the system, they are simply stacked on top in the available memory, without issue.  If user 1 requires their data, it is simply retrieved and then re-added to the top of the pile when more information is saved.  That is fine as long as there is available memory, but what about a system with mass amounts of data being stored for each user, with thousands of users?  What happens when the memory fills up?  Simple, something has to go...

I didn’t write the State server from Microsoft, so I cannot be 100% sure this is how it works; but based on some of the problems we’ve had lately with the state server I’m pretty sure that it works in a very similar manner.  With this limitation of physical and available memory, we are presented with a more scalable solution, the SQL Server session database.

SQL Server Session Database:

With SQL Server data is written to the hard drive as it is accessed through the database, thus removing the limitation of in process memory which is presented with the State Server method.  In addition to this, the SQL Server is resistant to server crashes, as SQL Server can be set up in such a way that there is a failover database.  This means that if the first session database goes down for whatever reason, a second could be spun up containing a copy of the data in the first and no one loses their session information:

This is simply not an option with the State server method.

There is one large disadvantage of using a SQL server to store session information, as each piece is written to disk every time it is accessed; it is considerably slower than the other methods.  This will vary based on load and configuration but reports online place this at roughly 10% - 25% slower.   End of the day there is no perfect solution for every situation, the important part is we research the knowledge required to make the best decision for our situation.

I don't know what planet I've been living on for the past several years, but I JUST Discovered how useful XML can be with stored procedures and I wanted to write about it in hopes that I can help some other poor sap out there who's never even thought about it, or added it to his/her "To read about sometime" list. 

 Basically my problem is simple, a problem that many people have faced in the past.  I wanted to pass one or more values to a stored procedure on SQL Server 2005.  Coming from the VB 4 world, working through the ages up to C# I always remember having the use of an array available to me. A Simple concept I know, a series of items all related by some greater cause or purpose… but apparently not to SQL Server.

My First thought was a direct influence of my coding background, I'll just dynamically create a SQL Statement, and run it with an execute command:

DECLARE @SQL varchar(600)

SET     @SQL =

    'SELECT Error_Message

    FROM dbo.Errors

    WHERE Application_Id IN (' + @ListOfApplications + ')'

EXEC (@SQL)

Brilliant right? I pass in a comma separated list of applications, which than compiles into a string that is executed! I've done it a million times in the earlier days when I didn't know any better, and it worked than so why not now? Well turns out that one little word Compiles comes back to kick me in the ass. Every time my stored procedure runs this code needs to be compiled, and an execution path has to be figured out. That cannot be cheap resource wise. So I turned to Google, my best friend most days.

It wasn't long before I came across an MSDN article which highlighted the concept of XML for me, in a very obscure and difficult fashion. To use XML, they had me creating three extra stored procedures in the north wind database, and full applications just to test the call to the one that I wanted. So, after deciphering the typical Microsoft MSDN Confusion, I created this… my pride of the night:

ALTER PROCEDURE dbo.GetErrorsByAplications

(

    @ApplicationIds nText

)

AS

DECLARE @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT, @ApplicationIds

SELECT    Error_Message

FROM    Error

WHERE    Application_Id IN (

SELECT    Application_Id FROM OPENXML (@hdoc, '/Applications/Application', 1)

WITH        (Application_Id NCHAR(5))

)

EXEC sp_xml_removedocument @hDoc

RETURN

Pure brilliance on the part of Microsoft if you ask me, and even as I sit here writing this I can only imagine the possibilities of expansion, what I could possibly do with XML in a stored procedure, you could say the possibilities where, well, endless. A Simple XML string is passed in:

<Applications><Application Application_Id="2" /><Application Application_Id="1" /></Applications>

Some magic happens and I get all of my results for Application 1 and 2, just like the first select statement, only significantly faster. I don't have time to figure out exactly how much faster right now, but it's definitely on my list of things to do! As far as I can tell, this little gem works like this:

1. Create an XML Document in SQL server, based on my nText String:

   exec sp_xml_preparedocument @hDoc OUTPUT, @ApplicationIds

2. Use XPath to extract the elements from that document, specifically the Application_Id from the Application Tags:

   SELECT    Application_Id FROM OPENXML (@hdoc, '/Applications/Application', 1)

   WITH        (Application_Id NCHAR(5))

3. And finally kill the XML document and return the results:

   EXEC sp_xml_removedocument @hDoc

And just like that I have all of the results that I need without that nasty downside of killing my applications performance!

One word… AWSOME!

Oh, and yes simple things do amuse me…