Stored Procedure’lerin aniden yavaşlaması, SQL Server veritabanı yöneticileri (DBA) ve yazılım geliştiricilerin karşılaştığı en yaygın performans sorunlarından biridir. Yukarıdaki T-SQL betiği, ilgili prosedürün içerdiği sorguların geçmiş performans verilerini çıkararak darboğazları tespit etmenize yardımcı olur.
Bu script’i kullanabilmemiz için veritabanı altında Query Store özelliğinin açılması gerekmektedir. Hangi procedure üzerinde sıkıntı yansıdığına inanılıyorsa @ProcedureName kısmına stored procedure’ün yazılması gerekmektedir.
DECLARE @ProcedureName nvarchar(256) = N'dbo.sp_KLNSRGTIPSRGSYSY';
SELECT
q.query_id,
p.plan_id,
CASE
WHEN p.is_forced_plan = 1 THEN 'FORCED'
WHEN p.plan_id = latest.LatestPlanId THEN 'CURRENT/LATEST'
ELSE 'OLD'
END AS PlanStatus,
p.is_forced_plan,
rs.last_execution_time,
rs.count_executions AS ExecutionCount,
CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS AvgDuration_ms,
CAST(rs.min_duration / 1000.0 AS decimal(18,2)) AS MinDuration_ms,
CAST(rs.max_duration / 1000.0 AS decimal(18,2)) AS MaxDuration_ms,
CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS AvgCPU_ms,
CAST(rs.avg_logical_io_reads AS bigint) AS AvgLogicalReads,
CAST(rs.avg_logical_io_writes AS bigint) AS AvgLogicalWrites,
CAST(rs.avg_physical_io_reads AS bigint) AS AvgPhysicalReads,
CASE
WHEN p.plan_id = fastest.FastestPlanId
THEN '*** FASTEST PLAN ***'
ELSE ''
END AS Performance,
qt.query_sql_text,
p.force_failure_count,
p.last_force_failure_reason_desc
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN
(
SELECT
plan_id,
SUM(count_executions) AS count_executions,
MAX(last_execution_time) AS last_execution_time,
SUM(count_executions * avg_duration)
/ NULLIF(SUM(count_executions),0) AS avg_duration,
MIN(min_duration) AS min_duration,
MAX(max_duration) AS max_duration,
SUM(count_executions * avg_cpu_time)
/ NULLIF(SUM(count_executions),0) AS avg_cpu_time,
SUM(count_executions * avg_logical_io_reads)
/ NULLIF(SUM(count_executions),0) AS avg_logical_io_reads,
SUM(count_executions * avg_logical_io_writes)
/ NULLIF(SUM(count_executions),0) AS avg_logical_io_writes,
SUM(count_executions * avg_physical_io_reads)
/ NULLIF(SUM(count_executions),0) AS avg_physical_io_reads
FROM sys.query_store_runtime_stats
GROUP BY plan_id
) AS rs
ON rs.plan_id = p.plan_id
OUTER APPLY
(
SELECT TOP (1)
p2.plan_id AS LatestPlanId
FROM sys.query_store_plan p2
WHERE p2.query_id = q.query_id
ORDER BY p2.last_execution_time DESC
) AS latest
OUTER APPLY
(
SELECT TOP (1)
p3.plan_id AS FastestPlanId
FROM sys.query_store_plan p3
INNER JOIN sys.query_store_runtime_stats rs3
ON rs3.plan_id = p3.plan_id
WHERE p3.query_id = q.query_id
GROUP BY p3.plan_id
ORDER BY
SUM(rs3.count_executions * rs3.avg_duration)
/ NULLIF(SUM(rs3.count_executions),0)
) AS fastest
WHERE q.object_id = OBJECT_ID(@ProcedureName)
ORDER BY
q.query_id,
AvgDuration_ms ASC;
Görseldeki çıktı, prosedürün farklı query_id değerlerine (içindeki farklı SQL cümlelerine) ait istatistiklerini sunar.
FORCED Planlar:
- PlanStatus = FORCED ve is_forced_plan = 1 ifadesi, bu sorgular için DBA tarafından belirli bir yürütme planının zorla kullandırıldığını gösterir.
- Satır 1 (query_id: 1932379) ortalama 0.25 ms sürmüş ve 238 kez çalışmış. Oldukça hızlı ve verimli bir plandır.
Satır 2 (query_id: 1932386): Ortalama süre 180,829 ms (~3 dakika), maksimum süre ise 3,778,110 ms (~63 dakika)! Mantıksal okuma (AvgLogicalReads) ise 1.45 milyon seviyesindedir. 237 kez çalışan bu sorgu sistem kaynaklarını tüketmektedir.
Satır 5 ve 6 (query_id: 2145930, 2146209): Sırasıyla ortalama 69 saniye ve 32 saniye süren ve her çalıştığında ~577 bin mantıksal okuma yapan diğer ağır sorgulardır.
Bu script’i aşağıdaki senaryolarda doğrudan bir teşhis aracı olarak kullanabilirsiniz.
- Bir Stored Procedure daha önce milisaniyeler içinde çalışırken aniden dakikalar sürmeye başladıysa, SQL Server Optimizer yeni (ve kötü) bir plan oluşturmuş olabilir. Bu script sayesinde PlanStatus sütunundan aktif planın ne kadar yavaş olduğunu (CURRENT/LATEST) görebilirsiniz.
- Sorgu aynı olmasına rağmen gönderilen parametre tipine göre SQL Server bazen Index Seek yerine Index Scan tercih edebilir. Script çıktısındaki MinDuration_ms ile MaxDuration_ms arasındaki devasa farklar (Örn: Satır 2’deki 3.4 saniye ile 63 dakika arasındaki fark) açık bir Parameter Sniffing göstergesidir.
- Veritabanında daha önce performansı düzeltmek için sabitlenen (FORCED) bir plan olup olmadığını ve bu zorlanmış planın hala verimli çalışıp çalışmadığını doğrulamak için kullanılır.
- Prosedür devasa bir T-SQL bloğuna sahip olabilir. Bu script sayesinde yüzlerce satırlık prosedürün tam olarak hangi iç sorgusunun (query_id) CPU ve Disk I/O tüketimine yol açtığını (Satır 2, 5 ve 6 gibi) anında filtreleyebilirsiniz.
Sorun olduğunun farkındaysanız ilgili query id değerinin incelenmesi gerekmektedir. Query store altında ilgili plan id bulunup execution plan yapısı incelenebilir. Veritabanı altında query store bölümünde ilgili sorguyu bulup yeni bir plan force edilmesi gerekmektedir. 3. bir seçenek Parameter Sniffing söz konusu ise prosedür içerisinde OPTION (RECOMPILE) veya OPTION (OPTIMIZE FOR UNKNOWN) ipuçlarını değerlendirilmesi gerekmektedir.
Aşağıdaki komut ile query_id değerini yazdığınızda ilgili plan ile ilgili execution yapısının vermektedir.
DECLARE @TargetQueryID INT = 1932386; -- Buraya aradığınız query_id değerini yazın
SELECT
q.query_id,
p.plan_id,
p.is_forced_plan,
p.last_execution_time,
-- Tıklandığında SSMS üzerinde grafiksel planı açar:
CAST(p.query_plan AS XML) AS GraphicalQueryPlan,
qt.query_sql_text
FROM sys.query_store_query q
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
WHERE q.query_id = @TargetQueryID
ORDER BY p.last_execution_time DESC;Bu makalede query store özelliği açık bir veritabanında store procedure yapısının incelenmesini ve hangi aksiyonların alınması gerektiğini ele almış olduk. Başka makalede görüşmek dileğiyle..
“Sakın Dünya Hayatı Sizi Aldatmasın” Fatır-5
