I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019)
Actual Execution Plan
https://www.brentozar.com/pastetheplan/?id=HJzjpF5Tv
Query
declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C030000408ED64540020000800DDC53C0)
exec sp_executesql N'WITH #myselect
AS (SELECT
BedroomsAvailable
,ListingId
,UnitTypeId
,Price,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)
OVER (PARTITION BY UnitTypeId, BedroomsAvailable) AS MedianCont
FROM Listings
WHERE ExpiryDate >= GetDate() AND Price >= @MinFilterPrice AND Price <= @MaxFilterPrice AND Location.STDistance(@CurrentLocation) <= @RadiusInKms )
SELECT BedroomsAvailable
,COUNT(listingid) AS Count
,MIN(price) AS MinPrice
,MAX(price) AS MaxPrice
,AVG(price) AS AveragePrice
,STDEV(price) as StandardDeviation
,MIN(MedianCont) MedianPrice
,UnitTypeId
FROM #myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId',N'@CurrentLocation (geography),@RadiusInKms int,@MinFilterPrice decimal(3,0),@MaxFilterPrice decimal(5,0)',@CurrentLocation=@p3,@RadiusInKms=20000,@MinFilterPrice=100,@MaxFilterPrice=10000
Indexes
CREATE SPATIAL INDEX (16_HHHH) ON (dbo).(Listings)
(
(Location)
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
CREATE SPATIAL INDEX (16_MMMM) ON (dbo).(Listings)
(
(Location)
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
CREATE SPATIAL INDEX (256_HHHH) ON (dbo).(Listings)
(
(Location)
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
CREATE NONCLUSTERED INDEX (IX_Listings_CityId) ON (dbo).(Listings)
(
(CityId) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON (PRIMARY)
GO
ALTER TABLE (dbo).(Listings) ADD CONSTRAINT (PK_Listings) PRIMARY KEY CLUSTERED
(
(ListingID) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON (PRIMARY)
GO