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.
In Dev execution plan, no hash match. It takes 1 second to run.
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.
