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
  • 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 01 '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 01 '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.) – Randolph West Aug 02 '16 at 02:43
  • 1
    Smells like IO. Can you measure old and new systems under the same workload? – Michael Green Aug 02 '16 at 04:02
  • @Forrest The log is a separated file, but it is in the same folder. – Luiz Eduardo Simões Aug 02 '16 at 12:18
  • @RandolphWest The power saving is already on High Performance. – Luiz Eduardo Simões Aug 02 '16 at 12:19
  • @MichaelGreen What exactly should I use to measure it? – Luiz Eduardo Simões Aug 02 '16 at 12:21
  • 1
    If it's a SAN the vendor will have tools. For DAS on the VM host there's perfmon. Here's some articles: http://www.sqlskills.com/blogs/paul/category/io-subsystems/ – Michael Green Aug 02 '16 at 22:43
  • Check/compare the MAXDOP values. – byrdzeye Aug 03 '16 at 19:57
  • 1
    while proc is running, check for current waits with sys.dm_os_waiting_tasks, or sp_whoisactive, or sp_blitzfirst. it may help narrow down where the slowness is – Bob Klimes Jul 07 '17 at 21:26
  • What are the differences between the two environments on the hypervisor version, hypervisor settings, resource allocation/reservations, and hardware? Do you have perfmon metrics from the old server regarding disk/cpu/ram/disk sec-read and disk sec-write? If you clear your wait stats and run the same test do the wait stats on both servers match closely or are they widely different? If different, what are the longest wait tasks and differences? Oh jeez, I just realized I replied to a months old thread. Maybe this will help someone else. – Ali Razeghi - AWS Oct 24 '17 at 16:44

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,225
  • 6
  • 120
  • 236
  • Tried all the things above and still slower than the other server. – Luiz Eduardo Simões Aug 02 '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 02 '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 02 '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 07 '17 at 21:29
  • 1
    @LuizEduardoSimões FWIW you can anonymize plan details using SQL Plan Explorer – LowlyDBA - John M 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.

MrG
  • 75
  • 9
  • 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 02 '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 02 '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 02 '16 at 13:36
  • Thats quite interesting, does the procedure have recompile somewhere in the code. – MrG Aug 02 '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 02 '16 at 13:51
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