April 23, 2013 at 9:53 am
The question is, what happens when statistics on NORECOMPUTE set tables go stale? i.e. pass the rowmodctr > (unfiltered rows * 0.2) + 500 (assuming unflitered rows > 500), i.e. go past the point where they would be automatically recomputed if the NORECOMPUTE wasn't set.
Normally, automatic recomputation of the stats would force a recompile. Does the optimizer even check the staleness of the stats if NORECOMPUTE is set? If it does and notices they are out of date, but it can't recompute them, does it recompile the plan anyway? When, for any reason, a plan is recompiled and the optimizer finds the stats it had previously used were out of date, does it use them anyway? Or does it ignore them and act as if no stats existed or could be automatically created?
And yes, before anyone says, I'm well aware that NORECOMPUTE is not advised. That's not the question.
April 23, 2013 at 11:24 am
pbowman-543344 (4/23/2013)
The question is, what happens when statistics on NORECOMPUTE set tables go stale?
Nothing.
Stats with norecompute will not be automatically updated, hence when they pass the threshold, nothing happens, they're not updated. Since they're not updated, no plans are invalidated and the optimiser will continue to use those stats for any new compiles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 2:32 am
OK, Thanks for the info. I thought that was probably the most likely behaviour but I wasn't entirely confident there wasn't an outside chance that the optimizer decides to ignore out of date statistics.
While I'm here, I may as well post the t-sql I use for looking at stats & rowmodctr etc, in case its useful for people looking at stats. NB this makes use of a dmf that is new in 2008 R2 SP2.
SELECT
sch.name + '.' + so.name AS [Table]
, ss.name AS "Statistic"
, cast(((modification_counter / ((unfiltered_rows * 0.2) + 500) ) * 100.0) as decimal(18,2)) as [pctstale]
, ss.no_recompute
, sp.unfiltered_rows AS [Unfiltered Rows]
, sp.modification_counter AS [Row Modifications]
, sp.last_updated AS [Stats Last Updated]
FROM sys.stats ss with (nolock)
JOIN sys.objects so with (nolock)
ON (ss.object_id = so.object_id)
JOIN sys.schemas sch with (nolock)
ON (so.schema_id = sch.schema_id)
OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp
WHERE so.TYPE = 'U'
--and so.name = 'Metric'
and cast(((modification_counter / ((unfiltered_rows * 0.2) + 500) ) * 100.0) as decimal(18,2)) > 90
and sp.rows > 500 -- NB above formula only relevant for rows > 500
and ss.no_recompute = 1
ORDER BY [pctstale] desc;
Which is based on code from Erin Stillato's blog[/url] about the new sys.dm_db_stats_properties dmf
April 25, 2013 at 2:22 am
Just for laughs, I include the test code I used to demonstrate the two (related) points for the NORECOMPUTE situation. 1) that stats going out of date don't force a recompile; & 2) that when stats are out of date, the next recompile still uses them regardless.
The first script is best run as output to text, the second as output to grid, for the convenience of being able to click open the xml plans (rather than having to copy the text xml plan into a file, rename with .sqlplan ext and then re-open in ssms - tedious).
/* create test table with no recompute stats & indexes */
set nocount on;
print 'create test table';
if exists (select * from sys.objects where name = 't1' and type = 'U')
drop table t1;
go
create table t1 (
primary_id int identity(1,1) constraint [pk_t1] primary key ,
charstuff char(100) not null,
secondary_id int not null
);
go
create nonclustered index [sk_t1] on t1 (secondary_id asc);
/* create sp on test table */
print 'create sp on test table';
if object_id('usp_t1_matches') is not null
drop proc usp_t1_matches
go
create proc usp_t1_matches
as
begin
declare @rowcount int;
select @rowcount = count(*) from t1 where primary_id = secondary_id;
return @rowcount;
end
go
print 'insert 1000 rows into test table';
declare @count int = 0, @lastid int = 5;
while (@count <= 1000)
begin
insert into t1 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
-- quick look
print 'check initial test data';
select * from t1;
print 'execute test sp';
declare @retval int;
exec @retval = usp_t1_matches;
select @retval;
/* check date of plan */
print 'check date of plan';
SELECT
OBJECT_NAME(eps.object_id,eps.database_id) [Object],
eps.cached_time,
qs.creation_time,
qs.plan_generation_num,
qs.statement_start_offset,
qs.statement_end_offset,
qs.last_execution_time,
qs.execution_count,
qs.query_plan_hash
FROM
sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
WHERE eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_matches')
ORDER BY [Object], qs.statement_start_offset
/* set stats norecompute */
print 'set stats norecompute';
update statistics dbo.t1 with norecompute;
/* add rows to test table */
print 'add rows to test table';
--declare @count int, @lastid int;
set @count = 0;
set @lastid = 5;
while (@count <= 850)
begin
insert into t1 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
/* check stats stale */
print 'check stats staleness';
select
sch.name + '.' + so.name AS [Table]
, ss.name AS "Statistic"
, cast(((modification_counter / ((unfiltered_rows * 0.2) + 500) ) * 100.0) as decimal(18,2)) as [pctstale]
, ss.no_recompute
, sp.unfiltered_rows AS [Unfiltered Rows]
, sp.modification_counter AS [Row Modifications]
, sp.last_updated AS [Stats Last Updated]
from sys.stats ss with (nolock)
JOIN sys.objects so with (nolock)
ON (ss.object_id = so.object_id)
JOIN sys.schemas sch with (nolock)
ON (so.schema_id = sch.schema_id)
OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp
where so.type = 'U'
and so.name = 't1'
--and cast(((modification_counter / ((unfiltered_rows * 0.2) + 500) ) * 100.0) as decimal(18,2)) > 90
and sp.rows > 500 -- NB above formula only relevant for rows > 500
--and ss.no_recompute = 1
order by [pctstale] desc;
/* check date of plan */
print 'check date of plan with stale stats';
select
OBJECT_NAME(eps.object_id,eps.database_id) [Object],
eps.cached_time,
qs.creation_time,
qs.plan_generation_num,
qs.statement_start_offset,
qs.statement_end_offset,
qs.last_execution_time,
qs.execution_count,
qs.query_plan_hash
from
sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_matches')
order by [Object], qs.statement_start_offset
/* force recompile of sp */
print 'force recompile of sp at ' + convert(nvarchar(25), getdate(), 121);
exec sp_recompile 'dbo.usp_t1_matches';
print 'execute test sp again';
exec @retval = usp_t1_matches;
select @retval;
/* check date of plan */
print 'check date of plan post-recompile'
select
OBJECT_NAME(eps.object_id,eps.database_id) [Object],
eps.cached_time,
qs.creation_time,
qs.plan_generation_num,
qs.statement_start_offset,
qs.statement_end_offset,
qs.last_execution_time,
qs.execution_count,
qs.query_plan_hash
from
sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_matches')
order by [Object], qs.statement_start_offset
/* create 2 test tables with no recompute stats & indexes */
set nocount on;
select 'create test table 1';
if exists (select * from sys.objects where name = 't1' and type = 'U')
drop table t1;
go
create table t1 (
primary_id int identity(1,1) constraint [pk_t1] primary key ,
charstuff char(100) not null,
secondary_id int not null
);
go
create nonclustered index [sk_t1] on t1 (secondary_id asc);
go
select 'create test table 2';
if exists (select * from sys.objects where name = 't2' and type = 'U')
drop table t2;
go
create table t2 (
primary_id int identity(1,1) constraint [pk_t2] primary key ,
charstuff char(100) not null,
secondary_id int not null
);
go
create nonclustered index [sk_t2] on t2 (secondary_id asc);
go
select 'set stats norecompute';
update statistics dbo.t1 with norecompute;
update statistics dbo.t2 with norecompute;
go
/* create sp on test tables */
select 'create sp on test tables';
if object_id('usp_t1_t2_matches') is not null
drop proc usp_t1_t2_matches
go
create proc usp_t1_t2_matches
as
begin
declare @rowcount int;
select @rowcount = count(*) from t1 join t2 on (t1.secondary_id = t2.secondary_id);
select @rowcount;
end
go
/* insert rows */
select 'insert 1000 rows into test table 1';
declare @count int = 0, @lastid int = 5, @retval int = 0;
while (@count <= 1000)
begin
insert into t1 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
select 'insert 1000 rows into test table 2';
select @count = 0, @lastid = 5;
while (@count <= 1000)
begin
insert into t2 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
go
/* now capture plans for optimization using secondary indexes and then without them */
select 'evaluate test sp plan';
go
exec usp_t1_t2_matches;
go
select 'check plan creation time & get plan'
select object_name(eps.object_id,eps.database_id) [Object], eps.cached_time, qs.creation_time, qs.plan_generation_num, qp.query_plan
from sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
cross apply sys.dm_exec_query_plan(eps.plan_handle) qp
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_t2_matches')
order by [Object]
select 'drop secondary_id indexes on tables'
drop index [sk_t1] on dbo.t1;
go
drop index [sk_t2] on dbo.t2;
go
waitfor delay '00:00:02';
select 're-evaluate test sp plan at ' + convert(nvarchar(25), getdate(), 121);
go
exec usp_t1_t2_matches;
go
select 're-check plan creation time & get plan'
select object_name(eps.object_id,eps.database_id) [Object], eps.cached_time, qs.creation_time, qs.plan_generation_num, qp.query_plan
from sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
cross apply sys.dm_exec_query_plan(eps.plan_handle) qp
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_t2_matches')
order by [Object]
/* Now re-create secondary indexes and make stats stale via inserts of new records */
select 're-create secondary indexes';
create nonclustered index [sk_t1] on t1 (secondary_id asc);
go
create nonclustered index [sk_t2] on t2 (secondary_id asc);
go
select 'reset stats norecompute';
update statistics dbo.t1 with norecompute;
update statistics dbo.t2 with norecompute;
go
select 'add rows to test table 1';
declare @count int = 0, @lastid int = 5;
while (@count <= 850)
begin
insert into t1 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
select 'add rows to test table 2';
select @count = 0, @lastid = 5;
while (@count <= 850)
begin
insert into t2 (charstuff, secondary_id)
values (cast(newid() as char(100)),
@lastid - 5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10);
--select -5 + cast((100000.0 * RAND(DATEPART(ms, GETDATE()) + @count % 10)) as int) % 10
set @lastid = @@identity;
set @count += 1;
end
/* check stats stale */
select 'check stats staleness';
select
sch.name + '.' + so.name as [Table]
, ss.name as [Statistic]
, cast(((modification_counter / ((unfiltered_rows * 0.2) + 500) ) * 100.0) as decimal(18,2)) as [pctstale]
, ss.no_recompute
, sp.unfiltered_rows as [Unfiltered Rows]
, sp.modification_counter as [Row Modifications]
, sp.last_updated as [Stats Last Updated]
from sys.stats ss with (nolock)
JOIN sys.objects so with (nolock)
ON (ss.object_id = so.object_id)
JOIN sys.schemas sch with (nolock)
ON (so.schema_id = sch.schema_id)
OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp
where so.type = 'U'
and so.name in ('t1', 't2')
order by [Table], ss.stats_id;
/* recompile and check new "stale" plan */
select 'force recompile of test sp';
exec sp_recompile 'usp_t1_t2_matches';
go
select 're-evaluate test sp plan at ' + convert(nvarchar(25), getdate(), 121);
go
exec dbo.usp_t1_t2_matches;
go
select 'check plan creation time & get plan'
select object_name(eps.object_id,eps.database_id) [Object], eps.cached_time, qs.creation_time, qs.plan_generation_num, qp.query_plan
from sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
cross apply sys.dm_exec_query_plan(eps.plan_handle) qp
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_t2_matches')
order by [Object]
/* update stats and re-check fresh plan for comparison with "stale" one */
select 'update stats';
update statistics dbo.t1 with norecompute;
update statistics dbo.t2 with norecompute;
go
select 're-evaluate test sp plan at ' + convert(nvarchar(25), getdate(), 121);
go
exec dbo.usp_t1_t2_matches;
go
select 'check plan creation time & get plan'
select object_name(eps.object_id,eps.database_id) [Object], eps.cached_time, qs.creation_time, qs.plan_generation_num, qp.query_plan
from sys.dm_exec_procedure_stats eps (nolock)
join sys.dm_exec_query_stats qs (nolock)
on qs.plan_handle = eps.plan_handle
cross apply sys.dm_exec_query_plan(eps.plan_handle) qp
where eps.database_id = db_id()
and eps.object_id = object_id('dbo.usp_t1_t2_matches')
order by [Object]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply