7

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.

  • Is the max memory same on both the servers ? Also, can you try running the query using OPTION (MAXDOP 1) ? As well as OPTION (QUERYTRACEON 4199, QUERYTRACEON 9481). Alternatively, you can use plan guide as a last option. – Kin Shah Sep 4 '15 at 0:48
10

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.