A Fine Slice Of SQL Server
Erik Darling adds some commas:
Sometimes it’s hard to get people to understand the scale of a number without some commas.
I don’t know why that is, but that’s why I added the option to format numbers.
You can do that by running the procedure like so:
If only the client tools worked that easily.
Comments closedErik Darling continues a series on sp_QuickieStore:
What good is writing all this dynamic SQL and whatnot if you’re just going to firehose data at people?
None. None at all.
You’re going to want to answer questions about what happened and when, and you’re not going to want to struggle with it.
So what can you do?
Read on to learn what you can do.
Comments closedErik Darling has a new stored procedure. First up, an introduction:
If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.
Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.
I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.
With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.
Erik then shows off the results:
Under the more concise mode, you get one set of results back that combines metrics from query_store_runtime_stats, along with query text, query plan, and context settings. Some previews below. There’s a lot more in the actual results, but the screen caps would get very repetitive.
This looks really interesting, so go check it out.
Comments closedAndrea Allred has a primer on Query Store:
So if I can’t modify or add indexes and I can’t change code, how do I get my query times to drop? Query Store to the rescue.
I love to tune queries. I feel so satisfied to see the times dropping on my server as I tune things. Recently, I have been tracking my Batch Requests per Second and my Instance Waits to see if I am making improvements when I tune. It has been awesome!
What I am going to show you today is how I dig into my query store to find those misbehaving queries and make their performance better.
Click through for some high-level tips on how to use Query Store.
Comments closedGrant Fritchey has another use for Query Store:
There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That’s because of the new cardinality estimation engine introduced in 2014. Most queries won’t notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool.
Read on to learn how.
Comments closedAndrea Allred ran into a problem:
Greetings! Today I was playing with query store and noticed that I had some failing forced plans. How do you find failing forced plans? I asked this question over and over and finally found an answer.
Click through to see how you can find failing plans, as well as the proper way to fix them.
Comments closedMatthew McGiffen does some research:
When I was writing the script shared in my last post Identify the (Top 20) most expensive queries across your SQL Server using Query Store a question crossed my mind:
Query Store is a configuration that is enabled per database, and the plans and stats for queries executed in that database are stored in the database itself. So what does query store do when a query spans more than one database?
Read on for the answer.
Comments closedMatthew McGiffen has a query for us:
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 query.
Comments closedJess Pomfret contributes to dbatools:
Once I was happy with my settings, I realised we were missing a ‘test’ command for dbatools. The suite of ‘test’ functions in dbatools (a lot that end up as checks in dbachecks btw!), give us an easy way to check our environment against best practices, or our desired settings.
Since dbatools is open-source I was able to write this function (Test-DbaDbQueryStore) and get it added into the module. It’s included as of version 1.0.131, so make sure you’re up to date. Taking Erin’s suggestions and wrapping them in a little PowerShell, I can make it easier for myself and everyone else to make sure we’re following her guidelines.
Click through to see what those settings look like and how you can compare against current settings.
Comments closedErin Stellato updates a prior post:
I wrote the original Query Store performance overhead post just over two years ago, and just like the data in your database keeps changing, so does SQL Server. However, the question, “What is the performance overhead of enabling Query Store?” is still the most frequent question I am asked.
So why am I writing this post? Because there have been many improvements specific to Query Store that have taken the feature to the point where it can support all workloads, including those that are ad-hoc. This is a big deal.
Read on to learn more.
Comments closed