Sql Server Error 8623: The query processor ran out of internal resources and could not produce a query plan

Question

Sql Server Error 8623: The query processor ran out of internal resources and could not produce a query plan

Navigation

#1 by (4 votes)
#2 by (3 votes)

3

Bank Sql Server 2012 and 2008.

I have been logged into the following error (sql server 2012):

error_reported  2017-12-27 09:14:16.2053050 8623    16  1   False   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.    

SELECT DISTINCT COUNT(e.id_evento) AS count
FROM syo_evento AS e
INNER JOIN syo_encaminhamento AS en ON en.id_evento = e.id_evento 
WHERE en.id_empresa IN (10)
  AND e.id_evento IN (SELECT i.id_evento 
                      FROM syo_interesse AS i
                        INNER JOIN syo_modeloversao ON id_modeloversao = CAST(i.ds_modelo AS INT)
                      WHERE i.ds_modelo != 'INDIFERENTE'
                        AND i.id_interesse = (SELECT MAX(id_interesse) FROM syo_interesse WHERE id_evento = i.id_evento)
                        AND id_modelo = 'STRADA')
                        AND (e.dt_limite <= 1514426360999 OR e.dt_proximaacao <= 1514426360999)
                        AND en.id_statusagenteativo = 'S'
                        AND e.id_evento IN (SELECT i.id_evento FROM syo_interesse AS i WHERE i.ds_marca = 'FIAT' AND i.id_interesse = (SELECT MAX(id_interesse) FROM syo_interesse WHERE id_evento = i.id_evento))
                        AND e.id_evento IN (5964767, 6377920, 6343493, 6343495, 6377927, 6060736, 6343496, 6377935, 6343498, 6343499, 6343509, 6377938, 6184147, 6343510, 6377940, 6377941, 6343504, 6377942, 6343507, 6343506, 6343517, 6377944, 6343512, 6343515, 6377951, 6343514, 6377953, 5999229, 6343523, 6377957, 6343532, 6377960, 6377967, 6343530, 6343531, 
<frame level='1' handle='0x02000000B4EC8B31D255052CB504E32C1088771073F1F154' line='1' offsetStart='0' offsetEnd='0'/> <frame level='2' handle='0x000000000000000000000000000000000000000000000000' line='1' offsetStart='0' offsetEnd='0'/>

The above query is incomplete because the log has a character limit, I have reduced it further because of the StackOverflow limit but only in the IN part of the parameter, the query in the log (incomplete) has about 3550 parameters, but in the system I have seen searches with many more (hundreds of thousands), another detail is that it occurs 37 times, in a period between 09:14 and 11:32 in the morning.

My question is, is this problem caused solely by the number of parameters in the IN clause or may it have been aggravated by other queries?

For example, a heavy, multi-character query that takes a few seconds to execute, will the resources consumed by this large query affect the features of this other query that was run after or at the same time?

That is, in the case of this error the resources are divided or individual between the queries?

sql sql-server

asked by anonymous 28.12.2017 / 21:25

2 answers

3

First, the amount you mentioned in the IN clause is not necessarily the main cause of the problem, but obviously the higher the amount, the slower the query. If the amount was > 10k could be a good indicator for your problem, I've had past experiences with this type of select.

See:

Yes, it is quite possible that your query has been aggravated by other "heavy" queries running at the same time, consuming a lot of the machine where your bank is allocated. Have you tried to run this query at a time that does not conflict with other queries? One suggestion, you can use SQL Server profiler to monitor your bank transactions in real time

And lastly, yes, if more than one transaction tries to access the same resource at the same time, depending on the configuration of your database (Read Level), this can lead to a deadlock. For this I separated two good articles on the subject:

Detecting and Closing Deadlocks

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Some suggestions about your query, it would not be possible to replace INDIFFERENT in the excerpt: WHERE i.ds_modelo != 'INDIFERENTE' for some index that is int, instead of string? The same thing is for the excerpt: AND id_modelo = 'STRADA')

29.12.2017 / 11:25

Convert QVectorQString to int in C ++ How to compare if two javascript objects are the same?

score 4 · Accepted Answer

According to the IN (Transact-SQL) , from MS itself, this problem can occur when a IN clause has a very large number of values (many thousands of values separated by commas) within parentheses .

-- Caso em que o erro pode ocorrer:
SELECT *
  FROM Tabela
 WHERE id IN (1,2,3,...)  -- milhares de itens

As a workaround, the paper suggests that you store the list items in a table and use a SELECT subquery within an IN clause.

-- Como solução de contorno:
-- 1) Criar tabela temporária com os itens 
CREATE TABLE #Temp (id_solicitado int)
INSERT #Temp VALUES (1),(2),(3),... -- milhares de itens

-- 2) Usar uma subquery na cláusula IN.
SELECT *
  FROM Tabela
 WHERE id IN (SELECT id_solicitado FROM #Temp)

About your other two questions related to resource usage during query execution, I suggest reading the article Query Processing Architecture Guide .

There are several factors that will influence this aspect. For example, if the server running SQL Server has multiple CPUs, this will allow multiple queries to be processed simultaneously. The article addresses these issues well.