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

Azure Managed Disks

Dave Bermingham explains what Azure Managed Disks are and why you might want to use them: What’s Managed Disks you ask? Well, just on February 8th Corey Sanders announced the GA of Managed Disks. You can read all about Managed Disks here. https://azure.microsoft.com/en-us/services/managed-disks/ The reason why Managed Disks would have helped in this outage is that […]

Read More

SQL On Linux Backups

Rob Sewell shows how to use Ola Hallengren’s solution to back up SQL Server databases on Linux using the SQL Agent: Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As […]

Read More

Categories

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