SQL Server 2022 ile Gelen Muhteşem Özellik, Parameter Sensitive Plan (PSP) Optimization

SQL server 2022 ile gelen harika özelliklerden biri de Parameter Sensitive Plan Optimizasyonu konusu.

İsmi karışık gibi olsa da elimden geldiğince kolay anlaşılabilir şekilde anlatmaya çalışacağım.

SQL Server’da Stored Procedure’ler bildiğimiz gibi performans, güvenlik gibi birçok konuda sunduğu avantajları ile ad hoc query’lere göre çok daha kullanışlı.

Aşağıdaki görselde bir stored procedure ile ad hoc query arasındaki farkı görebilirsiniz.

Bir adhoc query de 6 adımda gerçekleşen işlem, Stored Prcedure’de 2 adımda gerçekleşir. İlk dört adım procedure ilk çağırıldığında cache alınır ve bir execution plan çıkarılır. Böylece bu adımlara bir daha gerek kalmadığı için sp’ler hızlı çalışır.

Fakat bu durum genel olarak avantajlı gibi dursa da, execution planın her gelen parametre için aynı olması belli durumlarda dezavantaj olabilir.

İşte SQL Server 2022 ile gelen PSP özelliği, bir stored procedure için birden fazla execution plan saklamayı sağlıyor. Konuyu bir veriseti üzerinde anlatmak için basit bir veritabanı oluşturacağım.

Aşağıdaki script ile veritabanı oluşturma kısmını yapabilirsiniz.

https://drive.google.com/file/d/1wGqSGRoKwhxKtnp8cyO8tQo3k0AaVcJC/view?usp=sharing

Ya da database i doğrudan indirip kendinize restore edebilirsiniz.

https://drive.google.com/file/d/128jZOdZvTQSKwRzhTqlkD0FvsfveE_8c/view?usp=sharing

Bu veritabanında ITEMS tablosunda 100 satırlık veri var.

Aynı şekilde CUSTOMERS tablosunda da 100 satır veri var.

Şimdi bir de ürün ve müşteri tabloları ile bağlantılı SALES tablosuna bakalım;

Görüldüğü gibi bu tabloda 500.000 satır kayıt var.

Veritabanına kayıt atma scriptinde sorguyu ID’si 1 olan üründen daha fazla atacak şekilde ayarlamıştım. Şimdi değerlere bakalım.

Görüldüğü gibi diğer ItemID’lerden 500-600 satırlık satış verisi varken ID’si 1 olan üründen yaklaşık 450.000 ürün var.

SALES tablosunda ID alanına göre bir primary key ve clustered indeximiz, ITEMID alanına göre bir nonclustred indeximiz var.

Şimdi SQL Server 2019 üzerinde bir sorgu çekelim.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES 
WHERE ITEMID=20
GROUP BY ITEMID

Şimdi bu sorgumuzun ne kadar read yaptığına bakalım. Sorgumuz 1709 tane page okumuş ve bu da toplamda 1709×8/1024=13 MB’lık bir okuma yapmış. (1 Page=8 Byte)

Şimdi de oluşan execution plana bakalım.

Sistem sorguyu IX1 nonclustered indexini kullanacak şekilde optimize etmiş ve buna göre execution plan çıkarmış.

IX1 indexine baktığımız zaman ITEMID alanına göre bir index olduğunu görürüz. Mantık olarak ben sisteme ITEMID parametresi gönderiyorsam, sistemin de ITEMID alanına göre index okuması oldukça mantıklı zaten.

Peki, sistem doğru indexi bulamasa da primary key üzerinden clustered index scan yapsa ne olurdur? Bunu da index zorlaması yaparak görelim.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES WITH (INDEX=PK_SALES)
WHERE ITEMID=20
GROUP BY ITEMID

Bu durumda okunan page sayısı yaklaşık 2 katına çıkıyor. Şimdilik çok büyük bir problem yok gibi.

Peki, şimdi sorgumuzu ITEMID=1 olarak çekelim.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES  
WHERE ITEMID=1
GROUP BY ITEMID

Okunan page sayısı yine aynı ve 3043 adet 8 KB’lık page.

Şimdi de execution plana bakalım.

Sistem ITEMID alanına göre index kullanmak yerine primary key üzerinden clustered index scan yapmayı daha az maliyetli bulup bu yola göre bir execution plan çıkarmış. Peki bu durum doğru mu ona bakalım. Bu kezde ITEMID=1 iken nonclustered index’e zorlayalım.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES  WITH (INDEX=IX1)
WHERE ITEMID=1
GROUP BY ITEMID 

Görüldüğü gibi çok fazla okuma yapıyor.

Demek ki sistem ITEMID parametresi ile gelen değere göre olması gereken indexi doğru şekilde belirliyor ve doğru indexe gidiyor.

Buraya kadar tamam. SQL Server eldeki verilere göre doğru execution planı çıkarıyor. İşte burada stored procedure üzerinde şöyle bir sıkıntımız var. Stored procedure ün execution plan çıkarma işi ilk çalıştırıldığı zaman olur.

Bu durumda şöyle bir risk var. Şayet stored procedure bu örneğe göre ilk çalıştırıldığında ITEMID=1 ile çalıştırılırsa, farklı parametrelerle çağırıldığında da IX1 indexini kullanmaz ve Clustered Index scan yapar. Eğer ilk çalıştırılan parametre ITEMID=20 gibi az tekrar eden bir parametre ise, bu kez de IX1 e göre derlenir ve ITEMID=1 ile çağırıldığında da yine IX1 üzerinden çalışmaya çalışır ve bu da performans sorununa yol açar.

İşte SQL Server 2022 ile gelen PSP özelliği bir sp üzerinde birden fazla execution plan saklamayı sağlıyor.

Gelin deneyelim.

Bunun için bir stored procedure oluşturuyorum.

CREATE PROC [dbo].[GETSALES_BY_ITEMID]
@ITEMID AS INT
AS
BEGIN
	SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
	COUNT(*) ROWCOUNT_
	FROM SALES   
	WHERE ITEMID=@ITEMID 
	GROUP BY ITEMID 
END

SALESPSP üzerinde sağ tık properties diyorum ve db uyumluluk modunu 2019 olarak belirliyorum. Bunu yeni gelen özelliği test etmek ve 2019 da bu işlemin olmadığını göstermek için yapıyorum.

Şimdi stored procedure ümüzü ITEMID=20 değeri için çalıştıralım.

Şimdi de oluşan plana bakalım.

Görüldüğü gibi index seek işlemi yapıyor yani IX1’i kullanıyor.

Şimdi db mizin uyumluluk modunu tekrar 2022 yapalım.

Procedure’ü ITEMID=1 parametresi ile çağırdığımzda clustered index scan yapıyor.

Procedure’ü ITEMID=20 parametresi ile çağırdığımızda nonclustered index seek yapıyor.

Sonuç:

SQL Server üzerinde stored procedure ler şüphesiz performans için oldukça önemli. Fakat farklı türde gelen parametrelere karşı sadece 1 tane execution plan derlendiği için yanlış indexe yönelebiliyor. SQL Server 2022’de bir procedure için birden fazla execution plan tutarak gelen parametreye göre farklı indexe ve farklı execution plana yönelebiliyor. Bu gerçekten çok güzel bir özellik. Bir sonraki yazımda bunu büyük bir verisetinde uygulamalı olarak anlatmayı ve sunucu üstündeki performans kazancını görmenizi sağlayacak bir makale yazmayı planlıyorum.

Beğendiğinizi ümit ediyorum. Bir sonraki yazıda görüşmek üzere, sağlıcakla…