if OBJECT_ID('dbo._TestUpdateStats') is not null
begin
	exec ('drop procedure dbo._TestUpdateStats');
end
go

create procedure dbo._TestUpdateStats
	@RowModCounterThreshold bigint=50000
	,@RowModPercentagethreshold decimal(3,2)=0.05
	,@StatsSamplingPercentage tinyint=0
as
begin
	set nocount on;
	declare @CurrentRowModCounter bigint
		,@Msg nvarchar(max)
		,@Cmd nvarchar(max)
		,@QualifiedTableName nvarchar(256)
		,@CurrentTableRowcount bigint

	declare @TableList table
	(SchemaName sysname not null
	,TableName sysname not null
	,RowModCounter bigint not null
	,EstimatedRowcount bigint not null
	,primary key clustered (SchemaName,TableName))


	insert into @TableList
	(SchemaName, TableName, RowModCounter, EstimatedRowcount)
	select
		s.name as SchemaName
		,t.name as TableName
		,max(si.rowmodctr) as RowModCounter
		,SUM(p.rows) as EstimatedRowCount
	from sysindexes si
	inner join sys.tables t
	on si.id=t.object_id
	inner join sys.schemas s
	on t.schema_id=s.schema_id
	inner join sys.partitions p
	on p.object_id=t.object_id
	where si.indid in (0,1)
	and p.index_id in (0,1)
	group by s.name, t.name


	declare Csr cursor local read_only for 
	select
	N'['+SchemaName+'].['+ TableName+N']'
	,RowModCounter
	,EstimatedRowcount
	from @TableList
	where EstimatedRowcount > 0

	open Csr

	fetch next from Csr into @QualifiedTableName, @CurrentRowModCounter, @CurrentTableRowcount
	begin
		/*
		select
			@QualifiedTableName as TableName
			,@CurrentRowModCounter as RowModCounter
			,@RowModCounterThreshold as RowModCounterThreshold
			,@CurrentRowModCounter*1.0/@CurrentTableRowcount as PercentageOfTableChanged
			,@RowModPercentageThreshold as PercentageOfTableChangedThreshold
			,@CurrentTableRowcount as TableRowcount
		*/
		
		set @msg='--Description: '+
			case 
				when @CurrentRowModCounter > @RowModCounterThreshold
					and @RowModPercentageThreshold < @CurrentRowModCounter*1.0/@CurrentTableRowcount
					then 'Update stats on '+@QualifiedTableName
						+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
						+N' exceeded threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
						+N' and percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
						+N' exceeded threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
				when @CurrentRowModCounter > @RowModCounterThreshold
					then 'Update stats on '+@QualifiedTableName
						+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
						+N' exceeded threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
				when @RowModPercentageThreshold  < @CurrentRowModCounter*1.0/@CurrentTableRowcount
					then 'Update stats on '+@QualifiedTableName
						+' because percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
						+N' exceeded threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
				else 'do not Update stats on '+@QualifiedTableName
					+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
						+N' did not exceed threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
						+N' and percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
						+N' did not exceed threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
			end
		print @msg

		if @CurrentRowModCounter > @RowModCounterThreshold
		or @RowModPercentageThreshold  < @CurrentRowModCounter*1.0/@CurrentTableRowcount
		begin
			set @cmd='Update statistics '+@QualifiedTableName
				+case 
					when @StatsSamplingPercentage=0 
						then ' with resample'
					when @StatsSamplingPercentage is not null
						then ' with sample '+convert(nvarchar,@StatsSamplingPercentage)+' percent'
					else N'' end
				
				print (@cmd)
		end
		fetch next from Csr into @QualifiedTableName, @CurrentRowModCounter, @CurrentTableRowcount
	end

	close Csr
	deallocate Csr
end
go

--Update Statistics Script Test
declare @RowsToRun int
	,@PercentageToInsert decimal(2,2)
	,@PercentageToDelete decimal(2,2)
	,@RowsToInsert int
	,@RowsToDelete int
	,@InitialInsertRunDate datetime
	,@DeleteRunDate datetime
	,@StatsUpdateDateAfterInsert datetime
	,@StatsUpdateDateAfterDelete datetime
	
-- Parameters
set @RowsToRun=1000000
set @PercentageToInsert=.1
set @PercentageToDelete=.1
set @RowsToInsert=@RowsToRun*@PercentageToInsert*1.0
set @RowsToDelete=@RowsToRun*@PercentageToDelete*1.0

if exists
(
	select
		is_auto_update_stats_on
		,is_auto_create_stats_on
	from sys.databases
	where database_id=DB_ID()
	and 
		(is_auto_create_stats_on=0
		or is_auto_update_stats_on=0)
)
begin
	raiserror('Error! Auto Update stats and auto create stats need to be turned on',16,1);
end

-- Create the test table and load it with sample data
if object_id('dbo._TestData') is not null
begin
	exec ('drop table dbo._TestData');
end

create table [dbo].[_TestData]
(
IntColumn int not null
,GuidColumn uniqueidentifier
,BitColumn bit not null
,BigIntColumn bigint not null
,NumericColumn numeric(16,8) not null
,BinaryColumn varbinary(max) not null
,CharColumn nvarchar(128) not null
,CharColumn2 nvarchar(max) not null
,FloatColumn float not null
,LastUpdateDate datetime not null
,constraint [_TestDataPK] primary key clustered (IntColumn)
);

with
L0 as (select 0 as c union all select 1),
L1 as (select 0 as c from L0 cross join L0 as b),
L2 as (select 0 as c from L1 cross join L1 as b),
L3 as (select 0 as c from L2 cross join L2 as b),
L4 as (select 0 as c from L3 cross join L3 as b),
L5 as (select 0 as c from L4 cross join L3 as b),
nums as (select row_number() OVER (ORDER BY (select null)) as n from L5)

insert [dbo].[_TestData]
(
 IntColumn
,GuidColumn
,BitColumn
,BigintColumn
,NumericColumn
,BinaryColumn
,CharColumn
,CharColumn2
,FloatColumn
,LastUpdateDate
)
select 
	x.IntSeed as [IntColumn]
	,x.GuidSeed as [GuidColumn]
	,case when x.RandSeed > 0.500 then 1 else 0 end as [BitColumn]
	,convert(bigint,x.RandSeed * 10000000000000000 * rand()) as [BigintColumn]
	,convert(numeric(16,8),x.RandSeed * 100000 * rand()) as [NumericColumn]
	,convert(varbinary(max),x.RandSeed * 100000 * rand()) as [BinaryColumn]
	,convert(varchar(128),(convert(varbinary(max),x.RandSeed * 1000))) as [CharColumn]
	,convert(varchar(max),(convert(varbinary(max),x.RandSeed * 1000))) as [CharColumn2]
	,x.RandSeed as [FloatColumn]
	,GETDATE()
from 
(
select top (@RowsToRun)
		newid() as [GuidSeed]
		,rand(binary_checksum(newid())) as [RandSeed]
		,n as [IntSeed]
from nums
order by n
) x;  

-- Now run an insert on 10% of the table
insert [dbo].[_TestData]
(
 IntColumn
,GuidColumn
,BitColumn
,BigintColumn
,NumericColumn
,BinaryColumn
,CharColumn
,CharColumn2
,FloatColumn
,LastUpdateDate
)
select 
	x.IntSeed as [IntColumn]
	,x.GuidSeed as [GuidColumn]
	,case when x.RandSeed > 0.500 then 1 else 0 end as [BitColumn]
	,convert(bigint,x.RandSeed * 10000000000000000 * rand()) as [BigintColumn]
	,convert(numeric(16,8),x.RandSeed * 100000 * rand()) as [NumericColumn]
	,convert(varbinary(max),x.RandSeed * 100000 * rand()) as [BinaryColumn]
	,convert(varchar(128),(convert(varbinary(max),x.RandSeed * 1000))) as [CharColumn]
	,convert(varchar(max),(convert(varbinary(max),x.RandSeed * 1000))) as [CharColumn2]
	,x.RandSeed as [FloatColumn]
	,GETDATE()
from 
(
select top (@RowsToInsert)
		newid() as [GuidSeed]
		,rand(binary_checksum(newid())) as [RandSeed]
		,IntColumn+@RowsToRun as [IntSeed]
from dbo._TestData
order by IntColumn
) x;  


select 
	@StatsUpdateDateAfterInsert=max(STATS_DATE(object_id, stats_id))
	,@InitialInsertRunDate=GETDATE()
from sys.stats
where object_id=object_id('dbo._TestData')

exec dbo._TestUpdateStats @RowModCounterThreshold=50000


-- Now delete 10% of the table
delete from dbo._TestData
where IntColumn <= @RowsToDelete


select 
	@StatsUpdateDateAfterDelete=max(STATS_DATE(object_id, stats_id))
	,@DeleteRunDate=GETDATE()
from sys.stats
where object_id=object_id('dbo._TestData')

exec dbo._TestUpdateStats @RowModPercentageThreshold=0.05

select
	case when @StatsUpdateDateAfterInsert < @InitialInsertRunDate
	and @StatsUpdateDateAfterDelete < @InitialInsertRunDate
	and @StatsUpdateDateAfterInsert = @StatsUpdateDateAfterDelete
	then 'Stats are not being Updated automatically according to the newer thresholds'
	else N'Stats are being updated according to the newer thresholds. Hooray!' end as Description
	,case when @InitialInsertRunDate < @InitialInsertRunDate
		then 'Second Update did not get stats Updated'
		else 'Second Update did get stats Updated. Hooray!'
		end as SecondInsertDescription
	,case when @StatsUpdateDateAfterDelete < @InitialInsertRunDate
		then 'Delete did not Update statistics'
		else 'Delete Updated statistics. Hooray!'
		end as DeleteDescription
	,@InitialInsertRunDate as 'Run time of the first Insert command'
	,@StatsUpdateDateAfterInsert as 'After the first Insert, when were stats last Updated?'
	,@DeleteRunDate as 'Run time of the third command, a delete'
	,@StatsUpdateDateAfterDelete as 'After the third command, when were stats last Updated?'