March 12, 2015 at 2:35 pm
Hi, everybody!
We are ratifying a new server with SQL 2014 cluster. During the performance tests, something very strange happened. I have a routine made up of several SELECT, TABLE variables and CTE. Basically, what I'm running is:
USE MASTER
GO
ALTER DATABASE SET QVDSM3 COMPATIBILITY_LEVEL = 100 - on the second run, I trade for 120
GO
USE QVDSM3
go
DECLARE nPeriodo = 201501
...
And I register the elapsed time. Then I change the compatibility and rerun the query.
When I'm using a database with SQL 2008 compatibility at SQL 2014, the query takes zero seconds. But if I change the compatibility to 2014, the same routine takes about one minute to run. I thought it could be something in the structure of indexes or statistics, so I recreated all indexes and statistics on compatibility 2014. Even so, the query continues taking one minute to run.
Among some modifications, by trial and error, I changed all TABLE variables for temporary tables. Thus, the routine time dropped to zero !!!
In short, what I have in relation to time is:
SQL 2008 compatibility: the routine takes zero seconds using TABLE variables or temporary tables.
SQL 2014 compatibility: the routine takes 60 seconds using variables TABLE and zero seconds using temporary tables.
The routine consists of:
1.Vários SELECTS and CTE to load data into the TABLE variable or temporary tables.
2.Cinco INSERT / SELECT using the BD tables and objects created in item 1.
3.Três UPDATE / SELECT using the BD tables and objects created in item 1.
Then passed to the maintenance plan, which is composed of 17 segments. I validei each of them, modifying the routine and using:
1) Variable table in SQL 2008 and SQL 2014.
2) Temporary table in SQL 2008 and SQL 2014.
3) Variable table in a window and temporary table in another window, both in SQL 2008.
4) Variable table in a window and temporary table in another window, both in SQL 2014.
In all cases, the maintenance plan is always the same. The cost variance is minimal for each command, the maximum difference appeared between the cost was 5%, which would not justify a routine in SQL 2014 take 60 times more than itself in SQL 2008.
I do not know whether it is appropriate to consider this case as a query bottleneck. As I mentioned, the wheel routine at zero seconds in SQL 2008. The problem is when I run the same routine in SQL 2014 where it takes more than a minute to bring the same result.
My question is, because the use of TABLE variables in routine can leave it so long, when the use of temporary tables makes instant routine?
March 12, 2015 at 3:08 pm
Have you checked the execution plans with both compatibility levels? You should be getting different plans.
Table variables don't have statistics and the optimizer always treats them as if they have one row which leads into incorrect plans. Temp tables have statistics and can create better plans.
I can't give a more specific advice/answer. Maybe someone else can or they might need more information.
March 12, 2015 at 11:51 pm
Quick thought, could it be that changing the compatibility level flushes the plan cache and that's the real culprit?
😎
March 13, 2015 at 5:16 am
Luis, Hi!
As I mentioned before, I verified the man plan in each scenario. Nothing changes even when I switch the compatibility level.
Erick, Hi!
If the man flush was an issue, I guess the routine would take long with the table variable and the temp table. However, the problem occour only with table var.
Thank you both for the suggestion, but the question remains.... Why?:w00t:
March 13, 2015 at 5:47 am
I'd say there are two things at work here. First, and most importantly, table variables and temporary tables do have radically different behaviors within SQL Server due to the fact that there are statistics on the temporary tables and not on the table variable, as was already stated above. The differences caused by accurate statistics to execution plans is pretty radical and directly impacts query performance. Without a doubt you're seeing this played out.
Second, SQL Server 2014 when in the 120 compatibility mode has a new cardinality estimator. That estimator could be coming up with different values of rows for your other tables in the query, not the temp tables or table variables. Differences in the row estimates mean differences in the execution plans and that means differences in performance.
You said you compared the "maintenance plans" but I'm assuming that's a typo and you meant execution plans. But, you said you just compared costs. You can't really do that. The costs of plans are just estimates from the optimizer. They're useful... to a degree. But they don't represent actual differences in performance, just in expectations of possibilities of performance. You said that there are differences in the plans generated and that's where the answer lies. Those differences, even if they seem small, can lead to major differences in performance. It's not at all surprising.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 6:11 am
Hi, Grant!
You're right about some aspects.
I really confuse execution plan and maintenance plan. The maintenance plans we apply on these databases updates indexes and statistics. Is there anything else you suggest to include in this activity that can improve performance?
As for the execution plans, maybe I've not expressed myself right. What I meant was when Icompare the plans of the same query in different compatibilities, they remain the same. Of course the plan of a query with temporary table is different from that with table variable.
My problem is simple: I have a system with more than 650 procedures that use table variables, and the longest time to execute the slower procedure in SQL 2008 is 3 seconds to run. When I bring the database to SQL 2014 and keep the compatibility 2008, the times remain the same, 3 seconds. However, when I change the compatibility to 2014, the query that took 3 seconds takes almost five minutes to perform.
I thought it might be due to procedures compilation, then I recreated each one at compatibility 2014. Also I update indexes statistics in 2014. With all these actions, I cann't get the same performance with the procedures in 2014 as that in the 2008 version.
In this case, how can I migrate my system to SQL 2014?
That's why I need to understand what can be happening here.
March 13, 2015 at 6:35 am
But the plans between the 2008 compatibility and the 2014 compatibility are different, right? They have to be. There's nothing in just changing the compatibility mode that would make a query with an identical execution plan run slower on the same server with the same parameters against the same tables & statistics. It just doesn't work like that. So, you must be seeing changes in the plans. If so, it's most likely because of the cardinality estimation changes. So, the key is to understand what's going on there. The only way is to explore the plans. Then figure out what you have to do to fix them.
That's the long term solution. The short term solution would be to leave the compatibility mode on. There are traceflags for setting the cardinality estimator off (or on). Traceflag 9481 will enforce the old estimator. That can be used if you figure out that you only have a few queries that are giving you issues.
Here's a blog from Microsoft on some of the issues, troubleshooting, etc., from the new cardinality estimator. It should help. Here's another Microsoft blog on the new CE from the point of view of SAP. More hints there. Here's the documentation in the books online. It should also help.
It's really unusual for the new functionality to hurt ALL procedures. In fact, I've more often seen it help all of them. The new CE estimates are much more accurate than the old ones in most cases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 8:28 am
Grant,
I'm sending a small video about my situation here, running the query on SQL 2014, changing the compatibility level and executing the query.
Ignez
March 13, 2015 at 8:48 am
Can you post the full code, ddl and some sample data please?
😎
March 13, 2015 at 9:01 am
Eirikur, hi.
The query has 1.823 lines, but the specific part that takes more time to execute is this. Everything before and after this part runs in zero or one second.
;WITH CTE_ITENSCONTRLOCAL (ITENSCONTRLOCAL, ITENSCONTRATO, ITENSCONTRLOCALANT, ORIGEM, ITENSCONTRATOORIGEM)
AS
(SELECT ITENSCONTRLOCAL.ITENSCONTRLOCAL,ITENSCONTRATO.ITENSCONTRATO,ITENSCONTRLOCAL.ITENSCONTRLOCALANT,
ITENSCONTRLOCAL.ITENSCONTRLOCAL,ITENSCONTRATO.ITENSCONTRATO
FROM POSTO WITH (NOLOCK)
INNER JOIN ITENSCONTRATO WITH (NOLOCK) ON ITENSCONTRATO.VERSAOCONTRATO = POSTO.VERSAOCONTRATO
INNER JOIN ITENSCONTRLOCAL WITH(NOLOCK) ON ITENSCONTRLOCAL.ITENSCONTRATO = ITENSCONTRATO.ITENSCONTRATO
WHERE POSTO.POSTO = @POSTO
UNION ALL
SELECT ITENSCONTRLOCALFILHO.ITENSCONTRLOCAL,ITENSCONTRLOCALFILHO.ITENSCONTRATO,ITENSCONTRLOCALFILHO.ITENSCONTRLOCALANT,R2.ORIGEM,ITENSCONTRATOORIGEM
FROM ITENSCONTRLOCAL ITENSCONTRLOCALFILHO WITH(NOLOCK)
INNER JOIN CTE_ITENSCONTRLOCAL R2 ON ITENSCONTRLOCALFILHO.ITENSCONTRLOCAL = R2.ITENSCONTRLOCALANT),
CTE_EXTRACONTITENSCOMP(ITENSCONTRLOCALFAT,ITENSCONTRLOCAL,ITENSCONTRATO,ITENSCONTRATOORIGEM)
AS
(SELECT MAX(CTE_ITENSCONTRLOCAL.ITENSCONTRLOCAL), CTE_ITENSCONTRLOCAL.ORIGEM,
MAX(CTE_ITENSCONTRLOCAL.ITENSCONTRATO), CTE_ITENSCONTRLOCAL.ITENSCONTRATOORIGEM
FROM CTE_ITENSCONTRLOCAL
INNER JOIN EXTRACONTITENSCOMP WITH(NOLOCK) ON CTE_ITENSCONTRLOCAL.ITENSCONTRLOCAL = EXTRACONTITENSCOMP.ITENSCONTRLOCAL
GROUP BY CTE_ITENSCONTRLOCAL.ORIGEM, CTE_ITENSCONTRLOCAL.ITENSCONTRATOORIGEM)
INSERT INTO @AFFALTASATRASOSTEMP (VERSAOCONTRATO, ITENSCONTRATO, POSTO, ATRASOSQTDDIURNO, ATRASOSQTDNOTURNO, ATRASOSVALORDIURNO,
ATRASOSVALORNOTURNO, FALTASQTDDIURNO, FALTASQTDNOTURNO, FALTASVALORDIURNO, FALTASVALORNOTURNO,
POSTOVAGOQTDDIURNO, POSTOVAGOQTDNOTURNO, POSTOVAGOVALORDIURNO, POSTOVAGOVALORNOTURNO, PERIODOINI,
PERIODOFIM, MESCOMPETENCIA, PRORATA, STATUS, DTSTATUS, SITUACAO, DTSITUACAO, TRABALHADAQTDDIURNO,
TRABALHADAQTDNOTURNO)
SELECT FALTASATRASOS.VERSAOCONTRATO, FALTASATRASOS.ITENSCONTRATO, FALTASATRASOS.POSTO,
CASE WHEN FALTASATRASOS.REENVIO = 1
-- Caso seja reenvio de horas, a quantidade é a nova quantidade - a quantidade de horas existentes.
THEN SUM(FALTASATRASOS.ATRASOSQTDDIURNO) - (SELECT ISNULL(SUM(ATRASOSQTDDIURNO),0)
FROM AFFALTASATRASOS AS AFFALTASATRASOSSub WITH (NOLOCK)
WHERE AFFALTASATRASOSSub.GRUPOANALISEFATURAMENTO IS NOT NULL
AND AFFALTASATRASOSSub.POSTO = @POSTO
AND AFFALTASATRASOSSub.SITUACAO = 1
AND AFFALTASATRASOSSub.PERIODOINI = FALTASATRASOS.PERIODOINICIO
AND AFFALTASATRASOSSub.PERIODOFIM = FALTASATRASOS.PERIODOFIM
AND AFFALTASATRASOSSub.ITENSCONTRATO = FALTASATRASOS.ITENSCONTRATO)
ELSE SUM(FALTASATRASOS.ATRASOSQTDDIURNO)
END AS ATRASOSQTDDIURNO,
CASE WHEN FALTASATRASOS.REENVIO = 1
-- Caso seja reenvio de horas, a quantidade é a nova quantidade - a quantidade de horas existentes.
THEN SUM(FALTASATRASOS.ATRASOSQTDNOTURNO) - (SELECT ISNULL(SUM(ATRASOSQTDNOTURNO) ,0)
FROM dbo.AFFALTASATRASOS AS AFFALTASATRASOSSub WITH (NOLOCK)
WHERE AFFALTASATRASOSSub.GRUPOANALISEFATURAMENTO IS NOT NULL
AND AFFALTASATRASOSSub.POSTO = @POSTO
AND AFFALTASATRASOSSub.SITUACAO = 1
AND AFFALTASATRASOSSub.PERIODOINI = FALTASATRASOS.PERIODOINICIO
AND AFFALTASATRASOSSub.PERIODOFIM = FALTASATRASOS.PERIODOFIM
AND AFFALTASATRASOSSub.ITENSCONTRATO = FALTASATRASOS.ITENSCONTRATO)
ELSE SUM(FALTASATRASOS.ATRASOSQTDNOTURNO)
END AS ATRASOSQTDNOTURNO,
0 AS ATRASOSVALORDIURNO,
0 AS ATRASOSVALORNOTURNO,
CASE WHEN FALTASATRASOS.REENVIO = 1
-- Caso seja reenvio de horas, a quantidade é a nova quantidade - a quantidade de horas existentes.
THEN SUM(FALTASATRASOS.FALTASQTDDIURNO) - (SELECT SUM(FALTASQTDDIURNO)
FROM AFFALTASATRASOS WITH (NOLOCK)
WHERE AFFALTASATRASOS.POSTO = @POSTO AND AFFALTASATRASOS.SITUACAO = 1)
ELSE SUM(FALTASATRASOS.FALTASQTDDIURNO)
END AS FALTASQTDDIURNO,
CASE WHEN FALTASATRASOS.REENVIO = 1
-- Caso seja reenvio de horas, a quantidade é a nova quantidade - a quantidade de horas existentes.
THEN SUM(FALTASATRASOS.FALTASQTDNOTURNO) - (SELECT SUM(FALTASQTDNOTURNO)
FROM AFFALTASATRASOS WITH (NOLOCK)
WHERE AFFALTASATRASOS.POSTO = @POSTO AND AFFALTASATRASOS.SITUACAO = 1)
ELSE SUM(FALTASATRASOS.FALTASQTDNOTURNO)
END AS FALTASQTDNOTURNO,
0 AS FALTASVALORDIURNO,
0 AS FALTASVALORNOTURNO,
SUM(FALTASATRASOS.POSTOVAGOQTDDIURNO) AS POSTOVAGOQTDDIURNO,
SUM(FALTASATRASOS.POSTOVAGOQTDNOTURNO ) AS POSTOVAGOQTDNOTURNO,
0 AS POSTOVAGOVALORDIURNO,
0 AS POSTOVAGOVALORNOTURNO,
FALTASATRASOS.PERIODOINICIO,
FALTASATRASOS.PERIODOFIM,
FALTASATRASOS.MESCOMPETENCIA,
FALTASATRASOS.PRORATA,
COALESCE(NULLIF(FALTASATRASOS.STATUS,4),1) AS STATUS,
GETDATE() AS DTSTATUS,
1 AS SITUACAO,
GETDATE() AS DTSITUACAO,
SUM(TRABALHADAQTDDIURNO) AS TRABALHADAQTDDIURNO,
SUM(TRABALHADAQTDNOTURNO) AS TRABALHADAQTDDIURNO
FROM --Apurando as quantidades de faltas e atrasos para o posto no período a ser faturado
(SELECT VERSAOCONTRATO.VERSAOCONTRATO, POSTO.POSTO, ITENSCONTRATO.ITENSCONTRATO, 0 AS POSTOVAGOQTDDIURNO, 0 AS POSTOVAGOQTDNOTURNO,
CASE WHEN (CF.TRABALHOPLANEJADO = 6)
AND (PONTO.TIPOEVENTOREALIZADO = 8)
AND (MOVIMENTACAOFUNCIONARIO.MOVIMENTACAOFUNCIONARIO IS NOT NULL AND MOVIMENTACAOFUNCIONARIO.ALOCACAOMOCOBERTURA IS NULL)
THEN HORASPLANEJADAS.DIURNO - HORASREALIZADAS.DIURNO
ELSE 0
END AS ATRASOSQTDDIURNO,
CASE WHEN (CF.TRABALHOPLANEJADO = 6) AND (PONTO.TIPOEVENTOREALIZADO = 8) AND (MOVIMENTACAOFUNCIONARIO.MOVIMENTACAOFUNCIONARIO IS NOT NULL AND MOVIMENTACAOFUNCIONARIO.ALOCACAOMOCOBERTURA IS NULL)
THEN HORASPLANEJADAS.NOTURNO - HORASREALIZADAS.NOTURNO
ELSE 0
END AS ATRASOSQTDNOTURNO,
-- Se foi planejado para trabalhar e no contrato esse item é FATURADO e a necessidade não é reforço então..
CASE WHEN PONTO.TIPOEVENTOREALIZADO = 4 AND (MOVIMENTACAOFUNCIONARIO.MOVIMENTACAOFUNCIONARIO IS NOT NULL AND MOVIMENTACAOFUNCIONARIO.ALOCACAOMOCOBERTURA IS NULL)
THEN CASE WHEN HORASPLANEJADAS.DIURNO > 0 THEN HORASPLANEJADAS.DIURNO ELSE 0 END
ELSE 0
END AS FALTASQTDDIURNO,
-- Se foi planejado para trabalhar e no contrato esse item é FATURADO e a necessidade não é reforço então..
CASE WHEN PONTO.TIPOEVENTOREALIZADO = 4 AND (MOVIMENTACAOFUNCIONARIO.MOVIMENTACAOFUNCIONARIO IS NOT NULL AND MOVIMENTACAOFUNCIONARIO.ALOCACAOMOCOBERTURA IS NULL)
THEN CASE WHEN HORASPLANEJADAS.NOTURNO > 0 THEN HORASPLANEJADAS.NOTURNO ELSE 0 END
ELSE 0
END AS FALTASQTDNOTURNO,
PERIODOFATURAMENTO.STATUS,
PERIODOFATURAMENTO.PERIODOINICIO,
PERIODOFATURAMENTO.PERIODOFIM,
DATEADD(MONTH, +1,PERIODOFATURAMENTO.PERIODOFIM) AS MESCOMPETENCIA,
PERIODOFATURAMENTO.PRORATA,
PERIODOFATURAMENTO.REENVIO,
CF.DTTRABALHO,
CASE WHEN CF.TRABALHOPLANEJADO = 6 AND MFCOBERTURA.MOVIMENTACAOFUNCIONARIO IS NULL AND (ALOCACAOMO.COBERTURA = 0 OR ALOCACAOMO.POSTOVAGO = 1)
THEN HORASPLANEJADAS.DIURNO
ELSE 0
END AS TRABALHADAQTDDIURNO,
CASE WHEN CF.TRABALHOPLANEJADO = 6 AND MFCOBERTURA.MOVIMENTACAOFUNCIONARIO IS NULL AND (ALOCACAOMO.COBERTURA = 0 OR ALOCACAOMO.POSTOVAGO = 1)
THEN HORASPLANEJADAS.NOTURNO
ELSE 0
END AS TRABALHADAQTDNOTURNO
FROM @CRITICATEMP AS CF
INNER JOIN @PERIODOFATURAMENTO PERIODOFATURAMENTO ON CF.DTTRABALHO >= PERIODOFATURAMENTO.PERIODOINICIO AND CF.DTTRABALHO <= PERIODOFATURAMENTO.PERIODOFIM
INNER JOIN NECESSIDADEMO WITH (NOLOCK) ON CF.NECESSIDADEMO = NECESSIDADEMO.NECESSIDADEMO AND NECESSIDADEMO.SITUACAO=@VALOR_1
INNER JOIN POSTO WITH (NOLOCK) ON POSTO.POSTO = CF.POSTO
INNER JOIN ALOCACAOMO WITH(NOLOCK) ON NECESSIDADEMO.NECESSIDADEMO= ALOCACAOMO.NECESSIDADEMO AND ALOCACAOMO.SITUACAO = @VALOR_1
/* NÃO PRECISA DESTA CONVERSÃO, PQ AS COLUNAS SÃO DATETIME */
AND CONVERT(DATE,CF.DTTRABALHO) BETWEEN CONVERT(DATE,ALOCACAOMO.DTINICIO) AND CONVERT(DATE,ALOCACAOMO.DTFIM)
INNER JOIN PONTO WITH (NOLOCK) ON ALOCACAOMO.ALOCACAOMO = PONTO.ALOCACAOMOREALIZADO AND PONTO.SITUACAO = @VALOR_1
AND CONVERT(DATE,PONTO.DTINICIOPLANEJADO) = CONVERT(DATE,CF.DTTRABALHO)
LEFT JOIN MOVIMENTACAOFUNCIONARIO WITH(NOLOCK) ON ALOCACAOMO.ALOCACAOMO = MOVIMENTACAOFUNCIONARIO.ALOCACAOMO
AND CONVERT(DATE,MOVIMENTACAOFUNCIONARIO.DTINICIO) = CONVERT(DATE,PONTO.DTINICIOPLANEJADO)
AND MOVIMENTACAOFUNCIONARIO.SITUACAO = @VALOR_1
LEFT JOIN MOVIMENTACAOFUNCIONARIO MFCOBERTURA WITH(NOLOCK) ON ALOCACAOMO.ALOCACAOMO = MFCOBERTURA.ALOCACAOMOCOBERTURA
AND CONVERT(DATE,MFCOBERTURA.DTINICIO) = CONVERT(DATE,PONTO.DTINICIOPLANEJADO)
AND MFCOBERTURA.SITUACAO = @VALOR_1
INNER JOIN COMPOSICAOITENSCONTRATO WITH (NOLOCK) ON COMPOSICAOITENSCONTRATO.COMPOSICAOITENSCONTRATO = NECESSIDADEMO.COMPOSICAOITENSCONTRATO
INNER JOIN ITENSCONTRATO WITH (NOLOCK) ON COMPOSICAOITENSCONTRATO.ITENSCONTRATO = ITENSCONTRATO.ITENSCONTRATO
INNER JOIN ITENSCONTRLOCAL WITH (NOLOCK) ON ITENSCONTRATO.ITENSCONTRATO = ITENSCONTRLOCAL.ITENSCONTRATO AND POSTO.LOCALSERVICO = ITENSCONTRLOCAL.LOCALSERVICO
INNER JOIN VERSAOCONTRATO WITH (NOLOCK) ON POSTO.VERSAOCONTRATO = VERSAOCONTRATO.VERSAOCONTRATO
INNER JOIN CTE_EXTRACONTITENSCOMP WITH(NOLOCK) ON ITENSCONTRLOCAL.ITENSCONTRLOCAL = CTE_EXTRACONTITENSCOMP.ITENSCONTRLOCAL
OUTER APPLY (SELECT *
FROM FN_VERIFICA_HORAS_DIURNAS_E_NOTURNAS(PONTO.DTINICIOREALIZADO, PONTO.DTFIMREALIZADO,PONTO.PONTO) AS HORAS
WHERE HORAS.INICIO = PONTO.DTINICIOREALIZADO )AS HORASREALIZADAS
OUTER APPLY (SELECT *
FROM FN_VERIFICA_HORAS_DIURNAS_E_NOTURNAS(PONTO.DTINICIOPLANEJADO, PONTO.DTFIMPLANEJADO,PONTO.PONTO) AS HORAS
WHERE HORAS.INICIO = PONTO.DTINICIOPLANEJADO )AS HORASPLANEJADAS
CROSS APPLY (SELECT *
FROM FN_TABELA_HORARIO(NECESSIDADEMO.NECESSIDADEMO, NULL) TABELAHORARIO
WHERE DATEPART(WEEKDAY, CF.DTTRABALHO) = TABELAHORARIO.DIASEMANA ) TABELAHORARIO
)AS FALTASATRASOS
GROUP BY FALTASATRASOS.VERSAOCONTRATO, FALTASATRASOS.POSTO, FALTASATRASOS.ITENSCONTRATO, FALTASATRASOS.STATUS, FALTASATRASOS.PERIODOINICIO,
FALTASATRASOS.PERIODOFIM, FALTASATRASOS.MESCOMPETENCIA, FALTASATRASOS.PRORATA, FALTASATRASOS.REENVIO
ORDER BY FALTASATRASOS.PERIODOINICIO
March 13, 2015 at 9:14 am
Right. I watched the video. It doesn't change anything or provide us with more information than you already provided.
You need to look at the execution plan for the query. In the execution plan, it will show you the row estimates that the optimizer is getting from the cardinality estimator. Do this for 120 and the older compatibility. You'll see where the different estimates are coming from. It's those differences in estimates that are leading to differences in the execution plan and therefore differences in the execution time. It's possible that you might be able to do something with the statistics or indexes, if you know which ones are giving you differences. But you have to look at the execution plan to understand that.
Also, looking at the query, you are absolutely looking at the kind of complex queries that are most adversely affected any time the optimizer is upgraded, and, probably, in this case, upgrades to the cardinality estimator. I'll bet pretty solid money that if you look at the execution plan, go to the first operator, and look at the reason for early termination, it's a timeout. That means the optimizer never finished coming up with a good plan.
Highly dense, complex queries of this type are frequently subject to regressions when the optimizer gets changed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 12:57 pm
Grant,
I tried the fastest and simplest way to start. I used "DBCC TRACEON (9481);" and ran the query. And ... yes, it performs in only 4 seconds. I tried 4 other stored procedures in the same situation, and the result was also very fast. I think we can conclude that something in the Cardinality Estimator is affecting the queries at this system.
Unfortunately, this discovery did not solve my problem. How can I bring this system to 2014 version? Will I have to rewrite all stored procedures, functions and routines of this system because of Cardinality Estimator? We're talking about over 650 objects only on DB (sp and functions), and all the code within the system itself.
I need to think about the whole system. So I'd like your opinion about using the DBCC TRACEON (9481, -1).
Should I? What's the risks?
March 13, 2015 at 1:14 pm
First a quick question, why the nolock hint? Are you running of a read only database? If the database is not read only then remove the nolock hints, re-run the test and let us know the results.
Picking up from Grant's reply, please post the actual execution plans for both cases, I've had similar problems when porting from 2012 to 2014 but with the limited information it is hard/impossible to tell what the problem is. Also as I said earlier, DDL and sample data would be really helpful.
My guess is that when comparing the two execution plans there will be a big difference in the estimated cardinality on the outer side of one or more of the join operators, reconstructing the query normally mitigates that problem.
😎
March 13, 2015 at 1:49 pm
Eirikur,
I'll prepare all the information you asked and post here asap.
March 14, 2015 at 4:21 am
ignez.mello (3/13/2015)
Grant,I tried the fastest and simplest way to start. I used "DBCC TRACEON (9481);" and ran the query. And ... yes, it performs in only 4 seconds. I tried 4 other stored procedures in the same situation, and the result was also very fast. I think we can conclude that something in the Cardinality Estimator is affecting the queries at this system.
Unfortunately, this discovery did not solve my problem. How can I bring this system to 2014 version? Will I have to rewrite all stored procedures, functions and routines of this system because of Cardinality Estimator? We're talking about over 650 objects only on DB (sp and functions), and all the code within the system itself.
I need to think about the whole system. So I'd like your opinion about using the DBCC TRACEON (9481, -1).
Should I? What's the risks?
Looking at your example code, I hate to say it, yeah, you're probably looking at either using the traceflag for the database or rewriting that code. That code looks overly complex and I'm sure the optimizer is having a tough time with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply