21

I have a table with a column that I do not want statistics to be created or updated on. I get a better join cardinality estimate if I force the query optimizer to use density of statistics on the primary key as opposed to a statistics histogram on that column. Auto-update and auto-create statistics are on at the database level and I cannot change that.

If you want to suggest alternatives to preventing statistics creation keep in mind that the table is used in a view referenced by thousands of different queries. I do not have control over the queries that are run.

My initial strategy was to create statistics on the column with the NOCOMPUTE and SAMPLE 0 ROWS options. I was under the impression that SQL Server would not automatically create statistics on a column that already has a statistics object, but this has happened on our dev and QA servers.

New statistics were created for COL_GROUP. My NORECOMPUTE statistic was not updated. I don't know why the statistics were created and I've been unable to trigger that myself by running queries.

Is there a way to prevent SQL Server from automatically creating statistics on one column? My table only has two columns so a solution that prevents auto-stats from being created on a single table would also solve my problem.

Trace flags 4139 and 2371 are on in case it makes a difference.

If you want to play around with the table structure I've included it and sample data below:

CREATE TABLE X_NO_COLUMN_STATS(
    [COL_USER] [varchar](256) NOT NULL,
    [COL_GROUP] [int] NOT NULL,
 CONSTRAINT [PK_X_NO_COLUMN_STATS] PRIMARY KEY CLUSTERED 
(
    [COL_USER] ASC,
    [COL_GROUP] ASC
)WITH (DATA_COMPRESSION = PAGE)
);

-- prevent stats from being updated on COL_GROUP
CREATE STATISTICS [X_NO_COLUMN_STATS__COL_GROUP] ON X_NO_COLUMN_STATS ([COL_GROUP]) WITH NORECOMPUTE, SAMPLE 0 ROWS;

BEGIN TRANSACTION;
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',104);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',106);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',107);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',108);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',110);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',111);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',112);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',113);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',114);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',116);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',117);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',118);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',121);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',123);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',124);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',125);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',126);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',129);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',132);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',137);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',139);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',140);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',144);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',145);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',147);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',152);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',153);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',154);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',155);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',162);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',163);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',165);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',168);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',169);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',170);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',178);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',102);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',103);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',109);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',110);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',111);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',112);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',114);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',115);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',119);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',120);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',121);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',123);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',124);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',126);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',128);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',136);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',137);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',138);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',142);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',143);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',148);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',151);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',152);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',155);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',156);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',157);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',158);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',165);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',167);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',168);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',169);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',171);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',173);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',176);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',177);
INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',178);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',104);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',108);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',109);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',111);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',112);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',113);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',114);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',116);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',117);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',118);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',121);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',123);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',124);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',125);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',126);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',129);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',132);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',137);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',139);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',140);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',144);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',145);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',147);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',152);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',154);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',155);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',162);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',163);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',165);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',168);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',169);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',170);
INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',178);
COMMIT TRANSACTION;

I know about sp_autostats but I need something to prevent the creation of automatic statistics. sp_autostats only prevents the statistics from being auto-updated.

1
  • Just for the sake of the argument, according to error 33282 "Column '%.*ls.%.*ls' is encrypted using a randomized encryption type and is therefore not valid for use as a key column in a constraint, index, or statistics" should prevent statistics on the column. Sadly, it prevents constraints and a clustered index as well. I have not been able to test this.
    – Zikato
    Aug 17 at 6:56
4
+150

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 '
    + 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');
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
1

Create а new database (let's call it TestStats), disable Auto Create Statistics on and move the X_NO_COLUMN_STATS table there. After this create a view in your database which will point to the table without histogram:

CREATE VIEW X_NO_COLUMN_STATS
AS
    SELECT [COL_USER], [COL_GROUP] FROM TestStats.dbo.X_NO_COLUMN_STATS;
GO

If I got your problem correctly at this point you will achieve what you want. Your CRUD operations will work with a table without statistics (yes, it will be located in another database, and this should be kept in mind always) through the view with the same name as your table was.

2
  • Unfortunately this has a lot of drawbacks: it breaks foreign key relationships, introduces cross-database transactions, makes backup & recovery to a single point in time much more challenging, etc.
    – Brent Ozar
    Jul 13 '18 at 9:36
  • @BrentOzar for sure! But its happening quite often for the challenging tasks - deciding what will be worse right here :) Jul 13 '18 at 10:10

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.