Why does STATISTICS_NORECOMPUTE turn on by itself?

849 Views Asked by At

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?

0

There are 0 best solutions below