Search Results for: query store

Permissions Needed To Force Plans in Query Store

I was recently asked what permissions were needed to force plans in query store. I'm sure I knew at one point, but at the moment I was asked, I couldn't remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here's what I found. Permissions in Query Store Look through the blog, you'll find I'm pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy's work on that book, I just helped out). I haven't addressed security and Query Store. You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev…
Read More

Query Store on Azure SQL Database

Under the covers, Azure SQL Database is just good old fashioned SQL Server and this includes Query Store on Azure. While many things can be different when working with Azure, Query Store just isn't one of them. Let's talk about it a bit. Query Store on Azure Unlike your databases created on a SQL Server instance (big iron, VM, hosted VM, wherever), the databases you create on Azure SQL Database have Query Store enabled by default. Managed Instance and Synapse are different. In their case, they operate the same as an instance of SQL Server, off by default. Further, in the single database of Azure SQL Database, you can't, as in can not, disable Query Store. It's on by default and it's staying that way. This leads to a simple…
Read More

Query Store as an Upgrade Tool

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. The Steps We're going to…
Read More

Combining DMVs, Query Store and Extended Events Is Challenging

I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…
Read More

Find Indexes Used In Query Store

One of the most frequent questions you'll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead. I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one. What if we queried the information in Query Store? Indexes Used in Query Store Now Query Store itself doesn't store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and,…
Read More

Query Store, Plan Forcing, and DROP/CREATE

I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just recently found one that has quite a far reaching effect. Let's talk about what happens when you DROP and then CREATE a stored procedure. Query Store and Plan Forcing Let's quickly recap how Query Store works and how Plan Forcing works. First, Query Store is driven by query, not by procedure, not by batch, but by query. Second, plan forcing is also by query. However, there are queries and there are queries. Let's take this as an example: CREATE PROC dbo.AddressByCity @City NVARCHAR(30) AS BEGIN SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS…
Read More

Announcing a New Book: Query Store for SQL Server 2019

I'm pleased as punch to be able to share with you the fact that I helped Tracy Boggiano write a whole new book on Query Store. It will be available in October. You can pre-order it now. Tracy is a great author and a serious scholar of SQL Server. She's put together an excellent resource on Query Store. I added a couple of chapters just to help her out, but this is truly her book. Query Store is incredibly simple to use. It is however a bear to manage, so getting Tracy's book is a must if you're going to be implementing Query Store on your databases. By the way, you really should be implementing Query Store on your databases. Tracy covers all the topics from reporting with Query Store,…
Read More

Query Store, Plan Forcing and Table Variables

This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them. Don't panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What's spurring this blog post is that Steinar said he'd searched on the internet and no one had talked about the issue yet. So, let's talk about it. Plan Forcing With Table Variables First up, let's show…
Read More

Can You Force A Parallel Plan in Query Store?

I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven't a clue. The trick I think is going to be in setting up the test. Let's try it out. Reliably Getting a Parallel Plan Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I've decided to cheat. I'll take advantage of a little functionality that ensures I see a parallel plan when I want to. Here's my code: DBCC TRACEON(8649); GO SELECT soh.OrderDate, soh.ShipDate, sod.OrderQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID = 43705; GO DBCC TRACEOFF(8649); Traceflag 8649 will force all plans to go parallel by effectively making the Cost Threshold for…
Read More

Missing Indexes in the Query Store

I've shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store. Pulling from the Query Store The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here's the query modified for the Query Store: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT qsqt.query_sql_text, rts.plan_id, rts.NumExecutions, rts.MinDuration, rts.MaxDuration, rts.AvgDuration, rts.AvgReads, rts.AvgWrites, qsp.QueryPlan, qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName, qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName, qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact, ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage, ColumnGroupColumn.value('./@Name',…
Read More