Parameter Sniffing

Guess Post: Vũ Minh Tâm

Trước khi vào bài viết, tôi muốn kể lại một vài tình huống tôi đã trải qua.
Tình huống 1: Một ngày nọ, một thành viên trong nhóm Database của tôi gặp một hiện tượng kì lạ. Stored Procedure mà cậu ấy viết khi chạy trên môi trường test rất tốt, thời gian xấp xỉ 1 giây. Tuy nhiên khi đưa lên hệ thống thật, thì lại chạy ì ạch mất khoảng 30 giây.
Cậu ấy thay hết giá trị vào stored, chạy trên SSMS dưới dạng câu SQL bình thường thì lại chạy tốt. Vò đầu bứt tai một hồi cậu ấy đâm ra nghi ngờ khi sử dụng Stored Procedure và có kế hoạch thay hết Stored đã viết bằng dynamic sql để tăng hiệu năng.
Tình huống 2: Một công ty mà tôi quen chuyên triển khai các phần mềm quản lý. Thông thường khi có 1 dự án, công ty sẽ triển khai phần mềm cho tất cả các chi nhánh của khách hàng. Có 1 lần khi triển khai ở khoảng 20 chi nhánh thì có 3 chi nhánh phản ánh một báo cáo chạy không tốt. Và cũng tương tự như trên, khi copy câu sql tương ứng đã thay giá trị như trên Stored Procedure và chạy độc lập thì kết quả lại rất tốt.
Sau khi tìm hiểu, 2 trường hợp trên đều gặp phải parameter sniffing. Vậy parameter sniffing là gì, nó có ảnh hưởng như thế nào đến hiệu năng của truy vấn, cách khắc phục ra sao?
Trước khi đọc tìm hiểu nội dung bài viết, bạn có thể tham khảo một số bài viết liên quan như sau:
- Index Giúp Tăng Hiệu Năng Thực Hiện Như Thế Nào
- SQL Động
- Giới thiệu về Index (Slideshow)

Parameter Sniffing là gì

Khi một Stored Procedure được chạy lần đầu tiên, hoặc sau khi giải phóng SP cache, v.v… Bộ xử lý Optimizer trên SQL Server bắt buộc phải tạo ra một execution plan tương ứng cho Stored Procedure. Nó sẽ sử dụng các giá trị của tham số truyền vào, sử dụng các dữ liệu thống kê sẵn có để sinh ra một execution plan. Execution Plan này sẽ được lưu trong sp cache và được sử dụng cho các lần gọi Stored Procedure sau BẤT KỂ tham số truyền vào như thế nào.
Thông thường dữ liệu để thống kê trên Stored Procedure có sự khác biệt không đáng kê, do đó khi một Stored Procedure (SP) chạy tốt với tập dữ liệu này cũng sẽ đáp ứng được khi chạy với tập dữ liệu khác. Tuy nhiên một số trường hợp đặc biệt mà bản thân dữ liệu có sự phân bố không đều, dẫn đến 1 tập dữ liệu tương đối khác biệt với phần còn lại.
Parameter Sniffing rất tai hại nếu lần tạo execution plan đầu tiên mà các tham số truyền vào tương ứng với tập dữ liệu bất thường đó. Lúc này execution plan chỉ được coi là tối ưu với tập dữ liệu này mà hoàn toàn không tối ưu với phần lớn dữ liệu còn lại, dẫn đến hiệu năng thực hiện bị giảm sút nghiêm trọng.

Ví dụ về Parameter Sniffing

Tình huống đặt ra là chúng ta khảo sát những người sử dụng smartphone, xem họ sử dụng ứng dụng nào là nhiều nhất. Tôi thuê 5 cộng tác viên để đi khảo sát. Tuy nhiên Cộng tác viên 1 làm việc kiểu đối phó, anh ta chỉ đến một công ty lập trình ứng dụng trên mobile và thực hiện khảo sát. Do đó toàn bộ các nhân viên ở đây đều chọn phần mềm của công ty họ là phần mềm họ dùng nhiều nhất.
Script dưới đây tạo ra 1000 bản ghi, trong đó giá trị của Most_Played_ID đều là 1.

CREATE TABLE TEST_PLAYER 
(
Player_ID INT,
Most_Played_ID INT
);
 
DECLARE @Row_Count INT
SET @Row_Count = 0
 
WHILE @Row_Count < 1000
BEGIN
INSERT INTO TEST_PLAYER (Player_ID, Most_Played_ID)
VALUES (@Row_Count,1)
SET @Row_Count = @Row_Count + 1;
END

4 cộng tác viên còn lại làm việc nghiêm túc, nên dữ liệu của họ rất phong phú, 9000 người họ phỏng vấn đều sử dụng 9000 phần mềm khác nhau (hư cấu):

SET @Row_Count = 1001
 
WHILE @Row_Count < 10001
BEGIN
INSERT INTO TEST_PLAYER (Player_ID, Most_Played_ID)
VALUES (@Row_Count,@Row_Count)
SET @Row_Count = @Row_Count + 1;
END
 
CREATE INDEX IDX_TEST_MP_DI
ON TEST_PLAYER(Most_Played_ID)

Sau khi tạo Index cho cột Most_Played_ID. Stored Procedure chúng ta cần tạo có nội dung như sau, giá trị truyền vào là Most_Played_ID và cần trả ra danh sách những người sử dụng phần mềm đó:

CREATE PROC GetPlayerByApp(@MPID INT)
AS 
SELECT * FROM TEST_PLAYER
WHERE Most_Played_ID = @MPID

Trước khi chạy Stored Procedure, ta chạy thử code thường và xem execution plan tương ứng.
Với tập giá trị phong phú (chiếm 9/10 lượng dữ liệu):

SELECT * FROM TEST_PLAYER WHERE Most_Played_ID = 1001


Câu truy vấn trên chạy tốt, do lượng dữ liệu trả ra ít, cột có selectivity tốt nên sử dụng cách thức truy cập Index Seek, sau đó Lookup vào bảng để Select ra dữ liệu.
Tuy nhiên với truy vấn tương tự vào tập dữ liệu tồi thì execution plan như sau

SELECT * FROM TEST_PLAYER WHERE Most_Played_ID = 1


Index ở đây hoàn toàn không được sử dụng, do tập kết quả trả ra là khá lớn (1/10 dữ liệu trong bảng) nên bộ xử lý sử dụng Table Scan quét từng dòng trong bảng vì cách thức này tốt hơn.
Dĩ nhiên với từng giá trị truyền vào như trên, cách SQL Server xử lý là hoàn toàn phù hợp nhưng điều gì xảy ra nếu lần đầu tiên chạy Stored procedure chúng ta cho Most_Played_ID = 1?
Lúc đó SQL Server sẽ LUÔN dùng Table Scan trong bất kì trường hợp nào. Với một giá trị cụ thể khác 1, sử dụng Index Seek nhanh và chính xác hơn rất nhiều (bảng càng lớn thì sự khác biệt là càng rõ), nhưng hệ thống vẫn lờ đi không thực hiện tối ưu hóa lại thủ tục mà luôn sử dụng execution plan đã có sẵn. Điều này là rất tồi tệ.

Cách khắc phục

Cách khắc phục parameter sniffing tùy thuộc bạn muốn xử lý như thế nào.
Nếu bạn muốn sử dụng execution plan cho phần lớn tập dữ liệu, không quan tâm đến phần dữ liệu đặc biệt còn lại, chạy SP với tham số tương ứng.
Nếu đã truyền sai tham số, có thể sử dụng DBCC FREEPROCCACHE.
Bạn cũng có thể cho hệ thống recompile lại SP mỗi lần được gọi bằng khai báo WITH RECOMPILE khi viết thủ tục.
Nếu bạn muốn sử dụng execution plan tốt nhất trong cả hai trường hợp với ví dụ trên, hãy tách SP chính thành 2 SP, mỗi SP làm việc với 1 tập dữ liệu. Cách này nhiều nơi còn gọi là sử dụng cây quyết định (Decision Tree)
Còn một số phương pháp khác như sử dụng biến trung gian, HINT, v.v…




Tags:

20 Comments
Posted on 25/6/2013 | Categories: SQL Server Programming, Stored Procedure

Các bài viết tương tự

Comments
  • Vũ Huy Tâm (25/06/2013 9:28 am)

    Bài viết rất thú vị. Tôi muốn giải thích thêm về tên gọi parameter sniffing – sniffing nghĩa là đánh hơi, trong trường hợp này là hệ thống chỉ đánh hơi tham số được truyền vào chứ không phân tích kỹ lưỡng lại tham số. Và khi đánh hơi ra rồi – tực là nhận ra đã có một tham số như thế được truyền trước đó – thì nó lập tức lôi cái execution plan đã có ra thực hiện, thay vì đánh giá lại câu lệnh và tạo một execution plan mới cho phù hợp. Đây là một đặc tính, hay có thể gọi là nhược điểm trong cách tối ưu hệ thống của SQL Server.
    Parameter sniffing là vấn đề rất khó chịu đối với nhiều ứng dụng, và rất khó có cách giải quyết triệt để. Sử dụng WITH RECOMPILE dẫn đến thủ tục được dịch lại mỗi lần thực hiện, điều này có thể gây thiệt hại đáng kể về hiệu năng. Tách thành nhiều SP đòi hỏi mình phải biết sự phân bố của dữ liệu – phần dữ liệu đặc biệt nằm ở dải nào, nếu dữ liệu luôn biến đổi thì dải đặc biệt này cũng thay đổi theo và do đó cũng tạo thêm khó khăn khi tách SP.

  • Hoàng Tùng (10/07/2013 12:54 am)

    Thanks anh Tâm. Bài viết rất thú vị và bổ ích.

  • ecompc (04/08/2013 10:32 pm)

    Chào anh Tâm, nếu trong Store Procedure em thêm dòng

    DBCC FREEPROCCACHE
    ALTER PROC [dbo].[GetPlayerByApp](@MPID INT)
    AS 
    DBCC FREEPROCCACHE
    SELECT * FROM TEST_PLAYER
    WHERE Most_Played_ID = @MPID

    em đã thử đưa vào và lúc này nó chạy đúng cho mọi trường hợp

    Không biết nó có khác gì với

     WITH RECOMPLIE

    và hiệu năng nó như thế nào

    • ecompc (04/08/2013 11:28 pm)

      đã thử đưa DBCC FREEPROCCACHE vào store và kết quả không như mong đợi

      nếu mình truyền most_played_id =1 thì sql luôn sử dụng Table Scan, và ko tận dụng được cache tối ưu trước đó(trước đó truyền 1001 chẳng hạn)

  • Red Devilic (05/08/2013 12:48 am)

    Hi.

    - Về vấn đề thứ nhất, WITH RECOMPILE mỗi lần chạy sẽ sinh ra Execution Plan ứng với lần chạy đó. Có nghĩa là mỗi lần chạy với WITH RECOMPILE, bạn không tận dụng được execution plan trước đó. Với FREEPROCCACHE thì bạn sẽ giải phóng cache của các stored, và lúc này execution plan đầu tiên sẽ được coi là default với các lần chạy tiếp theo.
    - Bạn đã hiểu nhầm ý của bài viết. Trong trường hợp Most_Play_ID = 1 thì Table Scan là execution tối ưu vì lượng bản ghi trả ra chiếm phần lớn so với lượng bản ghi trong bảng. Nếu là Index Seek thì mới có vấn đề :D

    • Lê Quang (11/09/2013 2:55 am)

      Bạn có thể nói rõ hơn hoặc có demo nào về :

      WITH RECOMPILE.

      HOặc

      FREEPROCCACHE

      Khác nhau như thế nào hôn, cảm ơn bạn rất nhiều!

  • Ngô Quang Trung (06/08/2013 3:20 am)

    Chúng tôi cũng đã từng gặp qua trường hợp mà Parameter sniffing làm chậm đi rất nhiều một số tác vụ.

    Phương án chúng tôi giải quyết là hàng tuần kiểm tra xem các queries có thời gian thực thi lâu nhất, sau đó tiến hành test nhiều lần với các Option như with recompile, Optimized for nothing… để cuối cùng chọn lựa lại phương án tốt nhất.

    Có một cuốn sách rất hay có phần nói về Parameter Sniffing là cuốn “SQL Sever MVP Deep Dive”, của nhà xuất bản Manning, 2 tập.

  • tung (16/09/2013 2:54 am)

    vấn đề này vẫn chưa có cách nào giải quyết hiệu quả à các bạn . Ai có kinh nghiệm về vấn đề này có thể post lên cho mọi người tham khảo được ko ?

  • vhai (20/11/2013 4:18 am)

    Chào anh,

    Ngoài bài viết này e cũng có đọc thêm 2 bài nửa là:

    link1: http://www.sqlviet.com/blog/index-tren-truong-bit-co-loi-gi-khong
    link2: http://www.sqlviet.com/blog/co-phai-sql-dong-luon-luon-cham

    Trong link1 anh có đề cập đến selectivity, mà em thấy ví dụ trên của anh là có 10000 bản ghi có 9001 Most_Played_ID khác nhau => selectivity = 0.9001 > 0.3 vậy sao nó vẫn bỏ qua index thế anh???

    Còn trong link2 anh có nói phương án thực thi lưu sẵn trong cache chỉ trong khoảng 30 giây thôi sau đó sẽ được biên dịch lại. Vậy vấn đề Parameter Sniffing chỉ xảy ra khi nhiều user cùng gọi stored trong khoảng thời gian 30 giây đúng không anh???

    Cám ơn anh.

    • Vũ Huy Tâm (20/11/2013 11:41 am)

      Nó bỏ qua index vì nó vẫn dùng phương án thực thi đã có sẵn, và đây chính là mặt trái của parameter sniffing.
      Đúng rồi, hiện tượng này chỉ xảy ra khi có nhiều lần gọi liên tục đến stored procedure

      • vhai (20/11/2013 10:59 pm)

        Anh ơi, trong bài anh có viết:

        SELECT * FROM TEST_PLAYER WHERE Most_Played_ID = 1

        “Index ở đây hoàn toàn không được sử dụng, do tập kết quả trả ra là khá lớn (1/10 dữ liệu trong bảng) nên bộ xử lý sử dụng Table Scan quét từng dòng trong bảng vì cách thức này tốt hơn”

        Em không hiểu chổ này tại sao bộ xử lý lại dùng Table Scan trong khi selectivity = 9001/10000 > 0.3, em giả sử câu query trên chưa có phương án thực thi.

        Cám ơn anh.

        • tung (25/11/2013 5:46 am)

          theo mình hiểu thì do số lượng bản ghi quá lớn nên nó sẽ chọn phương pháp table scan vì nó tiết kiệm chi phí hơn , và sau đó nếu gọi lại proc thì nó mặc định dùng lại cái table scan ở lần gọi đầu tiên.

          • Vũ Huy Tâm (26/11/2013 10:51 am)

            Good question và Tùng trả lời cũng đúng rồi. Mình chỉ nói rõ thêm về chi tiết này mà trong bài không nhắc đến, đó là quyết định sử dụng index ngoài độ selectivity còn dựa vào statistics là thống kê về độ phân bổ dữ liệu. Statistics cho biết ước lượng số bản ghi của mỗi giá trị trong cột index. Khi chạy lệnh với Most_Player_ID = 1 nó xác định ra là sẽ có 1000 bản ghi được trả về trên tổng số 10000 và quyết định dùng table scan.
            Selectivity là thống kê tổng quan về index, nó càng có ý nghĩa khi dữ liệu càng được phân bổ đồng đều (mỗi giá trị chiếm 1 tỷ lệ như nhau). Bản thân SQL Server không lưu selectivity mà nó chỉ là con số mình quan sát để đánh giá hiệu quả của index.
            Statistics được duy trì bởi SQL Server và nó lưu chi tiết thống kê về từng giá trị hoặc dải giá trị (cấu trúc của nó giống như histogram trong xử lý ảnh).
            Quyết định dùng index hay không của SQL Server là một quyết định mờ, không có một biên giới rõ ràng là ở bên này thì dùng index, vượt quá thì bỏ. Con số selectivity = 0.3 cũng chỉ là con số quan sát được chứ không phải SQL luôn làm như vậy. Với bảng nhỏ, vừa trong 1 trang (data page) thì nó luôn luôn làm table scan kể cả khi selectivity=1, vì cách nhanh nhất là “hốt” luôn cả trang đó vào bộ nhớ. Ngược lại, với bảng lớn, ví dụ 100 triệu bản ghi, thì có thể chỉ vài triệu bản ghi (vài phần trăm) là đã dẫn đến table scan rồi vì nếu dùng index sẽ cần quá nhiều key lookup phải nhảy qua lại rất tốn kém, trong khi table scan đọc 1 lèo tất cả các trang lại nhanh hơn.

            • vhai (27/11/2013 3:23 am)

              Cám ơn 2 anh nhé, em hiểu rõ phần nào rồi

      • tung (06/01/2014 12:03 am)

        Bác ơi có nghĩa là khi user gọi stored procedure liên tục thì cái parameter sniffing mới xảy ra đúng không ạ? Nếu thỉnh thoảng mình mới gọi thì không sao phải không bác .

  • tung (03/12/2013 9:51 pm)

    Bác tâm cho em hỏi chút . Với linq to Sql thì cũng xảy ra parameter sniffing phải không bác ? Tại vì em có đoạn code dùng linq lúc thì nó rất nhanh , lúc thì nó chậm rì ạ ?

    • tung (23/12/2013 2:49 am)

      Biết là bác hơi bận nhưng nếu có thể bác trả lời dùm em vấn đề này với nhé ! Cảm ơn bác nhiều .

  • Vũ Huy Tâm (23/12/2013 10:27 am)

    linq cũng có parameter sniffing vì nó cũng làm tham số hóa. Nên nhớ là SQL Server không quan tâm query gửi từ đâu, nó nhận được query, tìm trong cache thấy đã có sẵn phương án thực thi, lôi ra thực hiện không cần tối ưu lại, và đó là parameter sniffing.

    • tung (23/12/2013 10:26 pm)

      Vâng em cảm ơn bác nhiều ạ . Chúc bác có 1 giáng sinh vui vẻ !

  • chaucv (11/10/2016 6:10 am)

    Ngoài 2 cách này
    WITH RECOMPILE.
    HOặc
    FREEPROCCACHE
    Các bạn có thể sử dụng biến tạm – cũng giải quyết đc problem
    VD:

    ALTER PROC [dbo].[GetPlayerByApp](@MPID INT)
    AS 
    BEGIN
    DECLARE @MPID1 INT=@MPID
    SELECT * FROM TEST_PLAYER
    WHERE Most_Played_ID = @MPID1
    END

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>