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
2

Time spent automatically updating statistics is included in compile time. The stats update takes 10 minutes, and the compile time for a query that triggers an auto stats update includes that time - which is why the query takes over 10 minutes every now and then.

The modification threshold for an automatic stats update, per the documentation, is

sqrt(table cardinality * 1000)

With your numbers (300,000,000 rows), you hit that threshold at 547,722 modifications.

Since 1,000,000 rows are being added every day, you're bound to hit an auto stats update around twice a day.

Since your query's estimates are good before and after the stats update, one possible solution would be to enable the database level AUTO_UPDATE_STATISTICS_ASYNC setting. This still let's SQL Server update stats automatically, but it's done asynchronously (so queries don't have to wait on it to happen):

ALTER DATABASE [Live] 
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Note that this affects the entire database, not just that table / statistic.

| improve this answer | |
  • Thanks Josh, this is the solution we came up with in the end, but I never came back and posted my findings. – Andrew Williamson Dec 27 '19 at 19:58
  • @AndrewWilliamson Oh awesome, great minds think alike =) – Josh Darnell Dec 27 '19 at 20:03

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.