Revisions to How to prevent statistics creation on a column?

Bounty Ended with 150 reputation awarded by Paul White♦

occurred Aug 18, 2021 at 17:14

added 48 characters in body

Source Link

edited Jul 18, 2021 at 6:01

90.3k
30
424
663

I think the trick is to create the user-defined Statisticstatistic using the same name that the equivalent auto-generated Statisticstatistic would take.

Try dropping the auto-generated Statisticstatistic (or starting with a new, empty table) and using this:-

DECLARE @stmt NVARCHARnvarchar(4000);
    
SELECT @stmt
  =  
   = N'CREATE STATISTICS [_WA_Sys_''
    + QUOTENAME
        (
        N'_WA_Sys_' 
        + RIGHT(N'0000000' + CONVERT(NVARCHARnvarchar(3211), column_id), 8) 
        + N'_' 
        + SUBSTRING(CONVERT(NVARCHARnchar(328), CAST([object_id] AS VARBINARYbinary(324)), 12), 
 3, 10      )
    + N']N' ON '
      + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + N'.' 
    + QUOTENAME(OBJECT_NAME([object_id])) 
    + N' ('
      + QUOTENAME([name]) 
    + N') WITH NORECOMPUTE, SAMPLE 0 ROWS;'
FROM sys.columns
WHERE 
    [object_id] = OBJECT_ID('dboN'dbo.X_NO_COLUMN_STATS')
      AND [name] = 'COL_GROUP';N'COL_GROUP';

RAISERROR('%s', 0, 1, @stmt) WITH NOWAIT;
    
EXEC sp_executesql @stmt = @stmt

The [auto_created][auto_created] and [user_created][user_created] flags indicate the new Statisticstatistic was user-defined, and that it is not updated on data-modification:-

SELECT stat.[name],
       stat.stats_id,
       stat.auto_created,
       stat.user_created,
       stat.no_recompute,
       sp.[rows],
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS stat
    CROSS APPLY sys.dm_db_stats_properties
    (stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID('dboN'dbo.X_NO_COLUMN_STATS');

Without using the equivalent naming approach, the auto-generated Statisticstatistic looks and behaves as expected:-

I think the trick is to create the user-defined Statistic using the same name that the equivalent auto-generated Statistic would take.

Try dropping the auto-generated Statistic (or starting with a new, empty table) and using this:-

DECLARE @stmt NVARCHAR(4000);

SELECT @stmt
     = N'CREATE STATISTICS [_WA_Sys_' + RIGHT(N'0000000' + CONVERT(NVARCHAR(32), column_id), 8) + N'_'
      + SUBSTRING(CONVERT(NVARCHAR(32), CAST([object_id] AS VARBINARY(32)), 1), 3, 10) + N'] ON '
      + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + N'.' + QUOTENAME(OBJECT_NAME([object_id])) + N' ('
      + QUOTENAME([name]) + N') WITH NORECOMPUTE, SAMPLE 0 ROWS;'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.X_NO_COLUMN_STATS')
      AND [name] = 'COL_GROUP';

RAISERROR('%s', 0, 1, @stmt) WITH NOWAIT;

EXEC sp_executesql @stmt = @stmt

The [auto_created] and [user_created] flags indicate the new Statistic was user-defined, and that it is not updated on data-modification:-

SELECT stat.[name],
       stat.stats_id,
       stat.auto_created,
       stat.user_created,
       stat.no_recompute,
       sp.[rows],
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS stat
    CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID('dbo.X_NO_COLUMN_STATS');

Without using the equivalent naming approach, the auto-generated Statistic looks and behaves as expected:-

I think the trick is to create the user-defined statistic using the same name that the equivalent auto-generated statistic would take.

Try dropping the auto-generated statistic (or starting with a new, empty table) and using this:

DECLARE @stmt nvarchar(4000);
    
SELECT @stmt =  
    N'CREATE STATISTICS '
    + QUOTENAME
        (
        N'_WA_Sys_' 
        + RIGHT(N'0000000' + CONVERT(nvarchar(11), column_id), 8) 
        + N'_' 
        + CONVERT(nchar(8), CAST([object_id] AS binary(4)), 2) 
        )
    + N' ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + N'.' 
    + QUOTENAME(OBJECT_NAME([object_id])) 
    + N' ('
    + QUOTENAME([name]) 
    + N') WITH NORECOMPUTE, SAMPLE 0 ROWS;'
FROM sys.columns
WHERE 
    [object_id] = OBJECT_ID(N'dbo.X_NO_COLUMN_STATS')
    AND [name] = N'COL_GROUP';

RAISERROR('%s', 0, 1, @stmt) WITH NOWAIT;
    
EXEC sp_executesql @stmt = @stmt

The [auto_created] and [user_created] flags indicate the new statistic was user-defined, and that it is not updated on data-modification:

SELECT stat.[name],
       stat.stats_id,
       stat.auto_created,
       stat.user_created,
       stat.no_recompute,
       sp.[rows],
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties
    (stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID(N'dbo.X_NO_COLUMN_STATS');

Without using the equivalent naming approach, the auto-generated statistic looks and behaves as expected:

Source Link

created Jul 16, 2021 at 9:52

Rob Dalzell

856
7
12

I think the trick is to create the user-defined Statistic using the same name that the equivalent auto-generated Statistic would take.

In my testing, when the correctly-named user-defined Statistic exists, an auto-generated one doesn't get created.

I'm using 15.0.4102.2 with your trace flags 2371 and 4139.

Try dropping the auto-generated Statistic (or starting with a new, empty table) and using this:-

DECLARE @stmt NVARCHAR(4000);

SELECT @stmt
    = N'CREATE STATISTICS [_WA_Sys_' + RIGHT(N'0000000' + CONVERT(NVARCHAR(32), column_id), 8) + N'_'
      + SUBSTRING(CONVERT(NVARCHAR(32), CAST([object_id] AS VARBINARY(32)), 1), 3, 10) + N'] ON '
      + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + N'.' + QUOTENAME(OBJECT_NAME([object_id])) + N' ('
      + QUOTENAME([name]) + N') WITH NORECOMPUTE, SAMPLE 0 ROWS;'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.X_NO_COLUMN_STATS')
      AND [name] = 'COL_GROUP';

RAISERROR('%s', 0, 1, @stmt) WITH NOWAIT;

EXEC sp_executesql @stmt = @stmt

The [auto_created] and [user_created] flags indicate the new Statistic was user-defined, and that it is not updated on data-modification:-

SELECT stat.[name],
       stat.stats_id,
       stat.auto_created,
       stat.user_created,
       stat.no_recompute,
       sp.[rows],
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS stat
    CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID('dbo.X_NO_COLUMN_STATS');

name	stats_id	auto_created	user_created	no_recompute	rows	rows_sampled	modification_counter
PK_X_NO_COLUMN_STATS	1	0	0	0	1000	1000	2000
_WA_Sys_00000002_04E4BC85	2	0	1	1	NULL	NULL	NULL

Without using the equivalent naming approach, the auto-generated Statistic looks and behaves as expected:-

name	stats_id	auto_created	user_created	no_recompute	rows	rows_sampled	modification_counter
PK_X_NO_COLUMN_STATS	1	0	0	0	1000	1000	2000
_WA_Sys_00000002_0E6E26BF	2	1	0	0	1000	1000	2000

Stack Exchange Network

Return to Answer