Query Store Pre-Con at PASS Summit: More Details

In the time since my last post about my Query Store pre-con at the PASS Summit (it’s just about a month away!) I’ve gotten several more questions about the session.  I’ve consolidated them and provided answers here, and if anyone still wants to know more, please email me!

What version of SQL Server will you be running?

With the announcement on Monday from Ignite about SQL Server 2017, I can now say for certain that is the release I will be using.  I will call out differences between SQL Server 2016 and SQL Server 2017 as needed.  I don’t expect everyone who’s attending to be running 2016 or 2017, but I do expect that you’re in the planning stages for an upgrade, are going to start planning soon, or you’re looking for some solid reasons to share with your company about why you should upgrade.  I would argue that Query Store is a very good reason to upgrade, but that is definitely not the only one (check out the new features list for SQL Server 2016 and SQL Server 2017 – including enhancements to Query Store).

Will you have labs during your session?

No, but I will make all scripts available for attendees, and if you want to follow along during the day, you can get the scripts that morning.  I tweak demos right up until a session – it’s what I expect to be doing the Sunday before.  I’ll have all the scripts on a share that attendees can access the morning of the 30th.  I *love* the idea of labs during a pre-con, and I know that some other presenters do them.  Huge props, as it’s not easy to do.  In order to cover everything I want to cover, I don’t have enough time for in-depth labs that everyone can walk through from start to finish in the time allotted.  But again, feel bring to a laptop with SQL Server 2017 installed, along with the latest SSMS, and you can follow right along.

Are you giving anything away?

Besides knowledge?  😊  Yes, I will have codes for 30 days of free access to Pluralsight, which gives you access to over 50 courses from the SQLskills team, including my 3-hour course on Query Store.  If there’s something you miss during the pre-con, you can watch the Pluralsight course to pick it up, or further enhance what you learned.  I’m also working on a new course about the enhancements to Query Store in SQL Server 2017 and Automatic Tuning, which I’m also covering in my full day session.

Additional thoughts…

I’m also hosting a panel session at Summit and the organizer has emailed me to discuss how we handle questions during the session.  In the case where there are a large number of attendees, sometimes people hesitate to ask questions, and sometimes it can slow down the flow of the session.  While I am not setting records in terms of attendance, there are a lot of people that have signed up (which is fantastic) and I do want to make sure that everyone has a chance to ask their questions.  I’ve thought about using Twitter, and I know that Slack has been suggested as an option.  I’m still thinking about it.  If you’re signed up and have a preference, feel free to let me know in the comments!

That’s it for the moment, but please keep those questions coming.  Again, I want to make sure this is the right session for you to attend – there are a lot of great choices this year!  Either way, I look forward to seeing at Summit next month!

 

Changes to query text and the effects in Query Store

In this week’s Insider newsletter, Paul talked about an interesting customer issue I ran into last week related to Query Store, and an inadvertent change to a query’s text.  I thought I’d provide some demo code so that those of you using (or planning to use) Query Store could see how this shows up in your database.

Recreating the issue

I started by restoring a copy of the WideWorldImporters database, which you can download here.  I enabled Query Store and cleared out the data, just as a precaution.

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak' WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD,
REPLACE,
STATS = 5;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE CLEAR;
GO

I then created a stored procedure with just one query in it:

USE [WideWorldImporters];
GO

CREATE PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS

SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;
GO

Then I executed the stored procedure twice, with two different input parameters.  I purposely used WITH RECOMPILE here because I knew the values would generate different plans.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO

Within Object Explorer I then drilled into the Query Store reports (Databases | WideWorldImporters | Query Store) and double-clicked to run Top Resource Consuming Queries.  My query has two different plans:

Initial query execution, two plans

Initial query execution, two plans

 

 

 

 

 

 

 

 

 

Plan_id 1 has a clustered index scan with a filter, that feeds into the probe phase of a hash match (with a columnstore index scan for the build). This was generated with the input value of 90.

Plan 1 for query_id 1

Plan 1 for query_id 1

 

 

 

 

 

 

 

 

 

 

Plan_id 2 has the same columnstore index scan, but it feeds into a nested loop, with a clustered index seek for inner input, and was generated with the input value of 224.

Plan 2 for query_id 1

Plan 2 for query_id 1

 

 

 

 

 

 

 

 

 

 

Now let’s suppose I decide to force one plan for that query. After analysis, I decide to force the second plan (plan_id 2), which I can do either through the UI, or using a stored procedure. Since change control and documentation is a good thing, we’ll use the stored procedure:

EXEC sp_query_store_force_plan @query_id = 3, @plan_id = 8;
GO

At this point, if I re-run the stored procedure with the two different input parameters and WITH RECOMPILE, I will get the plan with the nested loop.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO
SP execution after plan is forced

SP execution after plan is forced

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now…let’s assume that this forced plan is used going forward and there are no issues. And then let’s assume that a developer decides to add some comments to the stored procedure, and they know they should use ALTER to keep the object_id the same…but somehow, a couple extra spaces end up in the query.

ALTER PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS
/*
This is getting information based on ID
*may need to add more columns per BL
*/
SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;

GO

I re-run the stored procedures again (WITH RECOMPILE) and now I get that other plan again for the input value of 90:

SP execution after ALTER, spaces inadvertenly added

SP execution after ALTER, spaces inadvertenly added

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If I look in Query Store, I can see I now have two queries that look nearly identical:

Two queries in QS after SP was changed

Two queries in QS after SP was changed

 

 

 

 

 

 

There are different query_id values in sys.query_store_query, as well as different entries for each query in sys.query_store_query_text. Both queries are still tied to the stored procedure (object_id 1483152329) which can be confusing if you don’t understand what occurred. In order to confirm which query has executed most recently, you can use the query below, which joins both system views (replace the query_ids as appropriate):

SELECT
[qst].[query_text_id],
[qst].[query_sql_text],
[qst].[statement_sql_handle],
[qs].[query_id],
[qs].[query_hash],
[qs].[last_execution_time],
[qs].[object_id]
FROM [sys].[query_store_query] [qs]
JOIN [sys].[query_store_query_text] [qst]
ON [qs].[query_text_id] = [qst].[query_text_id]
WHERE [qs].[query_id] IN (1, 14);
GO
Information for both queries from Query Store views

Information for both queries from Query Store views

 

 

 

 

Summary

The take home message here is that you need to be careful when changing objects (stored procedures, functions) where you have queries with forced plans. Inadvertent changes in spacing or casing create entirely separate queries in Query Store, which means that the original query with a forced plan is no longer in use.  You’ll either have to ALTER the procedure again without the extra spaces in the query (which may not be as easy as it sounds), or get the new version of the query to execute the “right” plan, and then force that plan.  It may be appropriate to set up monitoring for object changes, or teach your developers how to identify what queries are forced and to what objects they below (hint: use the Queries With Forced Plans report that’s available in SSMS 17.x). Hope this helps, and if you want to learn more about Query Store, consider my full day pre-con at Summit next month where we’ll cover this type of scenario and a whole lot more! Feel free to reach out if you have questions about it 🙂

p.s. did you see that they announced the release date for SQL Server 2017? I’ll be running that build at my pre-con!

Query Store Pre-Con at the PASS Summit: Is it right for you?

I received an email over the weekend asking about my pre-con at the PASS Summit, my general session at the Summit, and my Query Store course on Pluralsight. The individual wanted to know the requirements for the pre-con, and what overlap exists between these three. Great question.

First, feel free to review the abstracts for all:

PASS Summit Pre-Con, October 30, 2017 (Seattle, WA)
Solving Common Performance Problems Using Query Store

PASS Summit General Session, Date TBA (Seattle, WA)
Query Store and Automatic Tuning in SQL Server

Pluralsight, online
SQL Server: Introduction to Query Store

None of these courses require pre-existing knowledge of Query Store.

The Pluralsight course starts at the beginning and walks you through configuring and using Query Store in SQL Server 2016 in find performance issues both retroactively and proactively (3 hours total).

The general session at the Summit discusses Query Store at a high level (what it is and how to use it), and talks about the new Automatic Tuning feature in SQL Server 2017 (Automatic Plan Correction) as well as Automatic Index Management in Azure SQL Database (75 minutes).

My full day pre-con covers everything from Pluralsight and the general session, and a whole lot more. If you want:

  • all the gory details on what Query Store is, how to configure it, and what data exists
  • best practices
  • a slew of demos about how you can use it to find performance issues (out-of-the-box Query Store methods and custom options not documented elsewhere)
  • tons of scripts that you can take home and use immediately
  • details about the wait statistics data added to Query Store in SQL Server 2017
  • additional uses for Query Store (beyond what’s documented)
  • how to use Automatic Plan Correction in SQL Server 2017 (automatic and manual) and what Automatic Index Management looks like in Azure SQL Database
  • an overview of how to visualize Query Store data

then the pre-con is going to give you all that and whatever else I can find time to fit in.  It is a packed day and I am in the thick of pulling all the slides and demos together (so if there’s something you want to see/know that you don’t see mentioned here, feel free to email me!).

I hope this answers any questions you have about my pre-con at Summit and other Query Store sessions available, but if not, just contact me!

Have a great week, and to my friends in the south – the Stellato family is keeping you in our thoughts.