Performance tuning using query storage in SQL Server 2016

As a DBA, troubleshooting SQL Server is one of our responsibilities, and a lot of people each month bring us a variety of performance issues that can't be explained but solved.

I've heard many times that the performance problems of previous SQL Server are fine and within normal range, but now everything has changed and SQL Server is starting to suck, crazy things can't be explained. In this case I stepped in, analyzed the installation of the entire SQL Server, and finally found the root cause of the performance problem with some magical investigative methods.

But many times the root of the problem is the same: the so-called planned return (plan Regression), that is, the execution plan for a particular query has changed. Yesterday SQL Server has cached a good execution plan in the plan cache, and today it builds, caches, and finally reuses a bad execution plan-repeating.

After entering SQL Server 2016, I became a bit redundant, thinking that Microsoft had introduced the query store. This is the hottest feature of this version! Query storage help you easily find out if your performance problem is due to a planned regression. If you find a plan to return, it's easy to force a specific plan to not use the Plan Wizard. Sounds interesting? Let's show you how to use the query store to find and eventually fix the planned regression in SQL Server 2016 with a specific scenario.

Query Store-my opponent

In SQL Server 2016, you enable it for this database before you use the query storage feature. This is implemented by the ALTER DATABASE statement, as you can see in the following code:

1 CREATE DATABASEQuerystoredemo2 GO3 4  UseQuerystoredemo5 GO6 7 --Enable the Query Store for our database8 ALTER DATABASEQuerystoredemo9 SETQuery_store=  onTen GO One  A --Configure the Query Store - ALTER DATABASEQuerystoredemoSETQuery_store - ( theOperation_mode=Read_write, -Cleanup_policy=(stale_query_threshold_days= 367),  -Data_flush_interval_seconds=  the,  -Interval_length_minutes= 1,  +Max_storage_size_mb=  -,  -Query_capture_mode=  All,  +Size_based_cleanup_mode= OFF A ) at GO

The online Help provides you with detailed information about each option. Next I create a simple table, create a nonclustered index, and finally insert 80,000 records.

1 --Create a new table2 CREATE TABLECustomers3 (4CustomerIDINT  not NULL PRIMARY KEY CLUSTERED,5CustomerNameCHAR(Ten) not NULL,6CustomerAddressCHAR(Ten) not NULL,7CommentsCHAR(5) not NULL,8ValueINT  not NULL9 )Ten GO One  A --Create a supporting new non-clustered Index. - CREATE UNIQUE nonclustered INDEXIdx_test onCustomers (Value) - GO the  - --Insert 80000 Records - DECLARE @i INT = 1 -  while(@i <= 80000) + BEGIN -     INSERT  intoCustomersVALUES +     ( A         @i, at         CAST(@i  as CHAR(Ten)), -         CAST(@i  as CHAR(Ten)), -         CAST(@i  as CHAR(5)), -         @i -     ) -      in     SET @i += 1 - END to GO

In order to access our table, I created a simple stored procedure that passed in value as a filter predicate.

1 --Create A simple stored procedure to retrieve the data2 CREATE PROCEDUREretrievecustomers3 (4     @Value INT5 )6  as7 BEGIN8     SELECT *  fromCustomers9     WHEREValue< @ValueTen END One GO

Now I use the 80000 parameter value to execute the stored procedure.

1 -- Execute the stored procedure. 2 -- This generates the execution plan with a Key Lookup (Clustered). 3 EXEC 80000 4 GO

Now when you look at the actual execution plan, you will see that the query optimizer has selected a clustered index scan operator with 419 logical reads. SQL Server does not use nonclustered indexes because this does not make sense because of the critical point. This query result is not selective.

Now suppose something happens to SQL Server (for example, reboot, failover), SQL Server ignores the plan that has been cached, and here I simulate SQL by executing DBCC freeproccache to erase each cached plan from the plan cache Server restart (do not use in a production environment!) ）。

1 -- Get rid of the cached execution plan ... 2 DBCC Freeproccache 3 GO

Now someone calls your stored procedure again, this time the input parameter value is 1. This execution plan is different because you will now have a bookmark search in the execution plan. The SQL Server estimated number of rows was 1 and no rows were found in the nonclustered index. Therefore, a bookmark lookup combined with a nonclustered index lookup makes sense because the query is selective.

Now I'll execute the query with the 80000 parameter value.

1 --Execute the stored procedure2 EXECRetrievecustomers13 GO4 5 --Execute the stored procedure again6 --This introduces now a plan regression, because now we get a Clustered Index Scan7 --instead of the Key Lookup (Clustered).8 EXECRetrievecustomers800009 GO

When you look at the output of the STATISTICS IO again, you will see that the query now produces 160,139 logical reads-just 419 logical reads from the query. This time the DBA's cell phone will be ringing, performance issues. But today we're going to do it in a different way-using the query store that you just enabled.

When you look at the actual execution plan again, in front of you you will see that there is a plan to return, because SQL Server has just reused the bookmark lookup for the plan cache. Just now you have the execution plan for the clustered index scan operator. This is a side effect of parameter sniffing in SQL Server.

Let's look at the problem in more detail by querying the store. In SSMs Object Explorer, SQL Server 2016 provides a new node called the query store, where you'll see some reports.

The first few resource usage queries show you the most expensive queries, based on the dimensions you choose. Switch to "logical read Count" here.

Here are some queries in front of you. The most expensive queries generate nearly 500,000 logical reads. This is our initial statement. This is already the first WOW effect of the query store: After the SQL Server restart, the query stored data is still there! The 2nd one is the SELECT statement in your stored procedure. Each captured query in the query store has an indicator number--here is 7. Finally, when you look at the right side of the report, you will see the different execution plans for this query.

As you can see, the query store captures 2 different execution plans, an ID of 7 and an ID of 8. When you click on the plan ID, SQL Server displays the estimated execution plan for you at the bottom of the report.

Schedule 8 is a clustered index scan, and plan 7 is a bookmark lookup. As you can see, using the Query storage analysis plan regression is very simple. But you're not finished yet. You can now enforce the plan for the specified query. Now you know that the execution plan that contains the clustered index scan has better performance. So now you can force query 7 to use the execution plan by clicking on "Enforce Plan".

We're done, we've solved the problem!

Now when you execute a stored procedure (with 80000 input parameter values), you can see the clustered index scan in the execution plan, and the execution plan generates only 419 logical reads--very simple, isn't it? Definitely not!!!!

Microsoft tells us to fix only "new ways"that are related to SQL Server performance. You just force a specific plan, everything is fine. There is a big problem with this approach, because the root cause of the performance problem is not solved! The key to this problem is that there is no stability for the bookmark lookup program. The execution plan is therefore constantly reused, depending on the default input values for the first execution plan.

Usually I would recommend adjusting your index design to create an overlay index to ensure the stability of the plan. But forcing a specific execution plan is just a temporary fix-you still have to fix the root cause of your problem.

Summary

Don't get me wrong: query storage in SQL Server 2016 is a great feature that will help you understand planned regression more easily. It will also help you "temporarily" enforce a specific execution plan. But the goal of performance tuning is the same: you need to find the source of the problem, try to solve the problem--don't hang out!

Thanks for your attention!

Reference article: http://www.sqlpassion.at/archive/2016/01/18/performance-troubleshooting-with-the-query-store-in-sql-server-2016

Performance tuning using query storage in SQL Server 2016