CODEWITHSUNDEEP
sqlsql-serverstored-proceduresquery-optimizationsql-server-2014
Michele
Michele

Reputation: 23

Different execution plan from the same query in different SQL Server

I have some issue with this query:

select distinct
    Date_Int,
    CodF,
    Desc_Com,
    DataDesc_Com,
    CodC,
    Function,
    Tratt_Number,
    Tratt_State
from 
    tmp_SIC_Trattative_Stato_com_l2

UNION 

SELECT DISTINCT
    case 
       when (ts.Date_Int is not null) 
          then ts.Date_Int 
          else All_Day.Date_Int 
    end as Date_Int,
    case 
       when (ts.CodF is not null) 
          then ts.CodF 
          else All_Day.CodF  
    end as CodF,
    case 
       when (ts.Desc_Com is not null) 
          then ts.Desc_Com 
          else All_Day.Desc_Com 
    end as Desc_Com,
    case 
       when (ts.DataDesc_Com is not null) 
          then ts.DataDesc_Com 
          else All_Day.DataDesc_Com 
    end as DataDesc_Com,
    case 
       when (ts.CodC is not null) 
          then ts.CodC 
          else All_Day.CodC 
    end as CodC,
    case when (ts.Function is not null) then ts.Function else All_Day.Function end as Function,
    case when (ts.Tratt_Number is not null) then ts.Tratt_Number else All_Day.Tratt_Number end as Tratt_Number,
    case when (ts.Tratt_State is not null) then ts.Tratt_State else All_Day.Tratt_State end as Tratt_State
FROM 
    Commerciali_All_Day as All_Day 
LEFT OUTER JOIN
    tmp_SIC_Trattative_Stato_com_l2 as ts ON ts.Date_Int = All_Day.Date_Int
                                          AND ts.CodF = All_Day.CodF
                                          AND ts.Desc_Com = All_Day.Desc_Com
                                          AND ts.DataDesc_Com = All_Day.DataDesc_Com
                                          AND ts.CodC = All_Day.CodC
                                          AND ts.Function = All_Day.Function
                                          AND ts.Tratt_State = All_Day.Tratt_State
WHERE 
    ts.Date_Int IS NULL

I execute this query in a stored procedure but the execution plan changes if the stored procedure is executed using Production SQL Server or using Test SQL Server.

This is the test execution plan:

Test Execution Plan

And this is the production execution plan:

Production Execution Plan

The source table and the stored procedure are the same in Test and Production and I do not understand because the execution plan and the time are different.

In Test the query is execute in 6 minutes and in production in 15 minutes.

The Test and Production SQL Server are Microsoft SQL Server 2014 version 12.0.4100.1.

I do not understand why the procedure is performing better in test environment and not in production environment.

Upvotes: 0

Views: 2531

Answers (2)

Michele
Michele

Reputation: 23

your remark about the title of the question is correct. Initially also the production and test execution plans were differents but expliciting the left outer join I obtained the same execution plan but with different execution time.

There is a different rows number between production and test because test's data are updated to one day earlier and so there are not 3,083 rows.

I checked the difference between the extra data processed in production environment and I found a temp table with 2 fields with type varchar(max) that are varchar(25) in test environment. I changed the types of these 2 fields and now the execution time and processed data are the same.

Thanks a lot for your help.

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

The title of the question is not what you are really asking for. You have the same query plan between your prod and test server. What you're really asking is why the prod server is slower than the test server with the same query.

In the comments you answered that the tables and their contents were identical between test and prod. Specifically you mention they have the same number of rows.

The prod plan shows more data being returned than the test plan. The biggest point of interest with data being returned is the table scan on Commerciali_All_Day which is your build input for the hash table. In test it returns 725,858 rows with a total size of 47MB. In prod it returns 728,941 rows with a total size of 120MB. Thats more than double the size with a difference of 3,083 rows.

With the hash build input table returning more than double the amount of data it is much larger in prod than in test. In test the hash table is 19,897,066 rows with a size of 2,713MB. In prod the hash table is 20,006,362 rows with a size of 4,732MB. Prod is crunching through an extra 2GB of data.

You need to go back and take a better look at what the difference is between the data in prod and test. None of your tables are returning the same amount of data when comparing prod and test plans. Your real pain point with this query specifically though is the Commerciali_All_Day table.

Upvotes: 1

Related Questions