Sql Server Error 8623: The query Processor Ran out of Internal Resources and could not Produce a query plan

Question

Sql Server Database 2012 and 2008.

They log 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 reduced even more due to the limit of Stackoverflow itself but only in the parameter part of IN, the query in the log (incomplete) has about 3550 parameters, but in the system I have seen searches with much more (hundreds of thousands), another detail is that it occurs 37 times, in a period between 9:14 and 11:32 am.

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

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

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

Answer 1

According to the paper IN (Transact-SQL), of MS itself, this problem can occur when a clause IN possesses 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 an outline solution, the paper suggests that store list items in a table and use a SELECT sub-label 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)

Regarding your other two questions related to the use of resources during the 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.

Answer 2

First the amount you mentioned in the clause IN not necessarily the main cause of the problem, but obviously the greater 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.

Behold : Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [Uplicate]

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 database is allocated. Have you tried running this query at a time that doesn’t conflict with other queries? A suggestion, you can use SQL Server profiler to monitor your bank’s transactions in real time

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

Detecting and terminating 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' by an index that is int instead of string? The same thing is for the snippet : AND id_modelo = 'STRADA')