SYMPTOM:
The following error message appears when running some specific reports.
An example in English
QueryEngine encountered error: Execute Query failed. Error type: ODBC error. ODBC operation attempted: SQLExecDirect. Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Error in Process method of Component: QueryEngineServer, Project 4, Job 6258, Error Code= -2147212544.
An example in Portuguese
QueryEngine encontrou um erro: Execute Query failed. Error type: ODBC error. ODBC operation attempted: SQLExecDirect. Intra-query parallelism caused your server command (process ID #67) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Erro no método de processamento do componente: QueryEngineServer, projeto DM FINANCEIRO, trabalho 25022, código de erro= -2147212544.
CAUSE:
Strategy is working as designed. According to Microsoft documentation, it occurs because the SQL Server detected a deadlock that involves the threads that are used by the same parallel query.
ACTION:
In this specific scenario, the chosen option was suppressing the generation of a parallel plan. This option was chosen because only the SQL Statement from the affected report is altered so that it is necessary to repeat the steps below for all affected reports.
There should be a blank line before the clause OPTION (MAXDOP1), otherwise the SQL STATEMENT is generated incorrectly as shown and it will not work as supposed to, as shown below:
Refer to the following Strategy Knowledge Base technical note for more information:
KB14316 (KB2000-000-0111): ‘Intra-query parallelism caused your server command (process ID #51) to deadlock’ error occurs while executing a report when using multi-processor SQL Server as a warehouse
