Category: Query Store

Grant Fritchey explains a second-order effect of Query Store:

I may have occasionally talked about the importance of Query Store, but today I want to emphasize just how much Microsoft is weaponizing query store.

Of course, I don’t mean they’re creating the Death Star or something, I simply mean they’re taking the information that Query Store gathers and using that to enable a number of new performance enhancements within SQL Server.

Grant shows several examples of how SQL Server can take data in Query Store and use it to make queries in that database faster. It took a few versions of SQL Server but we’re getting to see some of the long-run promise of the feature, now that most of the bugs have been ironed out and the SQL Server development team has had enough time to implement this functionality.

Comments closed

Chad Callihan does not fall astray of Betteridge’s Law of Headlines:

Query Store is useful for forcing a beneficial plan. What if something changes that makes the forced plan impossible to use? Let’s look at an example of that today.

Read on for an example. The answer is, I believe, the best possible outcome given the circumstances—certainly better than a hard failure.

Comments closed

Joe Billingham roots out failure:

Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server.

But it does fail for various reasons and Joe has a list of them.

Comments closed

Erik Darling makes a query purchase at the Query Store:

I’ve said quite a bit about missing index request utility generally in SQL Server, even as recently as last week!

But then I got a user question about using Query Store to do something similar, so here goes.

If you need a pre-2019 way to do this with Query Store, Kendra Little has a blog post about that here.

For the 2019 version, check out Erik’s query and then sp_QuickieStore to make it easier.

Comments closed

Kendra Little searches for timeouts:

Here are some basics regarding query timeouts for SQL Server, Azure SQL Database, and Azure SQL Managed Instance:

– Execution timeouts are NOT set or enforced by the database engine. It is happy to wait for your query to run or for your transaction to be open for hours, days, or years, until you commit, cancel, or get disconnected.

– Timeouts are set by the calling application. If you don’t explicitly set a timeout on your connection string, there is generally a default timeout that is used. Often, this is 30 seconds for query execution timeouts.

– Connection timeouts are different than execution timeouts. If you see a connection timeout, often this is a firewall or network issue preventing you from connecting to the database – or perhaps it’s even offline. Execution timeouts are when the calling application doesn’t want to wait any more for a query to complete.

Kendra shows how to generate an execution timeout and how you can use Query Store to find them. And Kendra’s post got Mark Freeman to write a blog post so that’s also worth something.

Comments closed

Deepthi Goguri has a few Query Store notes for us:

Did you enable Query store or have a plan to enable this amazing feature on your databases? There are many points that need to be remembered for effectively using Query Store on your databases. I have gathered some of the points as I learn about the Query Store. Hope the below list will be informative to you as well.

Click through a dozen tips.

Comments closed