Solving Blocking Without Sysadmin

Michael Swart has a story on blocking due to an edge case scenario:

SQL Server was struggling to compile the procedure in time and the application wouldn’t let it catch its breath. The query optimizer was attempting to create statistics automatically that it needed for optimizing the query, but after thirty seconds, the application got impatient and cancelled the query.

So the compilation of the procedure was cancelled and this caused two things to happen. First, the creation of the statistics was cancelled. Second, the next session in line was allowed to run. But the problem was that the next session had already spent 28 seconds blocked by the first session and only had two seconds to try to compile a query before getting cancelled itself.

The frequent calls to the procedure meant that nobody had time to compile this query. And we were stuck in an endless cycle of sessions that wanted to compile a procedure, but could never get enough time to do it.

There are two important lessons here:  how Michael solved the problem and also a reminder that plan cache entries are dependent upon specific application settings.

Related Posts

Handling Rogue Queries In Spark

Alicja Luszczak, et al, introduce the Query Watchdog: The previous query would cause problems on many different systems, regardless of whether you’re using Databricks or another data warehousing tool. Luckily, as an user of Databricks, this customer has a feature available that can help solve this problem called the Query Watchdog. Note: Query Watchdog is […]

Read More

Deploying Reports With Powershell

Jana Sattainathan has created a few Powershell functions to automate dealing with SQL Server Reporting Services report deployment: In this post, I want to publish a few functions that I created around SSRS. They are related to and depend on each other. Get-SSRS – Given the SSRS URI returns the WSDL endpoint Get-SSRSReport – Returns […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031