Aaron Bertrand

Potential enhancements to ASPState

January 11, 2013 by in IO Subsystem, System Configuration, T-SQL Queries, Transaction Log | 14 Comments
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts →

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, GETUTCDATE();
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 < GETUTCDATE(); -- 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, src.[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?