I am running SQL Server 13.0.5622.0. I have a number of large indexed views (>5MM rows) defined similar to:
CREATE VIEW MyView WITH SCHEMABINDING AS
SELECT T1.Column1, T1.Column2
FROM dbo.T1
INNER JOIN dbo.T2 ON T2.Id = T1.Id
WHERE T2.Column3 IS NULL
GO
CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView
(
Column1 ASC,
Column2 ASC
)
WITH (DATA_COMPRESSION = PAGE)
ON Primary
GO
After I create the view & index, everything is fine. When I come back a few hours later, I run the following code (from How do I find indexes that have statistics_norecompute = ON)
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
s.name
FROM sys.indexes AS i
LEFT JOIN sys.stats AS s ON
i.index_id = s.stats_id
AND i.object_id = s.object_id
WHERE s.no_recompute = 1
and notice that, for some reason, the index now has STATISTICS_NORECOMPUTE = ON. I edit the index options, set STATISTICS_NORECOMPUTE = OFF, and then several hours later when I check again, STATISTICS_NORECOMPUTE is back ON again. I'm unable to find documentation saying that SQL Server will automatically set STATISTICS_NORECOMPUTE = ON or under what conditions it does so. Does anybody know why this is happening? Will query efficiency suffer with STATISTICS_NORECOMPUTE = ON? Should I set up a background job to update statistics on these indexes manually at set times?