I have an interesting experience to share about the Query Ignoring CPU Threads. I was hired by a large eCommerce organization for  Comprehensive Database Performance Health Check. The primary issue they were facing was related to performance with the CPU. They had upgraded from 8 to 256 CPU threads and they were getting poor performance after that.
The common belief is that when we upgrade the CPU, our query should run faster. However, it is not necessarily true. Sometimes when we upgrade the CPU we can also get poor performance. We should be very careful how many CPU threads each of the queries are eventually going to get.
I have a strong preference that for the transactional workload we should have a single-digit lower number as MAXDOP at either server, database, or query level. However, I will write a separate blog post about this topic in the future.
Here is a video where I explain how a query can ignore Query Ignoring CPU Threads.
Well, that’s it for today. Do let me know what you think about this topic and video. I will be happy to read your comment and give a reply to your question. Additionally, do not forget to sign up for my channel – SQL in Sixty Seconds.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
