2

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
1
  • I have highlighted migration steps which will be useful in your case as well.
    – Kin Shah
    May 20, 2015 at 14:01

1 Answer 1

2

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.

3
  • Also, be aware that the new cardinality estimator is on by default in SQL 2014 if the database is in 120 compatibility level. If performance still isn't up to par after rebuilding indexes and updating stats, try changing the compatibility mode to 110 so that the old estimator is used.
    – Dan Guzman
    May 20, 2015 at 12:13
  • Yeah - I disabled them first based on an article over at SQLAuthority.com - not necessary then? May 20, 2015 at 13:11
  • Why does he say it's necessary to disable them? May 20, 2015 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.