程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL Server 2016 查詢存儲機能優化小結

SQL Server 2016 查詢存儲機能優化小結

編輯：MSSQL

SQL Server 2016 查詢存儲機能優化小結。本站提示廣大學習愛好者：（SQL Server 2016 查詢存儲機能優化小結）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server 2016 查詢存儲機能優化小結正文

作為一個DBA，消除SQL Server成績是我們的職責之一，每一個月都有許多人給我們帶來各類不克不及說明卻要處理的機能成績。

我就屢次聽到，之前的SQL Server的機能成績都還好且在正常規模內，但如今一切曾經轉變，SQL Server開端蹩腳，猖狂的工作不克不及說明。在這個情形下我參與，剖析下全部SQL Server的裝置，最初用一些奇異的查詢拜訪辦法找出機能成績的本源。

但許多時刻成績的本源是一樣的：所謂的籌劃回歸（Plan Regression），即特定查詢的履行籌劃曾經轉變。昨天SQL Server曾經緩存了在籌劃緩存裡緩存了一個好的履行籌劃，明天就生成、緩存最初重用了一個蹩腳的履行籌劃——赓續反復。

進入SQL Server 2016後，我就變得有點過剩了，認為微軟引進了查詢存儲（Query Store）。這是這個版本最熱點的功效！查詢存儲贊助你很輕易找出你的機能成績是否是籌劃回歸形成的。假如你找到了籌劃回歸，這很輕易強迫一個特定籌劃不應用籌劃領導。聽起來很成心思？讓我們經由過程一個特定的場景，向你展現下在SQL Server 2016裡，若何應用查詢存儲來找出並終究修改籌劃回歸。

查詢存儲（Query Store）——我的敵手

在SQL Server 2016裡，在你應用查詢存儲功效前，你要對這個數據庫啟用它。這是經由過程ALTER DATABASE語句完成，如你所見的以下代碼：

CREATE DATABASE QueryStoreDemo
GO

USE QueryStoreDemo
GO

-- Enable the Query Store for our database
ALTER DATABASE QueryStoreDemo
SET QUERY_STORE = ON
GO

-- Configure the Query Store
ALTER 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

在線贊助為你供給了各個選項的具體信息。接上去我創立一個簡略的表，創立一個非集合索引，最初拔出80000筆記錄。

-- Create a new table
CREATE 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 records
DECLARE @i INT = 1
WHILE (@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 += 1
END
GO

為了拜訪我們的表，我額創立了一個簡略的存儲進程，傳入value值作為過濾謂語。

-- Create a simple stored procedure to retrieve the data
CREATE PROCEDURE RetrieveCustomers
(
 @Value INT
)
AS
BEGIN
 SELECT * FROM Customers
 WHERE Value < @Value
END
GO

如今我用80000的參數值來履行存儲進程。

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

如今當你檢查現實的履行籌劃時，你會看到查詢優化器曾經選擇了有419個邏輯讀的集合索引掃描運算符。SQL Server並沒有應用非集合索引，由於如許沒成心義，因為臨界點。這個查詢成果並沒有選擇性。

如今假定SQL Server產生了些工作（例如重啟，毛病轉移），SQL Server疏忽曾經緩存的籌劃，這裡我經由過程履行DBCC FREEPROCCACHE從籌劃緩存裡抹失落每一個緩存的籌劃來模仿SQL Server重啟（不要在臨盆情況裡應用！）。

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

如今有人再次挪用你的存儲進程，此次輸出參數值是1。此次履行籌劃紛歧樣，由於如今在履行籌劃裡你會有書簽查找。SQL Server估量行數是1，在非集合索引裡沒有找就任何行。是以與非集合索引查找聯合的書簽查找才成心義，由於這個查詢是有選擇性的。

如今我再履行用80000參數值的查詢。

-- Execute the stored procedure
EXEC RetrieveCustomers 1
GO

-- 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 80000
GO

當你再次看STATISTICS IO的輸入，你會看到這個查詢如今發生了160139個邏輯讀——適才的查詢只要419個邏輯讀。這個時刻DBA的手機就會響起，機能成績。但明天我們要分歧的方法處理——應用適才啟用的查詢存儲。

當你再次看現實的履行籌劃，在你眼前你會看到有一個籌劃回歸，由於SQL Server剛重用了書簽查找的的籌劃緩存。適才你有集合索引掃描運算符的履行籌劃。這是SQL Server裡參數嗅探的反作用。

讓我們經由過程查詢存儲來具體懂得這個成績。在SSMS裡的對象資本治理器裡，SQL Server 2016供給了一個新的結點叫查詢存儲，這裡你會看到一些報表。

【前幾個資本應用查詢】向你展現了最昂貴的查詢，基於你選擇的維度。這裡切換到【邏輯讀取次數】。

這裡在你眼前有一些查詢。最昂貴的查詢生成了近500000個邏輯讀。這是我們的初始語句。這曾經是第一個WOW後果的的查詢存儲：SQL Server重啟後，查詢存儲的數據照樣存在的！第2個是你存儲進程裡的SELECT語句。在查詢存儲裡每一個捕捉的查詢都有一個標示號——這裡是7。最初當你看申報的左邊，你會看這個查詢的分歧履行籌劃。