• Adam Thurgar

Drop auto statistics after an upgrade/migration


In a recent blog on database corruption I mentioned that I had dropped all the statistical indexes and let the optimizer recreate them as required.

I find that this is also required if you do an upgrade of your SQL Server installation (in place or backup/restore). The reason for this is that the auto statistics were created under a certain version of the optimizer. Now if you take these old auto statistics it may cause the new optimizer to make less than optimal decisions, plus it always better to start with a clean plate. One database that we migrated to SQL Server 2014 had over 100,00 auto statistic. Not sure how many of these were used but better that we got rid of that old baggage and let the optimizer create new ones, as it required them. Initially there will be an overhead, but much better in the long run.

The following link is very useful.

https://blogs.msdn.microsoft.com/mvpawardprogram/2013/09/09/sql-server-auto-statistics-cleanup/


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t