sql-server – 如何防止在列上创建统计信息？

我有一个列的表,我不希望创建或更新统计信息.如果我强制查询优化器使用主键上的统计密度而不是该列上的统计直方图,我会得到更好的连接基数估计.自动更新和自动创建统计信息在数据库级别打开,我无法更改.

如果您想建议防止统计信息创建的替代方法,请记住该表在数千个不同查询引用的视图中使用.我无法控制运行的查询.

我最初的策略是使用NOCOMPUTE和SAMPLE 0 ROWS选项创建列的统计信息.我的印象是sql Server不会自动在已有统计对象的列上创建统计信息,但这种情况发生在我们的开发和QA服务器上.

为COL_GROUP创建了新的统计信息.我的NORECOMPUTE统计信息未更新.我不知道为什么统计数据已经创建,我无法通过运行查询来触发自己.

有没有办法阻止sql Server自动在一列上创建统计信息？我的表只有两列,因此阻止在单个表上创建自动统计数据的解决方案也可以解决我的问题.

跟踪标志4139和2371打开,以防它产生影响.

如果您想要使用表格结构,我已将其包含在内,并提供以下示例数据：

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;

我知道sp_autostats但我需要一些东西来阻止自动统计的创建. sp_autostats仅阻止统计信息自动更新.

解决方法

创建一个新数据库(让我们称之为TestStats),禁用自动创建统计信息并在那里移动X_NO_COLUMN_STATS表.在此之后在数据库中创建一个视图,该视图将指向没有直方图的表：

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

如果我在这一点上正确地解决了你的问题,你将实现你想要的.您的CRUD操作将使用没有统计信息的表(是的,它将位于另一个数据库中,并且应始终牢记这一点)通过与您的表同名的视图.

sql-server – 如何防止在列上创建统计信息？

解决方法

猜你在找的MsSQL相关文章