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)