Commonmark migration
Source Link

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?

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?

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?

Source Link

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