SQL 2016 statistics issue

After I upgrade SQL Server 2008R2 to 2016 with compatibility level 130 we experience statistics issues. With the database option LEGACY_CARDINALITY_ESTIMATION setting ON the database was performing good. So this was the quick fix.

I decided to start with a fresh set of statistics so this is what I did. 

  • I turned of the LEGACY_CARDINALITY_ESTIMATION
  • drop all auto created statitics on that particular database.
  • clears all executions plans for that specific  database.
  • did some index tuning.

Lets put this together in a SQL script.

-- Step 1 - Turn off the LEGACY_CARDINALITY_ESTIMATION option
use <database_name, sysname, your_database_name>
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO

-- Step 2 - Generate DROP STATISTICS statements for all auto created stats.
SELECT 
 'table_name' = d.name
,'DROP STATISTICS ' + quotename(Schema_NAME(d.Schema_id)) + '.' + 
quotename(OBJECT_NAME(a.object_id)) + '.' + 
quotename(a.name) as colum_name
FROM sys.stats a
INNER JOIN sys.Objects d ON d.Object_id = a.object_id
WHERE auto_created = 1 AND User_Created = 0 and d.is_ms_shipped = 0
GO

-- Step 3 - Clears all executionsplans for that specific database
USE <database_name, sysname, your_database_name>
GO
DECLARE @DBID INT = (SELECT DB_ID())
DBCC FLUSHPROCINDB(@dbid);
GO