stats get updated even when query is trivial

Question

Post reply

stats get updated even when query is trivial

robh0502

SSC Enthusiast

Points: 199

October 31, 2023 at 12:29 am

#4317885

hi,

I'm using the following version of SQL Server

Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)

In my database, I've got the following options set to TRUE

Auto Update Statistics

Auto Update Statistics Asynchronously

I've got the following script

create table testtab
(col1 int)

DECLARE @count INT;
SET @count = 1;
WHILE @count<= 100
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;

select * from testtab where col1 = 1 --to create statistics object

--insert another 600 records to have enough modifications
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 600
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;

select * from testtab where col1 = 1 --try to get stats updated. query again and xplan shows up to date stats!

Even though query optimization level is TRIVIAL, stats get updated. I thought stats don't get updated for TRIVIAL queries. Could someone pls explain?

thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Using the following code (your code with some stats measurements added), I'm not seeing any updates to the stats on the 600 row run.

--SELECT 500 + (0.20 * 700), SQRT(1000*700)

DROP TABLE IF EXISTS dbo.testtab;
create table testtab
(col1 int)
SET NOCOUNT ON
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 100
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;
--===== Show any stats for the table.
 SELECT sp.stats_id, st.name, sp.last_updated, sp.rows_sampled, sp.modification_counter   
   FROM sys.stats st
  CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp  
  WHERE st.object_id = object_id('dbo.testtab')
; 
select * from testtab where col1 = 1 --to create statistics object
;
--===== Show any stats for the table.
 SELECT sp.stats_id, st.name, sp.last_updated, sp.rows_sampled, sp.modification_counter   
   FROM sys.stats st
  CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp  
  WHERE st.object_id = object_id('dbo.testtab')
; 
GO
CHECKPOINT;
WAITFOR DELAY '00:01:00'; --Simulate time you spent looking through the exection plan
GO
--insert another 600 records to have enough modifications
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 600
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;
select * from testtab where col1 = 1 --try to get stats updated. 
--===== Show any stats for the table.
 SELECT sp.stats_id, st.name, sp.last_updated, sp.rows_sampled, sp.modification_counter   
   FROM sys.stats st
  CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp  
  WHERE st.object_id = object_id('dbo.testtab')
;

Results... no time difference in the "last_updated" column...

So the next question is, what are you looking at in the execution plan where you claim you see that it's updated the stats?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Extended Events, with causality tracking, can show you exactly when & how statistics get updated.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

robh0502 SSC Enthusiast Points: 199 More actions · Answer 3

hi Jeff, I think the problem was that I didn't restart the server after messing with the auto update stats and auto update stats async values. Without restarting the server, the behavior seems to be strange (see if you could try yourself).

After restarting the server, the behavior returned to expectations.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

robh0502 wrote:

hi Jeff, I think the problem was that I didn't restart the server after messing with the auto update stats and auto update stats async values. Without restarting the server, the behavior seems to be strange (see if you could try yourself).
After restarting the server, the behavior returned to expectations.

Interesting... thanks for the feedback on what you found.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robh0502 SSC Enthusiast Points: 199 More actions · Answer 5

Jeff, just an update regarding this post. What I learned is that trivial plans appear to update stats automatically when enough modifications are made, but only if the query isn't in the plan cache. If the query is still in the cache and enough modifications are made, auto stats won't work. Please try yourself and confirm back here if you could.