18

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
    How much data does your actual table contain? And how much data is added on a daily basis? – hot2use Apr 25 '18 at 9:08
  • Have you looked into creating a statistic on both columns and seeing how that affects performance of the query? CREATE STATISTICS [X_NO_COLUMN_STATS__COL_GROUP] ON X_NO_COLUMN_STATS ([COL_USER],[COL_GROUP]); If both columns are in the where clause it won't create a stat on COL_GROUP but if just COL_GROUP is it will. But in that case performance should be better with that statistic than the COL_USER statistic. – cfradenburg Jul 12 '18 at 12:53
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.

  • 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 :) – Denis Reznik 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.