Jan 112013
 

Many people have implemented ASPState in their environment. Some people use the in-memory option (InProc), but usually I see the database option being used. There are some potential inefficiencies here that you might not notice on low volume sites, but that will start to affect performance as your web volume ramps up.

Recovery Model

Make sure ASPState is set to simple recovery – this will dramatically reduce the impact to the log that can be caused by the high volume of (transient and largely disposable) writes that are likely to go here:

ALTER DATABASE ASPState SET RECOVERY SIMPLE;

Usually this database does not need to be in full recovery, especially since if you are in disaster recovery mode and restoring your database, the last thing you should be worrying about is trying to maintain sessions for users in your web app – who are likely to be long gone by the time you've restored. I don't think I've ever come across a situation where point-in-time recovery was a necessity for a transient database like ASPState.

Minimize / isolate I/O

When setting up ASPState initially, you can use the -sstype c and -d arguments to store session state in a custom database that is already on a different drive (just like you would with tempdb). Or, if your tempdb database is already optimized, you can use the -sstype t argument. These are explained in detail in the Session-State Modes and ASP.NET SQL Server Registration Tool documents on MSDN.

If you've already installed ASPState, and you've determined that you would benefit from moving it to its own (or at least a different) volume, then you can schedule or wait for a brief maintenance period and follow these steps:

ALTER DATABASE ASPState SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
ALTER DATABASE ASPState SET OFFLINE;
 
ALTER DATABASE ASPState MODIFY FILE (NAME = ASPState,     FILENAME = '{new path}\ASPState.mdf');
ALTER DATABASE ASPState MODIFY FILE (NAME = ASPState_log, FILENAME = '{new path}\ASPState_log.ldf');

At this point you will need to manually move the files to <new path>, and then you can bring the database back online:

ALTER DATABASE ASPState SET ONLINE;
 
ALTER DATABASE ASPState SET MULTI_USER;

Isolate applications

It is possible to point more than one application at the same session state database. I recommend against this. You may want to point applications at different databases, perhaps even on different instances, to better isolate resource usage and provide utmost flexibility for all of your web properties.

If you already have multiple applications using the same database, that's okay, but you'll want to keep track of the impact each application might be having. Microsoft's Rex Tang published a useful query to see space consumed by each session; here is a modification that will summarize number of sessions and total/avg session size per application:

SELECT 
  a.AppName, 
  SessionCount = COUNT(s.SessionId),
  TotalSessionSize = SUM(DATALENGTH(s.SessionItemLong)),
  AvgSessionSize = AVG(DATALENGTH(s.SessionItemLong))
FROM 
  dbo.ASPStateTempSessions AS s
LEFT OUTER JOIN 
  dbo.ASPStateTempApplications AS a 
  ON SUBSTRING(s.SessionId, 25, 8) = SUBSTRING(sys.fn_varbintohexstr(CONVERT(VARBINARY(8), a.AppId)), 3, 8) 
GROUP BY a.AppName
ORDER BY TotalSessionSize DESC;

If you find that you have a lopsided distribution here, you can set up another ASPState database elsewhere, and point one or more applications at that database instead.

Make more friendly deletes

The code for dbo.DeleteExpiredSessions uses a cursor, replacing a single DELETE in earlier implementations. (This, I think, was based largely on this post by Greg Low.)

Originally the code was:

CREATE PROCEDURE DeleteExpiredSessions
AS
  DECLARE @now DATETIME
  SET @now = GETUTCDATE()
 
  DELETE ASPState..ASPStateTempSessions
  WHERE Expires < @now
 
  RETURN 0
GO

(And it may still be, depending on where you downloaded the source, or how long ago you installed ASPState. There are many outdated scripts out there for creating the database, though you really should be using aspnet_regsql.exe.)

Currently (as of .NET 4.5), the code looks like this (anyone know when Microsoft will start using semi-colons?).

My idea is to have a happy medium here – don't try to delete ALL rows in one fell swoop, but don't play one-by-one whack-a-mole, either. Instead, delete n rows at a time in separate transactions – reducing the length of blocking and also minimizing the impact to the log:

ALTER PROCEDURE dbo.DeleteExpiredSessions
  @top INT = 1000
AS
BEGIN
  SET NOCOUNT ON;
 
  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;
 
  BEGIN TRANSACTION;
 
  WHILE @c <> 0
  BEGIN
    ;WITH x AS 
    (
      SELECT TOP (@top) SessionId
        FROM dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    ) 
    DELETE x;
 
    SET @c = @@ROWCOUNT;
 
    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END
 
  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END
END
GO

You will want to experiment with TOP depending on how busy your server is and what impact it has on duration and locking. You may also want to consider implementing snapshot isolation – this will force some impact to tempdb but may reduce or eliminating blocking seen from the app.

Also, by default, the job ASPState_Job_DeleteExpiredSessions runs every minute. Consider dialing that back a bit – reduce the schedule to maybe every 5 minutes (and again, a lot of this will come down to how busy your applications are and testing the impact of the change). And on the flip side, make sure it is enabled – otherwise your sessions table will grow and grow unchecked.

Touch sessions less often

Every time a page is loaded (and, if the web app hasn't been created correctly, possibly multiple times per page load), the stored procedure dbo.TempResetTimeout is called, ensuring that the timeout for that particular session is extended as long as they continue to generate activity. On a busy web site, this can cause a very high volume of update activity against the table dbo.ASPStateTempSessions. Here is the current code for dbo.TempResetTimeout:

ALTER PROCEDURE [dbo].[TempResetTimeout]
            @id     tSessionId
        AS
            UPDATE [ASPState].dbo.ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE())
            WHERE SessionId = @id
            RETURN 0

Now, imagine you have a web site with 500 or 5,000 users, and they are all madly clicking from page to page. This is probably one of the most frequently called operations in any ASPState implementation, and while the table is keyed on SessionId – so the impact of any individual statement should be minimal – in aggregate this can be substantially wasteful, including on the log. If your session timeout is 30 minutes and you update the timeout for a session every 10 seconds because of the nature of the web app, what is the point of doing it again 10 seconds later? As long as that session is asynchronously updated at some point before the 30 minutes are up, there is no net difference to the user or the application. So I thought that you could implement a more scalable way to "touch" sessions to update their timeout values.

One idea I had was to implement a service broker queue so that the application does not have to wait on the actual write to happen – it calls the dbo.TempResetTimeout stored procedure, and then the activation procedure takes over asynchronously. But this still leads to a lot more updates (and log activity) than is truly necessary.

A better idea, IMHO, is to implement a queue table that you only insert to, and on a schedule (such that the process completes a full cycle in some time shorter than the timeout), it would only update the timeout for any session it sees once, no matter how many times they *tried* to update their timeout within that span. So a simple table might look like this:

CREATE TABLE dbo.SessionStack
(
  SessionId  tSessionId,    -- nvarchar(88) - of course they had to use alias types
  EventTime  DATETIME, 
  Processed  BIT NOT NULL DEFAULT 0
);
 
CREATE CLUSTERED INDEX et ON dbo.SessionStack(EventTime);
GO

And then we would change the stock procedure to push session activity onto this stack instead of touching the sessions table directly:

ALTER PROCEDURE dbo.TempResetTimeout
  @id tSessionId
AS
BEGIN
  SET NOCOUNT ON;
 
  INSERT INTO dbo.SessionStack(SessionId, EventTime)
    SELECT @id, CURRENT_TIMESTAMP;
END
GO

The clustered index is on the smalldatetime column to prevent page splits (at the potential cost of a hot page), since the event time for a session touch will always be monotonically increasing.

Then we'll need a background process to periodically summarize new rows in dbo.SessionStack and update dbo.ASPStateTempSessions accordingly.

CREATE PROCEDURE dbo.SessionStack_Process
AS
BEGIN
  SET NOCOUNT ON;
 
  -- unless you want to add tSessionId to model or manually to tempdb 
  -- after every restart, we'll have to use the base type here:
 
  CREATE TABLE #s(SessionId NVARCHAR(88), EventTime SMALLDATETIME);
 
  -- the stack is now your hotspot, so get in & out quickly:
 
  UPDATE dbo.SessionStack SET Processed = 1 
    OUTPUT inserted.SessionId, inserted.EventTime INTO #s
    WHERE Processed IN (0,1) -- in case any failed last time
    AND EventTime < CURRENT_TIMESTAMP; -- this may help alleviate contention on last page
 
  -- this CI may be counter-productive; you'll have to experiment:
 
  CREATE CLUSTERED INDEX x ON #s(SessionId, EventTime);
 
  BEGIN TRY
    ;WITH summary(SessionId, Expires) AS 
    (
       SELECT SessionId, MAX(EventTime) 
         FROM #s GROUP BY SessionId
    )
    UPDATE src
      SET Expires = DATEADD(MINUTE, [Timeout], summary.Expires)
      FROM dbo.ASPStateTempSessions AS src
      INNER JOIN summary
      ON src.SessionId = summary.SessionId;
 
    DELETE dbo.SessionStack WHERE Processed = 1;
  END TRY
  BEGIN CATCH
    RAISERROR('Something went wrong, will try again next time.', 11, 1);
  END CATCH
END
GO

You may want to add more transactional control and error handling around this – I'm just presenting an off-the-cuff idea, and you can get as crazy around this as you want. :-)

You might think you would want to add a non-clustered index on dbo.SessionStack(SessionId, EventTime DESC) to facilitate the background process, but I think it is better to focus even the most miniscule performance gains on the process users wait for (every page load) as opposed to one that they don't wait for (the background process). So I'd rather pay the cost of a potential scan during the background process than pay for additional index maintenance during every single insert. As with the clustered index on the #temp table, there is a lot of "it depends" here, so you may want to play with these options to see where your tolerance works best.

Unless the frequency of the two operations need to be drastically different, I would schedule this as part of the ASPState_Job_DeleteExpiredSessions job (and consider renaming that job if so) so that these two processes don't trample on each other.

One final idea here, if you find you need to scale out even more, is to create multiple SessionStack tables, where each one is responsible for a subset of sessions (say, hashed on the first character of the SessionId). Then you can process each table in turn, and keep those transactions that much smaller. In fact you could do something similar for the delete job as well. If done correctly, you should be able to put these in individual jobs and run them concurrently, since – in theory – the DML should be affecting completely different sets of pages.

Conclusion

Those are my ideas so far. I'd love to hear about your experiences with ASPState: What kind of scale have you achieved? What kind of bottlenecks have you observed? What have you done to mitigate them?

  9 Responses to “Potential enhancements to ASPState”

  1. Last time I worked with the ASP session state database with a client, we implemented most of the above suggestions (they still touched sessions too much and did too much with them but that's another topic… ) but ended up bottlenecked on pagelatch contention. We actually ended up coming to the conclusion that they didn't need to have the sessions in the database with the way they were using the data and how they handled connection breaks and relogins.. So we went InProc.. If we had to stay in the DB we would have looked into seeing what we could do to stop some of that contention – if I recall – the SessionID is an identity column… http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/

    • SessionId is a long string, e.g. 23ghltgsuoensvlzbechhp2m2104c0f2

      Good post by Kejser though. Reminds us that over time (and especially when money is no object) our theories and opinions about bottlenecks need to be challenged and reconsidered. If you can afford to spread ASPState across multiple Fusion-IO drives, then maybe page splits aren't such a bad thing after all. :-)

      • This was sometime ago and I haven't had to deal with performance issues in that DB in a while for any clients. Either it was different then or my memory is going the same place my hair line is going…

  2. Great post!

    Years ago we changed the structure of our session table so the clustered index was not on the guid, like so.

    -- Disclaimer: don't let MS know... as it would be unsupported!
    USE [ASPState]
    GO
    CREATE TABLE [dbo].[ASPStateTempSessions](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[SessionId] [nvarchar](88) NOT NULL,
    	[Created] [datetime] NOT NULL,
    	[Expires] [datetime] NOT NULL,
    	[LockDate] [datetime] NOT NULL,
    	[LockDateLocal] [datetime] NOT NULL,
    	[LockCookie] [int] NOT NULL,
    	[Timeout] [int] NOT NULL,
    	[Locked] [bit] NOT NULL,
    	[SessionItemShort] [varbinary](7000) NULL,
    	[SessionItemLong] [varbinary](max) NULL,
    	[Flags] [int] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    CREATE CLUSTERED INDEX [ix_ASPStateTempSessions_ID] ON [dbo].[ASPStateTempSessions]
    (
    	[ID] ASC
    )
    WITH 
    (
      PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80
    ) ON [PRIMARY]
    .... Plus the other indexes...

    Yes, use the delete cursor or while loop, but perhaps delete the sessions that are an hour or so older (on a 20 minute expiration) will help reduce blocks by allowing the deletes to occur in pages that are not being written to.

    But I really like the idea of using AppFabric for session state! The load can be spread across multiple servers, it is in memory rather that on disk, and it is managed with Powershell which most sysadmins know how to use! Our Devs disagreed and moved session to MongoDB, but seems to be working well so far.

    • Thanks Mike, good point about the age of deletes and of course the disclaimer that such changes tend to cancel out supportability.

  3. Very good post. In the "SessionStack" technique you could consider switching out the entire table so that the application can immediately resume inserting into a fresh partition.

  4. Great post and detail of steps!

    This has saved my life as we had well over 11,000 users on-line using our software. Even though we'd already altered the delete session procedure, there were still hundreds of waiting tasks! It was generally at around 100 with 5,000 users and running ok but then as soon as it hit around the 10,000 mark it really went mad with 400+ waiting tasks and caused complete web page lock-ups and IIS to hit max connections and refuse connection.

    I implemented your procedure with a limit of 500 rows to be deleted which had an immediate affect in improving the system, then once settled I started work on the TempResetTimeout method which has now improved the server even better and is now on 10-20 waiting tasks with 5,000 users and then every minute when the delete sessions runs (with a step for the SessionStack) it will go up to anywhere between 30-80 waiting tasks for about 2 seconds.

    Is there anything else you can recommend for a website that's used this much? September is always our peak usage due to us programming assessment software.

    Regards and many thanks
    Liam

 Leave a Reply

(required)

(required)