SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison

SQL SERVER - 16 CPU vs 1 CPU : Performance Comparison 142-UnevenParallel-ytcover-final-800x450

Recently I was with a client helping them improve the performance of their SQL Server when I was working on Comprehensive Database Performance Health Check,  we faced an interesting situation with a query. A query was running very slow because it was struggling with the CPU. My client asked me if we add more CPU will it run faster. Well, let us figure out the answer to this question in today’s blog post where I will be doing a performance comparison of 16 CPU vs 1 CPU.

Disclaimer (Read it before you leave any comment): I have run extensive performance testing on many of the queries of my clients on many different scenarios due to the nature of the transactional trivial queries, they were all running faster with a single CPU. It is quite possible that your scenario is different and your query needs more CPU to run faster. You should always test your query with the IO and TIME as explained in this Performance Challenge – Write Efficient Query – SQL in Sixty Seconds #140.

16 CPU vs 1 CPU: Performance Comparison

Now it is quite common to believe that more CPU will improve the performance of a query but honestly, it is a very subjective topic. I have seen more often transactional queries running faster with 1 or 2 logical CPU. This is one of the reasons, I often suggest clients start with a lower number of the Maximum Degree of Parallelism and increase after doing various testing with a performance.

In this video, I have demonstrated that when I am providing more CPU to a query, the query looks like running efficiently in the execution plan but in the reality, the query with a single CPU takes less amount of the CPU, IO, and TIME.

You can use any query from your environment as long as it is using multiple CPU and do the test. You will have to adjust the MAXDOP value based on the maximum CPU available for your machine.

Remember, it is quite possible that you get a different answer than me and that means your query needs more CPU. Try out various different configurations to find the most optimal settings for your server.

Reference: Pinal Dave (https://blog.sqlauthority.com

Author: Shantun Parmar

2 thoughts on “SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison

  1. A lot of thanks for all your valuable efforts on this site. Kate loves getting into investigation and it’s really easy to understand why. I know all concerning the dynamic medium you give insightful solutions on this web blog and as well as foster participation from others on that content plus our daughter is certainly discovering a lot. Have fun with the remaining portion of the new year. You are conducting a powerful job.

  2. I needed to create you one little observation just to say thank you as before for these fantastic information you’ve discussed here. It has been certainly particularly generous of people like you to convey freely all that a lot of people could possibly have made available as an e book to earn some cash for their own end, especially considering the fact that you could possibly have done it if you ever decided. The guidelines also acted like a fantastic way to be sure that other individuals have similar dreams just like my own to grasp a whole lot more in terms of this issue. I’m sure there are many more fun occasions ahead for individuals that find out your blog post.

Leave a Reply

Your email address will not be published.