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
András Váczi
  • 31,278
  • 13
  • 101
  • 147
user3317155
  • 21
  • 1

1 Answers1

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.

Matan Yungman
  • 798
  • 4
  • 7
  • 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 '15 at 12:13
  • Yeah - I disabled them first based on an article over at SQLAuthority.com - not necessary then? – user3317155 May 20 '15 at 13:11
  • Why does he say it's necessary to disable them? – Matan Yungman May 20 '15 at 17:09