1

How to do Incremental statistics on auto_created stats(WA) in SQL server? I know I can do this for the non auto_created stats (indexed column) either by rebuilding index on the table or at DB level.

Is there any way to achieve this on auto_created stats(WA) also?

The underlying issue is-

On a good day query runs in 10 sec and on a bad day it take 5 min. This query uses 15 columns in a join on a large table. This table is partitioned. The only difference I found that on the bad run stats were also updated on those 15 columns at the same time query is running. please advise.

SQL Server 2017: 
is_auto_create_stats_on= True; 
is_auto_create_stats_incremental_on= False; 
is_auto_update_stats_on= True; 
is_auto_update_stats_async_on= False;
0
1

You have the default settings.

is_auto_update_stats_on= True; 
is_auto_update_stats_async_on= False;

So when you run a query that needs updated stats, the stats will update before your query runs. You could update stats more agressively, or allow the query to run with the old stats while the stats are updated by turning on 'is_auto_update_stats_async_on'.

And yes, modifying an auto-created statistic to be incremental appears to work fine. eg in AdventureWorksDW after partitioning FactInternetSales:

ALTER DATABASE current SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH INCREMENTAL = ON
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH RESAMPLE ON PARTITIONS(2)
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.