Press "Enter" to skip to content

Category: Query Store

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

Why Query Store Switched to Read-Only Mode

Published 2024-06-05 by Kevin Feasel

Chad Callihan tells us:

How often do you evaluate your Query Store configuration? Have you ever had Query Store configured in READ_WRITE mode only to return later and find it in READ_ONLY mode instead? It may be simple enough to switch back to READ_WRITE and carry on with your day, but you would be wise to track down what happened to cause the switch to READ_ONLY.

Click through to learn how.

Comments closed

Granting Developers Query Store Access

Published 2024-06-04 by Kevin Feasel

Josephine Bush wants to allow developers to solve their own problems:

Let’s have devs look at their own query performance. Yes, please, sign me up for that! Sometimes, it’s hard for me to know the best course of action, especially when they are using Entity Framework, but it’s a great start for them to use Query Store to see how impactful their queries are. I’m happy to help them decipher results if they are confused, but I really like performance tuning being a team sport. I was giving them a list of queries with, for example, high CPU usage, but it was even better when they could go in there and use Query Store for themselves on a regular basis.

The actual granting of rights takes a couple lines of T-SQL, and Josephine also provides an overview of Query Store along the way. Erik Darling’s sp_QuickieStore plays a prominent role in this post and I agree that it’s extremely helpful. I’d also be remiss not bringing up QDS Toolbox as well, as it’s a rather good solution in its own right.

Comments closed

Azure SQL Database Watcher and Query Store

Published 2024-05-17 by Kevin Feasel

Kendra Little is happy:

I’ve spent a bit of time with Microsoft’s new database watcher tool for Azure SQL recently.

There are a lot of things I like about database watcher– which is currently in preview and which refuses to Capitalize Its Name– but it does one big thing that I really, really like: it collects data from Query Store. You can access that Query Store data from built-in database watcher dashboards, query it using KQL, or (something something) in Microsoft Fabric if you’ve got money to burn on your monitoring data.

Query Store has been available since SQL Server 2016, but I haven’t yet heard of monitoring tools that truly take advantage of it. It’s about time.

This is where I’d also plug QDS Toolbox for on-premises environments. A good amount of the reporting information comes out of Query Store and it helps manage Query Store to boot.

Comments closed

Query Store Size-Based Cleanup Performance Issues

Published 2024-04-24 by Kevin Feasel

Kendra Little has a public service announcement:

I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.

When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.

Read on for more information. I’d also like to plug qdstoolbox, an open-source solution some of my former colleagues worked on. This includes QDSCacheCleanup, which works considerably better than the built-in cleanup process.

Comments closed

Working with Query Store Hints

Published 2024-04-04 by Kevin Feasel

Etienne Lopes shares some thoughts around a new feature in SQL Server 2022:

Last December I got lucky since after upgrading from SQL Server 2012 to 2022, one of my clients had a process running in a particular database that went from around 10 minutes to around 10 hours!

You might be thinking: “It got 60 times worse, where’s the luck in that?”

Well, it presented me with a great opportunity to try a new feature in SQL Server 2022 called “Query Store Hints” (in a real production environment) and confirm the fabulous results that can be obtained by using it, with no effort at all. I thought it could be worth to share this experience and that’s what I’ll present in this post 

Click through for more details on the problem and solution.

Comments closed