Have you ever had that moment when you realize that you’re dealing with bad parameter sniffing and you wish, “Oh, if only I could just quickly get another execution plan in place?” Yeah, you can use DBCC FREEPROCCACHE and pass it a plan handle to remove that plan from cache. You can quickly run the query with a good parameter so that you get a good plan. What if you could just substitute the plan for one that was working well in the first place? That exact scenario, is one of the many uses of the Query Store.
If you’re on the earthed version of SQL Server, you can’t even start using Query Store in production yet, unless you’re part of the TAP for SQL Server 2016. However, if you’re working in Azure SQL Database v12, there is a Preview of the functionality available right now. Access to Query Store was announced on May 28th. This is one piece of functionality that I’m very excited to check out.
First things first, there’s not a GUI available. Unless you’re running the SQL Server 2016 CTP2 release of SQL Server Management Studio, in which case a new GUI has been built. I’m not going to use that and will instead focus on using T-SQL and the Dynamic Management Objects and other commands that are immediately available to you within Azure.
First off, you have to turn on the Query Store on your database. Initially, I tried this command:
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
If you check the documentation for ALTER DATABASE, you’ll find that CURRENT is only supported in the earthed version of SQL Server, 2012 through 2016 (when you start working within Azure, get used to checking syntax occasionally, while most things are supported, the odd command is not). Altering the statement to this works just fine:
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON;
With that, I now have enabled the Query Store on my database. Nothing else to it really.
To see the Query Store in action, I have a very simple procedure that I use to illustrate bad parameter sniffing:
CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS
SELECT a.AddressID,
a.AddressLine1,
a.AddressLine2,
a.City,
sp.Name AS StateProvinceName,
a.PostalCode
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE a.City = @City;
If I execute this query and pass it the parameter value of ‘London,’ I’ll get one execution plan. If I pass it the value of ‘Mentor,’ I’ll get a different plan. This is caused by skew of data within AdventureWorks2014 and illustrates parameter sniffing perfectly. If I execute the procedure and then run this query, I can see the execution plan that’s created as well as metrics about my procedure:
SELECT OBJECT_NAME(qsq.object_id),
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS xmlplan,
qsp.is_online_index_plan,
qsrs.avg_duration,
qsrs.avg_cpu_time,
qsrs.avg_logical_io_reads
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE qsq.object_id > 0;
The WHERE clause is just filtering for any queries in my system that came from a stored procedure. The query_plan data type is nvarchar(max), so in order to make it easier to access the plan when I run this query from Management Studio I’m casting it as XML. There is a lot of additional information in the query_store_runtime_stats such as the last duration, the min and max duration, reads, writes, cpu, etc. It’s an excellent collection of information about your query. My example above just scratches the surface.
From this query, it looks like you’re just seeing a different way to look at information not that different from what’s available in sys.dm_exec_procedure_stats. To show the difference, I’m going to rerun my procedure and use the WITH RECOMPILE hint and a different parameter value so that I arrive at a different execution plan. Rerunning the query above, you now see multiple rows, representing the different execution plans. You can also see that I’ve been experimenting and have multiple executions of the different plans:
If you were to query the plan cache, you’d only see one of these plans. But both are available within the Query Store. Having the different plans available means that you can make a choice to choose to implement one of those plans if you prefer it. This process is called forcing the plan. It’s fairly simple. You have to get the query_id value from the Query Store. It’s in the sys.query_store_query view. You also have to retrieve the plan_id from sys.query_store_plan. A simple query to pull these out would look like this:
SELECT qsq.query_id,
qsp.plan_id
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
WHERE qsq.object_id = OBJECT_ID('Adventureworks2014.dbo.spAddressByCity');
With the id values needed, you can then choose to force the plan using a function:
EXEC sys.sp_query_store_force_plan
@query_id = 4,
@pland_id = 7;
When the query is called now, I’ll always get the same plan. This is a pretty quick and easy way to fix bad parameter sniffing. Being able to force plan choice is going to be applicable in other situations as well.
You can easily remove the plan from forcing. There are also a number of other views and functions for monitoring and controlling the Query Store. They’re detailed in a good overview article on MSDN.
There are a number of options you have available to manipulate how Query Store works on your system. That information is also available in the article. One of the views in particular should be something that you’re familiar with immediately. Sys.database_query_store_options will list the current settings on any of your databases that have Query Store enabled. I strongly recommend checking this out early so that you have a more thorough understanding of what information is being collected by the Query Store and how it’s being maintained.
The ability to track how a query is behaving over time through the statistics maintained in the Query Store means you can identify when a query starts to behave poorly. The Query Store exposes a number of new options for monitoring your systems and troubleshooting poor performance on your queries within Azure SQL Database. I'm extremely excited about the possibilities opened up by this new functionality.
