A Fine Slice Of SQL Server
I recently encountered an issue where an index rebuild set to
wait_at_low_priorityended up blocking an asynchronous statistics update. This interaction led to a large blocking chain where queries were waiting on the async stats update and started to timeout.
Read on for an explanation of all of the players involved, then a demo, and finally two solutions.
Comments closedJosephine Bush wants to find the deadlocks:
A couple of weeks ago, a developer came to me and wanted to know how to figure out what was causing a deadlock. I honestly didn’t know where to look or if this was even being captured in Azure SQL DB already. It turns out that Microsoft has you covered with deadlock tracking. At least for a period of time. It looks like you can go back about a month, maybe.
Read on to see how you can find this information in Azure SQL DB. If you’re working in on-prem SQL Server and you don’t have any tooling set up, you can find some deadlocks in the system health extended event.
Comments closedTom Zika performs an experiment:
In SQL Server, using the
KILLcommand to terminate a session results in an entry being logged in the error log.
This raises the question: Does theALTER INDEX REBUILDcommand with theWAIT_AT_LOW_PRIORITYoption also log its actions in the error log?
Tom does violate Betteridge’s Law of Headlines, but you should read the post regardless.
2 CommentsOne of the newer features introduced in Azure SQL Database is “Optimized Locking”. But what does that mean, and how does it compare to the apparently “un-optimized” locking the SQL relational engine has been using up to this point? Let’s dive in and take a look.
Read on to learn more about optimized locking and how it compares to the traditional form of locking available in SQL Server today.
Comments closedReducing 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 closed