A Fine Slice Of SQL Server
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 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 closedGrant 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 closedMatthew McGiffen takes a hint:
Another neat little feature in SQL Server 2022 is Query Store Hints. This is the ability to apply a query hint through Query Store rather than having to modify existing code or fiddle around with plan guides.
Query hints are a way to influence optimizer behaviour towards generating desired execution plans for a given query. The word “hint” is a bit of a misnomer as usually they mandate what you wish to happen.
Right. They’re ‘hints’ in the way that my wife ‘hints’ that I should take out the garbage.
Comments closedMatthew McGiffen wants you to put yourr wallet and your best plans in the bag:
I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.
I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.
Click through to see what Microsoft came up with along these lines.
Comments closed