A Fine Slice Of SQL Server
Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.
Read on for info on the changes.
Comments closedMilos Radivojevic shows an example where plan forcing might not work:
In my previous post about Query Store, you saw that persistence of forced plans does not imply that they are used. This post brings another example where forced plans are still in the system, with no failures, but they are not applied against a desired query.
For this demo, we need a database which is a part of the availability group.
Click through to see why failover might cause your forced queries no longer to work.
Comments closedMilos Radivojevic notes a peculiarity with Query Store:
Be very careful with forcing plans against queries referencing table variables and table valued parameters. The query_id attribute for such queries is very instable and forcing plans even as a workaround could have very short life time. You should use different approach to fix such issues.
Read the whole thing to understand why this is important.
Comments closedErin Stellato had a lot of questions about Query Store, and the answers turned into a blog post:
5. If you have 3+ plans how does SQL Server decide which plan to use?
A: I assume this is specific to the Automatic Plan Correction feature, and if so, it will force the last good plan (most recent plan that performed better than the current plan). More details in my Automatic Plan Correction in SQL Server post.
6. What equivalent options we have for lower versions?
A: There is an open-source tool called Open Query Store for versions prior to SQL Server 2016.
Click through for all 19 of the questions.
Comments closedErin Stellato shows how you can mine the Query Store tables to learn more about your workload:
The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively. The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max). This means that the data can be mined, looking for patterns and explicit use of objects. Want to know what queries use an index? Look for it in the plans. Want to know what queries have a RECOMPILE hint on them? Look for it in the query text. Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.
It’s hard to tune queries if you don’t know what’s running.
Comments closedErin Stellato continues a series on why you should be interested in Query Store:
Before you go too crazy trying to stabilize performance with hundreds of forced plans, here are a couple things worth mentioning about plan forcing.
1. I think of plan forcing as a temporary solution. I do not view it as a “set it and forget it” option.
2. Use T-SQL for forcing and un-forcing (versus the UI) so you can track when it was done, and by whom, in change control. Nothing is entered in the ERRORLOG, or anywhere else, and there is currently no event that captures it.
3. Evaluate both the execution statistics (average and total) and the different plans for the query to determine the “best” plan to force.
Erin makes some great points in this post, so read the whole thing.
Comments closedErin Stellato has started a series on the benefits of Query Store:
The Query Store feature previewed in Azure SQL Database in summer 2015, was made generally available that fall, and was part of the SQL Server 2016 release the following summer. Over the past four years (has it really been that long?!) I have devoted significant time to learning Query Store – not just understanding how it works from the bottom up, but also why it works the way it does. I’ve also spent a lot of time sharing that information and helping customers understand and implement the feature, and then working with them to use the query store data to troubleshoot issues and stabilize performance. During this time I have developed a deep appreciation for the Query Store feature, as its capabilities go far beyond its original marketing. Is it perfect? No. But it’s a feature that Microsoft continues to invest in, and in this series of blog posts my aim is to help you understand why Query Store is a tool you need to leverage in your environment.
Read on for a high-level overview of how Query Store is useful.
Comments closedMatthew McGiffen wants to find the queries which demand the largest memory grants:
I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be able to see what had happened over the last 24 hours.
Enter Query Store. In the run-time stats captured by Query Store are included details relating to memory.
Click through for a script which retrieves this data over a time frame.
Comments closedAndreas Wolter takes us through exactly which permissions are required for Query Store to work:
Typically, there are 3 aspects to the work with the Query Store, which can be reflected in roles:
1) Configuration – turning Query Store on and off, clearing the contents, flushing its contents to disk and changing its settings.
2) Viewing the reports or using the DMVs to analyze the Query Store contents (queries, plans and wait statistics) to gain insights but not necessarily having the authority to change anything
3) Actively change Plans by forcing or un-forcing, based on the information obtained from (2)
This is a nice overview of the problem and a fair amount of the solution.
Comments closedArun Sirpal wants to change the report interval for a Query Store report:
While not specific to SQL Server 2019 (I was using this version to do some testing) I was struggling to find how to change the time period of analysis for the Query Store reports within SSMS.
This is not a ground breaking post but hopefully a helpful one! So, I load up the “Top 25 resource consumers” report and by default it will show data for the past hour. So what do you do, or should I say what do you click to change the time interval for the report?
Read on for the two screenshots which answer this question for you.
Comments closed