ASPState database

Hi,
We experienced poor performance when we actually started storing the .NET
VIEWSTATE information inside the ASPState database. We were experiencing
high volume of locks, avg. disk queue lenghts of over 40 and poor page life
expectancy (around 100). It looked like our 4 processor DELL PowerEdge 6850
was memory bound. The data file grew big to 8.8 GB in less than 2 hours. We
don't know what is really going on here. The schema for ASPState database
just involves 3 tables really and a SQL job that runs every minute to delete
records that have an expired session.
Here are the tables involved in this database. Could you help me find if
there is anything fundamentally wrong? We are going by the book on what
microsoft has recommended in terms of the database and implementing the
Session state for .NET on the server side.
CREATE TABLE [ASPStateTempSessions](
[SessionId] [char](32) 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] [image] NULL,
PRIMARY KEY CLUSTERED
(
[SessionId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions]
(
[Expires] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateTempApplications](
[AppId] [int] NOT NULL,
[AppName] [char](280) NOT NULL,
PRIMARY KEY CLUSTERED
(
[AppId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplicatio
ns]
(
[AppName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateDeferredData](
[SessionId] [char](32) NOT NULL,
[KeyID] [char](48) NOT NULL,
[SessionItemLong] [image] NULL,
CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
(
[SessionId] ASC,
[KeyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOHi
You don't say what volume of messages were being written?
You can run SQL Profiler to see the activity on the machine and to report
blocking, lock escallation etc. Check any queries especially how the deletio
n
process to see if you using the indexes, or if changing the index to be
clustered/non-clustered or if you require different indexes.
Also read http://support.microsoft.com/kb/271509/EN-US/ on how to monitor
blocking.
These changes should be done in a test environment rather than on the live
system.
John
"Shiva" wrote:

> Hi,
> We experienced poor performance when we actually started storing the .NET
> VIEWSTATE information inside the ASPState database. We were experiencing
> high volume of locks, avg. disk queue lenghts of over 40 and poor page lif
e
> expectancy (around 100). It looked like our 4 processor DELL PowerEdge 685
0
> was memory bound. The data file grew big to 8.8 GB in less than 2 hours. W
e
> don't know what is really going on here. The schema for ASPState database
> just involves 3 tables really and a SQL job that runs every minute to dele
te
> records that have an expired session.
> Here are the tables involved in this database. Could you help me find if
> there is anything fundamentally wrong? We are going by the book on what
> microsoft has recommended in terms of the database and implementing the
> Session state for .NET on the server side.
> CREATE TABLE [ASPStateTempSessions](
> [SessionId] [char](32) 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] [image] NULL,
> PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions
]
> (
> [Expires] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateTempApplications](
> [AppId] [int] NOT NULL,
> [AppName] [char](280) NOT NULL,
> PRIMARY KEY CLUSTERED
> (
> [AppId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplicat
ions]
> (
> [AppName] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateDeferredData](
> [SessionId] [char](32) NOT NULL,
> [KeyID] [char](48) NOT NULL,
> [SessionItemLong] [image] NULL,
> CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC,
> [KeyID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>