A Fine Slice Of SQL Server
Deepthi Goguri fills us in on where Query Store has gone as of SQL Server 2022:
In this post, you are going to learn about the advancements of the Query Store released in SQL Server 2022. This is the first blog post for this series. You can expect more posts in the coming weeks related to the Query store usage in the Intelligent Query processing features released in SQL Server 2022.
Click through for that overview.
Comments closedGrant Fritchey answers a question:
A great question came up over at DBA.StackExchange regarding the query store reports time intervals:
How can SQL Server’s Query Store Reports show data for minute-length intervals, when “Statistics Collection Interval” is set to 1 hour?
Read on for the answer to this question.
Comments closedJose 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 closedI 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 closedJose 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 closedGrant 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 closedMatthew 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 closedJose 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<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 closedYou’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