Blocking Due to SPID in Sleeping State in SQL Server

SQLServerF1

Blocking is a common issues noticed in any of any relational database management system (RDBMS) which uses lock-based concurrency. In SQL Server, blocking generally occurs when one SPID holds a lock on a specific resource and a another SPID attempts to acquire a conflicting lock type on the same resource. Generally, the time frame which the first SPID holds locks on the resource is very small, so when it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and happens many a times throughout with no noticeable effect on system performance.

It is common for DBAs to notice blocking on various servers and typically the locks held for a short time does not cause any issues, nor is identified by any monitoring tool. Sometimes, blocking can get severe and cause performance issues from timeouts to slowness, etc. DBAs generally deal with blocking issues by checking on the head or lead blocker causing the blocking and try to understand why the head blocker is taking long time to complete and it is not very important, then DBAs kill the SPID. If the blocking issue is persistent then DBAs identify the culprit and tune it by adding indexes or rewriting the query.

However, sometimes there can be strange blocking issues, one of such issue is blocking caused by a SPID which is in sleeping state. In general, a SPID which finishes its task will leave any locks held, so the sleeping sessions are not expected to cause any blocking issues, however in some environments, persistent blocking issues are noticed due to SPID which is in sleeping state still holds the locks and does not release it until the connection is closed from the application of till the SPID is killed by the DBAs. If we try to understand why the blocking occurs with a session in sleeping state, there could be different reasons and some of the are mentioned below.

– Mostly this issue happens when someone uses SQL Server Management Studio(SSMS) query analyzer window, runs some bad queries and leave the session open. The queries which were run either have a explicit open transaction or an implicit transaction which holds locks and does not release the locks because no commit or rollback was issued. We need to identify such sessions, the user who is running queries from that session, what queries are run, what locks are held, from which application are the session from, hostname, etc and then DBAs can go ahead and kill the sessions. Then DBAs need to contact the respective user or management and report the issue to avoid happening in future.

– This can also happen when an application opens a connection with SQL Server, runs some query and then due to some issues, the application connection is lost either due to application crash or hang or some application problems. No signal is sent to SQL Server due to sudden crash, so SQL Server still assumes that the application may still send additional queries and keeps the session available. This is due to bad application design and needs to fix the application code.

– This can also happen if application has bad code, which opens transactions and fails to commit or rollback in case of query failures or other issues.

– Applications also uses session pooling, where is sessions are reused when required. Periodically applications need to close sessions which have not been reused after sometime.

– We can also suggest developer to use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. It is important to note that T-SQL statements following the statement which caused the error will not be executed.

– Some older or incompatible client components may cause these kind of issues too, so need to keep them updated and use latest and compatible connectivity components.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

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

 

Tips for DBAs to Troubleshooting SQL Server Performance Issues

SQLServerF1

Troubleshooting SQL Server performance issues is one of the common and important task of DBAs. It is not very easy to troubleshoot performance issues without prior experience or good knowledge about how SQL Server query execution works and what factors can contribute to performance issues with the SQL Server instances. It is very common to see many entry level DBAs or even DBAs with more than five years of experience still struggle dealing with performance issues, as there are just many things that can cause the performance problems and many symptoms may be interrelated and not easy to diagnose and come to a conclusion after which the solution to be found and implemented. In many organizations troubleshooting performance problems take many hours to days and even weeks too.

To help DBAs to be able to find a quick list of things which they can check or look into in case any SQL Server performance issues are related. It is important to note that troubleshooting performance issues with SQL Server is very broad ranging from SQL Server configuration, OS configuration, Network or other hardware configuration, blocking, waits, fragmentation, outdated statistics, etc. Below are some of the things which DBAs can look into when performance issues are reported with SQL Server instances.

– Understand about the problem. Generally performance issues are reported by developers, Managers, application users or some monitoring tools. Depending on the source of the issue reported, gather data by asking questions like what do their interpretation of performance issues mean, why do they think SQL Server is cause of performance issues, what data they have collected, what symptoms do they see with application, did they collect any data so far, etc. Collecting as much data can be helpful to move in the right direction.

– If the performance issues are reported by all users, that means it is general slowness, can be anything from Hardware configuration issues to SQL Server query problems. We can start looking into basic SQL Server configuration settings like MaxDoP, Max Server Memory, SQL Server version and patch details, platform, etc. Gather OS configuration details like CPU, Memory and disk details and if they are as per best practices like data and log files on different disks, tempdb on its dedicated disk, etc. Also, sometimes OS settings related to instant file initialization, lock pages in memory, CPU power plan set to balanced can show impact too.

– Quickly check on perform to see if there are any resource bottleneck. Look into counters for CPU, Memory, Disk IO and network to see any issues or any values which are outside of expected values.

– From SQL Server, we can check if there are regular maintenance like rebuild of indexes and statistics updates. Check for fragmentation and when stats were last updated. Check if there are any jobs currently running, if jobs related to reindexing, stats update, checkdb or backups running, then stop them. Check if there is any blocking happening on the SQL Server instance, if found, check the headblocker and take appropriate action.

– Check wait stats on the server and see the top contributing wait and see if we can related to any physical resource bottleneck.

– Check currently running sessions which are taking more time and see why they are taking more time. Try tune them by creating any missing indexes or rewrite queries, etc.

– If the application is related to Vendor, then check for any best practices from Vendor for improving the performance.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

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

 

Administering SQL Server Report Server Databases

SQLServerF1

Once DBAs install and configure SQL Server Reporting Servers, two new databases will get created in the respective SQL Server instance selected during Reporting Services configuration. By default, these two new databases names will be ReportServer and ReportServerTempdb. As part of Reporting Services configuration, we can choose different names too, but in general, if we choose not to change default names, then we will see these two new databases being created in the respective SQL Server instance.

ReportServer Database – The report server database is a SQL Server database that is used to store the content such as reports and linked reports, shared data sources, report models, folders, resources, Subscription and schedule definitions, Report snapshots and report history, System properties and system-level security settings, Report execution log data, Symmetric keys and encrypted connection and credentials for report data sources, etc.
ReportServerTempDB Database – Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database. This reportservertempdb has different behavior compared to regular tempdb database. Reportservertempdb does not get recreated upon SQL Server restart, and DBAs are allowed to delete this database, in which case we need to restore this database either using available backups or database files if available.

Backup Considerations for Report Server databases – It is important to backup report server databases to be able to recover reporting services instance in case of a server crash or a disaster. It is best to choose Full recovery model for ReportServer database and perform regular full and log backups. For ReportServerTempDB, we can choose to keep it in Simple recovery model and importantly perform regular Full backups to be able to get the sachems back. If we do not have backups of these databases, then report server configuration and settings will be lost and need to be built from scratch.

Also, it is very important to backup report server encryption keys and keep them safe on some backup share, as this is required to be able to restore reportserver databases and to be able to read data from the tables. Missing encryption keys will cause the loss of credentials and other settings which are encrypted using this key and will need to build missing things from scratch again. We can backup the key from the Report Server Configuration Manager. Not having backup of report server encryption keys may result in major issues, so just be aware and careful.

For troubleshooting report server issues, we can check eventlogs and report server logs which are mostly available at \Reporting Services\LogFiles folder. We may need to some times clean up the files if old logs or dumps are not cleaned up.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

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