1

I've got a large table (300 million rows), with about 1 million rows added per day. This is time-series data, so I have the following setup:

CREATE TABLE Readings (
    Id INT PRIMARY KEY,
    TrackerId INT,
    DateProcessed DATETIME,
    -- About 10 other columns of data
)

CREATE INDEX IX_TrackerId_DateProcessed
ON Readings (
    TrackerId,
    DateProcessed
) INCLUDE (
    -- all the other columns
) WHERE (
    TrackerId IS NOT NULL
)

When we view the data, I usually run something like the following query:

SELECT Latest.*
FROM Trackers
CROSS APPLY (
    SELECT TOP 1 *
    FROM Readings
    WHERE TrackerId = Trackers.Id
    ORDER BY DateProcessed DESC
) Latest

This used to work fine. Now, it takes forever to generate the query plan, even when I explicitly tell it to use the index. The only solution I have found so far is to update the statistics for the table with the following command:

UPDATE STATISTICS Readings

This takes about ten minutes to run, and then the select query runs fine, but only for a day. Then I have to update the statistics again. Why are the statistics getting out of date so quickly, and what can I do to prevent this?

Points worth mentioning:

  • I only ever append to the table, I never update or delete rows
  • There are some old rows with a null value for tracker id, but no more will be added
  • The database is running at compatibility level 14
  • I can't enable trace flag 2371 in Azure SQL

Additional answers to questions in the comments

The estimated query plan before updating the statistics

The estimated query plan after updating the statistics

The plans before and after updating the statistics look identical to me. The issue is not that I am getting a bad query plan, but how long it takes to get the plan. Before updating the statistics, I had to wait two minutes for the plan. After updating the statistics, it got the plan instantly.

While writing this, I just realized that it would actually be quicker to wait for the query, and let the cache do the work, rather than update the statistics, but that's still not solving the problem. This query is executed from within Entity Framework, with a timeout of 30 seconds, so two minutes is still too long.

  • On the duration, 69 seconds were spent compiling. You can see that in <QueryPlan ... CompileTime="69302" - that's not good. But I don't know if there's an obvious way to track down exactly why the compile time is high before the stats update, unless you take a much closer look (try SentryOne Plan Explorer with Live Query Profile enabled). I think you're right that the stats update doesn't directly affect query execution; after all, the plans are the same and the estimates are largely the same, outside of row goals. Actual plans will be good to see. – Aaron Bertrand Nov 12 '18 at 19:24
  • Hmm, I don't know if I can justify the amount of time I'm spending on this. I might just add the appropriate columns to the Tracker, and store the latest values inline – Andrew Williamson Nov 12 '18 at 19:53

Your Answer

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

Browse other questions tagged or ask your own question.