I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it is suddenly taking 35 minutes to finish. I have uploaded the actual plan on SQL Server 2014.
Sql-server – Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014
execution-planperformancequery-performancesql serversql server 2014
Related Solutions
This is not an issue, it is by design and is considered a performance improvement.
If your queries do not run as expected you will need to address this in the code of your database/application.
You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level (using OPTION (QUERYTRACEON 9481)). This will force it no matter what the compatibility level of the database is.
There is also a ton of information that the CSS Team posted here.
A more detailed post on the Cardanility Estimator and changes around that process in SQL Server 2014, includes example.
Why does this query need a Row Count Spool operator? ... what specific optimization is it trying to provide?
The cust_nbr column in #existingCustomers is nullable. If it actually contains any nulls the correct response here is to return zero rows (NOT IN (NULL,...) will always yield an empty result set.).
So the query can be thought of as
SELECT p.*
FROM #potentialNewCustomers p
WHERE NOT EXISTS (SELECT *
FROM #existingCustomers e1
WHERE p.cust_nbr = e1.cust_nbr)
AND NOT EXISTS (SELECT *
FROM #existingCustomers e2
WHERE e2.cust_nbr IS NULL)
With the rowcount spool there to avoid having to evaluate the
EXISTS (SELECT *
FROM #existingCustomers e2
WHERE e2.cust_nbr IS NULL)
More than once.
This just seems to be a case where a small difference in assumptions can make quite a catastrophic difference in performance.
After updating a single row as below...
UPDATE #existingCustomers
SET cust_nbr = NULL
WHERE cust_nbr = 1;
... the query completed in less than a second. The row counts in actual and estimated versions of the plan are now nearly spot on.
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT *
FROM #potentialNewCustomers
WHERE cust_nbr NOT IN (SELECT cust_nbr
FROM #existingCustomers
)
Zero rows are output as described above.
The Statistics Histograms and auto update thresholds in SQL Server are not granular enough to detect this kind of single row change. Arguably if the column is nullable it might be reasonable to work on the basis that it contains at least one NULL even if the statistics histogram doesn't currently indicate that there are any.
Related Question
- Sql-server – SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x
- Sql-server – Query wont compile/run
- Sql-server – SQL Server 2014: any explanation for inconsistent self join cardinality estimate
- Sql-server – SQL Server – Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014
- Sql-server – why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well
Best Answer
SQL Server 2014 with compatibility level 120 uses the new CE. SQL Server 2012 uses the legacy CE. The new CE uses a fundamentally different model for cardinality estimation that is expected by Microsoft to result in better performing queries for most workloads. However, they acknowledge that some workloads or queries may perform worse with the new CE. It is possible that is the situation that you're in.
When investigating a query that isn't finishing quickly enough, one of the first things I try to determine is why the query is slow. Your query is slow because over four billion rows are read from a single index seek operator:
You can verify that this is the most expensive part of the query by looking at the actual time statistics shown at the operator level. The index seek uses 222360 ms of CPU time and the overall query uses 224382 ms of CPU time. The most straightforward way to get better performance is by improving the index on the
RSTReclassedJournalEntriestable. This table only has 127787 rows, so it's unlikely that the additional maintenance required for a better index would be a concern. Creating an index with key columnsPostedandReclassedJRNENTRYshould suffice.The IX_RSTReclassedJournalEntries_1index already exists on the table and is a covering index, but you want a index that can filter onPostedandReclassedJRNENTRYas seek predicates instead of as filter predicates. With such an index SQL Server will read a maximum of 34646 rows from the index instead of 4382443766 rows. This will dramatically improve performance.The index change that I'm recommending addresses the symptom of the problem instead of the root cause, but sometimes that's enough to meet the business need. The root cause appears to be a join cardinality estimation problem with
[SOLMQ].[dbo].[AAG30000].[AK2AAG30000]. Addressing that is likely to be more difficult than making the suggested index change, although it's possible that you'll get lucky and updating statistics on that table will fix the problem as well.