I have been tuning some queries that had a warning on the sort operator, which is generally an expensive operator:
Operator used tempdb to spill data during execution with spill level 2
The way to improve the performance and get rid of the warning on the Sort Operator,was to update the statistics.
UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder] WITH FULLSCAN
--1 hour 04 min 14 sec
But as you can see it took over an hour to update the stats.
Now I have a similar query with the same problem.
when the @toDate is today, or yesterday I get the same warning: Operator used tempdb to spill data during execution with spill level 2
The query is below, the plan is here., Picture below for a quick look.
DECLARE @FromDate DATETIME = getdate()-1
DECLARE @ToDate DATETIME = getdate()-0
SELECT DISTINCT
ap.strAccountCode
,em.strEmail
,em.dtmLastUpdated
FROM Bocss2.dbo.tblBAccountParticipant AS ap
LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
ON ap.lngParticipantID = em.lngParticipantID
INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
ON ap.lngParticipantID = ah.lngParticipantID
INNER JOIN Bocss2.dbo.tblBOrder AS o
ON ap.lngParticipantID = o.lngAccountParticipantID
WHERE o.sdtmOrdCreated >= @FromDate
AND o.sdtmOrdCreated < @ToDate
OPTION (RECOMPILE)
But when @toDate is 2 days ago, I don't get any warning at all.
The query is below, the plan is here., Picture below for a quick look.
DECLARE @FromDate DATETIME = getdate()-3
DECLARE @ToDate DATETIME = getdate()-2
SELECT DISTINCT
ap.strAccountCode
,em.strEmail
,em.dtmLastUpdated
FROM Bocss2.dbo.tblBAccountParticipant AS ap
LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em
ON ap.lngParticipantID = em.lngParticipantID
INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah
ON ap.lngParticipantID = ah.lngParticipantID
INNER JOIN Bocss2.dbo.tblBOrder AS o
ON ap.lngParticipantID = o.lngAccountParticipantID
WHERE o.sdtmOrdCreated >= @FromDate
AND o.sdtmOrdCreated < @ToDate
OPTION (RECOMPILE)
Updating the stats of the order table solves this problem, however, it takes over an hour to run.
UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder] WITH FULLSCAN
--1 hour 04 min 14 sec
I have good reasons to update the stats manually.
I also found this link interesting: Why does SQL Server refuse to update these statistics with anything but fullscan?
Question:
Instead of updating the stats with full scan, is there a way I can just update the stats for the last day?
that would be something I could run on a daily basis.
this table in particular get 50k new records every day on average.
WITH FULLSCAN
? It might be better to just update using the entire table but with a sample instead of trying to read each and every row.