Hidden Blocking
Blocking occurs when you program must discontinue execution while waiting for a resource shared by another process. As our CPUs continue to grow the number of available cores and/or processors, this issue continues to grow. SQL Server takes advantage of multiple processors, and may be susceptible to blocking.
Built into SQL Server Management Studio are a number of standard reports I find extremely helpful in diagnosing what is going on in any server. I use the blocking queries report in order to determine if query performance may be degraded due to contention between different processes. Many times I execute this report and find that there are no known blocking queries.
The blocking query report demonstrates queries that generally are blocked because they are not able to obtain a lock on an index or table. It won’t tell you if your query is slow because there are no good indexes on a table, or if the statistics are out of date. Only if a query is on hold waiting for another query to complete. Try shrinking a database file in the middle of a heavy period and you’ll see what I mean (don’t do it on a production system).
There is another kind of blocking that happens in SQL Server, and it doesn’t show up in this report. There may be times when background tasks are being performed that must complete before your query may be executed such as flushing cache updates to disk, or writing to a transaction log. These show up as waits instead of blocking. It isn’t directly related to another specific query; it is the results of SQL Server or the operating system performing higher priority tasks.
Again, check into the DMVs for digging into wait issues. There are no standard reports helping you if this is occurring. Performance Monitor may also be used in conjunction with the DMVs to identify any processes that are slow and impacting your system performance.
Share your diagnosing tips by leaving a comment below, or sending an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)