Sql-server – Query is slow in SQL Server 2014, fast in SQL Server 2012

execution-planperformancequery-performancesql serversql server 2014

During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues.

One of the queries that completes within seconds on SQL Server 2012 seem to be hanging on SQL Server 2014.

SELECT  DISTINCT 
    src.[Id]
FROM
    [stg].[BaseVolumes] src
JOIN     
    [tmp].[Dates] d ON src.[CalWeek_Nmbr] = d.[CalYrWkDense_Nmbr]
WHERE   
    EXISTS (SELECT  *
            FROM    
                (SELECT     ctry.[ISOCode]                  AS  [Mkt_Code]
                            , so.[Code]                     AS  [SlsOrg_Code_AK]
                            , so.[DistributionChannelCode]  AS  [DistChnl_Code_AK]
                            , prd.[SupplierCode]                AS  [SKU_Code_AK]
                            , cl6.[Code]                        AS  [CstHierLvl06_Code_AK]
                            , lp.[BaseDateID]                   AS  [Dte_EK]
                 FROM   [PM_APP].[edw].[BaseVolumeDayCurrent]   lp
                 JOIN   [PM_APP].[dbo].[Country]                ctry    ON  lp.[CountryID] = ctry.[ID]
                 JOIN   [PM_APP].[dbo].[SalesOrganisation]      so      ON  lp.[SalesOrganisationID] = so.[ID]
                 JOIN   [PM_APP].[dbo].[Product]                prd     ON  lp.[ProductID] = prd.[ID]   
                 JOIN   [PM_APP].[dbo].[CustomerLevel6]         cl6     ON  lp.[CustomerID] = cl6.[ID]
                 WHERE  
                     lp.[ModifiedByApp] = 1) lkp
            WHERE   
                src.[Mkt_Code]                  =   lkp.[Mkt_Code]
                AND src.[SlsOrg_Code_AK]        =   lkp.[SlsOrg_Code_AK]
                AND src.[DistChnl_Code_AK]      =   lkp.[DistChnl_Code_AK]
                AND src.[SKU_Code_AK]           =   lkp.[SKU_Code_AK]
                AND src.[CstHierLvl06_Code_AK]  =   lkp.[CstHierLvl06_Code_AK]
                AND d.[Dte_EK]                  =   lkp.[Dte_EK]
        )

Row count: 

BaseVolumes:            23108 
Dates:                  18628 
BaseVolumeDayCurrent:   108115503 
Country:                249
SalesOrganisation:      29
Product:                18446
CustomerLevel6:         295

Wait stats showing high CXPacket, but there is one hanging task with SOS_SCHEDULER_YIELD, (print from sys.sysprocesses)

 spid | blocked | waittime | lastwaittype          | cpu     | physical_io 
 76   | 0       | 9886044  | CXPACKET              | 13902   | 31192
 76   | 0       | 0        | SOS_SCHEDULER_YIELD   | 9829719 | 83077
 76   | 0       | 11248    | CXPACKET              | 11110   | 0
.
.
.

Attached are the execution plans from SQL Server 2012 and SQL Server 2014.

Any help much appreciated.

Best Answer

The most likely situation is that the new SQL 2014 Cardinality Estimator is yielding a poor row estimate for one or more joins in your query and this has led SQL Server to choose an inefficient plan.

If you are able to run the query in SQL 2014 with "include actual execution plan" turned on, you can use the query below in another tab to view the real-time progress of rows flowing through each query operator. I noticed that you only have an estimated plan for 2014 (compared to an actual plan for 2012), presumably because you cannot run the query to completion in SQL 2014. So this could give you more insight into the actual rows flowing through the query in 2014 and may lead you to a way of tweaking the query that runs efficiently using the new Cardinality Estimator.

In the meantime, until you are able to optimize the query you could use QUERYTRACEON with trace flag 9481 for this query or you could follow Brent Ozar's advice of running the database at the SQL 2012 compatibility level, carefully testing your queries with the new Cardinality Estimator, and only updating the compatibility level to 120 (SQL 2014) once satisfied with these results.

/* Live query progress in SQL 2014 */
SELECT session_id,node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count, 
    CAST(SUM(row_count)*100 AS float)/NULLIF(SUM(estimate_row_count),0) AS percent_complete,
    SUM(elapsed_time_ms) AS elapsed_time_ms,
    SUM(cpu_time_ms) AS cpu_time_ms,
    SUM(logical_read_count) AS logical_read_count,
    SUM(physical_read_count) AS physical_read_count,
    SUM(write_page_count) AS spill_page_count,
    SUM(segment_read_count) AS segment_read_count,
    SUM(segment_skip_count) AS segment_skip_count,
    COUNT(*) AS num_threads
FROM sys.dm_exec_query_profiles 
WHERE session_id <> @@spid
GROUP BY session_id,node_id,physical_operator_name
ORDER BY session_id,node_id;
Related Question