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.

enter image description here

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.

  • Is it possible to use, instead of ad hoc SQL, a stored procedure to do the querying? Then it would be possible to compile the plan for the stored procedure in such a way that the optimiser chooses a good plan and then reuses it every time the procedure is run. More info with Brent Ozar – mendosi Nov 2 '16 at 12:57
  • To be honest, I'm not sure - the SQL is generated by a 3rd party application. I'm not sure either that I'd get a consistently good plan, as the optimize for unknown option is likely to pick a plan based on many more rows that we will get (as it's only picking the inserts since the last run), and if I didn't use that and the plan was recompiled, it would still not have stats available to create a good plan. Also, I still want to understand the Stationary branding, but thanks for your input! – Nik Nov 2 '16 at 14:00
  • plan guide? that's a fairly standard course of action for a 3rd party application and seems much less painful than what you're currently delving into, and wouldn't require the constant stats updates. – Bruce Dunwiddie Nov 3 '16 at 19:37
  • I haven't used plan guides before, so I'll investigate further, however the third party SQL has a habit of changing periodically, which, judging by what I've read so far, will mean I need to monitor it and adjust the guide as appropriate each time. – Nik Nov 4 '16 at 8:55
  • @Nik Did you try trace flag 4139 mentioned in the KB you reference? Or are you more concerned with an explanation of the general behaviour, and not looking for a workaround/fix as such? – Paul White Dec 19 '16 at 5:30

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
  • Many thanks for the repro, that backs up the 10% theory, but leaves me just as confused about why my PK index leading column is being branded as Stationary. I've updated the question with the results of last nights stats update - I must be missing something... – Nik Nov 3 '16 at 9:23
  • I did a quick test using the same script as above but only ran the insert and delete parts. It does seem if you delete a vastly larger amount of rows than inserted it will revert back to stationary (again about 10%). In your updated data, you've inserted roughly 10% of all data changes made - it seems the deletes are giving you grief. At this point I would suggest letting the stats object hit ascending and then freeze it by not updating it. – Sean Gallardy Nov 3 '16 at 15:09
  • I tried recreating what you had done, and ended up adding 10 more rows and deleting 1000, updating stats, then showing stats, and it still shows as ascending: Inserts since last update: 10, Deletes since last update: 1000, Leading column type: Ascending. Not sure why I get a different result to you? If your findings are correct, It might just be a case of putting up with less than optimal results now, then when our backlog of archiving has cleared, try again. – Nik Nov 4 '16 at 9:11

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.

  • Thanks, the IDs are all created by SQL Server. I understand flag 2371 just makes the stats updates more frequent. Judging by the table at brentozar.com/archive/2016/03/…, our table size (14M rows), and the number of daily inserts (28K rows), the stats would still only get updated every 4 days or so, so the extract of new data will most of the time not know of the new data. The queries I looked at aren't a concern, it's understanding how SQL Server brands the leading column that I'd like to know. Thanks again. – Nik Nov 30 '16 at 12:15

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.