Tuning SQL Server Configuration - Professional SQL Server 2005 Performance Tuning - page 175

You will get the following message:

Configuration option 'show advanced options' changed from 0 to 1. Run the
RECONFIGURE statement to install.

If you run sp_configure 'show advanced options' , you will see that the config_value is 1 whereas the
run_value is 0:

name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options 0 1 1 0

You can then run the following code:

reconfigure
go

Now if you run sp_configure again, you should see many more parameters to play with. Changing
values for parameters works exactly the same as you just saw for show advanced options.

Here is one very efficient shortcut: When specifying the parameter, you don't need to type the whole
string. You just need to type enough letters for SQL Server to uniquely identify it. For example, typing
sp_configure 'show' works the same as typing sp_configure 'show advanced options' .

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.

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).