Search code examples
sql-serversql-server-2016rdbms

Table stats are not getting updated

We are facing a strange scenario in SQL Server 2016. We have a table with more than 300000 rows.

We have an index created on the table. We are using this table as part of join (note: columns in join close have index created), but query optimizer is doing a table scan.

When we checked stats:

RowsInTable    RowsSampled
  NULL          NULL

Then I ran:

UPDATE STATISTICS tablename

Stats did not change.

Then we tried to rebuild the index:

ALTER INDEX ALL ON dbo.tablename
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)

But again: stats did not change.

We have verified there was no bulk update / insert / delete has happened.

Please help.

Solution

  • We tried so many combinations with statistic but did not helped out .

    At last we tried below and it worked.

    ALTER INDEX ALL ON dbo.Tablename
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON)

    Note : This we should be think twice before doing in prod . We should know what is the data size of table else we will have blocking.

    We still not sure what cause this type of behavior but I am suspecting we were having index on heap (non cluster unique index).