2

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)

enter image description here

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)

enter image description here

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.

  • 3
    Why do you insist on running your update 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. – Tom V Oct 25 '16 at 10:51
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.