July 22, 2019 at 7:04 pm
When creating a table [s1].[tmp_t1], inserting some data into same table (~500 rows) from table [s1].[t1], dropping [s1].[t1], and renaming [s1].[tmp_t1] to [s1].[t1] as a part of a maintenance process, I am seeing update stats without using the persisted option and n rows that i last executed manually.
I am wondering why the query optimizer automatically update stats without using the sampled rows 10 and the persist_sample_percent=on option. Instead, it executes (event_sequence=7 from xevent), select statman([sc0], [sc1], [sc2], [sb0000]) from (select top 100 percent [sc0], [sc1], [sc2], step_direction([sc0]) over (order by null) as [sb0000] from (select [creationtime] as [sc0], [fk_id] as [sc1], [pk_id] as [sc2] from [s1].[t1] tablesample system (9.659389e+001 percent) with(readuncommitted) ) as _ms_updstats_tbl_helper order by [sc0] , [sc1], [sc2], [sb0000]) as _ms_updstats_tbl option (maxdop 8) just before the statement insert into ..select from... (event_sequence=8 from xevent). Instead of using with option statements below
update statistics [s1].[t1](fk_id) with sample 10 rows, persist_sample_percent=on update statistics [s1].[t1](CreationTime) with sample 10 rows, persist_sample_percent=on
update statistics [s1].[t1](nci_1) with sample 10 rows, persist_sample_percent=on
(event_sequence=8) insert into [s1].[tmp_t1]([id],[fk_id],[istrue],[creationtime],[instant],[fk2_id]) select [id],[fk_id],[istrue],[creationtime],[instant],[fk2_id] from [s1].[t1](holdlock) where [creationtime] >= (dateadd(minute,-7, sysdatetime()))
I caught the statements by using xevents. These look like attached. I appreciate any feedback. Thanks!
July 23, 2019 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 23, 2019 at 8:25 pm
You may want to start by checking the persisted_sample_percent in the dmv sys.dm_db_stats_properties to see if the value really was persisted.
Sue
July 25, 2019 at 1:17 pm
Sue, thanks for your response. Indeed, i checked last_updated,[rows],[rows_sampled], sp.persisted_sample_percent columns by selecting from sys.stats, and using cross apply to sys.dm_db_stats_properties. Because of that, it seems to me that the parameters that i last executed manually are not being persisted. In fact, i just recently run UPDATE STATISTICS WITH SAMPLE 10 ROWS, PERSIST_SAMPLE_PERCENT=ON and i am seeing that rows value is equal to rows_sampled value, and this value is close to total the of rows (67828) from the related table. I was expecting to see rows_sampled=10. The persisted_sample_percent is ~0.014743174 what is ~1000 rows (67828 x 0.0147431739104794). perhaps, i am misunderstanding something? thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply