SQL Server : Compilation et recompilation

12 janvier 2018

Commentaires fermés

Votre CPU est trop occupé ? Peut-être est-il monopolisé par des tâches de compilation/recompilation à répétition… Quelle est la différence entre compilation et recompilation ? Comment les provoquer et les détecter ? C’est bien ou mal finalement ? La réponse est comme à l’accoutumée “Ca dépend”, ce que nous verrons dans cet article.

Le processus de compilation calcule le plan d’exécution d’une requête ou d’un objet, soit le meilleur itinéraire possible et le monte en mémoire pour réutilisation. Ce plan d’exécution peut être remis en question au fils du temps. Deux évènements peuvent donc avoir lieu :
– Compilation : Elle a lieu quand un plan d’exécution n’est pas en cache (ex : jamais exécuté, sortie du cache par manque de mémoire).
– Recompilation : Quand le plan est déjà disponible en cache, le plan sera recompilé pour les raisons suivantes :

– Schéma modifié
– Statistiques modifiées
– Compilation différée
– Option SET modifiée
– Table temporaire modifiée
– Resordset distant modifié
– Autorisation FOR BROWSE modifiée
– Environnement de notification de requête modifié
– Vue partitionnée modifiée
– Options de curseur modifiées
– OPTION (RECOMPILE)

Que ce soit une compilation ou recompilation, cette opération a un coût CPU à relativiser dans certains cas. Sauf exception, la norme est de ne pas avoir un taux de compilations et recompilations qui excède de 10% de l’ensemble des batch SQL exécutés. Nous verrons plus tard comment le calculer.

Recompilation à la demande

Parlons un peu de l’exception, il y a des contextes où l’on peut avoir besoin de forcer la recompilation d’un objet ou d’une requête quand le parameter sniffing a des effets négatifs telles qu’une forte consommation d’I/O et un allongement de la durée d’exécution. En d’autres termes, quand la distribution des données n’est pas homogène, donc avec une cardinalité qui varie fortement suivant la valeur des paramètres en entrée, la recompilation peut être une solution. Avant de crier au loup “parameter sniffing”, assurez-vous que vos statistiques sont bien à jour. Sinon voici comment lancer une recompilation sur une requête ou un objet (procédure stockée, UDF ou autre) pour répondre à une problématique de parametter sniffing individuelle :

-----------------------------------------------
-- Recompilation d'une requête à l'exécution --
-----------------------------------------------

SELECT	*
FROM	TbleTest
WHERE	Col  = @param1
OPTION (RECOMPILE)

-------------------------------------------------------
-- Recompilation d'un objet à la prochaine exécution --
-------------------------------------------------------

EXEC sp_recompile 'dbo.StoredProcedureName'

--------------------------------------------
-- Recompilation d'un objet à l'exécution --
--------------------------------------------

EXEC  dbo.StoredProcedureName @param1 = 'bidule', @param2 = 'bidulette' WITH RECOMPILE

-----------------------------------------------

-- Recompilation d'une requête à l'exécution --

-----------------------------------------------

SELECT *

FROM TbleTest

WHERE Col = @param1

OPTION (RECOMPILE)

-------------------------------------------------------

-- Recompilation d'un objet à la prochaine exécution --

-------------------------------------------------------

EXEC sp_recompile 'dbo.StoredProcedureName'

--------------------------------------------

-- Recompilation d'un objet à l'exécution --

--------------------------------------------

EXEC dbo.StoredProcedureName @param1 = 'bidule', @param2 = 'bidulette' WITH RECOMPILE

Pour éviter la recompilation sur une requête, à partir de SQL Server 2008, envisagez l’option OPTIMIZE FOR UNKNOWN qui utilise le vecteur de densité. Le but est d’obtenir une estimation “générique” du nombre de lignes afin de stabiliser les temps d’exécution. Sur une version plus ancienne de SQL Server, une autre méthode consiste à utiliser des variables locales.

Pour répondre à une problématique de parametting sniffing généralisée, il est possible de vider le cache stockant les plans d’exécution pour une base de données spécifique (DBCC FLUSHPROCINDB) ou pour l’instance (DBCC FREEPROCCACHE).

---------------------------------------------------------------------------------------
-- Clean du cache stockant les plans d'exécution pour une base de données spécifique --
---------------------------------------------------------------------------------------

DECLARE @DbId INT

SELECT	@DbId = [dbid] 
FROM	master.dbo.sysdatabases 
WHERE	name = 'DatabaseName'

DBCC FLUSHPROCINDB (@DbId);

--------------------------------------------------------------------
-- Clean de l'intégralité du cache stockant les plans d'exécution -- 
--- PS : Cete méthode est réservée aux kamikaze du vendredi soir ---
--------------------------------------------------------------------

DBCC FREEPROCCACHE

---------------------------------------------------------------------------------------

-- Clean du cache stockant les plans d'exécution pour une base de données spécifique --

---------------------------------------------------------------------------------------

DECLARE @DbId INT

SELECT @DbId = [dbid]

FROM master.dbo.sysdatabases

WHERE name = 'DatabaseName'

DBCC FLUSHPROCINDB (@DbId);

--------------------------------------------------------------------

-- Clean de l'intégralité du cache stockant les plans d'exécution --

--- PS : Cete méthode est réservée aux kamikaze du vendredi soir ---

--------------------------------------------------------------------

DBCC FREEPROCCACHE

Le recours aux commandes DBCC bien que consommateur en CPU, le temps du calcul des plans, sera toujours plus acceptable que de redémarrer l’instance moteur. En clientèle, je ne compte plus le nombre de fois où j’ai entendu “Dès qu’on redémarre SQL Server, ça va beaucoup mieux !”. Il y a parfois du parametting sniffing derrière.

Collecte des compilations et recompilations via le perfmon

Comment détecter les compilations et recompilations et plus précisément celles qu’on a pas demandé ? 3 indicateurs, soit le nombre de compilations et recompilations comparé au nombre de batch SQL, peuvent être collectés depuis le perfmon dans la section SQL Statistics :

Collecte des compilations et recompilations via la DMV sys.dm_os_performance_counters

Les informations sont aussi disponibles en interrogeant la DMV sys.dm_os_performance_counters mais sous forme de cumule (cntr_type = 272696576).

SELECT	counter_name, cntr_value
FROM	sys.dm_os_performance_counters
WHERE	counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')

SELECT counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')

Pour restituer les valeurs non cumulées entre deux collectes dans un intervalle de 5 secondes, on peut procéder comme suit :

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

DECLARE @CntReport AS TABLE (
	[Collect Time] DATETIME2,
	[Batch Requests/sec] BIGINT, 
	[SQL Compilations/sec] BIGINT, 
	[SQL Re-Compilations/sec] BIGINT
)

INSERT	INTO @CntReport ([Collect Time], [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])
SELECT  GETDATE(), [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec]
FROM 
(
	SELECT	counter_name, cntr_value
	FROM	sys.dm_os_performance_counters
	WHERE	counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')
) AS t 
PIVOT
( 
SUM(cntr_value) 
FOR counter_name IN ([Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])
) AS p

WAITFOR DELAY '00:00:05'

INSERT	INTO @CntReport ([Collect Time], [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])
SELECT  GETDATE(), [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec]
FROM 
(
	SELECT	counter_name, cntr_value
	FROM	sys.dm_os_performance_counters
	WHERE	counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')
) AS t 
PIVOT
( 
SUM(cntr_value) 
FOR counter_name IN ([Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])
) AS p

SELECT	TOP 1 [Batch Requests/sec], 
		[SQL Compilations/sec],
		[SQL Compilations %] = ISNULL(CAST(CAST([SQL Compilations/sec] AS FLOAT) / [Batch Requests/sec] * 100 AS DECIMAL(18,2)),0),
		[SQL Re-Compilations/sec],
		[SQL Re-Compilations %] = ISNULL(CAST(CAST([SQL Re-Compilations/sec] AS FLOAT) / [Batch Requests/sec] * 100 AS DECIMAL(18,2)),0)
FROM	(
		SELECT	[Collect Time],
				[Batch Requests/sec] = [Batch Requests/sec] - LAG([Batch Requests/sec], 1, 0) OVER (ORDER BY [Collect Time]),
				[SQL Compilations/sec] = [SQL Compilations/sec] - LAG([SQL Compilations/sec], 1, 0) OVER (ORDER BY [Collect Time]),
				[SQL Re-Compilations/sec] = [SQL Re-Compilations/sec] - LAG([SQL Re-Compilations/sec], 1, 0) OVER (ORDER BY [Collect Time])
		FROM	@CntReport
		) c 
ORDER BY [Collect Time] DESC

SET ARITHABORT OFF

SET ANSI_WARNINGS OFF

DECLARE @CntReport AS TABLE (

[Collect Time] DATETIME2,

[Batch Requests/sec] BIGINT,

[SQL Compilations/sec] BIGINT,

[SQL Re-Compilations/sec] BIGINT

)

INSERT INTO @CntReport ([Collect Time], [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])

SELECT GETDATE(), [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec]

FROM

(

SELECT counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')

) AS t

PIVOT

(

SUM(cntr_value)

FOR counter_name IN ([Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])

) AS p

WAITFOR DELAY '00:00:05'

INSERT INTO @CntReport ([Collect Time], [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])

SELECT GETDATE(), [Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec]

FROM

(

SELECT counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name IN('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')

) AS t

PIVOT

(

SUM(cntr_value)

FOR counter_name IN ([Batch Requests/sec], [SQL Compilations/sec], [SQL Re-Compilations/sec])

) AS p

SELECT TOP 1 [Batch Requests/sec],

[SQL Compilations/sec],

[SQL Compilations %] = ISNULL(CAST(CAST([SQL Compilations/sec] AS FLOAT) / [Batch Requests/sec] * 100 AS DECIMAL(18,2)),0),

[SQL Re-Compilations/sec],

[SQL Re-Compilations %] = ISNULL(CAST(CAST([SQL Re-Compilations/sec] AS FLOAT) / [Batch Requests/sec] * 100 AS DECIMAL(18,2)),0)

FROM (

SELECT [Collect Time],

[Batch Requests/sec] = [Batch Requests/sec] - LAG([Batch Requests/sec], 1, 0) OVER (ORDER BY [Collect Time]),

[SQL Compilations/sec] = [SQL Compilations/sec] - LAG([SQL Compilations/sec], 1, 0) OVER (ORDER BY [Collect Time]),

[SQL Re-Compilations/sec] = [SQL Re-Compilations/sec] - LAG([SQL Re-Compilations/sec], 1, 0) OVER (ORDER BY [Collect Time])

FROM @CntReport

) c

ORDER BY [Collect Time] DESC

NB : les options SET ARITHABORT OFF & SET ANSI_WARNINGS OFF sont utilisées pour éviter l’erreur Division par zéro :

Divide by zero error encountered

Date de mise en cache des PS via les DMV sys.dm_exec_procedure_stats et dm_exec_query_stats

La date de mise en cache des procédures stockées et requêtes associées peut être obtenue par les requêtes suivantes :

USE [master]
GO

SELECT	TOP 10 
		DatabaseName = DB_NAME(ps.database_id),
		ObjectName = CONCAT(OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id), '.', OBJECT_NAME(ps.object_id, ps.database_id)), 
		CachedTime = ps.cached_time
FROM	sys.dm_exec_procedure_stats ps
ORDER BY cached_time DESC

SELECT 	TOP 10
		DatabaseName = qt.dbid,
		ObjectName =  CONCAT(OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid), '.', OBJECT_NAME(qt.objectid, qt.dbid)),
		[QueryStatement] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
				(CASE WHEN qs.statement_end_offset = -1 
					THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
					ELSE qs.statement_end_offset END -
						qs.statement_start_offset
				)/2
			),
		CachedTime = qs.creation_time	
FROM	sys.dm_exec_query_stats qs 
OUTER	APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

USE [master]

SELECT TOP 10

DatabaseName = DB_NAME(ps.database_id),

ObjectName = CONCAT(OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id), '.', OBJECT_NAME(ps.object_id, ps.database_id)),

CachedTime = ps.cached_time

FROM sys.dm_exec_procedure_stats ps

ORDER BY cached_time DESC

SELECT TOP 10

DatabaseName = qt.dbid,

ObjectName = CONCAT(OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid), '.', OBJECT_NAME(qt.objectid, qt.dbid)),

[QueryStatement] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END -

qs.statement_start_offset

)/2

CachedTime = qs.creation_time

FROM sys.dm_exec_query_stats qs

OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

Les procédures stockées utilisant l’option WITH RECOMPILE ne seront évidemment pas présentes. Pour vous en assurer, effectuez la recherche suivante :

USE DatabaseName
GO

SELECT	ObjectName = OBJECT_NAME(object_id)
FROM	sys.sql_modules
WHERE	is_recompiled = 1
ORDER BY OBJECT_NAME(object_id)

USE DatabaseName

SELECT ObjectName = OBJECT_NAME(object_id)

FROM sys.sql_modules

WHERE is_recompiled = 1

ORDER BY OBJECT_NAME(object_id)

Collecte des objets sujets à la recompilation via la DMV sys.dm_exec_query_stats

Au préalable à une trace profiler ou XEvent, comme nous le verrons plus tard, il est possible de recenser les requêtes et objets associés qui sont sujets à la recompilation en s’appuyant sur la DMV sys.dm_exec_query_stats et plus précisément sur la colonne plan_generation_num. Elle correspond à un numéro de séquence permettant de distinguer les instances de plans après une recompilation.

SELECT TOP 10 DatabaseName = DB_NAME(st.dbid)
	,ObjectName = OBJECT_NAME(st.objectid)
	,last_execution_time
	,qs.plan_generation_num
	,qs.execution_count
	,st.TEXT
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC

SELECT TOP 10 DatabaseName = DB_NAME(st.dbid)

,ObjectName = OBJECT_NAME(st.objectid)

,last_execution_time

,qs.plan_generation_num

,qs.execution_count

,st.TEXT

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

ORDER BY plan_generation_num DESC

Collecte des recompilations via le profiler

Au delà du nombre de recompilations et des objets recompilés identifiés, il peut être intéressant de les analyser plus finement en les associant avec l’instruction en cause avec la raison comme énuméré plus haut. On peut procéder de plusieurs manières, en utilisant le profiler et plus précisément l’événement SQL:StmtRecompile ou SP:Recompile :

Les colonnes en sortie qui nous intéressent sont les suivantes :

– EventSubClass : Cause de la recompilation
– TextData : Instruction concernée
– ObjectName : Nom de l’objet associé à l’instruction

Il est recommandé d’appliquer des filtres en amont pour éviter de recenser un nombre d’événements de recompilation trop important.

La requête suivante permet d’obtenir le référentiel des raisons de recompilations au besoin :

SELECT      sv.subclass_name,
            sv.subclass_value
FROM        sys.trace_events AS e,
            sys.trace_subclass_values AS sv
WHERE       e.trace_event_id = sv.trace_event_id
AND         e.name = 'SQL:StmtRecompile'
AND         sv.subclass_value  < 1000
ORDER BY    sv.subclass_value;

SELECT sv.subclass_name,

sv.subclass_value

FROM sys.trace_events AS e,

sys.trace_subclass_values AS sv

WHERE e.trace_event_id = sv.trace_event_id

AND e.name = 'SQL:StmtRecompile'

AND sv.subclass_value < 1000

ORDER BY sv.subclass_value;

Collecte des recompilations via XEvent

Le profiler est supposé être déprécié à terme. Il est bien évidemment possible de collecter les mêmes informations en créant une trace XEvent. Dans la définition de la session ci-dessous, bien qu’il ait été commenté, un filtre est présent pour ne retenir les recompilations occasionnées sur les objets TempTableProc et VarTempTableProc.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Recompiles')  
    DROP EVENT session Recompiles ON SERVER;  
GO

CREATE EVENT SESSION [Recompiles] ON SERVER 
ADD EVENT sqlserver.sql_statement_recompile(SET collect_object_name=(1),collect_statement=(1)
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    --WHERE ([object_name]=N'TempTableProc' OR [object_name]=N'VarTempTableProc')
	)
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Recompiles',max_file_size=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [Recompiles] ON SERVER STATE = START
GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Recompiles')

DROP EVENT session Recompiles ON SERVER;

CREATE EVENT SESSION [Recompiles] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(SET collect_object_name=(1),collect_statement=(1)

ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)

--WHERE ([object_name]=N'TempTableProc' OR [object_name]=N'VarTempTableProc')

)

ADD TARGET package0.event_file(SET filename=N'C:\Temp\Recompiles',max_file_size=(10))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

ALTER EVENT SESSION [Recompiles] ON SERVER STATE = START

Voici comment explorer la trace XEvent :

DECLARE	@SessionName SYSNAME = 'Recompiles',
		@Target_File NVarChar(1000), 
		@Target_Dir NVarChar(1000), 
		@Target_File_WildCard NVarChar(1000)

SELECT	@Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM	sys.dm_xe_session_targets t
INNER	JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE	s.name = @SessionName
AND		t.target_name = 'event_file'

SET @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) 

SET @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'

IF OBJECT_ID('tempdb..#Events') IS NOT NULL
	DROP TABLE #Events

IF OBJECT_ID('tempdb..#Queries') IS NOT NULL
	DROP TABLE #Queries 

SELECT  CAST(event_data AS XML) AS event_data_XML
INTO	#Events
FROM	sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
ORDER BY File_name DESC, file_offset DESC 

SELECT  EventType = event_data_XML.value('(event/@name)[1]', 'varchar(50)')
	, ObjectName = event_data_XML.value ('(/event/data  [@name=''object_name'']/value)[1]', 'sysname')
	, ObjectType = event_data_XML.value ('(/event/data  [@name=''object_type'']/text)[1]', 'sysname')
	, UserName = event_data_XML.value ('(/event/action  [@name=''username'']/value)[1]', 'sysname')
	, Statement_Text = ISNULL(ISNULL(event_data_XML.value ('(/event/data  [@name=''statement'']/value)[1]', 'NVARCHAR(4000)'), event_data_XML.value ('(/event/data  [@name=''batch_text''     ]/value)[1]', 'NVARCHAR(4000)')), event_data_XML.value ('(/event/data[@name=''wait_type'']/text)[1]', 'NVARCHAR(60)'))
	, Recompile_Cause = event_data_XML.value ('(/event/data  [@name=''recompile_cause'']/text)[1]', 'sysname')
	, TimeStamp = DateAdd(Hour, DateDiff(Hour, GetUTCDate(), GetDate()) , CAST(event_data_XML.value('(event/@timestamp)[1]', 'varchar(50)') as DateTime2))
	, SPID = event_data_XML.value ('(/event/action  [@name=''session_id'']/value)[1]', 'BIGINT')
	, Database_Name = DB_Name(event_data_XML.value ('(/event/action  [@name=''database_id'']/value)[1]', 'BIGINT'))
	, EventDetails = event_data_XML 
INTO #Queries
FROM #Events

SELECT q.EventType
	, q.ObjectType
	, q.ObjectName
	, q.Statement_Text
	, q.Recompile_Cause
	, q.TimeStamp
	, q.SPID
	, q.UserName
	, q.Database_Name
	, q.EventDetails
FROM	#Queries q
--WHERE	q.ObjectName in ('TempTableProc', 'VarTempTableProc')
ORDER BY TimeStamp DESC

DECLARE @SessionName SYSNAME = 'Recompiles',

@Target_File NVarChar(1000),

@Target_Dir NVarChar(1000),

@Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address

WHERE s.name = @SessionName

AND t.target_name = 'event_file'

SET @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))

SET @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'

IF OBJECT_ID('tempdb..#Events') IS NOT NULL

DROP TABLE #Events

IF OBJECT_ID('tempdb..#Queries') IS NOT NULL

DROP TABLE #Queries

SELECT CAST(event_data AS XML) AS event_data_XML

INTO #Events

FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F

ORDER BY File_name DESC, file_offset DESC

SELECT EventType = event_data_XML.value('(event/@name)[1]', 'varchar(50)')

, ObjectName = event_data_XML.value ('(/event/data [@name=''object_name'']/value)[1]', 'sysname')

, ObjectType = event_data_XML.value ('(/event/data [@name=''object_type'']/text)[1]', 'sysname')

, UserName = event_data_XML.value ('(/event/action [@name=''username'']/value)[1]', 'sysname')

, Statement_Text = ISNULL(ISNULL(event_data_XML.value ('(/event/data [@name=''statement'']/value)[1]', 'NVARCHAR(4000)'), event_data_XML.value ('(/event/data [@name=''batch_text'' ]/value)[1]', 'NVARCHAR(4000)')), event_data_XML.value ('(/event/data[@name=''wait_type'']/text)[1]', 'NVARCHAR(60)'))

, Recompile_Cause = event_data_XML.value ('(/event/data [@name=''recompile_cause'']/text)[1]', 'sysname')

, TimeStamp = DateAdd(Hour, DateDiff(Hour, GetUTCDate(), GetDate()) , CAST(event_data_XML.value('(event/@timestamp)[1]', 'varchar(50)') as DateTime2))

, SPID = event_data_XML.value ('(/event/action [@name=''session_id'']/value)[1]', 'BIGINT')

, Database_Name = DB_Name(event_data_XML.value ('(/event/action [@name=''database_id'']/value)[1]', 'BIGINT'))

, EventDetails = event_data_XML

INTO #Queries

FROM #Events

SELECT q.EventType

, q.ObjectType

, q.ObjectName

, q.Statement_Text

, q.Recompile_Cause

, q.TimeStamp

, q.SPID

, q.UserName

, q.Database_Name

, q.EventDetails

FROM #Queries q

--WHERE q.ObjectName in ('TempTableProc', 'VarTempTableProc')

ORDER BY TimeStamp DESC

Conclusion

Reste à optimiser les requêtes identifiées pour réduire le nombre de recompilations. Typiquement :
– Evitez les opérations DDL post création (ex : ajout de colonnes à la volée)
– Eliminez les DELETE sur des tables ##temp et #temp, filtrez plutôt en amont
– Sur une faible cardinalité, remplacez les tables ##temp et #temp par des variables table
– Soyez le plus fin possible dans vos ordres de recompilation
– Etc

Pour rappel :

https://www.concatskills.com/2017/12/10/tables-tempoaires-variable-table/

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Configurer Database Mail avec Gmail

Scripter la création et suppression des contraintes (ex : FK)

sp_whoisactive : Processus actifs sur une instance SQL Server

Erreur : Transaction log for database 'xxx' is full due to 'ACTIVE_TRANSACTION'

Blog