When implementing SQL Server instances, performance is one of the key factors in determining the success or failure of a database and its applications. The database needs to be responsive to user requests and return query results in a reasonable amount of time. If it doesn’t perform up to expectations, the application will not be used as productively as planned and your database team will spend a lot of time searching for the issues that are slowing it down.
A Little Background on Blocking and Locking
In the SQL Server universe, blocking and locking are unwelcome entities that contribute to performance issues. Before we embark on a discussion of how to resolve these problems, let’s define some terms.
- Session ID (SPID) – Each logged-on session connected to an SQL Server is defined with a unique SPID. Each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A client can have multiple connections and SQL Server treats SPIDs the same whether they originate from the same or different clients.
- Locks – A lock is a hold on resources initiated by a SPID. Locks can be held for a variable amount of time depending on the duration and context of a query. Under normal circumstances, locks are obtained and released regularly by SPIDs and do not cause performance degradation.
- Blocking – When two SPIDs compete for the same system resource, blocking occurs. The second SPID cannot obtain its lock of the intended resource until the first one releases it. This results in a delay as the SPID waits its turn. Performance degradation occurs in two distinct ways.
- A SPID can hold a lock for an extended amount of time that eventually resolves itself. This type of blocking can be hard to pin down as performance can be impacted but the SPID responsible for the issue is difficult to identify.
- An indefinite lock can also be held by a SPID that never releases its resources. In this type of blocking, the culprit is easy to identify as the system is in a consistent state that lends itself to troubleshooting and addressing the problem SPID.
Expert Insight Into the Blocking Problem
One of the goals of this post is to direct the reader’s attention to an informative IDERA webcast. Hilary Cotter, an expert on SQL Server, is the presenter for the webcast which is titled How to Detect, Analyze, and Minimize SQL Server Blocking and Locking. Hilary explains the issues of locking and blocking in great detail and provides recommendations that will help you resolve performance problems with your SQL Servers.
Here’s an overview of the information available in the webcast. It may open your eyes to the blocking problem and what a DBA can do to minimize it.
- How long can blocking last? – There are some misconceptions regarding the length of time blocking can persist. Despite some theories that try to define this amount of time, it is essentially unpredictable.
- How does SQL Server work when handling transactions and disk writes? This includes a discussion of logged wait stats and how SQL Server performs parallel processing. Locking is the result of serial ownership of resources to the exclusion of other SPIDs. Blocking is the term used to describe the waiting involved when two SPIDS need to access the same resource.
- How can I determine if locking is a problem on my SQL Server? The prevalence of applications timings out, large numbers of lock escalations, and unpredictable or slow performance are all signs that your system may be experiencing issues with locking.
- The webcast also includes a discussion of transactions and lost updates, deadlocking, and how to control which process may be locked. Troubleshooting using WaitStats is also covered with some specific techniques to resolve excessive lock times and improve system performance.
- Reducing system locking by taking advantage of several different methods including minimizing indexes, partitioning a contended table, and monitoring page splits is also covered.
This just scratches the surface of the information presented in this webcast. Hopefully, we have whetted your appetite for the complete presentation. It is an hour well-spent by database professionals working with SQL Server.
Monitoring the Performance of Your SQL Servers
The webcast concludes with a demonstration of how to address the problems of blocking with IDERA’s SQL Diagnostic Manager for SQL Server. This comprehensive monitoring application for SQL Servers monitors and alerts on performance issues for on-premises and cloud instances of the database platform. It is extremely useful in identifying the problems that are leading to degraded response time.
Locks, blocks, and deadlocks are monitored in real-time for immediate troubleshooting. Historical data can be reviewed to help find recurring problems. The tool’s intuitive interface lets you observe SQL Server activity in multiple ways such as a list of events or a timeline calendar. Index fragmentation is also identified, providing DBAs with targets ripe for defragmentation. Users can create customized baselines against which system performance can be measured to assure they meet business requirements.
The combination of the information contained in the webcast and the functionality of SQL Diagnostic Manager for SQL Server sets up a database team with the ability to tackle blocking, locking, and any other types of performance issues. They will be suitably prepared to squeeze optimal performance from their systems and keep mission-critical applications running smoothly.