Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan”

Petit post rapide sur mon problème du matin: une erreur 8623 récurrente sur une instance:

Error: 8623, Severity : 16, State : 1.
The query processor ran out of internal resources and could not produce a query plan. 
This is a rare event and only expected for extremely complex queries or queries that 
reference a very large number of tables or partitions. Please simplify the query. If you 
believe you have received this message in error, contact Customer Support Services for more 
information.

On veut savoir quelle est la requête qui génère ce problème, on est en SQL Server 2008R2. On propose d’utiliser les xevents.

Création de la session XE sur SQL Server 2008R2:

CREATE EVENT SESSION catch_8623 ON SERVER
ADD EVENT sqlserver.error_reported (
	ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.tsql_stack)
	WHERE ERROR = 8623
)
ADD TARGET package0.asynchronous_file_target 
(SET filename = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\catch_8623.xel') 
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS) ;
GO 

ALTER EVENT SESSION catch_8623 ON SERVER
STATE = START;
GO

Sur SQL Server 2012+ on utilisera plutôt le target event_file:

CREATE EVENT SESSION catch_8623 ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE ERROR = 8623) 
ADD TARGET package0.event_file 
(SET filename = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\catch_8623.xel')
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS) ;

Inspecter le contenu de la trace:

On peut vérifier via xp_readerrorlog si l’erreur est de nouveau survenue ou avec une alerte de l’agent SQL par exemple. Lorsque c’est le cas on peut aller vérifier le contenu du fichier XEL après avoir stoppé la trace:

ALTER EVENT SESSION catch_8623 ON SERVER
STATE = STOP;
GO

SELECT 
    n.value('(@name)[1]', 'varchar(50)') AS event_name,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="error"]/value)[1]', 'int') as error,
    n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
    n.value('(data[@name="duration"]/value)[1]', 'int') as state,
    n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
    n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message,
	db_name(n.value('(action[@name="database_id"]/value)[1]', 'varchar(max)')) as database_name,
	n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM 
(SELECT
    CAST(event_data AS XML) AS event_data
 FROM sys.fn_xe_file_target_read_file('E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\catch_8623_0_131088100900560000.xel', 
'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\catch_8623_0_131088100900560000.xem', null, null)
) as tab
CROSS APPLY event_data.nodes('event') as q(n) ;
GO

 

Un référence officielle de cette erreur indique que le cas peut se produire lorsqu’il existe un trop grand nombre de valeurs passées dans une IN-list. C’est le cas ici:

SELECT 
    [Project7].[C1] AS [C1], 
    [Project7].[C2] AS [C2], 
    [Project7].[C3] AS [C3], 
    [Project7].[C4] AS [C4], 
    [Project7].[C5] AS [C5], 
    [Project7].[C6] AS [C6], 
    [Project7].[C7] AS [C7], 
    [Project7].[C8] AS [C8], 
    [Project7].[C9] AS [C9], 
    [Project7].[C10] AS [C10], 
    [Project7].[C11] AS [C11], 
    [Project7].[C12] AS [C12], 
    [Project7].[C13] AS [C13], 
    [Project7].[C14] AS [C14], 
    [Project7].[C15] AS [C15], 
    [Project7].[C16] AS [C16]
    FROM   (SELECT 
        [GroupBy1].[K1] AS [C1]
        FROM ( SELECT 
            [Project1].[C1] AS [K1]
            FROM ( SELECT 
                [Extent1].[tabledebase] AS [C1]
                FROM [dbo].[mabase] AS [Extent1]
            )  AS [Project1]
 WHERE ([Project1].[C1] IN ('005774303'
 '004954729'
 '006910515'
 '006726894'
   
(...) -- ~= 2450  lignes plus loin ... 

 '005533347'
 '006165593'
 '004386776'
 '003083548')

2456 valeurs différentes. Autant passer par une table temporaire et faire une jointure.

Je ressors de sous les fagots cet article de Conor sur l’implémentation du IN en fonction du nombre de prédicats passés dans la clause.

A+

Continuez votre lecture sur le blog :

• Déterminer la fréquence horaire d’exécution d’une procédure stockée sous SQL Server (Vincent Delabre) [SQL Server]
• How-To : réduire l’enveloppe de tempdb (David Baffaleuf) [SQL Server]
• Pourquoi il faut sauvegarder les bases systèmes (David Baffaleuf) [SQL Server]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Vincent Delabre) [SQL Server]
• Retrouver une transaction en échec (David Baffaleuf) [SQL ServerVintage]