A Fine Slice Of SQL Server
Query Store is my favorite way to gather information about problem queries and plans, and I wanted to share some information on the useful metrics I use most.
The first two are obvious, but there’s a difference between them. The last two are not obvious but offer an unusual utility. I also wanted to explain why I use logical reads and mostly ignore physical reads.
Read on for Jared’s list.
Comments closedKendra Little fixes a problem:
Forcing plans with Query Store can be a powerful tool—until it mysteriously fails. In real production systems, plan forcing sometimes just… doesn’t work. One common culprit is the cryptic
OPTIMIZATION_REPLAY_FAILEDerror.If you’re hitting
OPTIMIZATION_REPLAY_FAILED,try re-forcing the plan using@disable_optimized_plan_forcing=1.
Click through for a summary of the problem and additional helpful information on the issue.
Comments closedHugo Kornelis looks back at some nice additions to SQL Server:
It’s December. The last month of the year, and hence also the last T-SQL Tuesday of the year. Edition 181 is hosted by Kevin Chant. His chosen topic for this episode is to have us talk about which SQL Server announcement excited us the most.
This posed a bit of a problem for me. The only truthful answer here is that I have never been as excited about an announced new feature as when the Query Store was announced. But I also realized that I don’t have much to write about the Query Store, except how awesome it is. Okay, I could go on a full explanation, but many others do that much better, and have already done so. So that doesn’t leave much for me.
And that’s why I’ll also include my second favorite announcement.
Read on for a little bit around Query Store, followed up with coverage of several interesting additions to SQL Server over the years.
Comments closedAndy Brownsword gets into the spirit:
Reviewing performance for a SQL database over time can be a challenge without hand crafted tooling or using external monitoring apps. This isn’t about putting fires out, we’re looking at proactive monitoring and taking action for incremental improvements.
The Query Store comes to the rescue by providing built-in insights into key performance metrics over time. We can use different combinations of them to better understand how the database is working.
Query Store isn’t perfect, but it’s very good solution to an extremely common problem.
Comments closedKendra Little digs into a problem:
This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning. This one has taken top slot because it makes my life more difficult on a daily basis.
Background: SQL Server generates a
query_hashfor each query. This is stored insys.query_store_queryand it’s one of the primary ways you can identify what a query is across different Query Stores, or even the same Query Store over time, as surrogatequery_idvalues get reset if Query Store is cleared or data ages on. Thequery_hashis a “Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren’t included as part of the hash.” (Source)Except that’s wrong.
Read the whole thing.
Comments closedMatt Changchien does a bit of testing:
It’s beneficial to understand how Query Store captures query runtime stats when they are used for performance troubleshooting. According to public documentation: upon the query execution, runtime statistics are sent to Query Store.
I am curious whether Query Store will still capture execution statistics under certain scenarios. Hence, in this blog post, I will be testing the two following scenarios with my Azure SQL Database:
- Whether Query Store captures runtime stats for query that has completed within an uncommitted transaction, then the transaction rolls back due to a scaling process.
- Whether Query Store captures runtime stats for query that is blocked and does not finish before a disconnect caused by a disruptive event (e.g., a service tier scale-up).
Read on to learn more about these two scenarios. The specific context is Azure SQL Database, though the same rules would apply on-premises as well.
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 closedKendra Little has a script for us:
tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic “failed” forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.
This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.
Read on to learn more about what might cause these failure to occur and what you can do about them.
Comments closedEdwin Sarmiento shows a practical application of Query Store:
In a previous tip on SQL Server 2016 Query Store Introduction, we’ve seen how to enable Query Store and find out the top resource consumers. We’ve experienced high CPU utilization recently and wanted to know the root cause and how to fix it. How can we use Query Store to achieve this?
Read on for the scenario and how Edwin diagnosed and fixed the issue.
Comments closedKendra Little warns us not to let a person named General Failure run your military:
This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.
These bugs are most likely to impact you if:
- You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
- Anyone manually forces query plans with Query Store.
- You have slow storage, which can increase your likelihood of having longer compilation times.
The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.
In the words of the great John Madden, that’s a heckuva bug.
Comments closed