Parameter Sniffing
Parameter Sniffing; kullandığımız stored procedure’lerin, en son compile edilmiş hali için gelen parametrelere göre oluşturulan query plan’ının cache’de tutularak, başka parametlerle geldiğinde yeniden query plan oluşturmadan cache’deki query plan’ın kullanılması sonucu oluşur. Kısaca stored procedure en son compile edildiğinde gelen parametreye göre bir query plan oluşturulduğunda bu query plan gelecek bir sonraki parametre için uygun olmayabilir. Bu durumda stored procedure uygun olmayan bir query plan ile çalışıp beklenenden çok daha uzun sürebilir. Bu durum Stored procedure lerde olduğu gibi sp_executesql komutu parametreli olarak gönderildiğinde ve linq gibi parametrik kod generate eden başka teknolojilerde de meydana gelebilir.
Bazen sistemlerinizde ani bir yoğunlaşma fark edebilirsiniz. CPU’nun ortalama kullanımı %20 lerdeyken birden %100 lerde seyretmeye başlayabilir. Bazen bu başınıza geldiğinde acaba sistemi çok yoğun mu kullanıyorlar, rapor mu çekiyorlar diye düşünebilirsiniz. Tabi bunlar olabileceği gibi parameter siniffing de yaşıyor olabilirsiniz. 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;
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.
Peki parameter sniffing olmasını nasıl engelleyebiliriz?
Bu sorunun birden fazla çözümü var. Öncelikle verinin dağılımını ve gelen parametrelerin ağırlıklı olarak nasıl geldiğini biliyorsanız aşağıdaki gibi bir çözüm uygulayabilirsiniz.
Çözüm 1: Örneğin stored procedure, parametre olarak %99 oranında x değerini alıyorsa ve en son compile edildiğinde y değerine göre compile edilmişse, stored procedure’ü x değeriyle her çağırdığımızda yanlış query planla çalışacak ve parameter sniffing oluşacaktır. Böyle bir durumda sp’ye aşağıdaki şekilde bir hint eklediğimizde bu sorunu çözmüş oluruz. Sp’ye bu hinti koyduğumuzda, query plan x değerine göre oluşacaktır. Tabi bu çözüm çok spesifik durumlarda ihtiyaç duyulabilir.
SELECT *
FROM TableXXX
WHERE Kolon_A=@Param
OPTION(OPTIMIZE FOR (@Param=x))
Çözüm 2: SP’yi her defasında recompile etmek için aşağıdaki hinti ekleyebiliriz. Ama bu çözümde, sp her defasında recompile olacağı için CPU’ya gereksiz bir yük getirecektir.
SELECT *
FROM TableXXX
WHERE Kolon_A=@Param
OPTION(RECOMPILE)
Çözüm 3: Bu çözüm şu ana kadar kendi sistemlerimde benim kullandığım çözüm. 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(OPTIMIZEFOR (@parameter=UNKNOWN)) ekleyerek tek parametre için, ya da OPTION(OPTIMIZEFOR UNKNOWN) ekleyerek sorguda parameter sniffinge neden olabilecek tüm değerler için aynı query plan’ı oluşturmasını sağlayabilirsiniz.
Detaylı bir örnekle bu konuya açıklık getirelim.
Yeni bir session açıp aşağıdaki script yardımıyla stored procedure’ümüzü oluşturalım.
CREATE PROCEDURE PS (@param int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @param
Oluşturduğumuz bu sp’yi çalıştırmadan önce aşağıdaki resimde görüldüğü gibi include actual execution plan’a tıklayalım. Bu şekilde sorgu çalıştıktan sonra execution plan’ınıda bize verecektir.
Sorguyu çalıştırdığımızda aşağıdaki gibi bir sonuç elde ediyoruz.Gördüğünüz gibi Actual number of rows ve estimated number of rows aynı.
Sp’yi bu şekilde oluşturup çalıştırdığımızda Index’e ait histogramı kullanarak query plan’ı oluşturdu. Normal şartlar altında query plan’ı en iyi şekilde çıkartmak histogramla mümkündür. Şimdi Index’e ait histogramı inceleyelim. Aşağıdaki sorguyu çalıştırdığımızda RANGE_HI_KEY’i 709 olan kaydın karşılığındaki EQ_EOWS sütununda 188 değerini görüyoruz.
DBCC SHOW_STATISTICS('Sales.SalesOrderDetail', 'IX_SalesOrderDetail_ProductID') WITH HISTOGRAM
Ama konunun başında değindiğimiz gibi bazen parameter sniffing ile karşılaşabilirz. Sp’nin derlendiği anda gelen parametreye göre üretilen ilk query plan histogram kullanılarak en iyi şekilde üretilmiş olsa da, sonra gelecek bazı parametrelere göre çok kötü olabilir.
Bunu engellemek için sp’yi aşağıdaki şekilde değiştirelim. 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.
USE [AdventureWorks]
GO
ALTER PROCEDURE [dbo].[PS] (@param int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @param
OPTION (OPTIMIZE FOR (@param UNKNOWN))
Ve sp’yi yeniden çalıştıralım. Aşağıda görüldüğü gibi estimated number of rows 456 iken, actual number of rows 188.
Bu tekniği daha önce birkaç defa kullandım. Ve kullanıp faydasını gören birkaç arkadaşım da var. Fakat her zaman işe yarayacağının garantisi yok. Yukarıdaki örnekte de görüldüğü gibi actual number of rows 188 iken estimated number of rows 456 olarak hesaplandı. Density vector kullanılarak oluşturulan query plan, histogramlar kullanılarak oluşturulan query plan’a göre daha az sağlıklı olsa da bir çok senaryoda parameter sniffing’i engellediğini söyleyebilirim. Yukarıdaki örnekte 709 numaralı ProductId için histograma göre kötü bir değer üretmiş gibi gözükse de, 708 numaralı ya da 707 numaralı ProductId ile çalıştığında,709 numaralı parametre ile çalışıp histogramlardan faydalanarak üretilen query plan’a göre daha iyi bir query plan ile çalışmış olacaktır. Verinin dağılımı, hangi parametrenin ne sıklıkla geldiği ve objenin büyüklüğü gibi faktörler bu özelliğin faydalı olup olmayacağını belirler . Bu özelliği production ortamında uygulamadan önce mutlaka test etmelisiniz. Testi sanal bir yük oluşturarak yapabilirsiniz. İstatistiklere ait density vector ve histogram’ı daha detaylı incelemek isterseniz aşağıdaki iki makalede bulabilirsiniz.
http://www.practicalsqldba.com/2013/06/sql-server-part1-all-about-sql-server.html
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/06/27/sql-server-part-2-all-about-sql-server-statistics-histogram/