SQL Server 2022 ?? kh?c ph?c v?n ?? hi?u su?t khi cùng 1 k? ho?ch th?c thi s? d?ng l?i cho các tham s? khác nhau nh? th? nào?
Nh? bài vi?t tr??c mình có chia s? v? parameter sniffing khi execution plan s? d?ng l?i cho các giá tr? tham s? khác nhau ?áng k?. ?i?u này gay ra v?n ?? hi?u su?t truy v?n ch?m h?n khi khi d? li?u trong b?ng kh?ng ???c phan ph?i ??ng ??u.
Trong SQL Server 2022, m?t tính n?ng m?i g?i là Parameter Sensitive Plan Optimization (PSPO) ???c gi?i thi?u ?? gi?i quy?t v?n ?? này.
Parameter-sensitive plan optimization (PSPO)
Ta SET COMPATIBILITY_LEVEL = 160 ta ??t vào ch? ?? t??ng thích v?i SQL SERVER 2022
ta th?c hi?n ?ánh index t?i c?t Reputation trên b?ng dbo.User, th?c hi?n t?o PROCEDURE dbo.usp_UsersByReputation
Ta th?c hi?n ch?y nó v?i @Reputation = 2 ta có l?nh :
trên cùng c?a b?c ?nh nhìn g?n l?i b?n có th? th?y cau l?nh th?c thi PROCEDURE ???c chuy?n ??i thành cau l?nh SQL v?i "option" hint ??ng sau
Cau l?nh PLAN PER VALUE ? ?ay ngh?a là gì
Khi SQL Server biên d?ch query plan l?n ??u, nó nh?n th?y r?ng chúng ta ?ang th?c hi?n tìm ki?m "=" trên c?t Users.Reputation nh?n th?y r?ng các parameter khác nhau có th? d?n ??n s? l??ng hàng r?t khác nhau. ?i?u ?ó gi?i thích "predicate_range".
SQL server nh?n ra r?ng:
Vì v?y, SQL Server s? xay d?ng m?t k? ho?ch truy v?n nh?, trung bình và l?n cho m?t truy v?n này, s? d?ng các k? ho?ch khác nhau tùy thu?c vào tham s? ???c truy?n vào.
?i?u ?ó th?t s? th?ng minh vì execution plan kh?ng hardcode các giá tr? parameter c? th? - thay vào ?ó, m?i l?n th?c thi execution plan, SQL Server s? tra c?u parameter ?ó trong bi?u ?? th?ng kê và ch?n k? ho?ch truy v?n nh?, trung bình hay l?n d?a trên giá tr? mà nó ??c l??ng t? histogram.
?? xem nh?ng gì ???c ??a vào b? nh? cache k? ho?ch, chúng ta s? xóa cache, ch?y truy v?n, sau ?ó ki?m tra n?i dung c?a cache k? ho?ch v?i sp_BlitzCache:
sp_BlitzCache cho th?y th? t?c l?u tr? bên ngoài ?? ???c th?c thi m?t l?n, và cau l?nh bên trong c?ng ?? ???c th?c thi m?t l?n
?i?u ?ó th?t s? khéo léo vì nó có ngh?a là SQL Server ch? ??a plan kích th??c trung bình vào cache. Nó kh?ng xay d?ng k? ho?ch nh? hay l?n vì ch?a c?n thi?t - nh?ng k? ho?ch ?ó ch?a ???c th?c thi, và có th? s? kh?ng bao gi? ???c th?c thi.
Ta th?c thi m?t plan l?n h?n v?i reputation = 1. Nó tr? ra r?t nhi?u b?n ghi
Chi?n l??c th?c thi nó ?? s? d?ng parallel table scan.
SQL Server ?? vi?t l?i cau truy v?n ?? bao g?m m?t QueryVariantID khác
b? nh? cache k? ho?ch cho th?y th? t?c bên ngoài ?? ???c th?c thi hai l?n, v?i c? k? ho?ch truy v?n trung bình và l?n ??u ???c th?c thi m?t l?n m?i lo?i. T?c là v?i m?i giá tr? tham s? @Reputation ?? th?c hi?n 2 k? ho?ch khác nhau!
V?n ?? v?n x?y ra v?i PSPO
Ta ch?y ti?p v?i Reputation = 3 và th?y k?t qu?:
V?i reputation = 3 nó s? d?ng l?i query plan khi dùng v?i reputation = 2, tuy nhiên ta có th? th?y execuation plan ? ?ay SQL server ??c tính inner join v?i 9149 dòng tuy nhiên th?c t? nó là 191747 ( g?p 21 l?n so v?i ??c l??ng). ?i?u này là do k? ho?ch trung bình ?? l?y giá tr? ??u tiên mà nó ???c g?i v?i Reputation = 2.
Reputation = 3 tr? v? g?p 21 l?n s? l??ng dòng so v?i Reputation = 2, vì v?y nó ??c nhi?u logic page h?n s? l??ng logic page trong b?ng, ch? ch?y theo m?t lu?ng, và vi?c s?p x?p tràn vào TempDB.
N?u chúng ta gi?i phóng cache, ch?y v?i reputation = 3 ??u tiên:
Sau ?ó, k? ho?ch “trung bình” ???c xay d?ng v?i m?t k? ho?ch quét song song cùng v?i m?t l??ng b? nh? c?p phát l?n, ho?t ??ng tuy?t v?i khi Reputation = 3. Tuy nhiên, nó kh?ng ho?t ??ng t?t nh? v?y khi Reputation = 2:
?i?u này khi?n l??ng b? nh? c?p phát kh?ng l? b? b? phí và t?o ra m?t lo?t các ch? CX% vì ??c l??ng gi? ?ay l?ch t?i 21 l?n theo h??ng ng??c l?i.
Vi?c tri?n khai PSPO trong SQL Server 2022 kh?ng kh?c ph?c ???c v?n ?? parameter sniffing – nó còn khu?ch ??i v?n ??, b?i vì gi? ?ay chúng ta có nhi?u k? ho?ch th?c thi h?n trong b? nh?, và m?i k? ho?ch ??u b? sniffed. Trong h?u h?t các tình hu?ng, t?i ngh? ?ay v?n là m?t l?i ích t?ng th?, vì các k? ho?ch nh? và l?n có l? s? ít g?p ph?i các tình hu?ng kh?n c?p v? hi?u su?t nghiêm tr?ng h?n. Tuy nhiên, các k? ho?ch trung bình v?n s? d? b? t?n th??ng nh? tr??c
V?n ?? trung bình: ch? tìm ki?m v?i s? so sánh b?ng tr?c ti?p.
Khi procedure th?c hi?n nó ch? ho?t ??ng cho các tìm ki?m so sánh b?ng, ch? kh?ng ph?i tìm ki?m theo ph?m vi. M?t trong nh?ng v?n ?? ph? bi?n nh?t liên quan ??n vi?c 'sniffing' tham s? là v?n ?? v?i ph?m vi ngày tháng: các truy v?n có tham s? ngày b?t ??u và k?t thúc, nh? th? này:
PSPO kh?ng thêm option hint trong query ? ?ay:
Vì PSPO ch? ???c l?p trình cho các tìm ki?m so sánh b?ng tr?c ti?p.
Khi so sánh tr?c ti?p v?i các c?t có s? bi?n thiên l?n trong s? l??ng d? li?u nh? c?t PostTypeId.
Ta t?o m?t ch? m?c Index lên c?t PostTypeId c?a b?ng Posts. Khi ?ánh index trên c?t này SQL Server có th?c hi?n th?ng kê v? cách phan b? d? li?u trên c?t này
Vì t?i có m?t index trên PostTypeId, có ngh?a là chúng ta c?ng có th?ng kê ?? phan b? d? li?u v? PostTypeId, truy v?n này có th? có các k? ho?ch khác nhau cho các tham s? khác nhau.
Ta có cau l?nh l?y các lo?i bài vi?t và s? l??ng bài vi?t t??ng ?ng, nhóm theo lo?i bài vi?t và s?p x?p theo PostTypeId.
th? t?c usp_PostsByPostType l?y tham s? tên lo?i bài vi?t c? th? ('PrivilegeWiki'). ch? v?i 2 b?n ghi 'PrivilegeWiki'
PSPO ?? kh?ng ???c kích ho?t ? ?ay vì ??c l??ng >> so v?i s? l??ng th?c t?, kh?ng thay ??i k? ho?ch th?c thi.
V?n ?? l?n: ph?n m?m giám sát ?? th?t b?i
N?u b?n nhìn k? vào các ?nh ch?p màn hình c?a sp_BlitzCache, s? th?y m?t v?n ?? m?i kh?ng l?, to l?n, kh?ng l? ??n m?c kh?ng th? tin ???c:
Trong c?t “Query Type”, hai truy v?n ???c t?o ra b?i PSPO ch? ghi là “Statement”. Khi s? d?ng PSPO, các truy v?n SQL ???c t?o ra ch? hi?n th? là "Statement" mà kh?ng có th?ng tin chi ti?t v? ngu?n g?c c?a truy v?n. ( t? procedure). ?i?u này làm cho vi?c theo d?i và phan tích hi?u su?t c?a các truy v?n tr? nên khó kh?n, vì b?n kh?ng th? xác ??nh ???c các tham s? ho?c chi ti?t c?a truy v?n.