A Fine Slice Of SQL Server
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 closedJosephine 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 closedI’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 closedKendra 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 closedEtienne 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 closedKendra Little diagnoses a problem:
Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server.
I say “halfway invisible” because I could see the queries while they were running in SQL Server’s dynamic management views using free tools (sp_WhoIsActive and sp_BlitzWho).
But the queries had some odd characteristics:
- They had a sql_handle, but never a query_hash
- They also never showed a plan_hash or execution plan
- I couldn’t find the timeouts in Query Store like a normal timeout
Through the power of communication with other humans (eew, that idea sounds icky), Kendra was able to learn what the problem was and how you can track such issues outside of Query Store.
Comments closedBut let’s say one day you have performance issues regardless of all the recompiling in the world. No matter what set of parameters get passed in, your procedure is just sleepy-dog slow.
Things get so bad that you hire a young, handsome consultant with reasonable rates like myself to help you figure out why.
The plan cache will be pretty useless for troubleshooting the recompile-hinted queries, but we can still use Query Store.
Read on for more detail and a demonstration.
Comments closedDeepthi 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 closed