Performance Considerations for ASPSTATE Database in SQL Server for DBAs

SQLServerF1

One of the database which may appear on multiple SQL Server instances in multiple environments is the database with name ASPSTATE and most often or not these are very small in size, but are very busy with user activity. Most of the times, DBAs are not really concerned with this ASPSTATE database and not much aware of the purpose of this database and if there are any considerations as a DBA to be aware of this database. However at times the performance and management of this database becomes critical for the application to function smoothly. Below are some of the considerations for ASPSTATE databases for DBAs to be aware of.

The ASPSTATE databases is generally very busy with lot of user activity which involves multiple DML or select operations. DML operations like Insert, Update and Delete are performed very frequently on this database which causes lot of write operations to the transaction log file, so it is better to keep the ASPSTATE database in SIMPLE recovery model which will simplify the management of the transaction log file.

By default all the user data is stored in tempdb, so it is important to configure the ASPSTATE database in such a way that the user data is created and stored in the ASPSTATE database itself. As mentioned before this database can have lot of DML and select operations, it is advisable to make sure that the data and transaction log files are on different RAID disks and good to monitor the disk response times where the data and log files reside. Any disk latency can cause significant slowness.

Generally it is possible to have multiple applications use same ASPSTATE database, but as a best practice it is not a good idea, instead always prefer dedicated database for each application which makes monitoring and management of the ASPSTATE database efficient.

The database tables and indexes may show high fragmentation and blocking due to multiple requests to access the tables. For fragmentation, performing index rebuilds and update stats once a day should be good, however it may not show enough benefits as the data keeps changing very frequently. Another idea is to make sure that auto-update stats is turned off. Generally the blocking is common to see against ASPSTATE database, but is mostly very short time blocks, however if the blocking happens more frequently, then action needs to be taken based on what the head blocker is doing.

Starting with SQL Server 2014, In-Memory OLTP technology can be used to make the database access much faster. There are other optimizations too related to deletes and blocking but that are mode dealt by the developers than DBAs. If requested as DBA you could propose any missing index which may be beneficial, but keep in mind that this need to tested throughly before implementing as it can cause negative impact as well.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Basics of ASPSTATE Database in SQL Server for DBAs

SQLServerF1

It is common for DBAs to notice databases with name ASPSTATE on multiple SQL Server instances and most often or not these are very small in size, but are very busy with user activity. Most of the times, DBAs are not really concerned with this ASPSTATE database and not much aware of the purpose of this database and if there are any considerations as a DBA to be aware of this database. Below are some basics about the purpose of this ASPSTATE database and some points for SQL Server Database Administrators to have some knowledge about this database which may turn helpful in future.

What is ASP.Net Session State?
ASP.NET session state allows to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications.

In order to configure an ASP.NET application for ASP session state management, we need to configure the web.config file of the web application. There are different modes that are available for use to store the session state information as mentioned below.

OFF – In this mode the ASP session state is turned off for the web application.
InProc – In this mode ASP sessions are kept in memory on the web server. This is the default behavior.
StateServer – In this mode the session is stored in a separate process called the ASP.NET state service.
SQL Server – In this mode the session state is stored in SQL Server database generally named as ASPSTATE.
Custom – Developers can write their own custom mechanism or any other way that is readily available for the session storage mechanism.

This ASPState database is created by developers using aspnet_regsql.exe utility that comes with the .NET Framework. There are various parameters available for this utility to specify the instance name and other settings like authentication mode to connect to SQL instance, etc. ASPState database is used to store the stored procedures and functions required for session state management and by default all tables which store session state information is created in tempdb which will be lost after restart of the SQL Server instance. To avoid missing session state information even after restart of the SQL Server instance, there are various parameters passed to aspnet_regsql.exe utility which will create/store the tables which hold session state information in ASPSTATE database itself.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.