Category: Query Store

If I look at the plan that is stored in Query Store, I’ll see the identical plan up above, including the PlanGuideDB and PlanGuideName properties.

So, let’s force the plan using the values returned from the query above:

1

EXEC sys.sp_query_store_force_plan 6,7;

Now, when we run the query, we’ll see both the plan guide in use and that the plan is forced (see this earlier blog post explaining this behavior). This is all expected behavior.

Check it out to see how SQL Server behaves.

Comments closed

Query Store & Spinlocks

Published 2018-11-21 by Kevin Feasel

David Fowler ran into an issue with the Query Store process experiencing waits due to spinlocks:

We were enjoying a nice peaceful afternoon when we hear panicked shouting that a SQL Server had become unresponsive and the customers were unable to do anything.

We moseyed on down to the server in question to take a look at it. One thing stood out immediately, CPU was pegged out at 100% but SQL itself didn’t actually seem to be doing anything, transactions\second was on the floor. Unfortunately this happened a while back and I didn’t think to capture any graphs or metrics at the time so you’re just going to have to take my word for this.

The issue David ran into was subsequently fixed, making this a cautionary tale to keep those SQL Server instances patched.

Comments closed

Minor Differences Between Plan Cache And Query Store Plans

Published 2018-11-20 by Kevin Feasel

Grant Fritchey shows us some minor differences between what the Query Store shows for a particular execution plan versus what exists in the plan cache:

As you can see, while the structure of the plans are identical, not everything is. The Compile values are different (although sometimes, they’ll be the same, that one is kind of luck of the draw to a degree) because they were compiled at different times with varying load on the system, so certainly that will be reflected. However, the other differences are also interesting. Which of the plans was retrieved from cache for example and, more importantly, the statement for the plans. The one on the left is the plan from the Query Store. It was not retrieved from cache and, the statement is for the query, not the stored procedure. Meanwhile, the plan on the right is from cache and, it’s based on the plan handle from the stored procedure, so it reflects that in the Statement value.

Click through for the full set of differences as well as Grant’s explanation.

Comments closed

Using Query Store To Diagnose Implicit Conversion Issues

Published 2018-11-16 by Kevin Feasel

Tom Norman shares a case study of using Query Store to fix a nasty implicit conversion problem:

A while ago, we contracted with a third party to start using their software and database with our product. We put the database in Azure but within a year, the database grew to over 250 gigs and we had to keep raising the Azure SQL Database to handle performance issues. Due to the cost of Azure, a decision was made to bring the database back on-premise. Before putting the database on the on-premise SQL Server, the server was running with eight CPUs. In production, we are running SQL Server 2016 Enterprise Edition. When we put the vendor database into production, we had to dramatically increase our CPUs in production, ending up with twenty-eight CPUs. Even with twenty-eight CPUs, during most of the production day, CPUs were running persistently at seventy-five percent. But why?

Tom takes us from symptom (high CPU utilization) to diagnosis and is able to provide the third-party vendor enough information to improve their product.

Comments closed

The Performance Impacts Of Query Store

Published 2018-11-07 by Kevin Feasel

Erin Stellato explains the performance impacts of enabling Query Store in various types of environments:

The short answer:

The majority of workloads won’t see an impact on system performance
- Will there be an increase in resource use (CPU, memory)? Yes.
- Is there a “magic number” to use to figure out Query Store performance and the increase in resource use? No, it will depend on the type of workload. Keep reading.
An impact on system performance can be seen with ad-hoc workloads (think Entity Framework, NHibernate), but I still think it’s worth enabling. With an ad-hoc workload there are additional factors to consider when using Query Store.
You should be running the latest version CU for SQL Server 2017 and latest CU for SQL Server 2016 SP2 to get all performance-related improvements Microsoft has implemented specific to Query Store

Definitely read the long answer. There are also settings to reduce the load that Query Store puts on a system, and being up to date is critical.

Comments closed

When Query Store Skips An Interval Period

Published 2018-10-17 by Kevin Feasel

Rich Brenner explains why you might see gaps in your Query Store run times:

When messing about (developing) things that are based on the query store I came across an unusual phenomenon. We were missing query store intervals. It’s not that data had been deleted but rather than it was never created in the first place. Take a look at this;

I’d set the query store to 5 minute intervals for testing purposes and left it. Notice that there is no data between 15:20 and 15:25. It’s not that the data has been deleted, we know that because the runtime_stats_interval_id is still creating increments correctly.

Click through for the answer.

Comments closed

Forcing A Plan Is A Temporary Solution

Published 2018-10-04 by Kevin Feasel

Erin Stellato explains when she forces plans—and that this is not a permanent solution to a performance problem:

Whether you force plans manually, or let SQL Server force them with the Automatic Plan Correction feature, I still view plan forcing as a temporary solution. I don’t expect you to have plans forced for years, let alone months. The life of a forced plan will, of course, depend on how quickly code and schema changes are ported to production. If you go the “set it and forget it route”, theoretically a manually forced plan could get used for a very long time. In that scenario, it’s your responsibility to periodically check to ensure that plan is still the “best” one for the query. I would be checking every couple weeks; once a month at most. Whether or not the plan remains optimal depends on the tables involved in the query, the data in the tables, how that data changes (if it changes), other schema changes that may be introduced, and more.

Further, you don’t want to ignore forced plans because there are cases where a forced plan won’t be used (you can use Extended Events to monitor this). When you force a plan manually, forcing can still fail. For example, if the forced plan uses an index and the index is dropped, or its definition is changed to the point where it cannot be used in plan in the same manner, then forcing will fail. Important note: if forcing fails, the query will go through normal optimization and compilation and it will execute; SQL Server does not want your query to fail! If you’re forcing plans and not familiar with the reasons that it can fail, note the last_force_failure_reason values listed for sys.query_store_plan. If you have manually forced a plan for a query, and the force plan fails, it remains forced. You have to manually un-force it to stop SQL Server from trying to use that plan. As you can see, there are multiple factors related to plan forcing, which is why you don’t just force a plan and forget it.

There is much sound advice in this post.

Comments closed

Backing Up Query Store Data

Published 2018-09-18 by Kevin Feasel

Grant Fritchey explains that Query Store data gets backed up like regular data, but with a caveat:

The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you backup the database, you’re backing up Query Store data. When you backup the logs, you’re also backing up Query Store data. A point in time will include all the data written to the Query Store at that point.

However, that’s the kicker. At what point was the Query Store information written to disk?

Read on to learn when, and what you can do about it if you prefer otherwise.

Comments closed

When Query Store Alterations Are Blocked

Published 2018-08-31 by Kevin Feasel

Erin Stellato gives us some helpful tips on Query Store:

If you are trying to execute an ALTER DATABASE command to change a Query Store option (e.g. turn it off, change a setting) and it is blocked, take note of the blocking session_id and what that session_id is executing. If you are trying to execute this ALTER command right after a failover or restart, you are probably blocked by the Query Store data loading.

As a reminder, when a database with Query Store enabled starts up, it loads data from the Query Store internal tables into memory (this is an optimization to make specific capabilities of Query Store complete quickly). In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load. I have seen this take over 30 minutes to load for a very large Query Store (over 50GB in size).

Erin has a story which ties this together, so check that out.

Comments closed

Mining The Plan Cache, Query Store, And More

Published 2018-08-20 by Kevin Feasel

Erin Stellato shows the benefit of digging through the plan cache, Query Store, and third-party performance tool databases (using SentryOne’s SQL Sentry as an example):

As much as I love all this extra data, it’s important to note that some information is more relevant for an actual execution plan, versus an estimated one (e.g. tempdb spill information). Some days we can capture and use the actual plan for troubleshooting, other times we have to use the estimated plan. Very often we get that estimated plan – the plan that has been used for problematic executions potentially – from SQL Server’s plan cache. And pulling individual plans is appropriate when tuning a specific query or set or queries. But what about when you want ideas on where to focus your tuning efforts in terms of patterns?

The SQL Server plan cache is a prodigious source of information when it comes to performance tuning, and I don’t simply mean troubleshooting and trying to understand what’s been running in a system. In this case, I’m talking about mining information from the plans themselves, which are found in sys.dm_exec_query_plan, stored as XML in the query_plan column.

When you combine this data with information from sys.dm_exec_sql_text (so you can easily view the text of the query) and sys.dm_exec_query_stats (execution statistics), you can suddenly start to look for not just those queries that are the heavy hitters or execute most frequently, but those plans that contain a particular join type, or index scan, or those that have the highest cost. This is commonly referred to as mining the plan cache, and there are several blog posts that talk about how to do this. My colleague, Jonathan Kehayias, says he hates to write XML yet he has several posts with queries for mining the plan cache:

It’s a good article with a lot of useful information.

Comments closed