SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 6. CPU Load

My first SQL Server tuning project happened more than 20 years ago, and I’ve been dealing with many systems ever since. Over the years, I’ve noticed an interesting trend. Most of the systems I optimized in the past were I/O bound. Of course, there were other problems, but reducing I/O load through query tuning and code refactoring was usually enough to get the job done.

This started to change several years ago. While I still see nonoptimized and I/O-intensive queries, their impact is masked by high-performance, low-latency flash-based drives. Moreover, the availability of cheap hardware allows for bigger servers that can handle the load from more users. The need to reduce high CPU load is quite common nowadays.

In this chapter, I will talk about several common patterns that increase CPU load and options to address it. I will start with nonoptimized queries and inefficient database code. Next, I will cover query compilation overhead, along with plan caching, and the issues they can introduce. Finally, I will discuss the benefits and downsides of parallelism in systems and ways to tune your parallelism settings.

Nonoptimized Queries and T-SQL Code

Why does your server have a high CPU load?

There are several possibilities, but I’ll start with the most obvious and common one: nonoptimized queries. It does not matter how fast the disk subsystem is. Nor does it matter whether the servers have enough memory to cache all the data in the buffer pool and eliminate all ...