Press "Enter" to skip to content

Category: Query Store

Automated Alerts for Query Store Usage in Azure SQL DB

Jose Manuel Jurado Diaz doesn’t want to run out of disk space:

In this article, we will introduce a PowerShell script that helps monitor the usage of Query Data Store (QDS) in SQL Server databases. The script automatically sends an email alert when the storage space used by the Query Data Store reaches or exceeds 80% of its maximum allocated space. This can be particularly useful for database administrators to proactively manage and optimize their database storage.

Before we dive into the details, it’s essential to note that the script provided is a recommendation and should be adapted according to your specific environment and requirements. The responsibility of ensuring its compatibility and the impact on your system lies solely with the user.

Click through for the script. A quick glance of the code makes me think it will also work with on-premises SQL Server.

Comments closed

Finding a Particular Query Plan in Query Store’s UI

Andrea Allred does a search:

I have this problem where I want to see how a newly released query is performing, but it may not be bad enough to make any of the canned reports that SQL Server provides in QueryStore. I was able to look up the plan handle, but always struggled to get to the query id for QueryStore, until now.

Click through for a query to retrieve the query ID and then how to find data on that particular query. I’d also recommend QDSToolbox for more detailed query analysis.

Comments closed

Monitoring Query Store State Changes

Jose Manuel Jurado Diaz wants to know when the Query Store state changes:

This morning, I have been working on a support case where our client was not able to see certain queries when querying the Query Data Store. We have observed that the cause is due to the Query Data Store changing to a read-only mode due to the volume of data and the limitation our client had on the QDS database space. Therefore, I would like to share the following PowerShell script that can be executed at regular intervals to check and retrieve when the state of QDS has changed. Unfortunately, in Azure SQL Database, we cannot use the Extended Event ‘qds.query_store_db_diagnostics’.

Click through to see Jose’s alternative solution.

Comments closed

Query Hints: Ad Hoc vs Query Store

Grant Fritchey sets up a showdown:

I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there’s already a query hint on a query, what happens when you try to force a similar query hint?

Yeah, OK, that is a weird one. I don’t know the answer, but I’m about to find out.

Click through for a very interesting demo. To be honest, I expected the opposite result, so this was surprising.

Comments closed

Identifying Expensive Queries via Query Store

Matthew McGiffen takes a look at what’s slowing down that SQL Server instance:

Some time ago I wrote a query store version of the “Top 20 queries” query that will produce a ranked list of your most expensive queries – and I’ve ended up using this a lot.

The only downside of using the DMVs for Query Store is that they are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.

Click through for the script. This is where having a centralized Query Store, in which you regularly dump Query Store stats from various user databases into your own managed database, could be quite useful.

Comments closed

Finding All Implicit Warnings via Query Store

Jose Manuel Jurado Diaz has a script for us:

During our last session in SQL Data Saturday, we received a question about if it is possible to know all the conversion implicit captured by Query Data Store. In the following example, I would like to share with you an example how to capture this considering among of SQL Antipatterns. 

Basically, in sys.query_store_plan  we found the column called query_plan that contains the text of the execution plan. With this information plus other Query Data Store DMVs we could see the information required. 

Read on to see how you can shred out implicit conversions from the Query Store plans.

Comments closed

Bugs in SQL Server 2022 CU4

Brent Ozar facepalms:

<sigh> Okay, so, the last few Cumulative Updates have had known issues around broken remote queries using the generic ODBC connector and errors with contained availability groups, but I couldn’t really care less about those. If you use those features, I give you bombastic side eye anyway.

However, in the last few days, two more known issues have surfaced.

And those two issues are definitely bad. Click through to see what they are and what the workarounds are until fixes come in.

Comments closed

A Post-Migration Go-Live Plan for SQL Server 2022

Brent Ozar takes it slow:

You’re planning to migrate to SQL Server 2022, and you want your databases to be faster after the migration.

This is NOT a blog post about how to migrate – that’s the same as it’s been for a long time. Go build the new 2022 servers, and use log shipping or database mirroring to sync the old and new servers. (I’m not a fan of using Distributed Availability Groups to do version upgrades. You can, but it is a heck of a lot of moving parts to set up for a one-time migration.)

This is a blog post about what to do after you migrate:

Click through for five steps and what they entail.

Comments closed

Data Cleanup in Query Store

Grant Fritchey does some housekeeping:

The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:

Even with that limit, there are still several options available to you for when to clean up old Query Store data, whatever the definition of “old” (i.e., time-based or at the user’s whim).

Comments closed