December 25, 2014 at 6:55 pm
Hi all
I am a somewhat junior SQL Developer doing some research into the topic of measuring /baselining CPU performance & physical disk performance for SQL Server. In particular, the infrastructure team at my company want to decrease the number of CPU's for our main production server (it is a VM) due to licensing / cost constraints. I would like to put together a set of metrics for measuring any impacts this may have before & after. I will use perfmon or another tool (like SQL Server Monitor by Redgate or Solarwinds). I have put together what I think are the best metrics for this but would like some validation as to whether I am on the right track.
Metrics for CPU & Processor:
- sqlservr Process % Processor time
- % processor time (may be inaccurate due to being VM)
- Processor Queue Length
- Context Switches / sec
Physical Disk:
- avg disk sec/read
- avg disk sec/write
Workload performance;
- SQL Compilations & recompilations per sec as compared to batch requests
Thanking in advance for any guidance 🙂
December 26, 2014 at 5:48 am
Most of the figures you mention are helpful in working out a theoretical performance model, but your organisation uses SQL Server to give actual performance to the business.
My advice is to use SQL Profiler to find the most important queries to your business and how long these take to run. You should end up with a list of about 30 queries. Aim to get these queries into a .sql file so you can run them whenever you want and see how they perform - you are looking for minimum, maximum and average response times.
While you are doing this, capture the average and peak CPU usage as recorded by Windows, for at least 14 days. Don't worry too much about virtual to real conversion, this is probably not relevant unless it is planned to change this ratio after the number of cores is reduced.
You now have the figures you need to decide how many cores can be eliminated while you remain within acceptable peak and average CPU usage, plus the performance figures you need for post-change testing. Only the core count should be changed, memory and everything else must remain the same otherwise you will not know which factor affected the performance.
Allow the systems people to change the cores, then test performance over the next few days using your stored queries. If the queries still perform much the same as before, then reducing cores has not harmed the business. If there are problems, work with your management and the systems people to find an acceptable compromise.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 26, 2014 at 9:28 am
Measure workload during normal business hours. You can use Perf. Mon for that. One or two weeks should be ok, but no less than that. I prefer Extended Events instead of SQL profiler, but if you use SQL profiler, be sure is a server side trace and short enough so won't hurt performance.
After doing that, reduce the number of cores , while leaving else untouched, and measure performance again. If you immediatly get time outs or you use CPU utilization going to the roof, you can tell the change hurts performance.
One thing I have not tried and recently saw on previous PASS Summit is testing cores and sockets for the VM. Again, not tested, but under certain circumstances, less (or more) sockets while using same number of cores, may affect performance.
I also seen cases where too many vCPU are actually slowing things down. But again, you need testing before jumping into conclusions.
Last but not least, don't forget to tune up your code. Playing around with the hardware and/or VMware is faster, but if you reduce your vCPU utilization at code level, you will obtain the greatest and long term benefit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply