SQL Server 2016 query Storage performance Optimization Summary _mssql

As a DBA, it is one of our responsibilities to exclude SQL Server issues, and many people each month bring us a variety of performance problems that we cannot explain.

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

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 had cached a good execution plan in the plan cache, and today the build, cache finally reused a bad execution plan--repeated.

After entering SQL Server 2016, I became a bit redundant, thinking that Microsoft had introduced query storage. This is the most popular feature of this version! Query storage helps you easily find out if your performance problem is not due to planned regression. If you find a plan to return, it's easy to force a specific schedule without using the Schedule wizard. Sounds interesting? Let's take a specific scenario and show you how to use query storage in SQL Server 2016 to find and eventually revise the regression of the plan.

Query store--my opponent

In SQL Server 2016, you have to enable this database before you use the query storage function. This is implemented through the ALTER DATABASE statement, as you can see in the following code:

CREATE DATABASE Querystoredemo go with Querystoredemo go-

Enable the

Query Store
for our database ALTER DATABASE Querystoredemo
SET query_store = on
go

-Configure the QUERY STORE
ALTER Database Queryst Oredemo SET query_store
(
 operation_mode = read_write, 
 cleanup_policy = (Stale_query_threshold_days = 367), 
 data_flush_interval_seconds = 900, 
 interval_length_minutes = 1, 
 max_storage_size_mb = 100, 
 Query_capture_mode = all, 
 Size_based_cleanup_mode = off
)
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.

--Create a new table
create TABLE Customers
(
 CustomerID INT not NULL PRIMARY KEY CLUSTERED,
 CustomerName char (a) not NULL, CustomerAddress char (a) not
 null,
 Comments char (5) is not NULL,
 Value INT is not null
) 
   
    go

--Create a supporting new non-clustered Index.
CREATE UNIQUE nonclustered INDEX idx_test on Customers (Value)
go

--Insert 80000 records DECLARE
INT = 1 While
(@i <= 80000)
BEGIN
 INSERT into Customers VALUES
 (
  @i,
  cast (@i as Char),
  cast (@i as Char),
  CAST (@i as CHAR (5)),
  @i
 )
 
 SET @i = 1
end Go
   

To access our table, I created a simple stored procedure that passed the value as a filter predicate.

--Create a simple stored procedure to retrieve the data
Create procedure retrievecustomers
(
 @Value int
   )
as
BEGIN
 SELECT * Customers
 WHERE Value < @Value
end Go

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

--Execute the stored procedure.
 --This generates is execution plan with a Key Lookup (Clustered).
 EXEC retrievecustomers 80000 Go
 

Now when you look at the actual execution plan, you will see that the query optimizer has selected 419 logically read clustered index scan operators. SQL Server does not use nonclustered indexes because it makes no sense because of the critical point. This query result is not selective.

Now suppose that something happens to SQL Server (for example, reboot, failover), SQL Server ignores the cached plan, and here I simulate SQL by wiping out every cache plan from the plan cache by executing DBCC FREEPROCCACHE Server reboot (do not use in production environment!) ）。

 --get rid of the cached execution
 ... DBCC Freeproccache Go
 

Now someone calls your stored procedure again, this time the input parameter value is 1. The execution plan is different, because now you'll find bookmarks in the implementation plan. SQL Server estimates that the number of rows is 1, and no rows are found in the nonclustered index. Therefore, a bookmark lookup combined with a nonclustered index makes sense, because the query is selective.

Now I'm going to execute the query with the 80000 parameter value.

--Execute the stored procedure
EXEC retrievecustomers 1 Go
--Execute the

stored procedure
again--this Introduces now is regression, because now we get a Clustered Index Scan
-instead of the Key Lookup (Clustered). C6/>exec retrievecustomers 80000 Go

When you look at the output of statistics IO again, you'll see that the query now produces 160,139 logical reads--just 419 logical readings from the query. This time the DBA's phone will ring, performance problems. 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 a planned regression because SQL Server has just reused the plan cache for bookmark lookups. Just now you have the execution plan for the clustered index scan operator. This is the side effect of parameter sniffing in SQL Server.

Let's go through the query store to learn more about this problem. In the Object Explorer in SSMs, SQL Server 2016 provides a new node called query Storage, where you'll see some reports.

The first few resource use queries show you the most expensive queries, based on the dimensions you choose. Here, switch to logical read count.

Here are some inquiries 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 query storage: After SQL Server restarts, query stored data still exists! The 2nd one is the SELECT statement in your stored procedure. Each captured query in the query store has an indicator number--this 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, one with an ID of 7 and one with an ID of 8. When you click on the plan ID, SQL Server shows you the estimated execution plan at the bottom of the report.

Plan 8 is a clustered index scan, and plan 7 is a bookmark lookup. As you can see, using Query Storage analysis Plan regression is very simple. But you're not done 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 "Force execution plan".

Come on, we've solved the problem!

Now when you execute the 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--quite simply, isn't it? Definitely not!!!!

Microsoft has told us only "new ways" to fix SQL Server performance. You're just forcing a specific plan, everything's fine. This approach has a big problem, because the root of the performance problem is not resolved! The key to this problem is that the bookmark lookup program has no stability. The execution plan is continuously reused, depending on the default input value for the first execution plan.

I usually recommend that you adjust your index design to create a coverage index to keep the plan stable. But enforcing a specific execution plan is a temporary solution-you still have to fix the root of your problem.

Summary

Don't get me wrong: The query storage in SQL Server 2016 is great, and it's easier to understand the return of the plan. It will also help you to "temporarily" enforce specific execution plans. But the goal of performance tuning is the same: you have to find the root of the problem, try to solve the problem-don't hang out!