0

The query is generated by Entity Framework. It take 2 minutes in PROD, less than 1 second in DEV. On row counts of the tables, there are not big difference between PROD and DEV.

PROD: SQL Server 2008R2 Ent SP3, CPU 40, Max RAM 40gb, MaxDOP 4

DEV: SQL 2014 Ent, CPU 4, Max RAM 16gb

The query. I took out the SELECT part, it is too long and not a big issue with my question.

exec sp_executesql N'SELECT ...
FROM                      [dbo].[AwsEC2Instance] AS [Extent1]
LEFT OUTER JOIN  (SELECT [Extent2].[ComputerId] AS [ComputerId1], [Extent2].[EntityLastUpdated] AS [EntityLastUpdated1], [Extent2].[AreaOfAuthority] AS [AreaOfAuthority], [Extent2].[BuildId] AS [BuildId], [Extent2].[DomainId] AS [DomainId], [Extent2].[EnvironmentId] AS [EnvironmentId], [Extent2].[HardwareId] AS [HardwareId], [Extent2].[InstallDate] AS [InstallDate], [Extent2].[LocationId] AS [LocationId], [Extent2].[MemoryGBs] AS [MemoryGBs], [Extent2].[ModelId] AS [ModelId], [Extent2].[Name] AS [Name], [Extent2].[NetworkZoneId] AS [NetworkZoneId], [Extent2].[OperatingSystemId] AS [OperatingSystemId], [Extent2].[PowerState] AS [PowerState], [Extent2].[RetirementDate] AS [RetirementDate], [Extent2].[Status] AS [Status], [Extent2].[SupportOrganization] AS [SupportOrganization], [Extent3].[ComputerId] AS [ComputerId14]
    FROM  [dbo].[Computer] AS [Extent2]
    LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent3] ON [Extent2].[ComputerId] = [Extent3].[ComputerId] ) AS [Join1] ON [Extent1].[ComputerId] = [Join1].[ComputerId14]
LEFT OUTER JOIN  (SELECT [Extent4].[ComputerId] AS [ComputerId2], [Extent4].[EntityLastUpdated] AS [EntityLastUpdated2], [Extent4].[ComputerGraphicsProcessingUnitSettingsId] AS [ComputerGraphicsProcessingUnitSettingsId], [Extent4].[GraphicsProcessingUnitModelId] AS [GraphicsProcessingUnitModelId], [Extent4].[NumberOfProcessors] AS [NumberOfProcessors], [Join3].[ComputerId15], [Join3].[ComputerId16]
    FROM  [dbo].[ComputerGraphicsProcessingUnitSettings] AS [Extent4]
    INNER JOIN  (SELECT [Extent5].[ComputerId] AS [ComputerId15], [Extent6].[ComputerId] AS [ComputerId16]
        FROM  [dbo].[Computer] AS [Extent5]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent6] ON [Extent5].[ComputerId] = [Extent6].[ComputerId] ) AS [Join3] ON [Extent4].[ComputerId] = [Join3].[ComputerId15] ) AS [Join4] ON [Extent1].[ComputerId] = [Join4].[ComputerId16]
LEFT OUTER JOIN  (SELECT [Extent7].[ComputerId] AS [ComputerId3], [Extent7].[EntityLastUpdated] AS [EntityLastUpdated3], [Extent7].[ComputerDiscoveryDetailId] AS [ComputerDiscoveryDetailId], [Extent7].[LastHostInfoScanDate] AS [LastHostInfoScanDate], [Extent7].[LastRebootDate] AS [LastRebootDate], [Extent7].[ScannerName] AS [ScannerName], [Join6].[ComputerId17], [Join6].[ComputerId18]
    FROM  [dbo].[ComputerDiscoveryDetail] AS [Extent7]
    INNER JOIN  (SELECT [Extent8].[ComputerId] AS [ComputerId17], [Extent9].[ComputerId] AS [ComputerId18]
        FROM  [dbo].[Computer] AS [Extent8]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent9] ON [Extent8].[ComputerId] = [Extent9].[ComputerId] ) AS [Join6] ON [Extent7].[ComputerId] = [Join6].[ComputerId17] ) AS [Join7] ON [Extent1].[ComputerId] = [Join7].[ComputerId18]
LEFT OUTER JOIN  (SELECT [Extent10].[ComputerId] AS [ComputerId4], [Extent10].[EntityLastUpdated] AS [EntityLastUpdated4], [Extent10].[ComputerActiveDirectorySettingsId] AS [ComputerActiveDirectorySettingsId], [Extent10].[DistinguishedName] AS [DistinguishedName], [Extent10].[DnsHostName] AS [DnsHostName], [Extent10].[IsAccountDisabled] AS [IsAccountDisabled], [Extent10].[LastLogon] AS [LastLogon], [Extent10].[LastLogonTimestamp] AS [LastLogonTimestamp], [Extent10].[ObjectGuid] AS [ObjectGuid], [Extent10].[OperatingSystem] AS [OperatingSystem], [Extent10].[PasswordLastSet] AS [PasswordLastSet], [Extent10].[Path] AS [Path], [Extent10].[SamAccountName] AS [SamAccountName], [Extent10].[ServicePack] AS [ServicePack], [Extent10].[Sid] AS [Sid], [Extent10].[WhenCreated] AS [WhenCreated], [Join9].[ComputerId19], [Join9].[ComputerId20]
    FROM  [dbo].[ComputerActiveDirectorySettings] AS [Extent10]
    INNER JOIN  (SELECT [Extent11].[ComputerId] AS [ComputerId19], [Extent12].[ComputerId] AS [ComputerId20]
        FROM  [dbo].[Computer] AS [Extent11]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent12] ON [Extent11].[ComputerId] = [Extent12].[ComputerId] ) AS [Join9] ON [Extent10].[ComputerId] = [Join9].[ComputerId19] ) AS [Join10] ON [Extent1].[ComputerId] = [Join10].[ComputerId20]
LEFT OUTER JOIN  (SELECT [Extent13].[ComputerId] AS [ComputerId5], [Extent13].[EntityLastUpdated] AS [EntityLastUpdated5], [Extent13].[ComputerVirtualizationHostSettingsId] AS [ComputerVirtualizationHostSettingsId], [Extent13].[VirtualizationClusterId] AS [VirtualizationClusterId], [Join12].[ComputerId21], [Join12].[ComputerId22]
    FROM  [dbo].[ComputerVirtualizationHostSettings] AS [Extent13]
    INNER JOIN  (SELECT [Extent14].[ComputerId] AS [ComputerId21], [Extent15].[ComputerId] AS [ComputerId22]
        FROM  [dbo].[Computer] AS [Extent14]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent15] ON [Extent14].[ComputerId] = [Extent15].[ComputerId] ) AS [Join12] ON [Extent13].[ComputerId] = [Join12].[ComputerId21] ) AS [Join13] ON [Extent1].[ComputerId] = [Join13].[ComputerId22]
LEFT OUTER JOIN  (SELECT [Extent16].[ComputerId] AS [ComputerId6], [Extent16].[EntityLastUpdated] AS [EntityLastUpdated6], [Extent16].[ComputerNetworkSettingsId] AS [ComputerNetworkSettingsId], [Extent16].[DnsDomain] AS [DnsDomain], [Extent16].[DnsHostName] AS [DnsHostName], [Join15].[ComputerId23], [Join15].[ComputerId24]
    FROM  [dbo].[ComputerNetworkSettings] AS [Extent16]
    INNER JOIN  (SELECT [Extent17].[ComputerId] AS [ComputerId23], [Extent18].[ComputerId] AS [ComputerId24]
        FROM  [dbo].[Computer] AS [Extent17]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent18] ON [Extent17].[ComputerId] = [Extent18].[ComputerId] ) AS [Join15] ON [Extent16].[ComputerId] = [Join15].[ComputerId23] ) AS [Join16] ON [Extent1].[ComputerId] = [Join16].[ComputerId24]
LEFT OUTER JOIN [dbo].[ComputerHardware] AS [Extent19] ON [Join1].[HardwareId] = [Extent19].[ComputerHardwareId]
LEFT OUTER JOIN  (SELECT [Extent20].[ComputerId] AS [ComputerId7], [Extent20].[EntityLastUpdated] AS [EntityLastUpdated7], [Extent20].[CapacityManagedBy] AS [CapacityManagedBy], [Extent20].[ComputerJAPSettingsId] AS [ComputerJAPSettingsId], [Extent20].[ServerPool] AS [ServerPool], [Extent20].[ServerUsage] AS [ServerUsage], [Extent20].[Track] AS [Track], [Extent20].[Usage] AS [Usage], [Extent20].[Version] AS [Version], [Join19].[ComputerId25], [Join19].[ComputerId26]
    FROM  [dbo].[ComputerJAPSettings] AS [Extent20]
    INNER JOIN  (SELECT [Extent21].[ComputerId] AS [ComputerId25], [Extent22].[ComputerId] AS [ComputerId26]
        FROM  [dbo].[Computer] AS [Extent21]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent22] ON [Extent21].[ComputerId] = [Extent22].[ComputerId] ) AS [Join19] ON [Extent20].[ComputerId] = [Join19].[ComputerId25] ) AS [Join20] ON [Extent1].[ComputerId] = [Join20].[ComputerId26]
LEFT OUTER JOIN  (SELECT [Extent23].[ComputerId] AS [ComputerId8], [Extent23].[EntityLastUpdated] AS [EntityLastUpdated8], [Extent23].[ComputerProvisioningDetailsId] AS [ComputerProvisioningDetailsId], [Extent23].[Division] AS [Division], [Extent23].[NumberOfNetworkAdapters] AS [NumberOfNetworkAdapters], [Extent23].[PlannedActivity] AS [PlannedActivity], [Extent23].[PrimaryIPAddress] AS [PrimaryIPAddress], [Extent23].[PrimaryMacAddress] AS [PrimaryMacAddress], [Extent23].[RequestItem] AS [RequestItem], [Extent23].[RequestNumber] AS [RequestNumber], [Join22].[ComputerId27], [Join22].[ComputerId28]
    FROM  [dbo].[ComputerProvisioningDetails] AS [Extent23]
    INNER JOIN  (SELECT [Extent24].[ComputerId] AS [ComputerId27], [Extent25].[ComputerId] AS [ComputerId28]
        FROM  [dbo].[Computer] AS [Extent24]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent25] ON [Extent24].[ComputerId] = [Extent25].[ComputerId] ) AS [Join22] ON [Extent23].[ComputerId] = [Join22].[ComputerId27] ) AS [Join23] ON [Extent1].[ComputerId] = [Join23].[ComputerId28]
LEFT OUTER JOIN  (SELECT [Extent26].[ComputerId] AS [ComputerId9], [Extent26].[EntityLastUpdated] AS [EntityLastUpdated9], [Extent26].[CentralProcessingUnitModelId] AS [CentralProcessingUnitModelId], [Extent26].[ComputerCentralProcessingUnitSettingsId] AS [ComputerCentralProcessingUnitSettingsId], [Extent26].[NumberOfLogicalProcessors] AS [NumberOfLogicalProcessors], [Extent26].[NumberOfProcessors] AS [NumberOfProcessors], [Join25].[ComputerId29], [Join25].[ComputerId30]
    FROM  [dbo].[ComputerCentralProcessingUnitSettings] AS [Extent26]
    INNER JOIN  (SELECT [Extent27].[ComputerId] AS [ComputerId29], [Extent28].[ComputerId] AS [ComputerId30]
        FROM  [dbo].[Computer] AS [Extent27]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent28] ON [Extent27].[ComputerId] = [Extent28].[ComputerId] ) AS [Join25] ON [Extent26].[ComputerId] = [Join25].[ComputerId29] ) AS [Join26] ON [Extent1].[ComputerId] = [Join26].[ComputerId30]
LEFT OUTER JOIN  (SELECT [Extent29].[ComputerId] AS [ComputerId10], [Extent29].[EntityLastUpdated] AS [EntityLastUpdated10], [Extent29].[BackupExclusionRFC] AS [BackupExclusionRFC], [Extent29].[BackupInclusionRFC] AS [BackupInclusionRFC], [Extent29].[BackupMethod] AS [BackupMethod], [Extent29].[BackupType] AS [BackupType], [Extent29].[ComputerBackupSettingsId] AS [ComputerBackupSettingsId], [Extent29].[InterfaceId] AS [InterfaceId], [Extent29].[IsBackupMediaServer] AS [IsBackupMediaServer], [Extent29].[RetentionPeriod] AS [RetentionPeriod], [Extent29].[Status] AS [Status1], [Join28].[ComputerId31], [Join28].[ComputerId32]
    FROM  [dbo].[ComputerBackupSettings] AS [Extent29]
    INNER JOIN  (SELECT [Extent30].[ComputerId] AS [ComputerId31], [Extent31].[ComputerId] AS [ComputerId32]
        FROM  [dbo].[Computer] AS [Extent30]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent31] ON [Extent30].[ComputerId] = [Extent31].[ComputerId] ) AS [Join28] ON [Extent29].[ComputerId] = [Join28].[ComputerId31] ) AS [Join29] ON [Extent1].[ComputerId] = [Join29].[ComputerId32]
LEFT OUTER JOIN [dbo].[ComputerDomain] AS [Extent32] ON [Join1].[DomainId] = [Extent32].[ComputerDomainId]
LEFT OUTER JOIN  (SELECT [Extent33].[ComputerId] AS [ComputerId11], [Extent33].[EntityLastUpdated] AS [EntityLastUpdated11], [Extent33].[ComputerMonitoringSettingsId] AS [ComputerMonitoringSettingsId], [Extent33].[IPAddressId] AS [IPAddressId], [Extent33].[IsExcludedFromBpm] AS [IsExcludedFromBpm], [Extent33].[MonitoringExclusionRFC] AS [MonitoringExclusionRFC], [Extent33].[Profile] AS [Profile], [Join32].[ComputerId33], [Join32].[ComputerId34]
    FROM  [dbo].[ComputerMonitoringSettings] AS [Extent33]
    INNER JOIN  (SELECT [Extent34].[ComputerId] AS [ComputerId33], [Extent35].[ComputerId] AS [ComputerId34]
        FROM  [dbo].[Computer] AS [Extent34]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent35] ON [Extent34].[ComputerId] = [Extent35].[ComputerId] ) AS [Join32] ON [Extent33].[ComputerId] = [Join32].[ComputerId33] ) AS [Join33] ON [Extent1].[ComputerId] = [Join33].[ComputerId34]
LEFT OUTER JOIN  (SELECT [Extent36].[ComputerId] AS [ComputerId12], [Extent36].[EntityLastUpdated] AS [EntityLastUpdated12], [Extent36].[ComputerDisasterRecoverySettingsId] AS [ComputerDisasterRecoverySettingsId], [Extent36].[Setup] AS [Setup], [Extent36].[Usage] AS [Usage], [Join35].[ComputerId35], [Join35].[ComputerId36]
    FROM  [dbo].[ComputerDisasterRecoverySettings] AS [Extent36]
    INNER JOIN  (SELECT [Extent37].[ComputerId] AS [ComputerId35], [Extent38].[ComputerId] AS [ComputerId36]
        FROM  [dbo].[Computer] AS [Extent37]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent38] ON [Extent37].[ComputerId] = [Extent38].[ComputerId] ) AS [Join35] ON [Extent36].[ComputerId] = [Join35].[ComputerId35] ) AS [Join36] ON [Extent1].[ComputerId] = [Join36].[ComputerId36]
LEFT OUTER JOIN [dbo].[ComputeBuildRelease] AS [Extent39] ON [Join1].[BuildId] = [Extent39].[ComputeBuildReleaseId]
LEFT OUTER JOIN [dbo].[NetworkZone] AS [Extent40] ON [Join1].[NetworkZoneId] = [Extent40].[NetworkZoneId]
LEFT OUTER JOIN  (SELECT [Extent41].[ComputerId] AS [ComputerId13], [Extent41].[EntityLastUpdated] AS [EntityLastUpdated13], [Extent41].[ComputerVirtualMachineSettingsId] AS [ComputerVirtualMachineSettingsId], [Extent41].[IdentifyingNumber] AS [IdentifyingNumber], [Extent41].[LeaseEndDate] AS [LeaseEndDate], [Extent41].[LeaseType] AS [LeaseType], [Extent41].[PerformanceTier] AS [PerformanceTier], [Extent41].[PhysicalHostId] AS [PhysicalHostId], [Extent41].[Pool] AS [Pool], [Extent41].[VirtualizationClusterId] AS [VirtualizationClusterId], [Join40].[ComputerId37], [Join40].[ComputerId38]
    FROM  [dbo].[ComputerVirtualMachineSettings] AS [Extent41]
    INNER JOIN  (SELECT [Extent42].[ComputerId] AS [ComputerId37], [Extent43].[ComputerId] AS [ComputerId38]
        FROM  [dbo].[Computer] AS [Extent42]
        LEFT OUTER JOIN [dbo].[AwsEC2Instance] AS [Extent43] ON [Extent42].[ComputerId] = [Extent43].[ComputerId] ) AS [Join40] ON [Extent41].[ComputerId] = [Join40].[ComputerId37] ) AS [Join41] ON [Extent1].[ComputerId] = [Join41].[ComputerId38]
LEFT OUTER JOIN [dbo].[Environment] AS [Extent44] ON [Join1].[EnvironmentId] = [Extent44].[EnvironmentId]
LEFT OUTER JOIN [dbo].[ComputerOperatingSystem] AS [Extent45] ON [Join1].[OperatingSystemId] = [Extent45].[ComputerOperatingSystemId]
LEFT OUTER JOIN [dbo].[ComputerModel] AS [Extent46] ON [Join1].[ModelId] = [Extent46].[ComputerModelId]
LEFT OUTER JOIN [dbo].[Location] AS [Extent47] ON [Join1].[LocationId] = [Extent47].[LocationId]
WHERE [Extent1].[AwsEC2InstanceId] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='01875F53-704D-E811-80E5-0200003F9F37'

Due to execution plan exceeding 2mb, it cannot be pasted on BrentOzar's website.

In PROD execution plan, several hash matches cost a lot. It takes 2 minutes to run. enter image description here

In Dev execution plan, no hash match. It takes 1 second to run. enter image description here

My question is why so much different execution plan? Any chance it can finish in PROD less than 90 second?

If the query could be rewritten by human, all the subqueries can disappear and changed to simple left join, it runs very fast.

  • 2
    The optimizer changed from 2008 to 2012 and again in 2014. You should not expect a 1:1 when testing in your dev environment to your prod environment. I believe you can put your dev environment down to a different compatibility level and adjust it to run as the old engine though. – Shaulinator May 25 '18 at 19:39
  • 1
    In addition to @Shaulinator You said there aren't any big difference on row counts in prod and dev, but what is the difference? Index differences? Updated statistics in both environments? Can you do a back up and restore to dev to make them a wee bit closer? – scsimon May 25 '18 at 19:40
  • You can always post the XML on pastebin to share. – Erik Darling May 25 '18 at 19:43
  • 1
    @Wendy - how about TF 9481 just to rule out the new CE affecting the plan ? – Kin Shah May 25 '18 at 20:47

Your Answer

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

Browse other questions tagged or ask your own question.