If you’ve setup a Sitecore instance with MSSQL-based shared sessions, you’ve probably followed this guide:  https://doc.sitecore.net/sitecore_experience_platform/setting_up__maintaining/xdb/session_state/walkthrough_configure_a_shared_session_state_database_using_the_sql_server_provider.  Under the “Optimize SQL Server performance” section, you’ve seen a note to run a script to help boost performance.  Here’s what’s in the script for SC 8.1:

The long and short of that translates to moving both tables into tempDB and setting up some SQL Synonyms. These route all updates/inserts/deletes to the tempDB.  You can see the synonyms here:


Clicking on and viewing the Properties gives you this information.

Synonym Details

Essentially, when someone requests “Sessions” in your DB, it routes them to “SessionState” in the tempdb.

Is this better than running in your own DB?  I’m not sure, but I’ve asked Sitecore for clarification as to why it’s being done  See update below!.  In the interim, I want to undo that script, but how?  There’s a couple of ways to do that.  First, you could just drop the DB and attach the clean one from your repository.  This has everything in place and doesn’t require any SQL knowledge.  Second, you can rescript the tables in your Sitecore Sessions DB. This is pretty cake too:

  1. Right click the Application/SessionState table and click “Script Table as” -> “CREATE To” -> “New Query Editor Window”
  2. Change the DB Name at the top of the script to point to your current Sitecore Session DB.  Ensure on the “SessionState” table, you name it “Sessions” because this is what Sitecore is going to be looking for.
  3. Run it

You’re also going to want to recreate the index and drop the synonyms that were created.  Sounds a little painful?  Just run this script in context of the appropriate DB and all your worries are gone:

That should get everything back to using the Sitecore Sessions DB you created.  I’ll update this post with more information when I hear back from Sitecore!

[Update: I did hear back from Sitecore.  They said “This SQL script (optional for use) intentionally uses “tempdb” since it works in memory.” While that’s not 100% true, it’s a gem for 2014.  Check out this link for more details. It seems if you use SQL2014 and SQL 2012 SP1 CU 10 or SP2 CU1, you can get a bit of a performance boost.]

[Update 2: As Shaun pointed out in the comments, while it’s all fine and dandy to run in the tempdb, you’re likely going to run into security issues with your sitecore user having access to the tempdb for read/writ permissions.  You can add him manually, but as soon as the server restarts, the tempdb is recreated and that is lost. We found this out the hard way.  You can keep things persistent, however, by modifying the script it adds to the master database as follows:

It would be a lot cleaner if you didn’t have to run that in an EXEC statement, but switching DB context in a stored procedure is a huge security no-no.