Production query suddenly timing out

Question

I have a query that is suddenly timing out. I'm using Dapper.NET in my application and this query was running fine up until a couple days ago. This query when copied directly from Azure performance insights will run perfectly fine from SSMS or Azure Data Studio. I even tried running the query through ADO.NET and it times out as well.

I recently transferred a subscription over for the Microsoft Partner Network, but that's the only thing I've recently changed. Any thoughts on this? Happy to provide the query, but it runs fine directly in SSMS so not sure how useful that would be.

Answer 1

My query is below. The crazy thing that makes it run, is if I rename the parameter @Eslam Nader to @ajax , the query works fine. What am I missing? Why would this solve the issue?

SELECT
MONTH(Received) as Month,
YEAR(Received) as Year,
SUM(dd.AmountCents) as TotalDonated,
AVG(dd.AmountCents) as AverageDonation,
COUNT(dd.DonationId) as TotalDonations,
COUNT(Distinct DonorId) as UniqueDonors,
MAX(m.Median) as MedianDonation
FROM [dbo].[Donations] d
inner join DonationDesignations dd on dd.DonationId = d.DonationId
inner join
(
Select
DonationId,
Month(Received) as Month,
Year(Received) as Year,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY AmountCents desc) OVER (PARTITION BY YEAR(Received), MONTH(Received)) as Median
from [dbo].[Donations]
where OrganizationId = @orgId
and Status = 'succeeded'
and Received >= @START
and Received <= @Eslam Nader
and IsRefunded = 0
) m on m.DonationId = d.DonationId
where OrganizationId = @orgId and IsRefunded = 0 and Status = 'succeeded'
and Received >= @START and Received <= @Eslam Nader
group by MONTH(Received), YEAR(Received)
order by YEAR(Received), MONTH(Received)

Answer 2

Please try below changes, and let me know.

Select
DonationId,
Month(Received) as Month,
Year(Received) as Year,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY AmountCents desc) OVER (PARTITION BY YEAR(Received), MONTH(Received)) as Median
INTO #temp
from [dbo].[Donations]
where OrganizationId = @orgId
and Status = 'succeeded'
and Received >= @start
and Received <= @end
and IsRefunded = 0

CREATE NONCLUSTERED INDEX ix_tempNCIndex_Temp_DonationId ON #temp ([DonationId] ASC) INCLUDE ([Median]);

SELECT
MONTH(Received) as Month,
YEAR(Received) as Year,
SUM(dd.AmountCents) as TotalDonated,
AVG(dd.AmountCents) as AverageDonation,
COUNT(dd.DonationId) as TotalDonations,
COUNT(Distinct DonorId) as UniqueDonors,
MAX(m.Median) as MedianDonation
FROM [dbo].[Donations] d
inner join DonationDesignations dd on dd.DonationId = d.DonationId
inner join #temp m on m.DonationId = d.DonationId
where OrganizationId = @orgId and IsRefunded = 0 and Status = 'succeeded'
and Received >= @start and Received <= @end
group by MONTH(Received), YEAR(Received)
order by YEAR(Received), MONTH(Received)

DROP TABLE IF EXISTS  #temp

Answer 3

The root problem is likely to be due to parameter sniffing. As for the strange behaviour with the query being slow in the application and fast in SSMS, it has to do with how SQL Server organizes its cache.

I will not go into details here, but I have a longer article Slow in the Application, Fast in SSMS? on my web site which explains in detail what is going on, and I also attempt to give solutions for the root problem.