Professional SQL Server® 2005 Performance Tuning

7.2. Inspecting Current SQL Server Settings

For all things SQL Server, there are generally two ways to do things: via the graphical user interface (GUI) (whether it is SQL Server Management Studio, Configuration Manager, or others) or via scripts. You will take a look at inspecting server settings via the GUI interface first.

7.2.1. Inspecting Server Settings with SQL Server Management Studio

For most server parameters, you can see and change their values using SQL Server Management Studio. Here are the steps to do it:

1. Connect to the database server in question with Management Studio.

2. Within Object Explorer, right-click the server itself, and select Properties from the context menu.

3. You will see the Server Property window (shown in Figure 7-1). From here, you can inspect your server parameter settings. Click different sections in the Select a Page Frame, and you will see settings pertaining to the property you select.

Figure 7-1. Figure 7-1

Note that anybody who can connect to the server will be able to view these settings this way. However, to change any of them, the user must have the ALTER SETTINGS permission. By default, a user with the sysadmin or serveradmin fixed server role has this right.

7.2.2. Inspecting Server Settings with Scripts

Another way to inspect server settings is to use scripts. You can use DMVs to get some of those settings, but the best way to do it is ...