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.

  • 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
  • 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.) – Randolph West Aug 2 '16 at 2:43
  • 1
    Smells like IO. Can you measure old and new systems under the same workload? – Michael Green 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 – Michael Green Aug 2 '16 at 22:43
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.

  • Tried all the things above and still slower than the other server. – Luiz Eduardo Simões 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. – Luiz Eduardo Simões 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 – LowlyDBA Oct 24 '17 at 16:30
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.

  • 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? – Luiz Eduardo Simões 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. – Gelder 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? – Luiz Eduardo Simões Aug 2 '16 at 13:36
  • Thats quite interesting, does the procedure have recompile somewhere in the code. – Gelder 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. – Luiz Eduardo Simões Aug 2 '16 at 13:51

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.