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 [ASPStateTempApplications]
(
[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 deletion
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 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 [ASPStateTempApplications]
> (
> [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
>
>

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
>
>

ASP.NET & anti-virus & SSRS 2005

Below, I have pasted a portion of an MS MSDN article dealing with a specific issue we are having with Report Server performance. The URL (http://support.microsoft.com/kb/821438/en-us) is the MS KB article on the described fix. The fix mentioned is for "ASP.NET 1.1" but our BP Report Server is using "ASP.NET 2.0".

Has anyone encountered and resolved and how?

Running on box:
ASP.NET 2.0.50727;
SSRS 2005 Sp1 + Hotfix#2175;
Win Server 2003, R2, x64, SP1
Trend Micro OfficeScan Cleint for Win 2003/xp v7.3
IIS Version (I cannot find this #) - ? IE 6.0

===========================
Report Manager or the report server runs very slowly

In some circumstances, ASP.NET applications run very slowly on computers that are running anti-virus software. If the Report Server Web service is restarting frequently, and you are running anti-virus software, you can obtain an ASP.NET fix from Microsoft Customer Support Services.

Symptoms include Web applications or Application Domains restarting for no apparent reason, slow performance, session restarts, and more. For more information about the symptoms, cause, and resolution, see Microsoft Knowledge Base article 821438.

You can find out whether there are excessive server restarts by viewing the number of reportserver_<timestamp>.log files. A new log is created each time the server starts. A large collection of logs created at very short intervals is an indication that the conditions described in article 821438 exist on your server
===========================

It appears the issue goes away with SQL2K5 SP-2 load. Thanks to whoever.....