2

I have a big database, about 250GB, and recently our system had to be transfered to a new machine.

The old machine:

  • Has a Windows Server 2008 R2 Standard Edition
  • Runs SQL Server 2012
  • 32GBs of RAM, with 20GBs defined for SQL MAX RAM.
  • 22 VCPUs

The new machine:

  • Windows Server 2012 R2 Standard
  • Runs SQL Server 2012
  • 40GBs of RAM, with 24GBS defined for SQL MAX RAM
  • 22 VCPUs

As you can see, the new server should be enought to run the system with the same performance that it ran in the old one, but it is slower. The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

Things that I tried to solve the problem:

  • Enable flag 1118 (wasn't enabled in the old one)
  • Turn on Instant File Initialization (also wasn't active in the old one)
  • Set the MAX RAM to 20GBs, as in the old one
  • Reindexing and checking integrity with the Ola Hallegren's SQL Server Maintenance Solution
  • Changing SQL Process priority

The Database was transfered by detaching on the old server and attaching on the new server.

Any more information that should be needed do identify my problem I'll be glad to provide.

And, to finish, I am not a DBA, I'm just a programmer in charge of a task that I shouldn't be.

Luiz Eduardo Simões
  • 61
  • 1
  • 5

3 Answers3

1

The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

You should recompile the stored proc using sp_recompile 'procedureName'. This should be one of the post migration step.

Another thing to be aware is if you are going to use more max memory (since you have more RAM on new server), you should look into testing and enabling TF2335

Also check the power option on the server. It should be set to high performance.

I agree with @Phil, check with your VM admin to see if the host machine is over committed in-terms of resources or not. Depending on if you are using VMWare there are things like balloon driver (pdf whitepaper). For Hyper-V, read Running SQL Server with Hyper-V Dynamic Memory whitepaper.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

Your server will be slow until the SQL server can re-build it cache plans which it will of had on the old server.

MrG
  • 75
  • 9
0

Our database was extremely slow after detach/attach with only moving the files to a new SSD on the same server. Even after running the same query several times, so nothing to do with cached query plans or other hot/cold issues.

Eventually we rebuild all indexes using the code below and all was fine again:

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
Hugo Delsing
  • 113
  • 6