Caution: what you are about to read is risky. If you do decide to go this route, make sure to do a lot of testing!
I recently tracked some intermittent pausing on our website to the ASPState database waiting on the
WRITELOG command in the database.
This was puzzling because we don't really use session state very heavily and we
definitely weren't hitting I/O bottlenecks.
I opened up profiler and started looking into what was going on.
Immediately I found some areas that could use improvement:
- TempGetStateItemExclusive3 had an eager-spool operator in it's execution plan.
- Excessive updating of indexed columns
- Updating even when nothing was being changed
Sometimes clever hurts
Without testing, I too would have thought that reading and updating in one operation
would provide the best performance, but the query analyzer thinks differently. Sirens started sounding in my head when I looked at the execution plan for the
TempGetStateItemExclusive3 procedures and saw an eager-spool happening for
what should be a simple index seek operation. According to the documentation, an eager-spool will write all of the input data to a temporary table.
While this should only be one row, writing the data to a temporary table is unnecessary and also causes additional I/O to the tempdb.
Documentation is light on why the query analyzer would choose to do an eager spool, but the analyzer is picking it due to the fact that the update is storing the values in variables, updating the values, and using the values to determine what other values should be all within the same statement. For everything to work right, order is very important, which is why SQL Server is choosing to get all the input data into a table before going through and doing the updates to avoid the halloween problem.
The next thing I noticed is that in the
TempGetStateItemExclusive3 you see a side effect of using the
UPDATE statement as a
statement: copious amounts of
CASE statements that conditionally modify the data only when we are actually locking the session or not. Almost every field follows
the logic if locked then set to this value, otherwise leave the original value. While clever and works, this has some unfortunate side effects:
Expirescolumn is always updated, which is indexed
- Even though we aren't changing values, we are still writing the values (I think, feel free to correct me if I'm wrong)
Let's break things
I've tried to make the procedures work as close to the originals as possible, though there are places where I deviate for performance reasons where I find the trade-off acceptable.
Let's start off with
TempResetTimeout procedure since it's the simplest and safest to modify (it also happens to be executed the most often).
All we need to do is add a
WHERE clause on the update statement that prevents the expiration being updated as frequently.
Notice the database name in the update statement. If you are using a custom state database, it will be different.
Now, we'll only update the index and write the updated values at most once per minute. The risk here is that someone may have their session timeout potentially one minute earlier than expected, and I am completely OK with this trade-off. You can tweak the interval, 1 minute is just a safe default. The major benefits here are both less index updating and less writing (this procedure is fired at the end of every request using session I believe).
Next we have the procedures that update the session state data:
TempUpdateStateItemShortNullLong. The most we can do here is avoid unnecessary index updates on the
Expires column. Here, we just need to conditionally update the
Finally, we get to the expensive procedures:
TempGetStateItemExclusive3. This procedures are almost identical, the
primary difference being that one locks the session while the other just reads the state (i.e. when the handler uses read-only session state). There's a lot
going on here, so I'll just post the code and then we'll go through it section by section.
The way the original code worked, your session would be extended if the session could be locked as it attempts to lock the session repeatedly until it actually acquires the lock. This is a breaking change from the original, but if you are using session on pages that take a long time, I would suggest looking into getting rid of session usage on those pages.
To start with, we grab our session values in the select statement. Before, all of this was done in the update statement, and thus caused the eager-spool problem.
The IIF statements make it so that we only grab the values if the session is unlocked, heck the "exclusive" aspect of the procedure.
SELECT statement instead of an
UPDATE statement has the drawback of possibly seeing the session is unlocked and someone locking
it before we can, which means that we need to hold a lock on the record in case we end up locking the session, and this is accomplished with the
Next, we get to some initialization stuff that it does. Basically, if the Flags value is 1, set it to 0 and return 1 in the
It's still doing the bitwise operations like before, but I don't think the values are ever different than 0 or 1.
The last part is straightforward. If we weren't locked, lock the session and read out the value in the
SessionItemLong if there was one.
A couple of things I want to point out: we are conditionally updating the value of the
Expires column as before, and we are updating the
LockCookie column with the
@lockCookie parameter value, which has an incremented value from the original
So how much does this all help? Well, with a simple benchmark using 8 active sessions, running 4 concurrent requests per session I saw ~15% total throughput. The session was being updated everytime, so throughput should increase even more with normal usage scenarios.
Feel free to tweak the changes where you see fit, you should have a decent understanding of the overall flow and requirements now. You can find all the scripts necessary on this gist on GitHub. The only other suggestion I have is that you make sure your session state database's recovery model is set to simple.