Press "Enter" to skip to content

Category: Query Store

Query Store Not Capturing Runtime Stats for Unfinished Queries

Published 2024-10-01 by Kevin Feasel

Matt 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:

  1. 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.
  2. 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 closed

Query Start Times in Query Store

Published 2024-09-11 by Kevin Feasel

Hugo 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 closed

Automating Unforcing of Failed Query Store Forced Plans

Published 2024-09-03 by Kevin Feasel

Kendra 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 closed

Troubleshooting a Performance Issue using Query Store

Published 2024-09-03 by Kevin Feasel

Edwin 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 closed

“General Failure” and Query Store Compilation Times

Published 2024-08-14 by Kevin Feasel

Kendra 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

Using Query Store on Azure Database for PostgreSQL

Published 2024-07-24 by Kevin Feasel

Marisa Mathews covers an episode of Data Exposed with Anna Hoffman and Grant Fritchey:

Capturing query metrics in PostgreSQL can be a challenge. Add in running that PostgreSQL database on Azure and things get even more challenging. However, Microsoft has a great addition to PostgreSQL when running in Azure Database, Query Store. This session will show the great query information available within the Query Store and how to query it.

Click through for a link to the video and additional links.

Comments closed

Tips to Identify Poorly-Performing Code

Published 2024-07-16 by Kevin Feasel

Rich Benner shares a few tricks:

So, you have a database or block of code. You’ve been told to “make it fast” but you’re not sure where to start. I’ve got you. We’re going to create a process to follow to ensure we can tune effectively and prove that we’ve made things faster. It’s all about having a structure when performance tuning.

Read on for Rich’s recommendations on how to identify poorly-performing code.

Comments closed

Dealing with Query Store in Error State

Published 2024-07-05 by Kevin Feasel

David Fowler turns it off then back on again, like a true IT professional:

I recently received a complaint that Query Store for a particular database was turned off, which was strange as that particular database has seen quite a few performance issues and I know that I’d ensured Query Store was enabled in the past.

No problem, I flicked the switch and Query Store was enabled again.

Half an hour or so later and I’m being told that Query Store is again disabled. What’s going on?

Read on to learn what to do if you get stuck with this problem.

Comments closed

Modifying Column Return Order in sp_QuickieStore

Published 2024-07-05 by Kevin Feasel

Josephine Bush demands order:

I love QuickieStore, but I wanted some columns to be at the front end of the results returned. Namely, I wanted top_waits, query_sql_text, and query_plan right after database name. This way I don’t have to scroll over to see those values.

Unfortunately, it would appear that there’s no advanced functionality for column ordering like we have for sp_whoisactive. But that didn’t deter Josephine, and you can grab a copy of an updated script that includes columns in this different arrangement.

Comments closed

Using Query Store to Fix a Cardinality Estimation Problem

Published 2024-06-07 by Kevin Feasel

Michael Bourgon solves an issue:

This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today.  It was SLOW. Like, 10 minutes slow. With the added bonus that it’s done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn’t call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick – query store hints (which used to be Plan Guides)

Click through for a list of actions Michael took.

Comments closed