A Fine Slice Of SQL Server
Jared Poche digs into the topic of estimates:
Estimates and statistics are often discussed in our community, but I doubt the average DBA knows how they flow. So I wanted to write a post with examples showing how SQL Server estimates the rows for a specific operation.
Read on to learn more.
Comments closedHugo Kornelis describes an issue:
I was hired by a customer who had a very annoying issue with the daily data load of their data warehouse. The volume of data to be loaded is high and they were already struggling to finish the load before business opens. But that was not their biggest issue. The biggest problem, the real pain point that they hired me for, is that at unpredictable moments, the load would run much longer than normal, pushing it well into business hours. They wanted me to find out what caused those irregular delays, and find a way to stop them from happening.
Read on to learn more about the issue itself, as well as a discrepancy in what Query Store showed. Hugo also points out that the quick-and-easy solution may not be the right solution.
Comments closedLet’s talk about the trade-offs.
Auto-updated stats are triggered by queries needing to access a table with “stale” stats. (It doesn’t matter how we define stale, so lets not think about it.) With the exception of very small tables, auto-updated stats use a random small data sample of the table data; in contrast scheduled jobs that usually have a larger sample size defined—I always say “
FULLSCANif you can!”
Read on for Andy’s thesis.
Comments closedKendra Little explains how storage can affect query compilation time:
Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.
Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.
Click through for more details, as well as a video by Erik Darling on compile-time locks.
Comments closedAndrew Lepikhov breaks out the stopwatch:
In the previous post, I passionately advocated for integrating extended statistics and, moreover, creating them automatically. But what if it is too computationally demanding to keep statistics fresh?
This time, I will roll up my sleeves, get into the nitty-gritty and shed light on the burden extended statistics put on the digital shoulders of the database instance. Let’s set aside the cost of using this type of statistics during planning and focus on one aspect – how much time we will spend in an ANALYZE command execution.
Read the whole thing if you’re a Postgres admin or developer.
Comments closedAndrei Lepikhov compares two physical join operators:
Today’s post is sparked by a puzzling observation: users, especially those who use an abstraction layer like REST or ORM library to interact with databases, frequently disable the MergeJoin option across the entire database instance. They justify this action by citing numerous instances of performance degradation.
Considering how many interesting execution paths MergeJoin adds to the system elaborating IncrementalSort or sort orderings derived from underlying IndexScan, it looks strange: one more bug of skewed cost balance inside the PostgreSQL cost model?
This is an interesting peek into how complex the query optimizers in database engines are, as well as how small amounts of information (via statistics or indexes) can matter to a query.
Comments closedI 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 closedKevin Wilkie breaks out the abacus:
Today, I was working with SQL Server to get row counts from several tables so I thought I’d be smart and work with some functions in SQL Server to make it smarter / easier.
Now, if I am truly only getting “straight” row counts from these tables, I would be able to create a query like the below that would provide the answers with no problem:
Read on for the normal approach, as well as a more complicated approach made necessary due to some business logic requirements.
Comments closedJose Manuel Jurado Diaz searches for clones:
Some time ago, we encountered a support case where a customer experienced significant delays in updating auto-created and user-created statistics. I would like to share the insights gained from this experience, including the underlying causes of the issue and the potential solutions we identified and implemented to address the problem effectively.
Read on for a demo to set up the scenario and the cause of the problem, as well as how to fix it.
Comments closedKendra Little explains a problem:
If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.
This has been the case for a few years, and it’s unclear if Microsoft will fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.
Read on for Kendra’s thoughts. I haven’t run into this before, myself, but I also don’t tend to make very heavy use of readable secondaries.
Comments closed