Sql-server – Stats Maintenance after Restoring to Newer SQL Server

sql serversql server 2014sql-server-2012

What's the ideal (or necessary) sequence of call when restoring a SQL Server 2012 backup onto a SQL Server 2014 instance and updating the compatibility level?

-- Update Statistics for all tables
EXEC sp_MSforEachtable 'Update Statistics ? with FULLSCAN';

-- Update page and row counts in catalogviews
DBCC UPDATEUSAGE('MY_DATABASE') ;

-- Change DB Compatibility level
EXEC sp_dbcmptlevel 'MY_DATABASE', '120';

I'm not sure if the db compatibility level effects the stats or simple the engine on the server. If the db compatibility level influences stats than it should be done before update stats. Also unclear if UPDATEUSAGE is still recommend and if it should be before update stats as well.

Best Answer

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version

As a side note, please don't use sp_MSforEachtable - Its not supported. There is a clever way of doing it.

Compatibility level matters - see What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels form Aaron Bertrand

Related Question