MSSQL Server Parameter Sniffing

Parameter Sniffing, SQL Server’da, bir stored procedure veya dinamik SQL sorgusu çalıştırıldığında kullanılan parametrelerin ilk değerine göre sorgu planının (execution plan) belirlenmesi olayıdır. Yani, SQL Server, ilk çalıştırılan parametre değerine dayanarak bir sorgu planı oluşturur ve bu plan sonrasında aynı prosedür veya sorgu başka parametrelerle çalıştırıldığında bu planı tekrar kullanır. Ancak, bazen bu durum, parametrelerin dağılımına bağlı olarak verimsiz bir sorgu planının oluşmasına sebep olabilir.

Parameter Sniffing Sorunu Nasıl Oluşur?

Bir stored procedure’de parametrelerin farklı değerlerle çalışması, SQL Server’ın her bir parametre için en uygun sorgu planını oluşturmasını gerektirir. Ancak SQL Server, ilk çalıştırma sırasında bir parametre değeri gördüğünde, bu değeri kullanarak bir execution planı oluşturur ve sonrasında bu planı saklar. Eğer sonraki çalıştırmalarda parametre değeri farklıysa, SQL Server bu planı kullanmaya devam eder. Bu durumda, yeni parametre değeri için sorgu planı verimsiz olabilir ve performans sorunları ortaya çıkabilir.

Parameter sniffing yaşandığının en büyük göstergesi, sistemde o an çalışan sorgular listelediğinde(sp_WhoIsActive’i kullanabilirsiniz.) en uzun süren sorguların aynı sorgulardan oluştuğunu görmektir. Ama bundan emin olmak için aşağıdaki yol izlenebilir.

sp_WhoIsActive’de ki text kolonunu alıp yeni bir session’a aktaralım. Sorguda gördüğümüz spesifik bir alanı belirleyip aşağıdaki scriptte ilgili yere ekleyelim.

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
and text like '%Sorgunuzdaki spesifik text'
ORDER BY cp.size_in_bytes DESC;

Plan cache üzerinden tüm planların görülmesi isteniyorsa aşağıdaki komut kullanılabilir.

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'

Yukarıdaki sorguyu çalıştırdığımızda, soruna neden olan sorgunun cache’de ki query plan değerini sıfırlayıp sorgunun yeniden query plan oluşturmasını sağlamak için, dönen değerlerden kendi sorgumuza ait plan_handle değerini aşağıdaki sorguda ilgili yere ekliyoruz.

DBCC FREEPROCCACHE (plan_handle kolonundaki değer);

Bu şekilde sorgunun yeniden query plan oluşturmasına izin vermiş oluyoruz. Bu işlemi gerçekleştirdikten sonra sistemde ki CPU kullanımı  bir süre sonra normale dönüyorsa bu sorguda parameter sniffing yaşadığımızı söyleyebiliriz. 

Parameter Sniffing Sorununun Örneği

Aşağıdaki stored procedure örneği üzerinden bu durumu inceleyelim. Bir önceki stored procedure makalesinde kullandığımız procedureler üzerinden örneklerimizi gerçekleştirelim.

CREATE PROCEDURE GetUsersByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT UserID, FirstName, LastName, Email
    FROM Users
    WHERE LastName = @LastName;
END;

Bu prosedür, LastName parametresi ile kullanıcıları filtreler. Diyelim ki:

1. İlk kez bu prosedür çalıştırıldığında, @LastName = ‘Yücel’ değeri ile çalıştırılsın.
2. SQL Server, bu parametre değeri için bir sorgu planı oluşturur ve bu planı belleğe alır.
3. Ancak, sonraki çalıştırmada @LastName = ‘Polat’ gibi farklı bir parametre kullanıldığında, SQL Server bu önceden oluşturulmuş sorgu planını kullanır. Eğer ‘Polat’ ismi çok az veri döndürüyorsa, bu plan önceki plandan daha verimli olabilir.

EXEC GetUsersByLastName @LastName = 'Yücel';
EXEC GetUsersByLastName @LastName = 'Polat';

Polat sadece 1 tane olmasına rağmen aynı execution plan yapısını kullanmış.

Parameter Sniffing Sorununun Çözülmesi Yapılabilecekler

1. OPTIMIZE FOR Önerisi Kullanma

SQL Server’da OPTION (OPTIMIZE FOR …) ifadesi ile sorgu planı oluşturulurken belirli bir parametre değeri göz önünde bulundurulabilir. Bu, parameter sniffing’in olumsuz etkilerini azaltabilir. Detaylı bilgi için ilgili makale okunabilir.

CREATE PROCEDURE GetUsersByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT UserID, FirstName, LastName, Email
    FROM Users
    WHERE LastName = @LastName
    OPTION (OPTIMIZE FOR (@LastName = 'Polat'));
END;

Bu çözümde, SQL Server her zaman ‘Polat’ değeri üzerinden bir sorgu planı oluşturur ve bu planı kullanır. Eğer parametre ‘Polat’ dışındaki bir değer olursa, yine aynı sorgu planı kullanılır, bu da performans açısından bazı durumlarda faydalı olabilir.

Gerçek sistemde SP’nin sonuna parameter sniffinge neden olan değişken için aşağıdaki gibi bir ekleme yaparak, bu parametre için gelen her değerde aynı query plan’ı oluşturmasını sağlayabilirsiniz.

OPTION(OPTIMIZE FOR (@parameter=UNKNOWN)) ekleyerek tek parametre için, ya da OPTION(OPTIMIZE FOR UNKNOWN) ekleyerek sorguda parameter sniffinge neden olabilecek tüm değerler için aynı query plan’ı oluşturmasını sağlayabilirsiniz.

SP’yi aşağıdaki şekilde değiştirdiğimizde query plan oluşturulurken histogramlara bakarak değil, density vector’e bakarak oluşturuluyor. Ve sorgu hangi parametreyle gelirse gelsin her defasında aynı query plan’ı kullanıyor.

ALTER PROCEDURE GetUsersByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT UserID, FirstName, LastName, Email
    FROM Users
    WHERE LastName = @LastName
    OPTION (OPTIMIZE FOR (@LastName UNKNOWN));
END;

2. Recompile (Yeniden Derleme) Kullanma

Stored procedure’de her çalıştırıldığında yeni bir sorgu planı oluşturulması için WITH RECOMPILE ifadesi kullanılabilir. Bu, parameter sniffing’in önüne geçer, ancak her seferinde yeni bir sorgu planı oluşturmak gerektiği için bazı durumlarda daha yüksek maliyetli olabilir.

CREATE PROCEDURE GetUsersByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    SELECT UserID, FirstName, LastName, Email
    FROM Users
    WHERE LastName = @LastName;
END

EXEC GetUsersByLastName @LastName = 'Polat' WITH RECOMPILE;

Bu yöntem, her çalıştırmada yeni bir sorgu planı oluşturur ve böylece parameter sniffing sorununu engeller. Ayrıca stored procedure her sorgu çalıştırıldığı zaman cpu’ya ekstradan bir yük getirecektir.

3. LOCAL Variables Kullanma

Bir diğer yaygın çözüm, parametreleri yerel değişkenlere atamaktır. Bu sayede SQL Server, parametreyi doğrudan kullanmak yerine yerel değişken üzerinden işlem yapar ve parametre sniffing’in önüne geçilmiş olur.

CREATE PROCEDURE GetUsersByLastName
    @LastName NVARCHAR(50)
AS
BEGIN
    DECLARE @LocalLastName NVARCHAR(50);
    SET @LocalLastName = @LastName;

    SELECT UserID, FirstName, LastName, Email
    FROM Users
    WHERE LastName = @LocalLastName;
END;

Burada, @LastName parametresi bir yerel değişken olan @LocalLastName’e atanır. SQL Server bu şekilde parametreyi yerel bir değişken üzerinden kullanır ve önceki değerin etkisiyle yanlış bir sorgu planı kullanılmaz.

4. Query Plan Caching’i Temizleme

Bazen, SQL Server’ın query plan cache’ini temizlemek ve yeni bir plan oluşturmasını sağlamak için DBCC FREEPROCCACHE komutunu kullanabilirsiniz. Ancak, bu yöntem genellikle önerilmez, çünkü bu komut tüm sorgu planlarını bellekten temizler ve veritabanı genelinde performans sorunlarına yol açabilir.

Özetle:

Parameter sniffing, SQL Server’ın parametre değerine göre oluşturduğu sorgu planının bazen farklı parametre değerleriyle verimsiz olmasına neden olabilen bir sorundur. Bu sorunu çözmek için farklı stratejiler uygulanabilir:

1. OPTION (OPTIMIZE FOR …) ile belirli bir parametre için sorgu planı optimize edilebilir.
2. WITH RECOMPILE ifadesi her çalıştırmada yeni bir sorgu planı oluşturulmasını sağlar.
3. Yerel değişkenler kullanmak, parameter sniffing’in etkilerini azaltabilir.

Query Store veya çalışıtığım ortamda görmüş olduğum parameter sniffing yapısını ele alalım.

İlk olarak bir tablo oluşturulur.

CREATE TABLE [dbo].[LRQ](
[total_elapsed_s] [decimal](10, 5) NULL,
[statement] [varchar](8000) NULL,
[date] [datetime] NULL,
[loginname] [varchar](50) NULL,
[programname] [varchar](8000) NULL
) ON [PRIMARY]

Yukarıda tablo oluşturduktan sonra aşağıdaki procedure yardımıyla  tablomuzun içerisine parameter sniffing olan sorguları tablomuza kaydetmiş olacağız.

CREATE PROCEDURE [dbo].[sp_ParameterSniffing]
AS
BEGIN
Declare @totalelapsed_s decimal(20,5),@plan_handle varbinary(64),@statement varchar(8000),@loginname varchar(50),@program varchar(8000)
dECLARE @TBL AS TABLE( [total_elapsed_s] [decimal](10, 5) NULL,
    plan_handle varbinary (max),
[statement] [varchar](max) NULL,
[date] [datetime] NULL,
[loginname] [varchar](100) NULL,
[programname] [varchar](8000) NULL )
INSERT INTO @TBL
Select top 100 
  totalelapsed_s=r.total_elapsed_time / 1000.0
, plan_handle=r.plan_handle
,statement=SUBSTRING(t.text, (r.statement_start_offset/2) + 1,  ((CASE statement_end_offset         WHEN -1 THEN DATALENGTH(t.text)        ELSE r.statement_end_offset END            - r.statement_start_offset)/2) + 1)
,[date]=null
,loginname=s.login_name,program=s.program_name from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
cross apply sys.dm_exec_query_plan(plan_handle) as cp
where s.program_name not like 'SQL Server Profiler%' AND s.program_name not like '%SQLAgent%' AND  r.session_id <> @@SPID  and r.blocking_session_id=0
AND t.text NOT LIKE '%WAITFOR%'
AND t.text  NOT LIKE '%BACKUP%'
aND r.total_elapsed_time / 1000.0>5
order by r.total_elapsed_time asc
 
DECLARE db_cursor CURSOR FOR
select [total_elapsed_s] ,plan_handle ,statement ,loginname ,[programname]  from  @TBL
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @totalelapsed_s ,@plan_handle ,@statement ,@loginname ,@program 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 DBCC FREEPROCCACHE (@plan_handle);
INSERT INTO  [AdventureWorks2014].[dbo].[LRQ]
Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
      FETCH NEXT FROM db_cursor INTO @totalelapsed_s ,@plan_handle ,@statement ,@loginname ,@program 
END
CLOSE db_cursor 
DEALLOCATE db_cursor
END
GO

Bu procedure özellikle parameter sniffing için değil, genel olarak performans sorunu yaratabilecek tüm uzun süren sorguları tespit etmek ve plan cache’ini temizlemek için kullanılır. Filtreler sadece Profiler ve SQLAgent gibi sistem sorgularını hariç tutuyor.

Eğer bu procedure’ü sadece parameter sniffing sorunlarını tespit etmek için kullanmak isterseniz, şu değişiklikleri yapabilirsiniz:

-- Mevcut WHERE koşullarına ek yapılabilir
WHERE ...
AND (t.text LIKE '%@%' OR t.text LIKE 'sp_%') -- Parametre veya stored proc arar
AND t.text NOT LIKE '%CREATE%' -- CREATE ifadelerini hariç tutar

Daha sonra bir job aracılığıyla sorgularımızı belirli aralıklarla çalıştırabiliriz.

DECLARE @tsql nvarchar(max)=''
SET @tsql = 'EXECUTE  [DBAMON].[dbo].[sp_ParameterSniffing] '
DECLARE @Counter tinyint=1
While (@Counter<10)
BEGIN
exec (@tsql)
WAITFOR DELAY '00:00:05';
SET @Counter=@Counter+1
END

Daha sonra ilgili tablomuza sniffing yaşayan sorgularımızı görebiliriz.

• total_elapsed_s: Sorgunun çalışma süresini saniye cinsinden gösterir (örneğin 50.186 saniye)
• statement: Çalıştırılan SQL ifadesinin tipi (SELECT, UPDATE, INSERT, DELETE vb.)
• date: Sorgunun çalıştırıldığı tarih ve saat
• loginname: Sorguyu çalıştıran uygulama/kullanıcı
• programme: Sorguyu çalıştıran program/istemci

Aşağıdaki komut yardımıyla sorgumuzun plan handle değerini bulabiliriz.

USE DB_NAME;
SELECT plan_handle,cp.usecounts,cp.objtype, st.text,DB_NAME(st.dbid) as [Database Name]
FROM sys.dm_exec_cached_plans as cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE  text LIKE N'Statement kısmındaki sorgumuz yazılmalıdır.%' OPTION (RECOMPILE);

Yukarıdaki kodumuzu ve dönen ifadeleri açıklamak gerekirse;

Hangi veritabanı üzerinde çalışacaksak ilgili veritabanının seçilmesi gerekmektedir.

plan_handle: Sorgu veya stored procedure yürütme planının benzersiz kimliği.
objtype: Planın türü. Örneğin, ‘Proc’ stored procedure için, ‘Adhoc’ ise doğrudan çalıştırılan sorgular içindir.
usecounts: İlgili sorgunun  kaç kez kullanıldığını gösterir.

Neden OPTION (RECOMPILE) Kullanılıyor?

Bu, sorgunun kendisinin önbelleğe alınmasını engeller ve daha doğru bir sonuç almanızı sağlar. Aksi halde, sorgunun planı da önbelleğe alınabilir ve yanlış ‘usecounts’ bilgisi gösterebilir.

Belirli bir sorgu planını temizlemek için:

DBCC FREEPROCCACHE(plan_handle);

Burada plan_handle, temizlenmek istenen sorgu planının kimliğidir.

Aşağıdaki ifade ile ilgili handle execution plan cache’den silinmektedir.

Yukarıda  yöntemler ve  son kısımda kod kısımları sorguların daha verimli çalışmasını sağlamak ve parameter sniffing’in olumsuz etkilerini ortadan kaldırmak için kullanılabilir.

Not: Procedure çalıştırıldıktan sonra execution planda bakıldığında default olarak hangi değerin kullanılıp kullanılmadığını anlarız.

Başka bir makalede görüşmek dileğiyle..

“Sãd. Öğüt ve uyarı dolu o şerefli Kur’an’a yemin olsun ki, tek kurtuluş yolu İslâm yoludur! Sâd -1“