SQL Server – Statistics – Ascending Value Columns

Background

Reviewing Trace Flags and ran into a few dedicated to Statistics on Ascending Values.

We are going to use Fabiano Amorim’s most glamorous post as our teaching tool.

BTW, Fabiono’s blog post is titled “Statistics on Ascending Columns” and it is published via Simple Talk and available here.

 

Trace Flags

To help us, let us list corresponding Trace Flags.

Trace Flag Description Version Link
 2371 Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server  v2008R2-SP1+  Link
 2388 Change DBCC SHOW_STATISTICS output to show stats history and lead key type such as known ascending keys  v2005-SP1+  Link
 2389  Enable automatically generated quick statistics for ascending keys (histogram amendment). If trace flag 2389 is set, and a leading statistics column is marked as ascending, then the histogram used to estimate cardinality will be adjusted at query compile time.  v2005-SP1+  Link
 2390 Trace flag 2390 enables the same behavior even if the ascending nature of the column is not known.  As long as the column is a leading column in an index, then the optimizer will refresh the statistic (with respect to the highest value) at query compile time.  v2005-SP1  Link

 

 

 

Historical Background

The Trace Flag 2388 was introduced with SQL Server 2005 SP1.

It is the only one we will dig into during this post.

 

Outline

Here is a brief outline of the steps we will take:

  1. Create Table
  2. Add Initial Data
  3. Review Stats using Trace Flag 2388 and w/o the Trace Flag
  4. Add data in batches of three and update Statistics, as well
  5. Go back and review Statistics using Trace Flag 2388 and also the traditional No Trace Flag way

 

Create Tables

Create Table – ascendingKeyColumns.Orders

Let us create our table.

We will place it in its own schema ascendingKeyColumns.  And, title it Orders.


use [DBLab]
go

if schema_id('ascendingKeyColumns') is null
begin

	exec('create schema [ascendingKeyColumns]  authorization [dbo]')
end
go

/*

	drop table [ascendingKeyColumns].[Orders]

*/
if object_id('[ascendingKeyColumns].[Orders]') is null
begin
	
	create table [ascendingKeyColumns].[Orders]
	(

		  [id] bigint identity(1,1) not null

        , [batchID] int not null
			constraint [AKCBatchID] default (0)

		, [clientID] int not null

		, [dateAdded] datetime not null

		, [amount]    money not null

		, constraint [PK_AKC_Order] 
			primary key clustered
			(
				[id]
			)
	)


end
go


if not exists
(
	select *
	from   sys.indexes tblSI
	where  tblSI.object_id = object_id('[ascendingKeyColumns].[Orders]')
	and    tblSI.name = 'INDX_BatchID'
)
begin

	create nonclustered index [INDX_BatchID]
	on [ascendingKeyColumns].[Orders]
	(
		[BatchID]
	)

end

go
if not exists
(
	select *
	from   sys.indexes tblSI
	where  tblSI.object_id = object_id('[ascendingKeyColumns].[Orders]')
	and    tblSI.name = 'INDX_DateAdded'
)
begin

	create nonclustered index [INDX_DateAdded]
	on [ascendingKeyColumns].[Orders]
	(
		[dateAdded]
	)

end

go

sp_autostats  '[ascendingKeyColumns].[Orders]', 'off'
go


Explanation

  • Create Schema ascendingKeyColumns
  • Create Table ascendingKeyColumns.Orders
  • Create Clustered Primary Key PK_AKC_ORDER
  • Create NonClustered Index INDX_DateAdded
  • Turn AutoStats off for the table
    • This allows us to control when stats will be updated

 

 

Add Data to Tables

Initial

Here is the initial load of data

use [DBLab]
go

set nocount on
go

truncate table [ascendingKeyColumns].[Orders];
go


declare @seed int
declare @date datetime
declare @dateNext datetime
declare @datePart varchar(30)

declare @batchID     int
--declare @batchIDNext int
declare @numberofRows int

--set @datePart = 'millisecond'
--set @datePart = 'second'
set @datePart = 'minute'

set @numberofRows = 50E3
set @seed = 1E6
set @date = getdate()
set @dateNext = '1970-01-01'

set @batchID = ( select max([batchID]) from [ascendingKeyColumns].[Orders])

if (@batchID is null)
begin
	set @batchID = 0
end

;with cte
as
(
	SELECT TOP (100) PERCENT 
		[rowNumber] = ROW_NUMBER() OVER 
						(
							ORDER BY tblSPT.[number]
						) 

	FROM [master]..spt_values tblSPT

	cross apply [master]..spt_values tblSPTNext
			
	ORDER BY [rowNumber]
)
-- Inserting 50000 rows in the table
INSERT INTO [ascendingKeyColumns].[Orders]
WITH(TABLOCK) 
(
      [batchID]
	, [clientID] 
	, [dateAdded]
	, [amount]
)
SELECT 
		   TOP ( @numberofRows )

		   [batchID]
			= @batchID

		,  [clientID] 
			= ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000)))

		, [dateAdded]
			--= dateadd(minute, (rand() * @seed) * -1, @date)
			  = 
				case @datePart
						when 'millisecond' then dateadd(MILLISECOND, cte.rowNumber, @dateNext)
						when 'second' then dateadd(SECOND, cte.rowNumber, @dateNext)
						when 'minute' then dateadd(MINUTE, cte.rowNumber, @dateNext)
				end
	
		, [amount]
			= rand(cte.rowNumber) * @seed

from cte 

go

--UPDATE STATISTICS  [ascendingKeyColumns].[Orders] [INDX_DateAdded] WITH FULLSCAN
UPDATE STATISTICS  [ascendingKeyColumns].[Orders] WITH FULLSCAN
go


Explanation

  1. Truncate table
  2. Add 50K records
  3. Update statistics

 

Metadata

Show Statistics / TF 2388 (ON)

Let us review statistics using Trace Flag 2388

Code


-- Enable trace flag 2388
DBCC TRACEON(2388)
GO

	DBCC SHOW_STATISTICS ( 'ascendingKeyColumns.Orders', 'INDX_DateAdded' )
	GO

-- Disable trace flag 2388
DBCC TRACEOFF(2388)
GO

Output

dbccshowstatistics-local-2388-initial-20161124-0434pm

 

Explanation

  • Columns
    • Updated
      • Update Timestamp
    • Table Cardinality
      • 50K
    • Rows Above & Rows Below
      • Null & Null
    • Inserts Since Last Update
    • Deletes Since Last Update
    • Leading column Type

 

 

Show Statistics / Classic

Let us review statistics without using Trace Flag 2388

Code


-- Disable trace flag 2388
DBCC TRACEOFF(2388)
GO

DBCC SHOW_STATISTICS ( 'ascendingKeyColumns.Orders', 'INDX_DateAdded' )
GO

Output

dbccshowstatistics-local-no2388-incremental-20161125-0811pm

 

Explanation

  1. Number of Rows is 50000
  2. Range_HI_KEY
    • Entries
      • 1970-01-01 00:01
      • 1970-02-04 17:19
      • 1970-02-04 17:20

Add Data to Tables

Incremental Load

Code


set nocount on
go
 
 
declare @seed int
declare @date datetime
declare @dateBaseline  datetime
declare @dateNext datetime
declare @datePart varchar(30)
declare @numberofRows int
declare @commit bit
 
declare @numberofRecordsAffected int
declare @log varchar(600)
  
declare @iAttempt smallint
declare @iAttemptMax smallint
declare @updateStats bit
 
declare @id bigint
 
declare @CHAR_TAB char(1)
 
declare @batchID     int
declare @batchIDNext int
 
set @CHAR_TAB = char(9)
 
--set @datePart = 'millisecond'
--set @datePart = 'second'
set @datePart = 'minute'
set @iAttempt = 1
set @iAttemptMax = 3
set @updateStats = 0
set @updateStats = 1
 
  
set @seed = 1E6
 
set @numberofRows = 50E3
set @commit = 0
set @commit = 1
 
set @batchID = 0
 
delete
from   [ascendingKeyColumns].[Orders]
where  [batchID] >0
 
set @id = ( select max([id]) from [ascendingKeyColumns].[Orders])
 
if (@id is null)
begin
 
    set @id = 0
 
end
 
 
 
DBCC CHECKIDENT ('[ascendingKeyColumns].[Orders]', RESEED, @id)
  WITH NO_INFOMSGS
  ; 
 
while (@iAttempt <= @iAttemptMax)
begin 

	begin tran 

		set @date = getdate() 

		set @dateBaseline = 
								cast(datepart(year, @date) as varchar(4))
							  + '-01-01' 
	
		set @dateNext = dateadd(year, @iAttempt, @dateBaseline)
	
		set @batchIDNext = @batchID + @iAttempt 

		-- Batch Inserts into the table
		;with cte 
		as 
		( 
			SELECT TOP (100) PERCENT 

					[rowNumber] = ROW_NUMBER() OVER ( ORDER BY tblSPT.[number] ) 

			FROM [master]..spt_values tblSPT 

			cross apply [master]..spt_values tblSPTNext
		
			ORDER BY [rowNumber] 
		) 
	
		INSERT INTO [ascendingKeyColumns].[Orders] 
		WITH(TABLOCK) 
		( 
			  [batchID] 
			, [clientID] 
			, [dateAdded] 
			, [amount] 
		) 
		SELECT TOP ( @numberofRows ) 
				  [batchID]
					 = @batchIDNext 
				, [clientID] 
					= ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000))) 
				, [dateAdded] 
					= case @datePart 
							when 'millisecond' then dateadd(MILLISECOND, cte.rowNumber, @dateNext) 
							when 'second' then dateadd(SECOND, cte.rowNumber, @dateNext) 
							when 'minute' then dateadd(MINUTE, cte.rowNumber, @dateNext) 
					  end 
				, [amount] = rand(cte.rowNumber) * @seed 
	
		from cte 
	
		order by 
				cte.rowNumber 
			
			
		set @numberofRecordsAffected = @@rowcount 

		set @log = @CHAR_TAB 
					+ cast(@iAttempt as varchar(60)) + ') '
					+ cast(@numberofRecordsAffected as varchar(10)) 
					+ ' records appended' 
				
		print @log 
	
	while (@@TRANCOUNT > 0)
	begin
 
		if (@commit = 1)
		begin
 
			commit tran
 
		end
		else
		begin
 
			rollback tran
		end
 
	end
 
    if (@updateStats =1)
    begin
 
        UPDATE STATISTICS  [ascendingKeyColumns].[Orders] [INDX_DateAdded] WITH FULLSCAN
 
        print 
                   @CHAR_TAB
                 + @CHAR_TAB
                 + 'Updated Statistics - Completed'
 
    end
    else
    begin
     
        print 
                  @CHAR_TAB
                + @CHAR_TAB 
                + 'Updated Statistics - Skipped'
 
     
    end
     
    set @iAttempt = @iAttempt + 1   
 
end
go
 


Explanation

  1. Adding data in batches of 20, 000 at a time
  2. And, updated statistics each time

 

Output

adddatablockatatime-20161125-0825am

Review Data

Let us show record counts inserted during each batch.

Code


 
select
          [batchID]
                =tblO.[batchID]
 
        , [id] = 
                      cast(min(tblO.[id]) as varchar(10))
                    + ' - '
                    + cast(max(tblO.[id]) as varchar(10))
 
        , [dateAdded_Min] 
             = min(tblO.[dateAdded])
 
        , [dateAdded_Max]
             = max(tblO.[dateAdded])
 
        , [count] 
             = count(*)
 
from   [ascendingKeyColumns].[Orders] tblO
 
group by
        [batchID]
 
order by
        [batchID]

go

 

Output

reviewcount

Metadata

Show Statistics / TF 2388 (ON)

Let us review statistics using Trace Flag 2388

Code


-- Enable trace flag 2388
DBCC TRACEON(2388)
GO

DBCC SHOW_STATISTICS ( 'ascendingKeyColumns.Orders', 'INDX_DateAdded' )
GO

-- Disable trace flag 2388
DBCC TRACEOFF(2388)
GO

Output

dbccshowstatistics-local-2388-incremental-20161125-0828am

Explanation

  1. Here we have 4 rows
  2. Based on the Updated and Table Cardinality Columns we can see what happens as we add data
    • The most recent is listed first
    • The Step Column lets us see that our Statistics depth increases as we add data
    • Because our date column is progressively increasing
      • The “Rows Above” is increasing, as well
      • The “Rows Below” is at 0
    • We have actual valid counts for
      • Inserts Since Last Update ( 50K)
      • Deletes Since Last Update ( 50K )
    • After 3 or so batches of progressive inserts, the system is comfortable codifying that the column is ascending
      • This is reflected in the “Leading column Type” column
  3. We have 4 entries
    • The last entry has 50000 rows
      • The Rows Above, Rows Below, Inserts and Deletes are NULL

 

Show Statistics / TF 2388 (OFF)

Let us review statistics without Trace Flag 2388

Code

-- Enable trace flag 2388
DBCC TRACEOFF(2388)
GO

DBCC SHOW_STATISTICS 
( 
     'ascendingKeyColumns.Orders'
   , 'INDX_DateAdded' 
)
GO

Output

dbccshowstatistics-local-no2388-incremental-20161125-0841am

Explanation

  1. Our Third Grid
    • For our initial load we started at Year 1970
      • The first two rows are indicative of that
      • We added about 50K records and the system read the numeric equivalence  ( 49,862 )
    • For our incremental loads
      • We initiated at the beginning of successive years
      • And, we added about 50K records during each run

 

Version

We observed the behavior described here on v2005 SP3 and v2014.

 

Summary

There is a huge amount of studious engineers that have covered the issue of poor Execution Plan choices that can be traced back to poor statistics.

This post discusses how Trace Flag 2388 can be used to review Statistics on a progressive timeline.

 

References

 

Trace Flags

  • Trace Flags
    Link

    • Generic
      • Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
        KB :- 2801413
        Link
    • Trace Flag – 4199
      • SQL Server query optimizer hotfix trace flag 4199 servicing model
        Link
      • Trace Flag 4199 – Enable Globally
        Link
      • Trace Flag 4199 fixes w/o individual trace flags: Complex Risk Assessment: Part II
        Link

 

MS Support

  • Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level
    KB :- KB2801413
    Link
  • FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1
    KB :-  KB922063
    Link
  • FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server
    KB :- KB2952101
    Link

 

Blog

  • Fabiano Amorim
    • Statistics on Ascending Columns
      Link
  • Benjamin Nevarez
    • Statistics on Ascending Keys
      Link
  • Ian Jose
    • Ascending Keys and Auto Quick Corrected Statistics
      Link
  • Steinar Andersen
    • Updated: Microsoft SQL Server Trace Flag list
      Link
  • Brent Ozar
    • SQL Server 2016: The Death of the Trace Flag
      Link
  • Antapex.org
    • traceflags_sqlserver.txt
      Link
  • Prince Kumar Rastogi
    • sql server trace flag 2388
      Link

Q/A

  1. Nik
    • Ascending Key Problem – Leading column branded “Stationary” – SQL Server
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s