Ascending Key Problem - Leading column branded "Stationary" - SQL Server

Question

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.

Answer 1

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?

It'll be branded stationary if, like you've already stated, that 10% or more of the inserts aren't ascending. If 100% of your inserts were as you say... then you might not have this problem, until of course you delete but then it would go back to unknown.

Here is a repro of your issue:

use master;
go
-- create a database for this to test
create database AscendingKey;
go

use AscendingKey;
go
-- create a test table
create table dbo.AscendingKeyTableTest
(
    SomeData        char(100) default('A'),
    AscendingKey    bigint not null,
);
go

-- insert some dummy data
set nocount on
go

declare @i int = 1

while(@i <= 1000)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- create stats on the ascendingkey column
create statistics AscendingKeyStats on dbo.AscendingKeyTableTest(AscendingKey);
go

-- look at the stats
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- ascending!
-- we hit the 3x stats updates to have it 'learn'

-- what happens if we insert more than 10% that isn't ascending
declare @i int = 1;

while(@i <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- still says ascending... but...
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- what if we update again?
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go
-- stationary
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- get it back to ascending
declare @i int;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+1);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+2);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+3);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go

-- what about the deletes?
delete from AscendingKeyTableTest where AscendingKey % 3 = 0
go

update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- back to unknown

-- cleanup
use master
go

drop database AscendingKey
go

Answer 2

In my estimation, unless you are generating the leading key column on the server itself, you can get into little potholes relying on it being generated on individual clients.

Also, have you tried Trace Flag 2371.

TF 2371 is documented here.

The KB is titled "Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server." and the KB is 2754171.

It will be very useful if you were to list for us the actual impacts of the new data not making it into the stats in time.

Were wrong indexes chosen. And, if so can you please list for us the indexes and their primary keys?

Also, can you please share the plans that are generated when the stats are dated against when they are timely. I will like to compare the two.

My thought is that SQL Cost Based Optimizer ( Rules and Costing ) decisions are quite good; outside of esoteric areas like this one.

If this is a special case, more explanation may be be helpful and justify opening a Connect Item.

As an aside, in my humble opinion you will have greater overall benefits in having the vendor use SP than ad-hoc SQL.