Each night we have to run a job that takes about 10 hours to complete. As it runs, it uses 100% of the CPU. What can I do?

Question

Each night we have to run a job that takes about 10 hours to complete. As it runs, it uses 100% of the CPU. Obviously, other users are unable to access SQL Server during this time period. What can we do to speed SQL Server up?

Answer

I see lots of questions like this one, where a DBA knows there is an obvious performance problem, but doesn't know howto start to identifying it. As you might guess, it is very hard for me to make specific recommendations about problems when the DBA has yet to do any investigation into the problem.

Almost always, your first step when trying to identify a SQL Server performance problem is to use Performance Monitor (System Monitor on Windows 2000 and Windows 2003) to identify what the bottleneck is.

But you might say that the bottleneck in this case is obviously the CPU. You may be right, it might be the CPU. On the other hand, it might be another bottleneck, such as a lack of memory that is causing the excessive CPU usage, and if more memory were added to the server that the CPU bottleneck would go away. Until you run Performance Monitor and see exactly what is happening in your server, you will never know for sure where the bottleneck is.

Hardware bottlenecks generally occur in one of these key areas:

• CPU
• Memory
• I/O Subsystem
• Network

Fortunately, SQL Server has many different counters you can use to identify bottlenecks on each of these four areas. If you are not familiar with them, you can learn more about them on this website.

While it is sometimes possible to watch Performance Monitor in real time and identify a bottleneck, generally it is not that easy. Instead, you need to watch Performance Monitor counters over a period of time, which means that you will need to use Performance Monitor's logging capability, or a third-party tool that has the ability to log Performance Monitor counters over time.

By comparing Performance Monitor counters during times of good performance, to counters taken during times of bad performance, you should be able to identify the one or more bottlenecks that your server is experiencing.

Of course, this is just a first step. Once you have identified the bottleneck(s), the next step is to figure out what you can do to correct the problem, which is an entirely different situation.

The point I want to make here is that if you have a SQL Server performance issue, your first step to identifying the problem is to use Performance Monitor.