Vấn Đề Hiệu Suất Truy Vấn SQL Server Với Khoảng Thời Gian Dữ Liệu và Hiện Tượng Sniffing Tham Số

Posted on by admin

Vấn Đề

Tôi thường được hỏi tại sao SQL Server lại bỏ qua một chỉ mục. Một trong những lý do thú vị nhất cho điều này là hiện tượng Sniffing Tham Số. Tôi viết mẹo này để chỉ cho bạn cách khắc phục hiện tượng Sniffing Tham Số, giúp bạn tối ưu hóa các truy vấn.

Giải Pháp

Hãy tưởng tượng bạn làm việc với một thủ tục lưu trữ nhận hai tham số đầu vào, ngày bắt đầu và ngày kết thúc. Một người truyền vào một khoảng thời gian nhỏ, trong khi người khác truyền vào một khoảng thời gian lớn. Thực tế, có thể có hai chế độ tìm kiếm dựa trên lượng dữ liệu, do đó một chỉ mục có thể không hiệu quả cho cả hai lựa chọn khoảng thời gian.

Bạn tạo một chỉ mục khác để hỗ trợ cả hai chế độ tìm kiếm, nhưng trình tối ưu hóa truy vấn luôn chỉ sử dụng một chỉ mục.

Điều quan trọng là phải hiểu rằng khi bạn thực thi một thủ tục lưu trữ lần đầu tiên, SQL Server xây dựng một kế hoạch thực thi dựa trên giá trị của các tham số đầu vào được truyền trong lần thực thi ban đầu đó. SQL Server lưu trữ kế hoạch thực thi đã tạo vào bộ nhớ đệm kế hoạch cho các lần thực thi sau. Khi chạy lại thủ tục lưu trữ, ngay cả khi sử dụng các giá trị tham số đầu vào khác nhau, SQL Server sẽ sử dụng kế hoạch có sẵn trong bộ nhớ đệm, mà không tạo ra một kế hoạch mới. Điều này có thể dẫn đến vấn đề liên quan đến hiện tượng Sniffing Tham Số.

Thứ hai, lưu ý rằng SQL Server 2022 đi kèm với một tính năng gọi là Tối Ưu Kế Hoạch Nhạy Cảm Tham Số (PSPO), nhằm giải quyết các vấn đề liên quan đến Sniffing Tham Số. Tuy nhiên, PSPO không thể xử lý ví dụ này. Nó chỉ cố gắng giảm thiểu vấn đề Sniffing Tham Số.

Trong mẹo này, tôi sẽ chỉ cho bạn cách mà vấn đề Sniffing Tham Số xảy ra và cung cấp một giải pháp đơn giản để khắc phục nó.

Thiết Lập Môi Trường Kiểm Tra

Hãy bắt đầu bằng cách thiết lập môi trường của chúng ta. Tôi đang sử dụng SQL Server 2022 và sẽ sử dụng cơ sở dữ liệu StackOverflow. Để sử dụng cơ sở dữ liệu mã nguồn mở này, bạn có thể truy cập StackOverflow.com hoặc tải xuống từ liên kết này.

Tôi muốn tận dụng các tính năng trong SQL Server 2022 CU 7, vì vậy tôi đã đặt mức tương thích là 160.

ALTER DATABASE StackOverflow SET Compatibility_Level = 160
GO

Tạo Bảng Mới

Có một bảng gọi là Users trong cơ sở dữ liệu StackOverflow chứa thông tin người dùng, như Tên Hiển Thị, Ngày Tạo, v.v. Hãy tạo một bảng mới với cùng cấu trúc và nội dung như bảng Users.

DROP TABLE IF EXISTS [dbo].[Users_new];
GO
 
CREATE TABLE [dbo].[Users_new](
   [Id] [int] IDENTITY(1,1),
   [AboutMe] [nvarchar](max) NULL,
   [Age] [int] NULL,
   [CreationDate] [datetime] NOT NULL,
   [DisplayName] [nvarchar](40) NOT NULL,
   [DownVotes] [int] NOT NULL,
   [EmailHash] [nvarchar](40) NULL,
   [LastAccessDate] [datetime] NOT NULL,
   [Location] [nvarchar](100) NULL,
   [Reputation] [int] NOT NULL,
   [UpVotes] [int] NOT NULL,
   [Views] [int] NOT NULL,
   [WebsiteUrl] [nvarchar](200) NULL,
   [AccountId] [int] NULL)
GO
 
SET IDENTITY_INSERT dbo.Users_new ON;
GO
 
INSERT INTO dbo.Users_new With (Tablock) ([Id], [AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
SELECT [Id],[AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId]
FROM dbo.Users;
GO
 
SET IDENTITY_INSERT dbo.Users_new OFF;
GO

Tạo Chỉ Mục

Sau khi tạo và điền dữ liệu cho bảng, hãy tạo một chỉ mục phân cụm duy nhất sử dụng các cột CreationDate và Id làm khóa. Ngoài ra, tôi cũng đã tạo một chỉ mục columnstore không phân cụm trên các cột CreationDate và Reputation.

CREATE UNIQUE CLUSTERED INDEX IX_CreationDate_Id ON dbo.Users_new (CreationDate, Id) WITH (DATA_COMPRESSION = PAGE)
GO
 
Create Nonclustered Columnstore Index IXNCI_1 On Users_New(CreationDate, Reputation)
GO

Thực Thi Truy Vấn

Nếu chúng ta thực thi truy vấn sau, SQL Server sẽ thực hiện một thao tác tìm kiếm chỉ mục phân cụm để lấy và hiển thị kết quả. Truy vấn này lấy giá trị danh tiếng cao nhất cho mỗi tháng trong khoảng thời gian từ ‘2018-03-01’ đến ‘2018-06-01’.

Select Month(CreationDate) As Monthly, Max(Reputation) AS MaxReputation 
From dbo.Users_New 
Where CreationDate Between '2018-03-01' And '2018-06-01'
Group By Month(CreationDate)
Order By MAX(Reputation)
GO

Kết Quả Thao Tác Tìm Kiếm

Như bạn có thể thấy trong hình ảnh bên dưới, một thao tác tìm kiếm chỉ mục phân cụm đã được thực hiện.

So Sánh Thời Gian Thực Thi Truy Vấn

Truy vấn đầu tiên đã sử dụng khoảng thời gian ba tháng.

Tôi sẽ chạy lại truy vấn với khoảng thời gian một năm. Để so sánh thời gian thực thi truy vấn, tôi sẽ bật tính năng thống kê thời gian để theo dõi thời gian trôi qua trong quá trình thực thi truy vấn.

SET STATISTICS TIME ON
GO 
Select Month(CreationDate), Max(Reputation) 
From dbo.Users_New 
Where CreationDate Between '2017-01-01' And '2018-01-01'
Group By Month(CreationDate)
Order By MAX(Reputation) 
GO

Kết Quả Thao Tác Quét Chỉ Mục

SQL Server đã thực thi truy vấn với một thao tác quét chỉ mục columnstore không phân cụm để hiển thị kết quả.

Dưới đây, bạn có thể thấy rằng thời gian thực thi của truy vấn là 317 mili giây.

Cho đến nay, mọi thứ đều ổn, mỗi truy vấn đều sử dụng chỉ mục dự kiến.

Chạy Kiểm Tra Với Thủ Tục Đã Lưu

Hãy lưu ý những ảnh hưởng tiềm tàng của việc kết hợp một truy vấn vào một thủ tục đã lưu. Dưới đây, chúng ta sẽ tạo một thủ tục nhận hai tham số và thực hiện cùng một truy vấn.

Create Procedure [dbo].[USP_GetMaxReputation]
(@SDate Datetime, @EDate DateTime)
AS
Select Month(CreationDate), Max(Reputation) From dbo.Users_New 
Where CreationDate Between @SDate And @EDate
Group By Month(CreationDate)
Order By MAX(Reputation)
GO

Sau khi thủ tục đã lưu được tạo, tôi sẽ thực thi nó bằng cách sử dụng khoảng thời gian ba tháng.

Exec [USP_GetMaxReputation] '2018-03-01', '2018-06-01'
GO

Như mong đợi, SQL Server đã sử dụng một thao tác tìm kiếm chỉ mục phân cụm, như được thể hiện trong hình ảnh bên dưới.

Tiếp theo, tôi sẽ thực thi thủ tục với khoảng thời gian một năm.

Exec [USP_GetMaxReputation] '2017-01-01', '2018-01-01'
GO

SQL Server đã không sử dụng chỉ mục columnstore mà thay vào đó đã thực hiện một thao tác tìm kiếm chỉ mục phân cụm.

Lưu ý thời gian thực thi thủ tục bên dưới. Nó chậm hơn khoảng bốn lần so với khi SQL Server sử dụng chỉ mục columnstore không phân cụm. Vấn đề này liên quan đến Parameter Sniffing.

Hãy chạy lại bài kiểm tra nhưng thêm hint OPTION RECOMPILE vào thủ tục đã lưu để cải thiện hiệu suất.

Alter Procedure [dbo].[USP_GetMaxReputation]
(@SDate Datetime, @EDate DateTime)
AS
Select Month(CreationDate), Max(Reputation) From dbo.Users_New 
Where CreationDate Between @SDate And @EDate
Group By Month(CreationDate)
Order By MAX(Reputation)
Option (Recompile)
GO

Đầu tiên, hãy thực thi thủ tục với khoảng thời gian ba tháng:

Exec [USP_GetMaxReputation] '2018-03-01', '2018-06-01'
GO

Sau đó, hãy thực thi thủ tục với khoảng thời gian một năm:

Exec [USP_GetMaxReputation] '2017-01-01', '2018-01-01'
GO

Trong lần thực thi thứ hai, SQL Server hiện đang sử dụng thao tác quét chỉ mục columnstore không phân cụm khi được cung cấp khoảng thời gian một năm.

Thời gian thực thi đã giảm xuống còn 329 mili giây.

Tóm Tắt

Việc sử dụng hint OPTION (RECOMPILE) đã giải quyết vấn đề bằng cách khiến SQL Server tạo ra một kế hoạch truy vấn mới cho mỗi lần thực thi của thủ tục đã lưu, từ đó sử dụng chỉ mục phù hợp nhất.

Tuy nhiên, việc sử dụng hint này không nhất thiết là lựa chọn tốt nhất cho các thủ tục đã lưu được sử dụng nhiều. Khi một thủ tục đã lưu thường xuyên được thực thi với hint này, việc tăng mức sử dụng CPU là điều thường thấy.

Do đó, đây có thể là giải pháp tốt cho một số trường hợp, nhưng không phải cho mọi tình huống. Bạn sẽ cần thử nghiệm trong môi trường của mình để xem giải pháp nào hoạt động tốt nhất.