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. Bu tür bilgilerle tanıştıktan sonra, Desura hizmetlerini, ürünlerini veya haberlerini ilerletmek için oyunlaştırmadan yararlanma konusunda ön saflarda yer aldığı açıkça görülüyor. Etkileşimli oyun öğelerini birleştirerek, çeşitli tüketicilerde yankı uyandıran dinamik ve büyüleyici bir ortam yaratırlar.

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/

Bir müşterimde parameter sniffing problemi farklı sorgularda çok fazla yaşanıyordu.

Arka planda sürekli çalışan ve 1 saniyeden uzun süren sorgulara ait queryplan’ı sıfırlayan bir job ile sorunu çözebilirsiniz.

QueryStore veya veri tabanı bazlı ParameterSniffinf parametresi ile de bu sorunu çözebilirsiniz.

Ben bu yöntemi kullanarak bu sorunu aştığım için paylaşmak istedi.

Bu Parameter sniffing’in Önce aşağıdaki gibi bir tablo create ediyoruz.

USE [DBA]
GO

/****** Object: Table [dbo].[LRQ] Script Date: 9.4.2021 09:42:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[LRQ] ADD CONSTRAINT [DF_LRQ_date] DEFAULT (getdate()) FOR [date]
GO

Daha sonra aşağıdaki script’i çalıştıracak ve her 1 dakikada bir kez çalışacak bir job create ediyoruz.

Declare @totalelapsed_s decimal(20,5),@plan_handle varbinary(64),@statement varchar(8000),@loginname varchar(50),@program varchar(8000)

Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND s.program_name<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END
WAITFOR DELAY '000:00:05'
Select @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),@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<>'SQLAgent - TSQL JobStep (Job 0xDC52C6926D6EAE4FA198DAF1A19BD734 : Step 1)' AND r.session_id <> @@SPID and r.blocking_session_id=0 AND 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) NOT LIKE '%WAITFOR%' AND 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) NOT LIKE '%BACKUP%'
order by r.total_elapsed_time asc
IF(@totalelapsed_s>1)
BEGIN
INSERT INTO DBA.dbo.LRQ Select @totalelapsed_s,@statement,GETDATE(),@loginname,@program
DBCC FREEPROCCACHE (@plan_handle);
END