A Fine Slice Of SQL Server
Reducing waits is a great way to improve the performance of your SQL Servers. Minimizing PAGELATCH_EX and PAGELATCH_SH wait types are more involved than most. There are generally two causes; one of which is largely solved in recent versions, and one which requires real thought and planning to resolve.
Read on for both of those causes, as well as several tips you can use to minimize the risk of waiting.
Comments closedI’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:
- sp_BlitzFirst @SinceStartup = 1 showed very clearly that their top wait, by a long shot, was blocking. Hundreds of hours of it in a week.
- sp_BlitzIndex showed the “Aggressive Indexes” warning on a single table, but… only tens of minutes of locking, nowhere near the level the database was seeing overall.
- sp_BlitzCache @SortOrder = ‘duration’ showed a couple queries with the “Long Running, Low CPU” warning, and they did indeed have blocking involved, but … pretty minor stuff. Plus, their plan cache was nearly useless due to a ton of unparameterized queries pouring through constantly, overwhelming Azure SQL DB’s limited plan cache.
- sp_Blitz wasn’t reporting any deadlocks, either. (sp_BlitzLock doesn’t work in Azure SQL DB at the moment because Microsoft’s no longer running the default system health XE session up there. They turned that off in order to save money on hosting costs, and passed the savings on to… wait… hmm)
- As a last-ditch hail-Mary, I ran sp_BlitzWho repeatedly, trying to catch the blocking happening in action. No dice – the odds that I’d catch it live weren’t great anyway.
Click through for the story and how all the pieces ultimately fit together.
Comments closedAndy Mallon troubleshoots an issue:
In January, one of our Staff Engineers sent the following message to the DBRE help channel in Slack:
Morning folks, we had a pretty significant wait spike on the [database]. Circuit breakers closed and reopened quickly. Is anyone immediately aware of a reason why this could’ve happened?
Read on for Andy’s quick analysis and then the root cause and solution.
Comments closedChad Callihan disambiguates two terms:
You may hear or read about locks and latches and assume these terms are being used interchangeably. After all, if you search for synonyms for the word “lock”, you’ll find “latch” as a potential substitute:
In database terms, lock and latch are similar but have unique purposes of their own. It’s not as simple as “you say toMAYto, I say toMAHto.”
Click through for the difference and a few details on latches.
Comments closedAndy Brownsword removes a blocker:
Blocking in SQL Server will reduce throughput. Excessive blocking can be cause bottlenecks on our environments so helping to mitigate it. Here we’re going back to basics to look at how it happens and how having effective indexes can reduce it.
The word “effective” is the critical one there.
Comments closedI found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an “online” index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually “online”. In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.
If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.
Click through for the details, including repo scripts and explanation of what should happen in this case.
Comments closedDeadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes.
An example where I’ve used this effectively was for a queue table where work would be placed and a number of processes would nibble away at it throughout the day. Separate to this was a maintenance routine which ran each evening to manage partitions on the table. The maintenance job would kick in and would deadlock all those processes.
This was solved with an Application Lock.
Read on to learn more about application locks, how they work, and why they fixed the problem in this scenario. Generally, this happens when two operators access two resources in different orders. For example stored procedure A has a transaction which locks table 1 and then locks table 2 and then commits. Meanwhile, stored procedure B has a transaction which locks table 2 and then locks table 1 and then commits. The classical solution is to fix the ordering such that both are consistent. But not all deadlocking behavior is that straightforward or that simple, and so other solutions like app locks can be quite helpful to know about.
Comments closedErik Darling talks up a famous Canadian:
I know there’s been a lot of blog content about the creation of and updates to my stored procedures lately. I try to consolidate as much as possible, but this one comes from Valued Contributor©.
The one, the only, the Canadian: Michael J Swart (b|t) recently contributed a really cool piece of code that I’ve been dying to have since I created sp_HumanEventsBlockViewer: the entire blocking chain is visualized in the results.
Click through to see what it looks like, and next time you see Michael, give him a thumbs up.
Comments closedEitan Blumin just wants to switch the database type:
Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.
To resolve it, I first tried running the following command:
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;In response, I got deadlocked with the dreaded error 1205:
There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.
1 CommentJose Manuel Jurado Diaz writes a program:
Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? – Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one.
First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.
I was a little surprised the answer wasn’t to use Extended Events, though this does work if you simply need something to run in ad hoc scenarios.
Comments closed