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.

  • 1
    Looking at the database properties, is the log on a separate drive from the data files? Are both on different drives from the installation drive?
    – Forrest
    Aug 1 '16 at 15:21
  • 2
    Could be a million different things. The new physical hardware that your VM is on could be heavily loaded. The disks could also be shared with other VMs that have an incompatible IO profile (lots of random writes to spinny hard disks etc).
    – Philᵀᴹ
    Aug 1 '16 at 15:30
  • 1
    Check that the Power Saving settings are set to High Performance. There will be an option under Control Panel for that, and possibly the BIOS as well, depending on the hardware. (This being a VM makes checking the latter a little tricker.) Aug 2 '16 at 2:43
  • 1
    Smells like IO. Can you measure old and new systems under the same workload? Aug 2 '16 at 4:02
  • 1
    If it's a SAN the vendor will have tools. For DAS on the VM host there's perfmon. Here's some articles: sqlskills.com/blogs/paul/category/io-subsystems Aug 2 '16 at 22:43

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.

  • Tried all the things above and still slower than the other server. Aug 2 '16 at 13:05
  • @LuizEduardoSimões can you post the actual plan xml for the slow query ? Use pastebin and link it here
    – Kin Shah
    Aug 2 '16 at 14:17
  • Sorry, there is a lot of information on the XML that I can't post here, but the part that has the highest cost, 91%, is a Key Lookup (Clustered) on the table that I'm selecting data of. This query itself is taking like 2 to 3 seconds to run, even more when called from the application aparently, on a server with no load at all, as no one is using that database besides me. On the other side, it runs instantly on the old server, where everyone is using the application right now. Aug 2 '16 at 15:01
  • 1
    @kin curious why sp_recompile on a migration. There would be nothing in cache on new server, so nothing to recompile.
    – Bob Klimes
    Jul 7 '17 at 21:29
  • 1
    @LuizEduardoSimões FWIW you can anonymize plan details using SQL Plan Explorer Oct 24 '17 at 16:30

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

  • But by detaching and attaching the database from on server to the other, with the MDF and LDF files, the cache wasn't transfered too? Aug 2 '16 at 13:07
  • Exactly, attaching and re-attaching will have the same affect as restarting a SQL server instance or executing DBCC FREEPROCCACHE it will remove all complied query plans from memory. Therefore its like a new start, every query which is subsequently executed will need to build a new query plan which can take time.
    – MrG
    Aug 2 '16 at 13:29
  • Well, I think I understand, but as I executed this proc at least 50 times, shouldn't it bt as fast as it was in the old server now? Aug 2 '16 at 13:36
  • Thats quite interesting, does the procedure have recompile somewhere in the code.
    – MrG
    Aug 2 '16 at 13:37
  • Sorry, was that a question? Anyway, the there is no recompile on the app code neither in the procedure, it's just a simples recursive select with a cte. Aug 2 '16 at 13:51

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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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