Sql-server – Speed up Key Lookup (clustered)

execution-planquery-performancesql server

I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%.

Is there anyway I can speed this query up?

Execution plan: https://www.brentozar.com/pastetheplan/?id=r1oIcWofd

exec sp_executesql 
N'
SELECT TOP(@__p_4) 
    [v].[County], 
    [v].[Community], 
    [v].[DataProvider], 
    [v].[Site], 
    [v].[Latitude], 
    [v].[Longitude], 
    [v].[Elevation], 
    [v].[Instrument], 
    [v].[MonitorId], 
    [v].[ParameterId], 
    [v].[Parameter], 
    [v].[MeasurementStartTime], 
    [v].[MeasuredValue], 
    [v].[AdjustedValue], 
    [v].[Units], 
    [v].[AdjustmentDesc], 
    [v].[LowerLimitCheck], 
    [v].[UpperLimitCheck], 
    [v].[RepeatingValueCheck], 
    [v].[SpikeCheck], 
    [v].[QualityPreliminary]
FROM [vw_DownloadDataSubhourly] AS [v]
WHERE 
(
    (([v].[CountyId] = @__Parse_0) AND ([v].[MeasurementStartTime] >= @__start_1)) 
    AND ([v].[MeasurementStartTime] <= @__end_2)
) 
AND [v].[ParameterType] IN (''PM2.5'')
ORDER BY [v].[Site], [v].[MonitorId], [v].[ParameterId], [v].[MeasurementStartTime]
',
N'@__p_4 int,@__Parse_0 int,@__start_1 datetime,@__end_2 datetime',
@__p_4=750000,@__Parse_0=34,@__start_1='2019-10-01 00:00:00',@__end_2='2020-12-31 23:59:59'

vw_DownloadDataSubhourly
Execution plan: https://www.brentozar.com/PasteThePlan/?id=BkiX1Vsz_


CREATE view [dbo].[vw_DownloadDataSubhourly] as

SELECT      
                DataProvider.DataProviderId, 
                County.CountyId, 
                Community.CommunityId, 
                County.CountyName as "County",
                Community.CommunityName as "Community",
                DataProvider.DataProviderShortName as "DataProvider", 
                Site.SiteLongName as "Site",
                SiteConfig.LatitudeTrunc as "Latitude",
                SiteConfig.LongitudeTrunc as "Longitude",
                SiteConfig.Elevation,
                Monitor.ExternalMonitorId as "MonitorId",
                Parameter.ParameterId as "ParameterId",
                Parameter.ParameterNamePublic "Parameter",
                Parameter.ParameterType "ParameterType",
                SubHourlyMeasurementData.StartDateTime "MeasurementStartTime",
                SubHourlyMeasurementData.AObs as "MeasuredValue",
                SubHourlyMeasurementData.AObsAdj as "AdjustedValue",
                UOM.UOMAbbreviation as "Units",
                AdjCode.AdjCodeDescription as "AdjustmentDesc",
                concat(MonitorConfig.MonitorMfr, ' ', MonitorConfig.MonitorModel) as "Instrument",
                case 
                    when SubHourlyMeasurementData.QF06 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF06 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF06 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF06 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF06 = '1' then 'Fail'
                    else NULL
                    end as "LowerLimitCheck",
                case 
                    when SubHourlyMeasurementData.QF05 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF05 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF05 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF05 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF05 = '1' then 'Fail'
                    else NULL
                    end as "UpperLimitCheck",
                case 
                    when SubHourlyMeasurementData.QF04 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF04 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF04 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF04 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF04 = '1' then 'Fail'
                    else NULL
                    end as "RepeatingValueCheck",
                case 
                    when SubHourlyMeasurementData.QF07 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF07 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF07 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF07 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF07 = '1' then 'Fail'
                    else NULL
                    end as "SpikeCheck",
                case 
                    when SubHourlyMeasurementData.QOverall = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QOverall = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QOverall = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QOverall = '0' then 'Passed all checks'
                    when SubHourlyMeasurementData.QOverall = '1' then 'Suspect'
                    else NULL
                    end as "QualityPreliminary"

FROM           SubHourlyMeasurementData 
                         INNER JOIN
                         DataProvider ON SubHourlyMeasurementData.DataProviderId = DataProvider.DataProviderId 
                         INNER JOIN
                         Site ON SubHourlyMeasurementData.SiteId = Site.SiteId AND Site.DataProviderId = DataProvider.DataProviderId
                         LEFT JOIN Community ON Site.CommunityId = Community.CommunityId
                         INNER JOIN 
                         SiteConfig ON SiteConfig.SiteId = SubHourlyMeasurementData.SiteId
                         INNER JOIN
                         County ON County.CountyId = SiteConfig.CountyId
                         INNER JOIN
                         Monitor ON Monitor.MonitorId = SubHourlyMeasurementData.MonitorId
                         INNER JOIN
                         MonitorConfig ON MonitorConfig.MonitorConfigId = SubHourlyMeasurementData.MonitorConfigId
                         INNER JOIN
                         Parameter ON Parameter.ParameterId = SubHourlyMeasurementData.ParameterId
                         INNER JOIN
                         UOM ON SubHourlyMeasurementData.UOMId = UOM.UOMId
                         LEFT JOIN
                         AdjCode ON SubHourlyMeasurementData.AdjCodeId = AdjCode.AdjCodeId
                         INNER JOIN
                         StreamSegment ON SubHourlyMeasurementData.StreamSegmentId = StreamSegment.StreamSegmentId
                         INNER JOIN 
                         MeasurementTechnique ON MeasurementTechnique.MeasurementTechniqueId = StreamSegment.MeasurementTechniqueId
                         AND MeasurementTechnique.ParameterId = SubHourlyMeasurementData.ParameterId
                         INNER JOIN
                         SamplingDuration ON SamplingDuration.SamplingDurationId = StreamSegment.SamplingDurationId
                         INNER JOIN
                         SamplingFrequencyUnits ON SamplingFrequencyUnits.SamplingFrequencyUnitsId = StreamSegment.SamplingFrequencyUnitsId

WHERE SubHourlyMeasurementData.StartDateTime >= MonitorConfig.StartDateTime and StreamSegment.IsDownloadable = 1 and SubHourlyMeasurementData.iscalculated = 1 

GO

Best Answer

J.D. has covered the stats bit - essentially, you might get better estimates if you have a more representative stats sample for the indexes on the SubHourlyMeasurementData table. Right now it's only sampling around 1% of the rows in the table:

Screenshot of stats details

Here we can see that the stats have been updated (ModificationCount is 0), but the SamplingPercent was only 1.41871. SQL Server picks this percentage automatically based on the number of rows in the table.

You can try updating stats with FULLSCAN, which might take a while:

UPDATE STATISTICS dbo.SubHourlyMeasurementData
WITH FULLSCAN;

If that helps you can work down to a more manageable percentage that still solves the problem:

UPDATE STATISTICS dbo.SubHourlyMeasurementData
SAMPLE 50 PERCENT;

Regarding the key lookup, that is required in order check the residual predicate IsCalculated=1:

Screenshot of key lookup doing a residual predicate

You can avoid the lookup by adding IsCalculated to the INCLUDE list for the <IDX_DownloadDataSubHourly> index:

CREATE NONCLUSTERED INDEX [<IDX_DownloadDataSubHourly>]
ON dbo.SubHourlyMeasurementData (...)
INCLUDE (..., IsCalculated);

If that predicate is used a lot, and it's fairly selective, you might consider creating a filtered index for it (or adding WHERE IsCalculated=1 to the current nonclustered index, but that might break other queries trying to use it).

Related Question