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