Performance Considerations for ASPSTATE Database in SQL Server for DBAs
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.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.