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:

USE [Sitecore_Sessions];
GO

BEGIN TRANSACTION;
GO

IF( OBJECT_ID( N'dbo.sessions', N'SN' ) IS NOT NULL )
BEGIN
  DROP SYNONYM [dbo].[Sessions];
END;
GO

CREATE SYNONYM [dbo].[Sessions] FOR [tempdb].[dbo].[SessionState];
GO

IF( EXISTS( SELECT 1 FROM [information_schema].[tables] WHERE ([table_schema] = 'dbo') AND ([table_type] = 'BASE TABLE') AND ([table_name] = 'SessionState') ) )
BEGIN
  DROP TABLE [dbo].[SessionState];
END;

IF( OBJECT_ID( N'dbo.applications', N'SN' ) IS NOT NULL )
BEGIN
  DROP SYNONYM [dbo].[Applications];
END;
GO

CREATE SYNONYM [dbo].[Applications] FOR [tempdb].[dbo].[Application];
GO

IF( EXISTS( SELECT 1 FROM [information_schema].[tables] WHERE ([table_schema] = 'dbo') AND ([table_type] = 'BASE TABLE') AND ([table_name] = 'Application') ) )
BEGIN
  DROP TABLE [dbo].[Application];
END;

COMMIT TRANSACTION;
GO



USE [master];

BEGIN TRANSACTION;
GO

IF( OBJECT_ID( N'dbo.Sitecore_InitializeSessionState', N'P') IS NOT NULL )
BEGIN
  DROP PROCEDURE [dbo].[Sitecore_InitializeSessionState];
END;
GO

CREATE PROCEDURE [dbo].[Sitecore_InitializeSessionState] AS
BEGIN
  EXECUTE [Sitecore_Sessions].[dbo].[CreateTables];
END;
GO

EXECUTE [dbo].[Sitecore_InitializeSessionState];
GO

COMMIT TRANSACTION;
GO

EXECUTE [sp_procoption] @ProcName = 'dbo.Sitecore_InitializeSessionState', @OptionName = 'startup', @OptionValue = 'true';
GO

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.

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:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Sessions](
	[Id] [nvarchar](88) NOT NULL,
	[Application] [uniqueidentifier] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Expires] [datetime] NOT NULL,
	[LockTimestamp] [datetime] NOT NULL,
	[LockCookie] [char](32) NOT NULL,
	[Timeout] [int] NOT NULL,
	[Flags] [int] NOT NULL,
	[Item] [image] NULL,
 CONSTRAINT [PK_SessionState] PRIMARY KEY CLUSTERED 
(
	[Id] ASC,
	[Application] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SessionState] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[SessionState] ADD  DEFAULT ((0)) FOR [Flags]
GO

CREATE NONCLUSTERED INDEX [IX_Session_State_Expires] ON [dbo].[Sessions]( [Expires] );
GO

CREATE TABLE [dbo].[Application](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](280) NOT NULL,
 CONSTRAINT [PK_Application] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


DROP SYNONYM [dbo].[Applications];
GO


DROP SYNONYM [dbo].[Sessions];
GO

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:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Sitecore_InitializeSessionState]    Script Date: 8/25/2016 1:08:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sitecore_InitializeSessionState] AS
BEGIN
  EXECUTE [Sitecore.Sessions].[dbo].[CreateTables];

  EXEC('USE tempDb; CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER] WITH DEFAULT_SCHEMA=[dbo]; ALTER ROLE [db_datareader] ADD MEMBER [YOUR_USER]; ALTER ROLE [db_datawriter] ADD MEMBER [YOUR_USER]')
END;

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.