I've been researching slow running queries in our database, and have concluded that this is a classic Ascending Key Problem. As new rows are inserted almost constantly, and a given piece of SQL to pull the latest data out of the DB runs every 30 minutes, the first option of updating stats every 30 minutes sounded like it could be a waste of resource.
So, I looked into Trace Flag 2389 which in principle should help, however that requires the Leading column to be branded as Ascending, and when I used Trace Flag 2388 to check the (PK) index stats, I see that the leading column is actually branded as Stationary - as it is for several of the PK indexes on other tables updated at the same time.
There doesn't seem to be much guidance on what results in a branding of Stationary, however I did find KB2952101 that says if less than 90% of inserts were greater than the old maximum value, it would be classed as Stationary. All our inserts are new submissions, and the leading column is a bigint IDENTITY column, so 100% of inserts should be greater than the maximum previous value.
So my question is why would the column be branded as Stationary, when it is obviously Ascending?
An earlier attempt to resolve this problem for some daily running SQL (which worked really well) resulted in a job being setup to update statistics for this table nightly. The update doesn't do a FULLSCAN, so could it be that the sampled scan is missing the new rows sometimes, so it's not always showing as ascending?
The only other thing I can think of that might affect this, is that we have an archive job running behind the scenes deleting rows over a certain age. Could this have an affect on the branding?
The server is SQL Server 2012 SP1.
Update: Another day, another stats update - same stationary branding. There have been 28049 new inserts since the previous stats update. Each row has a timestamp of when it was inserted, so if I select max(id) from table where timestamp < '20161102' I get 23313455 Similarly, if I do that for when the stats were updated today, I get 23341504.
The difference between these is the 28049 new inserts, so as you can see, all the new inserts were given new ascending keys (as expected), suggesting the leading column should be branded as ascending rather than stationary.
During the same period, our archiving job deleted 213,629 rows (we're slowly clearing old data). Is there any chance that a reducing rowcount could contribute to the stationary branding? I've tested this before and it didn't look like it made any difference.
Update 2: Another day, another stats update, and the column is now flagged as Ascending! As per the theory about deletes affecting this, I checked the percentage of updates being inserts compared to deletes, and yesterday 13% were inserts, whereas the previous two days inserts accounted for about 12%. I don't think that gives us anything conclusive.
Interestingly, a related table that gets on average 4 rows inserted for each row inserted into this main table, and has it's stats updated at the same time, has it's IDENTITY PK column still as Stationary!?
Update 3: Over the weekend we get more inserts. This morning the leading column is back to Stationary. On the last stats update, we had 46840 inserts and only 34776 deletes.
Again, interestingly, the related table I mentioned above now has it's leading column branded as Ascending. Is there no documentation that can explain this?
Update 4: It's been a week or so now, the archiving job has cleared the backlog, so we're consistently deleting about two thirds of the number of rows being inserted. The stats are showing mixed results across the related tables, with one showing stationary, and two showing ascending, despite them all being updated proportionally similarly.