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 ile ilgili detaylı bilgi almak için parameter sniffing makalesi okunabilir.
Parameter Sniffing olayının yaşandığını veya sorgularda yaşana bilme ihtimalini takip edeceğimiz bir procedure yapısının oluşturulması gerekmektedir. Bunun için aşağıdaki yapıyla oluşturulabilir.
İ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“
