Professional SQL Server® 2005 Performance Tuning

7.3. Important Server Settings: CPU, Memory, and I/O

Generally speaking, an application needs to have a solid design and architecture for it to perform and scale well. However, if you are tasked with putting out fires and improving the performance of an existing application in short order, simply complaining about bad design and sloppy code is not going to cut it. One area you can look at is server configuration parameters. Application refactoring and enhancement will come later, hopefully. On the server side of things, from a high level, there are three areas you need to focus on: CPU, memory, and I/O. They will be the focus of this section.

7.3.1.

7.3.1.1. CPU

CPU obviously can have a big impact on database server performance. Since we are talking about SQL Server, when the server allocates processor resources, it should naturally favor background processes such as SQL Server Service. To verify this, go to the properties page of your server (right-click My Computer and pick Properties) and then pick the Advanced tab, as seen in Figure 7-3.

Now click Settings within the Performance box, and you will see the Performance Options window. Click the Advanced tab (see Figure 7-4).

Figure 7-3. Figure 7-3

Take a look at Processor scheduling box. Ensure that the Background services option is selected, as a SQL Server box should favor background processes.

In addition, you might also ...