We recently migrated from a traditional hosting environment with a back end of SQL Server 2012 ENT to an Azure VM running SQL Server 2014 STD. We did all the cool stuff with BPEs on SSD, etc. The problem is, performance is down. Specs of the old "server" and the new "VM" are identical (in theory, it is azure…).
Question being – I have run the following scripts on staging DBs in the new environment and seen improvement, but I am generally afraid to run these scripts in production. Is there any real risk in running the following? It seems to solve the problem and get SQL Server 2014 to work with the data much better.
EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? DISABLE'
GO
EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? REBUILD'
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
GO
Best Answer
The scripts go over all of your tables, rebuild all the indexes and update their statistics. The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable.
I'm not sure, though, why you need to run the first command which disables the indexes..
Before running it in production you need to verify they run well on a test system that is similar to the production system. Also do it during a maintenance window and not during the work day.