How can I update statistics adding the data of the last day only?

Question

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.

Answer 1

The first thing to try is experimenting with sampling instead of performing a full scan every time.

If lower samples are causing poor plans or still taking too long you could consider partitioning and if you are on SQL Server 2014 or higher you could use Incremental Statistics.

Incremental Statistics allow you to rebuild statistics at the partition level. There is still just one statistics object for the whole table but the statistics for the partition are merged into the histogram.

If you're not running on Enterprise edition or you are on a version prior to 2014 you might consider using partitioned views. This way you can partition your data into completely separate tables with their own statistics objects.

Partitioning and partitioned views bring their own sets of complications but are usually required for very large tables.

You may be suffering from the ascending key problem, see here. If you are then one last option could be to update statistics less often and use the trace flags 2389 and 2390 to improve cardinality estimates in the time between updates.