SQL Server 2016 query storage performance optimization summary, 2016 Performance Optimization

SQL Server 2016 query storage performance optimization summary, 2016 Performance Optimization

As a DBA, troubleshooting SQL Server problems is one of our responsibilities. Every month, many people bring us various performance problems that cannot be explained but need to be solved.

I have heard many times that the performance problems of SQL Server have been okay and within the normal range, but now everything has changed. SQL Server is getting worse, and crazy things cannot be explained. In this case, I intervene, analyze the installation of the entire SQL Server, and finally use some magical investigation methods to find out the root cause of the performance problem.

But in many cases, the root cause of the problem is the same: the so-called Plan Regression, that is, the execution Plan of a specific query has changed. Yesterday, SQL Server has cached a good execution plan in the Plan cache. Today, a bad execution plan is generated, and the cache is finally reused-repeated.

After entering SQL Server 2016, I became a little redundant and thought that Microsoft introduced Query Store ). This is the most popular feature in this version! Query storage helps you easily find out if your performance problems are caused by planned regression. If you find planned regression, it is easy to force a specific plan not to use the Planning Wizard. It sounds interesting? Let's use a specific scenario to show you how to use the query storage in SQL Server 2016 to find out and finally correct the regression plan.

Query Store-my opponent

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

CREATE DATABASE QueryStoreDemoGOUSE QueryStoreDemoGO-- Enable the Query Store for our databaseALTER DATABASE QueryStoreDemoSET QUERY_STORE = ONGO-- Configure the Query StoreALTER DATABASE QueryStoreDemo 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

Online Help provides you with detailed information about each option. Next, I will create a simple table, create a non-clustered index, and insert 80000 records.

-- Create a new tableCREATE TABLE Customers( CustomerID INT NOT NULL PRIMARY KEY CLUSTERED, CustomerName CHAR(10) NOT NULL, CustomerAddress CHAR(10) NOT NULL, Comments CHAR(5) NOT NULL, Value INT NOT NULL)GO-- Create a supporting new Non-Clustered Index.CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value)GO-- Insert 80000 recordsDECLARE @i INT = 1WHILE (@i <= 80000)BEGIN INSERT INTO Customers VALUES (  @i,  CAST(@i AS CHAR(10)),  CAST(@i AS CHAR(10)),  CAST(@i AS CHAR(5)),  @i )  SET @i += 1ENDGO

In order to access our table, we created a simple stored procedure and passed in the value as the filter predicates.

-- Create a simple stored procedure to retrieve the dataCREATE PROCEDURE RetrieveCustomers( @Value INT)ASBEGIN SELECT * FROM Customers WHERE Value < @ValueENDGO

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

-- Execute the stored procedure. -- This generates an execution plan with a Key Lookup (Clustered). EXEC RetrieveCustomers 80000 GO

Now, when you view the actual execution plan, you will see that the query optimizer has selected the clustered index scanning operator with 419 logical reads. SQL Server does not use non-clustered indexes because it does not make sense because of the critical point. The query results are not selective.

Now, if something happens to SQL Server (such as restart and Failover), SQL Server ignores the cached plan, here, I run dbcc freeproccache to erase each cache plan from the plan cache to simulate SQL Server restart (do not use it in the production environment !).

 -- Get rid of the cached execution plan... DBCC FREEPROCCACHE GO

Now someone calls your stored procedure again. The value of this input parameter is 1. The execution plan is different this time, because now you have bookmarks to search for the plan. SQL Server estimates that the number of rows is 1, and no rows are found in non-clustered indexes. Therefore, it makes sense to search for bookmarks combined with non-clustered indexes, because this query is selective.

Now I will run the query with the 80000 parameter value.

-- Execute the stored procedureEXEC RetrieveCustomers 1GO-- Execute the stored procedure again-- This introduces now a plan regression, because now we get a Clustered Index Scan-- instead of the Key Lookup (Clustered).EXEC RetrieveCustomers 80000GO

When you look at the output of statistics io again, you will see that this query now produces 160139 logical reads-the query just now has only 419 logical reads. At this time, the DBA's mobile phone will sound, causing performance problems. But today we need to solve it in a different way-use the query storage that was just enabled.

When you look at the actual execution plan again, you will see a plan regression in front of you, Because SQL Server has just reused the bookmarked query plan cache. You have an execution plan for the clustered index scanning operator just now. This is a side effect of parameter sniffing in SQL Server.

Let's take a closer look at this issue through query and storage. In object Resource Manager of SSMS, SQL Server 2016 provides a new node called query storage. Here you can see some reports.

[Previous resource usage query] shows you the most expensive query based on the selected dimension. Switch to logical read count ].

There are some queries in front of you. The most expensive query generates nearly 500000 logical reads. This is our initial statement. This is the first WOW query storage: After SQL Server is restarted, the stored data still exists! 2nd are SELECT statements in your stored procedure. In the query storage, each captured query has a tag number, which is 7. Finally, when you look at the right side of the report, you will see different execution plans for this query.

As you can see, the query storage captures two different execution plans, one ID is 7 and the other ID is 8. When you click the plan ID, SQL Server displays the estimated execution plan at the bottom of the report.

Plan 8 is a clustered index scan, and plan 7 is a bookmarks search. As you can see, using the query, storage, and analysis plan for regression is very simple. But you are not finished yet. You can now enforce the execution plan for the specified query. Now you know that the execution plan containing clustered index scanning has better performance. Therefore, you can click Force Execution Plan to force query 7 to use the execution plan.

We have solved the problem!

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

Microsoft told us only to "New Methods" related to SQL Server performance correction ". You only forced a specific plan, and everything was fine. This method has a big problem because the root cause of the performance problem has not been solved! The key to this problem is that the bookmarked search plan is not stable. Depending on the default input value of the first execution plan, the execution plan is continuously reused.

I usually recommend that you adjust your index design and create a covered index to ensure the stability of the plan. However, forcing a specific execution plan only solves the problem temporarily-you still need to fix the root cause of your problem.

Summary

Do not misunderstand me: the query and storage functions in SQL Server 2016 are great, which helps you better understand regression planning. It will also help you "Temporarily" to force specific execution plans. However, the goal of performance optimization is the same: You need to find the root cause of the problem and try to solve the problem-do not sway outside!

Articles you may be interested in:

• SQL SERVER performance optimization Overview (good summary, don't miss it)
• SQL Server paging query Stored Procedure Code
• Experience in optimizing SQL Server databases with High Performance
• Sqlserver2005 uses temporary tables and @ RowCount to Improve the Performance of paging query stored procedures
• Summary of key features of SQL Server 2016 CTP2.3
• Significant improvements in SQL Server 2016 TempDb