2 Replies Latest reply on Jul 20, 2017 12:57 PM by cjfranca

    Using DPA to correctly size a new SQL server

    steveng2

      I have to migrate from a physical SQL server to a VMWare virtual server.

       

      My current server while older is well equipped with many cores and a decent amount of RAM.

       

      My server guy always says why do you need so much ram and cpus.

      He says, I only see the average CPU usage at 25%.

       

      So I need to adequately back up my need for more RAM and processing power some how. I was hoping I could use DPA to report on how adequate my existing server is.

       

      What would best practice be in this case?

        • Re: Using DPA to correctly size a new SQL server
          mandevil

          Average doesn't adequately address peak needs. Steps I'd take:

           

          When in the instance in question, click on the resources view, then CPU tab.

          Set your timeframe to last 24 hours or 1 week (keep in mind though that the more you zoom out in time, the more averaging will knock off peaks and valleys).

          Note a couple timeframes where CPU utilization is elevated.

          Then go back to the Trends view.

          Drill into those timeframes noted, make sure you have the Resources correlation tab showing beneath the timeslice graph.

          Move OS CPU utilization up so that it's prominently under the timeslice graph. (you may need to add that resource metric if it's not in there already)

          This should show some good examples where CPU is being used heavily. Even better - you can highlight some of the SQL statements that are using CPU resources for that timeframe.

          Click on the Waits tab, locate your cpu/memory activity, click on that and it will show you the top contributors to CPU/memory usage.

           

          Couple of things to note:

          • When going P2V, you may have to do a calculation taking into account chip clock speeds (MHz to GHz kind of considerations depending on age of older physical machine).
          • Take into account your setting for DOP (degree of parallel) which allows the database engine flexibility when choosing parallel executions.
          • If you don't have the VM option for DPA, you may consider it. If not feasible, I'd strongly recommend you get access to vSphere so that you get visibility into the hypervisor and physical servers (ESXi nodes).
          • After you come to whatever agreement you come to on this - look back at historical performance. Are you being impacted by lack of CPU resources (is it pegged)? Are you suffering from any CPU Ready Time pressure (your VM asking for CPU cycles from the physical host and having to wait for processors to free up - especially could impact parallel executions as the database engine will hand off the processing request to the OS which will wait for the correct number of processors to free up before beginning)? What is your prioritization to resources on that physical host compared to other VMs (referred to as shares in VMware)? Is the physical host you are running on over-provisioned (cpu and memory allocated over physically available - may not be an issue, but look for Ready Time and memory swapping as indicators there is contention)?
          • Track and adjust if needed - remember, in virtual environments, changes can be done dynamically for CPU and memory resources.

           

          You may note that to answer my list of questions, you will need visibility into the virtual infrastructure...

           

          Anyone else have a good strategy for justifying resource asks?

          • Re: Using DPA to correctly size a new SQL server
            cjfranca

            you need to see the requisits for all modules. You can to configurate same hardware fisical server for virtual server.