19 - Maintaining Your SQL Server

• How to Use SQL Enterprise Manager for database administration - Extend your knowledge of SQL Enterprise Manger with new techniques to perform database administration and maintenance tasks. Learn how to accomplish tasks such as setting database options, managing transaction logs, and checking disk space utilization.

• How to import and export data - Learn how to automatically transfer database objects from one SQL Server to another. Find out how to use the SQL Enterprise Manager to generate Transact-SQL (T-SQL) scripts that will re-create database objects on other servers.

• How to Use SQL scripts, stored procedures, and the SQL Executive for maintenance tasks - Find out how to build script files containing T-SQL commands that can be used to perform maintenance tasks. Learn how to create stored procedures that can be run manually, or scheduled and executed by the SQL Executive.

• How to use SQL Server security - Learn how to use the SQL Security Manager to manage Windows NT accounts that need SQL Server access, and how to grant database access and object permissions to SQL Server usernames.

• How to performance-tune SQL Server - Learn the basics of SQL Server performance tuning. The techniques presented here provide you with a solid foundation to build a comprehensive approach to SQL Server performance tuning.

In the last chapter, you learned the basics of setting up SQL Server, creating databases, and creating database objects. By combining these skills with the information about database design, you are well on your way to being successful with SQL Server. In this chapter, you learn about keeping SQL Server running properly. You learn techniques for monitoring the health of SQL Server and some proactive steps you can take to ensure that no problems arise. Procedures for importing and exporting data are also discussed.

To avoid problems, follow the guidelines already provided in this book for sizing the computer you select to build your SQL Server and adhere to sound database design principles. It is only natural, however, that conditions will change as databases are put into production use. Over time, some design decisions made initially will no longer be valid. Additional tables and relationships may need to be added to reflect new requirements. New triggers may be needed to enforce changing business rules.

As information is added, the data itself may develop characteristics that suggest changes. Perhaps after months in production it becomes evident that "hot spots" have developed in the database - some areas are very active, whereas other areas are rarely used. By performing some tuning, you may be able to balance the distribution of data and improve performance.

Database Administration with the SQL Enterprise Manager

Creating Groups of Servers

In version 6.5, the SERVERS.BIN file has been replaced with registry entries. The SQL Enterprise Manager is a 32-bit application and cannot, therefore, be run on 16-bit Windows operating systems. Both Windows NT and Windows 95 computers can run the SQL Enterprise Administrator, and both of these operating systems use a hierarchical database called the registry to contain configuration information, largely replacing the use of configuration files such as WIN.INI and SYSTEM.INI. If you upgrade an existing SQL Server 6.0 installation, the information in SERVERS.BIN will be migrated into the registry the first time SQL Enterprise Manager is run.

Many INI files have been retained on 32-bit Windows platforms for compatibility with older applications. They are used much less than in the past, however, and their use should eventually cease.

Although the SERVER.BIN file is no longer used, it is still possible to share a particular configuration for SQL Enterprise Manager. To set up a shared configuration, follow these steps:

1. Select a computer on which to store the shared configuration information. This should be a computer that will always be running and available such as a server. You can use a server to store the information even if you will use Windows NT Workstation and Windows 95 desktop computers to run SQL Enterprise Manager.

2. On the computer you have selected, start SQL Enterprise Manger, create the server groups you desire, and register your SQL Servers.

3. Exit the SQL Enterprise Manager. This saves the group and server registration information in the registry on the local computer. You can then connect to this computer from other administrative workstations and use the shared configuration. At each computer that you want to share this configuration, complete the remaining steps.

4. Start SQL Enterprise Manager.

5. From the menu, choose Tools, Preferences/Configure. The Configure SQL Enterprise Manager dialog box appears (see fig. 19.1).

   Fig. 19.1 - Use the Configure SQL Enterprise Manager dialog box to select the computer whose shared configuration you want to utilize and make other global settings affecting the operation of SQL Enterprise Manager.

6. Select the Application tab.

7. In the Read From Remote text box, enter the name of the server containing the configuration information. Use the form \\servername. Click OK.

See "Creating Server Groups and Registering Servers," (Chapter 18)

Checking Space Utilization

A number of questions arise when you want to characterize a server's storage. Following is a short list of questions that might be of interest:

• What devices have been defined on a server?

• What database(s) and/or log(s) have been placed on a device?

• How much, if any, available space is left on a device?

• On which device(s) has a database been placed?

• How much, if any, available space is left in a database?

The answers to these questions can be found using isql or ISQL/w and running system stored procedures (for example, sp_helpdb and sp_helpdevice). You can also use the SQL Enterprise Manager to view the status of devices and databases on a server. The information is displayed in bar graph format. New in version 6.5, is the capability to set options to control the format of the graphs and output them to a printer.

To view a graph depicting the devices that have been defined on a server, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Devices toolbar button or choose Manage, Devices from the menu. The Manage Database Devices dialog box appears, as shown in figure 19.2.

   Fig. 19.2 - The Manage Database Devices dialog box provides a graphical display of the currently configured database devices. Toolbar buttons are available to create, edit, or delete devices.

4. All the devices defined on this server are displayed in a bar graph. Two colors are used to show the space used and the space available.

   
   When viewing a device graph in SQL Enterprise Manager, bear in mind that "space used" implies that one or more databases have been placed on this device and are "using" the space. The databases may be nearly empty, but will still use the space. The space has been preallocated to the databases shown and is no longer available. If you want to gauge how much actual data is on a server, you must view the space used in the Manage Databases dialog box.
   

5. If you want to view the databases that have been placed on a device, click the Edit Device toolbar button. The Edit Database Device dialog box appears, as shown in figure 19.3.

   Fig. 19.3 - The Edit Database Device dialog box provides a display of the databases stored on a device. It is used to expand the size of a database device, an operation that can be scheduled for off-peak hours. It is also used to set up mirroring for a device.

6. The total size of the device is listed in the Size (MB) box. By clicking a database name at the left of the graph, the name, size of the database, and free space will be listed in the status bar at the bottom of the graph. If you position your mouse over one of the bars, the status bar changes to reflect information about that device.

To view a graph depicting the databases that have been defined on a server, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Databases toolbar button or choose Manage, Databases from the menu. The Manage Databases dialog box appears (see fig. 19.4).

   Fig. 19.4 - The Manage Databases dialog box provides a display showing the currently defined databases on a server. Toolbar buttons are available to create, edit, or delete databases.

4. All the databases defined on this server are displayed in a bar graph. Two colors are used to show the space used and the space available.

5. If you want to view database information, configuration options, and permissions for a particular database, click the name of the database to the left of the graph (or click anywhere on the bar itself) and then click the Edit Database toolbar button. The Edit Database dialog box appears (see fig 19.5).

   Fig. 19.5 - The Edit Database dialog box is a three-tabbed dialog box that is used to change the size of a database, set database options, and grant permissions.

6. Click the Database tab if it is not already selected. The creation date, database owner, size of the database and log, data space available, and log space available are all displayed. By clicking the Options tab, you can view and set options for this database. By clicking the Permissions tab, you can view and grant permissions for the database. See the sections, "Setting Database Options" and "Understanding Object Ownership and Permissions," later in this chapter for more information.

Devices can be expanded, but they cannot be shrunk. To expand a device, you again have the option of using a command (DISK RESIZE) or SQL Enterprise Manager. To expand a device with SQL Enterprise Manager, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Devices toolbar button or choose Manage, Devices from the menu. The Manage Database Devices dialog box appears.

4. All the devices defined on this server are displayed in a bar graph. Two colors are used to show the space used and the space available.

5. If you want to view the databases that have been placed on a device, click the Edit Device toolbar button. The Edit Database Device dialog box appears.

6. The total size of the device is listed in the Size (MB) box. You can enter a larger number to expand the size of this device. You cannot shrink a device using this technique.

To achieve the effect of shrinking a device, you must back up all data on the device, drop the device, delete the physical file that contained the device, create a smaller device with the same name, and reload databases.

If you decide, after looking at the databases on your server, that one of the databases needs to be larger or smaller, you can use the SQL Enterprise Manager to expand or shrink a database. To expand a database, use this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Databases toolbar button or choose Manage, Databases from the menu. The Manage Databases dialog box appears.

4. All the databases defined on this server are displayed in a bar graph. Two colors are used to show the space used and the space available.

5. Select the database you want to resize by clicking the name of the database to the left of the graph (or click anywhere on the bar itself). Then click the Edit Database toolbar button. The Edit Database Device dialog box appears.

6. Click the Database tab if it is not already selected. The creation date, database owner, size of the database and log, data space available, and log space available are all displayed. Click Expand. The Expand Database dialog box appears (see fig. 19.6).

   Fig. 19.6 - The Expand Database dialog box is used to increase the size of a database. You can optionally schedule the expansion for a later time when the server is less busy.

7. A graph appears depicting the devices defined on this server. The graph uses two colors to indicate used and free space. In the Data Device drop-down list box, select the device on which you want to expand this database. In the Size (MB) text box, enter the amount of additional space to allocate to this database. As you enter numerals in the text box, the graph is dynamically updated with a third color indicating the relative amount of new space to be added. You can, if you want, use the same technique to select a device and amount for expansion of the transaction log as well.

8. You now have the option to execute the expansion now or schedule it for later. If you want to immediately execute the expansion operation, click Expand Now. The expansion begins, and you are finished with this task.

   Because database expansion can take some time and may impact throughput on an active database in the middle of a busy period, you may want to schedule expansion of production databases for off-peak time periods. If you want to create a task to initiate the expansion operation at a later time, click Schedule button. The Schedule Database Expansion dialog box appears, as shown in figure 19.7.

   Fig. 19.7 - The Schedule Database Expansion dialog box allows you to schedule when database expansion should occur.

9. This dialog box represents a task that has automatically been created to complete the expansion operation. The task name and SQL Command (ALTER DATABASE ...) have already been entered. Typically, you would schedule the task to run one time at an appropriate date and time. Click OK to schedule the task.

To shrink a database, use this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Databases toolbar button or choose Manage, Databases from the menu. The Manage Databases dialog box appears.

4. All the databases defined on this server are displayed in a bar graph. Two colors are used to show the space used and the space available.

5. Select the database you want to resize by clicking the name of the database to the left of the graph (or click anywhere on the bar itself). Then click the Edit Database toolbar button. The Edit Database Device dialog box appears.

6. Click the Database tab if it is not already selected. The creation date, database owner, size of the database and log, data space available, and log space available are all displayed. Click Shrink. A warning, shown in figure 19.8, informs you that the database must be put into single-user mode before the shrink operation can proceed.

   Fig. 19.8 - A warning from SQL Enterprise Manager informing you the database must be set to single user mode.

7. Clearly this is an operation that cannot be performed during active use of the database. If it is an appropriate time to proceed, click Yes to continue. The Shrink Database dialog box appears (see fig. 19.9). SQL Enterprise Manager automatically sets the single-user option and clears it when the operation is complete or has been canceled.

   Fig. 19.9 - The Shrink Database dialog box allows you to reduce the size of a database.

8. The minimum size to which this database can be reduced is displayed at the top of the dialog box. Enter the size you want in the Database Size (MB) text box, or use the up and down arrows to select a size. Click OK to initiate the shrink operation.

After shrinking a database, you should dump the master database. See "Backing Up and Restoring Data" later in this chapter for more information.

Managing Transaction Logs

It is usually a good idea to place the transaction log on a different device from the data. This may result in better performance, especially if the device is on a different physical drive with a different controller. It also allows you to save transaction log dumps so that you can completely restore the condition of your database in the event of a media failure.

Under normal circumstances, transaction logging cannot be turned off. However, if you are adding a large amount of information to a database, you may want to limit the amount of logging that occurs. For example, you can limit logging when using the fast mode of the bulk copy program, bcp. This improves the speed of the bulk copy operation, and the original data you are copying into the database serves as a backup copy temporarily. After the operation is complete, however, you must resynchronize the data and the log by dumping the database.

Transactions are bracketed by BEGIN TRANSACTION and COMMIT TRANSACTION statements. Conceptually, when a transaction has been committed it has been written to disk and is durable. Its effects will be maintained even if there is a catastrophic failure. The means by which this is implemented will now be outlined.

As processing occurs, the log grows. Obviously, there must be some mechanism by which the size of the log is reduced or it would soon fill the device. This mechanism is known as truncating the log, and SQL Server can be configured to perform this task automatically, or it can be initiated manually, usually after dumping the log. The Truncate Log on Checkpoint database option controls whether automatic log truncation occurs. See the next section, "Setting Database Options," for the procedure to change this and other database options.

When a COMMIT TRANSACTION statement is issued, all logging for the transaction is immediately written to disk. It is possible that some of the actual data pages may not be immediately written to disk, but remain in the data cache in memory. These are referred to as dirty pages. If additional cache memory is required, SQL Server will always write dirty pages to disk before reallocating cache to another operation.

Try to avoid allowing dirty pages to exist in memory for long periods of time, even when the data cache is not needed for another operation. At a checkpoint, SQL Server flushes all data cache buffers, physically writing the information to disk. Checkpoints can be initiated manually by issuing the CHECKPOINT statement. SQL Server also initiates implicit checkpoints.

If the Truncate Log on Checkpoint option is set, SQL Server attempts to truncate the transaction log at the completion of implicit checkpoints. At that point, the transaction has been logged and written to disk, and the data itself has been written to disk (there are no dirty pages in cache). The transaction log will be truncated up to the page containing the oldest transaction that has not been committed, rolled back, or replicated. This process occurs approximately every 60 seconds. The transaction log also will be truncated after the log has been backed up with a DUMP TRANSACTION statement. Truncating the log without backing it up is usually appropriate only in development or test environments. See the next section, "Setting Database Options," for more information.

It is important to manage the relationship between the size of the transaction log and the amount of logging that occurs so that the transaction log never becomes full. A full transaction log generates an 1105 error and processing stops. You can use the Performance Monitor to monitor the size of the transaction log for a database. By setting an alert based on this counter, it is possible to automate the process of dumping and truncating the log. This is a more appropriate method for managing transaction log size in production environments than using the Truncate Log on Checkpoint option.

Two other situations occur that can cause the log to fill up, even if you have an appropriately sized log and are initiating log truncation at regular intervals. Because only committed transactions can be truncated, a stranded transaction caused by an application error can prevent truncation and cause the log to grow abnormally large. If the user process that caused the problem cannot be completed, it can be forcibly stopped, and the transaction rolled back. Also, some transactions cause extensive logging because of their nature. Deleting a table or updating every row in a table, are good examples. These transactions should be broken down into multiple transactions that each accomplish part of the work with log truncation occurring after each stage. Table deletions can usually be replaced with the TRUNCATE TABLE command, which only logs space deallocation operations. For more information, see "Stopping Processes" in Chapter 24 of the Administrator's Companion. This manual is part of the SQL Server documentation set and is included in SQL Server Books On-line for SQL Server 6.5. An icon to launch the on-line books is included by default in the Microsoft SQL Server 6.5 program group.

Setting Database Options

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Open the Databases folder by clicking on the plus sign (+) to the left of the folder. Select the database you want to manage.

4. Position the mouse cursor over the name of the database and click the right mouse button. A context-sensitive menu appears, as shown in figure 19.10.

   Fig. 19.10 - The SQL Enterprise Manager makes extensive use of context-sensitive pop-up menus, which are accessed with a right-click (click the right mouse button).

5. Select Edit from the menu. The Edit Database dialog box appears (see fig. 19.11).

   Fig. 19.11 - The Options tab of the Edit Database dialog box is used to set database options. For example, you can set the database to operate in single user mode for maintenance operations using this tab.

6. Set or clear the desired options by clicking the check boxes.

7. Click OK.

Setting and Responding to Alerts

SQL Server provides a powerful alert processing mechanism that can be a tremendous aid to DBAs. A range of error conditions, from severe to relatively benign, can be monitored. A group of operators can be defined, usually composed of administrators and database owners. A schedule can be created that specifies which operators should be notified when certain types of alerts occur and what days and times they are "on call." When an alert condition is detected, an e-mail message can be sent to the appropriate operator. With optional third-party software and services, a pager can be activated by an e-mail message.

To define operators, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Alerts toolbar button or choose Server, Alerts from the menu. The Manage Alerts dialog box appears. Select the Operators tab if it is not already active (see fig. 19.12).

   Fig. 19.12 - The Operators tab of the Manage Alerts dialog box displays a list of operators who have been defined. They can subsequently be assigned responsibility for one or more alerts.

4. Click the New Operator toolbar button to define a new operator, or click the Edit Operator toolbar button to edit an existing operator. Defining a new operator is demonstrated here, but editing an existing operator is essentially the same. The New Operator dialog box appears (see fig. 19.13).

   Fig. 19.13 - The New Operator dialog box is used to define administrative operators who will be responsible for various alerts triggered by error conditions.

5. Enter a name for the new operator. If this operator will be activated immediately, be sure the Enabled option is selected. You can enable and disable operators to reflect availability based on vacations, special projects, and so on.

6. Enter an e-mail name for the new operator. To receive e-mail alert notifications, you must set up SQL Mail.

7. In the Alerts Assigned to this Operator box, click the e-mail or pager option buttons to select which alerts are the responsibility of this operator and the method of notification. E-mail will be sent immediately If you are using third-party paging support, identify the schedule when this operator will receive pages.

8. Click OK.

See "Using SQL Mail and Alerts to Monitor SQL Server," (Chapter 30)

To view and define alerts, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Alerts toolbar button or choose Server, Alerts from the menu. The Manage Alerts dialog box appears. Select the Alerts tab if it is not already active, as shown in figure 19.14.

   Fig. 19.14 - The Alerts tab of the Manage Alerts dialog box lists the alerts that have been defined, the condition that triggers the alert, and information about the last time the alert was raised.

4. Click the New Alert toolbar button to define a new alert, or click the Edit Alert toolbar button to edit an existing alert. Defining a new alert is demonstrated here, but editing an existing alert is essentially the same. The New Alert dialog box appears (see fig. 19.15).

   Fig. 19.15 - The New Alert dialog box allows you to define a new alert by specifying the conditions that will trigger the alert, defining a response, and assigning an operator.

5. Enter a name for the new alert. Be sure the Enabled button is selected to activate the new alert.

6. In the Alert Definition box, select either a specific Error Number or Severity level that you want to monitor. You can narrow the scope of the alert further by selecting a particular database and/or entering a character string that the error message must contain to fire the alert.

7. In the Response Definition box, indicate what should happen when the alert is fired. You can execute a predefined task and/or send an alert notification message to specific operators. You can include the error message text in the notification message if you desire. New in SQL Server 6.5, you can raise an Simple Network Management Protocol (SNMP) trap when the alert occurs so that an SNMP console application can recognize the alert as well.

8. Select the operators to notify for this alert.

9. Notice that you can open this dialog box later by using the Edit Alert button to view the contents of the Most Recent Occurrence box.

10. Click OK.

You can set alert engine options that affect the way all alert operations are handled. These options include a fail-safe operator who will be notified if all other pager notifications for an alert fail. You can also designate another server to which any unhandled events will be forwarded. SQL Server errors of a specified severity will generate events that are sent to the event log of the specified server.

To view and define alert engine options, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Alerts toolbar button or choose Server, Alerts from the menu. The Manage Alerts dialog box appears. Select the Alerts tab if it is not already active.

4. Click the Engine Options toolbar button. The Alert Engine Options dialog box appears, as shown in figure 19.16.

   Fig. 19.16 - The Fail-Safe tab of the Alert Engine Options dialog box is used to establish an operator to contact if all other operators are unavailable.

5. If you want to use fail-safe options, enter the name of the operator who will receive fail-safe notifications and/or the name of the server to forward unhandled events

6. Click the Pager Email tab, as shown in figure 19.17.

   Fig. 19.17 - The Pager Email tab of the Alert Engine Options dialog box allows you to enter the proper e-mail account for use by SQL Mail.

7. You can use this dialog box to format the To Line, CC Line (CC for carbon copy), and Subject Line of e-mail notifications sent for pager alerts. Prefixes and suffixes can be combined with operator e-mail names to create special addresses for pager e-mail.

8. Click OK.

Importing and Exporting Data

The SQL Transfer Manager is a newer, graphical utility. It transfers information, both data and objects, from one SQL Server to another. With the release of SQL Server 6.5, the SQL Transfer Manager is no longer a part of the product. Instead, the SQL Enterprise Manager has a transfer management interface that offers essentially the same capabilities and performs in much the same way. The interface is organized a little differently, and should be easier for most people to use. You can also use SQL Enterprise Manager to generate a Transact SQL (T-SQL) script that will re-create a database object on another server. This section describes the use of SQL Transfer Manager and SQL Enterprise Manager for the transfer of data and objects.

Using the Transfer Management Interface in SQL Enterprise Manager

To use SQL Enterprise Manager to transfer information from one SQL Server to another, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to use as the source of the information that will be transferred.

3. Open the Databases folder by clicking the plus sign (+) to the left of the folder. Select the database you want to manage.

4. Select Object, Transfer from the menu. The Transfer Objects dialog box appears (see fig. 19.18).

   Fig. 19.18 - The Transfer Objects dialog box is used to transfer objects, up to and including all objects in a database, from one SQL Server to another.

5. In the Destination box, select a Destination Server and a Destination Database using the drop-down combo boxes provided. If the server you want to use as a destination server does not appear in the list, you can register a new server by clicking the New Server button.

   
   The transfer management interface will not let you select a version 6.0 server as a destination. You are not doing anything wrong. The transfer management interface only allows a version 6.5 server to be used as the destination. The source server can be versions 4.x or 6.x.
   

6. If you want to transfer all objects in a database and start the transfer immediately, simply click Start Transfer. You see a series of progress indicators and then a message box informing you the transfer is complete. Your transfer is done, and you can click Close to exit the transfer management interface and skip the remaining steps.

7. To schedule the transfer for a later time, perhaps when the load on the server is at a minimum and users are not connected to the databases involved in the transfer, click Schedule. The Schedule Transfer dialog box appears (see fig. 19.19).

   Fig. 19.19 - The Schedule Transfer dialog box is used to create a task that initiates the transfer you have specified at a later time.

8. Enter the date and time you would like the transfer to occur. You can specify a recurring schedule if you want this transfer to occur at regular intervals. Click OK to register the task and return to the Transfer Objects dialog box. Click Close to exit the transfer management interface.

See "Creating Server Groups and Registering Servers," (Chapter 18)

Using SQL Transfer Manager

To use SQL Transfer Manager, follow these steps:

1. Start SQL Transfer Manager. The SQL Transfer Manager connect dialog box appears. Enter the names of the source and destination servers. Enter the appropriate login IDs and passwords.

2. Click Connect. The SQL Transfer Manager - <servername> to <servername> dialog box appears, as shown in figure 19.20.

   Fig. 19.20 - The SQL Transfer Manager dialog box is used to specify the source and destination databases and the objects to transfer.

3. Using the From <servername> Database drop-down list box, select the database from which to transfer data or objects. Select the To database in the same manner.

4. Specify the objects to be transferred. The default selection is All Objects, which transfers the entire database.

5. You can use SQL Transfer Manager to copy the data or simply create an identical, but empty, database structure. If you want to include the data, select the Include Data check box.

6. If you want to transfer security information (which is appropriate if the same users will need to access the information on the new server), select the appropriate check boxes in the Logins area of the dialog box. You should select all the boxes if you want identical permissions for all users and groups currently defined.

7. The Export Only check box determines whether the actual transfer occurs. If this box is checked, scripts will be created (and saved in the location specified in the Script directory text box) that can later be run to create all objects that have been specified. If the Export Only check box is cleared, the scripts are still created, and all objects and data are transferred.

8. Click Transfer. If you want to interrupt the transfer process after it has started, press Ctrl+C.

Using SQL Enterprise Manager to Generate SQL Scripts

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Open the Databases folder by clicking the plus sign (+) to the left of the folder. Select the database you want to manage.

4. Open the database containing the object of interest. Open the Objects folder and then the folder containing the type of objects (tables, stored procedures, rules, and so on.) you need.

5. Position the mouse cursor over the object and click the right mouse button. A context-sensitive menu appears, as shown in figure 19.21.

   Fig. 19.21 - The context-sensitive menu for a table object can be used to initiate the generation of SQL scripts.

6. Select Generate SQL Scripts from the menu. The Generate SQL Scripts dialog box appears (see fig. 19.22).

   Fig. 19.22 - The Generate SQL Scripts dialog box allows you to specify the scripts you would like to create. You can specify the objects for which scripts should be generated and the type of scripts.

7. Use the Add and Remove buttons to identify additional objects for which you want to generate scripts. The object you selected earlier in step 5 should already appear in the list at the right of the dialog box.

8. Select scripting options and identify the security statements that should be added (if any).

9. Click Preview to view the SQL statements that will be generated based on your current selections. The Object Scripting Preview dialog box appears (see fig. 19.23).

   Fig. 19.23 - The Object Scripting Preview dialog box provides you with the opportunity to view the scripts that have been generated prior to saving them to disk.

10. If you are not satisfied with the current results, click Close and return to step 7.

11. When you are satisfied with the SQL statements that will be generated, click the Save As button. A standard Save As dialog box appears so that you can name the file and save it in the location you choose. Alternatively, you can click the Script button from the main dialog box to generate the script without a preview.

Using SQL Scripts and Stored Procedures for Maintenance Tasks

A script file can be created using any editor, such as Notepad (which is included with most versions of Windows), or it may be entered directly into ISQL/w and executed immediately or saved for later. Files that have been created and saved can be loaded in ISQL/w and executed or provided as a parameter to the isql command directly from the operating system prompt.

It is also possible to save a script in SQL Server as a stored procedure. This causes SQL Server to compile the SQL statements in the script and store them in the database. Because the statements have already been compiled by SQL Server, execution time is generally faster. In addition, this procedure can now be called by application programs or initiated from clients using command-line utilities if they have been granted permission to run the procedure. So turning a script into a stored procedure allows you to control access with SQL Server security, makes it potentially available to all clients, and can improve execution speed.

See "Stored Procedures," (Ch. 18)

An enhancement to the EXEC statement first introduced in version 6.0 allows you to provide a variable argument to EXEC. This mechanism gives you the capability to write general purpose scripts that can be given the name of a database, table, or other object as an argument. In addition, it is possible to query a system table for the names of all objects of a certain type (for example, tables in a database) and execute a script sequentially on each of them. This creates exciting opportunities for using short, elegant scripts to perform routine maintenance chores on SQL Server. Some examples of maintenance scripts that are applicable to specific situations are provided in the Administrator's Companion. See "Creating Supplemental Stored Procedures" in Chapter 24 of the Administrator's Companion for additional examples.

To use ISQL/w for script creation and execution, follow these steps:

1. Start ISQL/w. The Connect Server dialog box appears (see fig. 19.24).

   Fig. 19.24 - The Connect Server dialog box is used to specify the name of the server you want to connect to and the type of connection you will use.

2. Enter the name of the server to which you want to connect. Select the type of connection you will use and if you are using an untrusted connection (Standard Security), enter your SQL Server login ID and password. If you are using a trusted connection, leave this blank. Click Connect, and a Query window appears, as shown in figure 19.25.

   Fig. 19.25 - This figure depicts a query window being displayed in ISQL/w.

3. Select the database you want to manage from the DB drop-down list box. You can now click your mouse cursor anywhere in the query window to get an insertion point and begin entering and editing a query. Alternatively, you may want to use a full-featured editor to create a text file with an extension of SQL. You can then load and execute it here. To load a previously prepared T-SQL script, click the Load SQL Script button. An Open File dialog box appears, as shown in figure 19.26.

   Fig. 19.26 - The Open File dialog in ISQL/w is used to browse for, and open, an existing script file containing T-SQL statements for subsequent modification or execution.

4. Your script is now visible in the Query tab of the Query window (see fig. 19.27).

   Fig. 19.27 - A query has been opened and is about to be executed in this figure.

5. Before executing the query, there are two optional buttons you can select to provide additional information about the query execution process. The two options are the Display Statistics I/O button and the Display ShowPlan button, both of which are located in the lower left corner of the query window. Click one or both of these to activate the use of the Statistics I/O tab and/or the ShowPlan tab. Click the Execute Query button to run your query. The Results tab automatically appears (see fig. 19.28).

   Fig. 19.28 - The Results tab of a Query window in ISQL/w displays the outcome when a query is executed.

6. To view the statistics on input and output operations for the query, click the Statistics I/O tab. A bar graph summarizing input and output operations required to resolve the query appears, as shown in figure 19.29.

   Fig. 19.29 - When a query is executed (and the Statistics I/O toolbar button has been clicked) the Statistics I/O tab displays a bar graph summarizing the input/output operations required to resolve the query.

7. To view a graphical Showplan output, click the Showplan tab. A chart appears, indicating index usage and table scans needed to resolve the query (see fig. 19.30).

   Fig. 19.30 - The Showplan tab provides a graphical representation of the plan used to resolve the query. The indexes used, and any table scans that were required, are represented on the display.

8. Click the Remove Query toolbar button if you no longer need a query. Click the New Query toolbar button to open a new query window and continue. You can use the Queries drop-down list box to select a query from among active queries.

Automating Maintenance with the SQL Executive

Single T-SQL statements may be entered directly in the Command box of a New Task dialog box. T-SQL scripts can either be compiled as stored procedures and called with a T-SQL command, or saved as an operating system file and executed as a command-line process using isql.

An example of a task ideally suited to this type of automation is the creation of backups for your data and transaction logs. A typical schedule might involve backing up, or dumping, the database (data and log) once a week and dumping just the transaction log each night when processing activity is at a minimum.

To create a task for routine maintenance, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Task Scheduling toolbar button or choose Server, Tasks from the menu. The Task Scheduling dialog box appears, as shown in figure 19.31.

   Fig. 19.31 - The Task Scheduling dialog box lists the currently defined tasks included those not currently enabled.

4. Click the New Task toolbar button, and the New Task dialog box appears, as shown in figure 19.32.

   Fig. 19.32 - New tasks are defined using the New Task dialog box.

5. Enter a name for the task in the Name text box. Select the type of task this will be - T-SQL or CmdExec. It is possible to create your own tasks using the special types for replication (Distribution, LogReader, and Sync), but it is not usually necessary.

   See "Data Replication," (Ch. 20)

6. Make sure that the Enabled check box is selected if this task should be activated immediately, even if it will not be scheduled for execution until later. Select the database this task will be executed upon in the Database drop-down list box.

7. Enter the command that will actually perform the task in the Command box. You can specify additional options for the command by clicking Options. A Task Options dialog box appears (see fig. 19.33).

   Fig. 19.33 - A T-SQL Task Options dialog box.

8. Select an operator to be e-mailed upon success or failure of this task if you want. You can also write success or failure notification to the Windows NT Application Event log. You can also indicate a retry delay and number of retry attempts to be used in the event of failure. Click OK to return to the New Task dialog box.

9. Select the appropriate option button in the Schedule box. On Demand tasks are used by alerts and executed manually using the Run Task toolbar button from the Task Scheduling dialog box. Auto Start tasks are used by replication processes and are launched automatically every time the SQL Executive is started.

   One Time tasks run at the date and time specified and are then disabled but not deleted. Recurring tasks are launched at regular intervals that you specify. Click Change if you need to modify the schedule for a recurring task, and the Task Schedule dialog box appears (see fig. 19.34).

   Fig. 19.34 - The Task Schedule dialog box is used to specify the scheduled times at which this task will be run.

10. Make appropriate selections to indicate the recurring interval when this task should be launched. Click OK to return to the New Task dialog box.

11. Click Add to add the task.

You may want to view tasks that are already defined, check the status of currently running tasks, or review the history of a task.

To monitor the status of tasks, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Task Scheduling toolbar button or choose Server, Tasks from the Server menu. The Task Scheduling dialog box appears

4. Some task status information is displayed directly in the Task List tab of the Task Scheduling dialog box. You can see the frequency of the task, when it was last run, and the status (success or failure) of the last run. For additional status information on a particular task, highlight the task and click the Task History toolbar button. The Task History dialog box appears, as shown in figure 19.35.

   Fig. 19.35 - The Task History dialog box displays the history for a particular task including when it was executed, the status of its execution, and the disposition of any operator e-mails.

5. You can see a list of times when the task has been run, the status (success or failure) of the task, whether an operator was e-mailed, and the amount of time it took for the task to execute. The Last Error/Message displays any messages that may have been generated when the task was last executed.

6. Click Clear All if you want to clear the history for this task. Click Close to return to the Task Scheduling dialog box.

7. To see which tasks are actively running on the server, select the Running Tasks tab. The Running Tasks dialog box appears, as shown in figure 19.36.

   Fig. 19.36 - If you have any actively running tasks, they will be displayed in the Running Tasks tab of the Task Scheduling dialog box.

8. The name, owner, type, start date, and status of running tasks are displayed.

Using SQL Server Security

SQL Server can support users gaining access over two types of connections - trusted and nontrusted. Using Integrated security, SQL Server validates users based on their Windows NT user ID and establishes a trusted connection. With Standard security, SQL Server does not rely on any external security authority and uses a nontrusted connection to accept a login ID and password that are created and managed by SQL Server. Mixed security allows the use of either type of connection.

Native SQL Server login IDs can be created with T-SQL statements or by using SQL Enterprise Manager. These can be used to log on using Standard security, or they can be associated with a Windows NT user ID with Integrated or Mixed security. The SQL Security Manager is the tool that accomplishes this mapping of Windows NT accounts to SQL login IDs. If you are only using Standard security, the SQL Security Manager plays no useful role for your server.

However you connect to SQL Server, the login ID you are using (explicitly, or through an implicit mapping) will be associated with a username when you use a database. The username may match the login ID, or it may be completely different. Some administrators, especially with Integrated security, prefer to have user's IDs match with usernames to keep things simple. In other cases, a collection of IDs may all be mapped to the same username (which would then be an alias) to provide equivalent privileges to those users.

Having connected to SQL Server and initiated use of a particular database, the access privileges you accrue will depend on the rights granted to you by the system administrator (SA) or database owner. The SA can grant the ability to create databases to other users. The creator of a database automatically becomes the database owner (DBO). Only the DBO can grant privileges to create, use, and modify objects in a database. The SA, however, can impersonate any user in a database.

The next section introduces you to the SQL Security Manager and describes how to map Windows NT user accounts to SQL login IDs. Next, you learn how to use SQL Enterprise Manager to grant privileges to create databases and then, within a database, grant privileges to create and use objects. Remember that anyone can use SQL Enterprise Manager. A user's login ID and username in a database determines exactly what operations a user can or cannot perform.

See "Choosing a Security Model, " (Ch. 18)

Using the SQL Security Manager

The SQL Security Manager maps an entire Windows NT group to SQL login IDs in one operation. You cannot specify an individual Windows NT account to be mapped. These groups can be granted user privileges or SA privileges. A suggested strategy to simplify the process and make it manageable as users' needs change over time is as follows:

• Create Windows NT local groups (not global groups) that correspond to the desired categories of SQL Server users.

• For example, create a group called SQLAdmins, or something similar, and add any users who will be responsible for administration of SQL Server.

• Also create one or more groups of users that will receive only user privileges. For easier management of permissions, it is a good idea to create multiple groups that correspond to different access needs in SQL Server.

• For example, if you need to allow a group of users specific privileges for an inventory application, you might create a group called InvUsers. Perhaps some of the users are managers and require different permissions. An additional group, called InvMgrs for example, should be created to facilitate this process.

The first time you use SQL Security Manager, you will either need to log on using the built-in sa login ID, or be logged on to Windows NT with an account that is a member of the Windows NT Administrators local group. By default, this group includes the global group Domain Admins if you are using Windows NT domain security. Also by default, this group is granted SA privileges during SQL Server setup. If you are using integrated security, and your Windows NT account meets the criteria outlined earlier, you do not need to enter a login ID or password.

If you are not a Windows NT administrator but are a member of a newly created SQLAdmins group that will be assigned SA privileges (following the suggestion made earlier), you should connect the first time using the built-in sa login ID. Then use the following procedure to grant SA privileges to the SQLAdmins group. The next time you connect, you will not need to enter a login ID and password, and your Windows NT account status (as a member of the SQLAdmins group) will map you to SA privileges.

To use the SQL Security Manager to map a Windows NT group to SQL Server login IDs and grant them user privilege, follow these steps:

1. Start the SQL Security Manager. The Connect Server dialog box appears (see fig. 19.37).

   Fig. 19.37 - The Connect Server dialog box is used to specify the name of the server you want to connect to and the type of connection you will use.

2. Enter the name of the server you want to manage, or select the name from the Server drop-down list box. If needed, enter a Login ID and Password. Click Connect. The SQL Security Manager window appears, as shown in figure 19.38.

   Fig. 19.38 - The SQL Security Manager window is depicted in this figure. A single Windows NT group (quebook) from the GSULLIVAN domain has been granted user privilege on this server.

3. To assign a Windows NT group user privilege in SQL Server, click the User Privilege toolbar button. From the menu, choose Security, Grant New. The Grant User Privilege dialog box appears (see fig. 19.39).

   Fig. 19.39 - The Grant User Privilege dialog box allows you to assign user privileges on the SQL Server to which you are connected for selected Windows NT groups.

4. Select the option button for either Local Groups or Groups on Default Domain. If you are following the suggested guidelines, the Windows NT groups you defined should be local groups defined in the domain, not local groups defined in the account database of the computer running SQL Server. Therefore, you should select Groups on Default Domain.

   
   Your newly defined groups appear when you select Local Groups. You have created the new groups improperly. Start User Manager for Domains and recreate the local groups in the proper Windows NT domain. Do not use the User Manager tool. Make sure that you run User Manager for Domains.
   

5. If you want each user in the group to have her own SQL Server login ID, make sure that the Add Login IDs for Group Members check box is selected. If you don't, these users will all be assigned the default login ID when they use SQL Server. Be sure that the default login ID has been defined if you clear this check box.

6. You can add these users to a database and make that database their default database. This is usually a good idea. If you don't explicitly make an assignment, the master database will be a user's default database. It is usually not appropriate for users to create new databases in the master database.

7. Click Grant.

See "Default Login ID," (Ch. 18)

To use the SQL Security Manager to map a Windows NT group to SQL Server login IDs and grant them SA privilege, follow these steps:

1. Start the SQL Security Manager. The Connect Server dialog appears.

2. Enter the name of the server you want to manage, or select the name from the Server drop-down list box. If needed, enter a Login ID and Password. Click Connect. The SQL Security Manager window appears.

3. To assign a Windows NT group SA privilege in SQL Server, click the SA Privilege toolbar button.

4. From the menu, choose Security, Grant New. The Grant System Administrator Privilege dialog box appears (see fig. 19.40).

   Fig. 19.40 - The Grant System Administrator Privilege dialog box allows you to assign SA privileges on the SQL Server to which you are connected for selected Windows NT groups.

5. Select the option button for either Local Groups or Groups on Default Domain. If you are following the suggested guidelines, you should select Groups on Default Domain and choose the group called SQLAdmins (or the other name you chose for the system administrator's group).

6. The Add Login IDs for group members and the Add Users to Database check boxes are dimmed because all users in this group will be mapped to the sa login ID. They are assigned master as their default database.

7. Click Grant.

To see what privileges a group has been granted, follow this procedure:

1. Start the SQL Security Manager.

2. Click the User Privilege toolbar button.

3. Press the Ctrl key and double-click the group name. The Account Detail dialog box appears (see fig. 19.41).

   Fig. 19.41 - The Account Detail dialog box provides the opportunity to add a Windows NT group to a database.

4. The name of the Windows NT group and the mapped SQL Server group are displayed.

   
   In general, it is a good idea to let SQL Server generate random unique passwords for the login IDs created for users in a group. The passwords are not needed when using integrated security. Because your Windows NT account and password establish a trusted connection, you need not enter a login ID and password when prompted. However, if you do not place a password on the account, the account may be compromised under Mixed security, or should you ever change to a Standard security model.
   

5. View or modify information as necessary.

6. Click Close.

Understanding Object Ownership and Permissions

An SA or DBO can use SQL Enterprise Manager to assign permissions for a user or group. Permissions can be assigned to access and use various database objects or to use particular T-SQL statements. Statement permissions are set for an entire database - they are not object-specific. For example, if a user is granted permission to use the CREATE RULE statement, that user can create rules for columns in any table in the database.

To grant permissions for database objects using SQL Enterprise Manager, follow these steps:

1. Start SQL Enterprise Manager.

2. Select the server you want to manage. Open its database folder and select the database whose permissions you want to view or modify.

3. Choose Object, Permissions from the menu. The Object Permissions dialog box appears, as shown in figure 19.42. If it is not already displayed, select the By Object tab.

   Fig. 19.42 - The By Object tab of the Object Permissions dialog box allows you to select an object in a database and assign permissions for one or more users and groups in the database.

4. Select an object from the Object drop-down list box. The current set of permissions for this object is displayed. New selections you make will be marked pending until you click Set at the end of this procedure.

5. You can choose Grant All to grant all permissions to all users. Revoke All revokes all permissions. Sometimes it is helpful to use these buttons to start with and then selectively add or remove specific permissions by clicking individual check boxes to fine-tune your selection.

6. If you want to set column-level permissions, select a table or view in the Object drop-down list box. Then select a user or group name from the list of names and click the Column Level Permissions check box. Click the appropriate check boxes indicating which columns may be selected or updated by the selected user or group.

7. Click Set to grant the selected permissions.

To grant permissions to use particular T-SQL commands, follow this procedure:

1. Start SQL Enterprise Manager.

2. Select the server you want to manage. Open its database folder and double-click the database whose statement permissions you want to view or modify. The Edit Database dialog box appears, as shown in figure 19.43. Select the Permissions tab.

   Fig. 19.43 - The Permissions tab of the Edit Database dialog box allows you to grant permissions for the creation of database objects to particular users.

3. The current set of permissions are indicated by check marks. As you grant new permissions, they will be indicated as pending until you click OK to update your new selections. Make changes by clicking the check boxes with the mouse cursor.

4. Click OK to update your permission changes.

The capability to grant the Create DB permission is only given to an SA and may only be applied to users of the master database. Therefore, there is no check box for this permission when viewing user databases or the sample PUBS database.

Using Dial-Up Access to SQL Server

RAS works very well with most SQL Server applications. Because SQL Server was designed using the client-server process model, the processing workload is divided between clients and servers. A client typically passes a request for information to SQL Server; the request is processed and resolved on the server; and only the appropriate answer set is returned over the connection. If the connection is a relatively slow link, this distinction is important.

All administrative tools, with the exception of the SQL Transfer Manager, work well over a RAS connection. Obviously, it would not be a good idea to initiate a large transfer operation over a slow connection.

The Administrator's Companion describes how to set up remote servers and remote users. Do not confuse this terminology with the similar concepts used for RAS. In SQL Server terminology, a "remote" server is defined so that a user on the "local" server can execute stored procedures on the "remote" server. The remote server is usually on the same LAN or WAN as the local server.

SQL Server does not support the concept of a domain like Windows NT Server. In other words, you cannot have a group of SQL Servers automatically share the same login IDs. As you have already learned, SQL Server usernames and groups are only defined within a particular database, not across an entire server, and certainly not across multiple servers. With integrated security, it is possible to use SQL Security Manager to map the same domain groups to each SQL Server on a LAN, thereby creating the same login IDs on all servers, but they do not automatically stay synchronized. There is no default process that automatically keeps the login IDs for all SQL Servers the same.

In practice, this is not a problem. The nature of database server usage, and the appropriate use of groups to assign permissions, makes the task of managing access to databases a relatively straightforward task. This discussion has been provided to eliminate any potential confusion from arising due to the similar terminology used by different BackOffice components.

Backing Up and Restoring Data

This section outlines rudimentary backup procedures that can be used on an ad hoc basis to provide a backup of essential data. Before you put a system into production, you should spend time to thoroughly familiarize yourself with backup and restore operations. Procedures for creating a backup (dump) device have already been described.

To back up a database to a disk dump device, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Open the Databases folder by clicking the plus sign (+) to the left of the folder. Select the database you want to backup.

4. Choose Tools, Database Backup/Restore from the menu. The Database Backup/Restore dialog box appears (see fig. 19.44).

   Fig. 19.44 - This dialog box is used to specify the source database and the destination dump device for a backup operation. Additional options, including scheduling the backup for a later time, can be designated.

5. If you want, you can select a different database to be backed up instead of the one you originally highlighted. Use the drop-down combo box in the Database Backup box.

6. Select the information you want to back up - the entire database, the transaction log, or a single table.

7. Select the backup (dump) device that will be the destination of the backup. You can define a new dump device by clicking New if you want.

8. Click Backup Now to begin the backup operation. A progress indicator provides feedback on the process. When it is finished, a Backup Completed! message box appears.

See "Backup (Dump) Devices," (Chapter 18)

Tuning SQL Server

Configuring RAM

SQL Server Memory

The setup program (in version 6.0 and 6.5) assigns memory to SQL Server based on the amount of total memory installed in the computer. If your server has less than 32M of RAM, SQL Server is assigned 8M. If your server has 32M or more, SQL Server is assigned 16M.

In some situations, this straightforward approach needs to be improved upon. For example, if you are setting up a very large server to handle gigabytes (G) of data, you will want to configure your server with 128M of RAM (or more) and assign much more than 16M to SQL Server. In addition, servers participating in replication scenarios typically need more memory in general and more assigned to SQL Server.

To assign more memory to SQL Server, follow this procedure:

1. Start SQL Enterprise Manager.

2. Open the appropriate server group and select a server to manage.

3. Choose Server, Configurations from the menu. The Server Configuration/Options dialog box appears, as shown in figure 19.45. Select the Configuration tab.

   Fig. 19.45 - The Configuration tab of the Server Configuration/Options dialog box is used to change the value of various SQL Server configuration parameters. Alternatively, you can use the sp_configure system procedure to change these values.

4. The amount of memory assigned to SQL Server appears in the Running column. This amount indicates the number of 2K blocks of memory assigned, so 16M appears as the number 8192. Enter the new amount for the memory option in the Current column.

   
   One kilobyte (1K) equals 1024 (28) bytes or characters.
   

5. Click OK.

6. This change does not take effect until SQL Server is stopped and restarted. You can use the Stop/Pause/Start button on the SQL Enterprise Manager toolbar (the stoplight button) or the SQL Service Manager. If there are active users, you should wait until a later time when the server is not being used. Alternatively, you can pause the server, notify users to sign off, wait an appropriate time period, and then stop and restart the server. See the next section, "Using the Performance Monitor," for information on monitoring SQL Server's resource utilization during operation.

The manner in which SQL Server allocates memory is relatively complex as one might expect. An overview of the way SQL Server uses memory is as follows:

• A certain amount (approximately 2M) of static overhead is used by SQL Server to contain the program itself.

• Configurable options have an impact on memory usage. Significant options are user connections, open databases, open objects, locks, and devices. The DBCC MEMUSAGE statement provides a per-resource value for these items, which can then be multiplied by the setting for that option. Be sure when performing the calculation to use the same scale (bytes or M) for all figures.

• The total cache size (data + procedure cache) can be estimated by subtracting both the static overhead and the memory used by configurable options from the total SQL Server memory setting. The remaining memory is the approximate total cache size.

Procedure Cache

SQL Server allocates some of its memory to cache frequently used procedures. There are a number of approaches for estimating an appropriate size for the procedure cache, but all of them will only yield a "best guess" to use as a starting point. Validate your settings with regular testing if maximum performance is critical to your organization.

One approach to estimating procedure cache size is the following:

1. Analyze the stored procedures defined on your server and identify those frequently used throughout a normal work day.

2. Estimate the average size of the query plan for these procedures. The smallest plan size is one page (2K). You can use the DBCC MEMUSAGE command to determine the query plan size for a procedure.

3. You would like at least one copy of all frequently used procedures to fit in the cache to avoid swapping. Therefore, multiply the number of frequently used procedures by the average size of these procedures to calculate a minimum procedure cache size. For example, ten frequently used procedures with an average size of ten pages would yield a size of 100 2K pages, or approximately 2M. Add an additional 10 to 20 percent for additional procedures that are used less often.

Remember that this is a rough approximation. You must perform testing if you want to be sure that your settings are appropriate.

The procedure cache option is set using the same steps outlined previously for changing the memory option. The setting does not represent a number of pages, however, but rather a percent of the total cache space allocated to the procedure cache. The rest is allocated to the data cache.

Any time you are testing something, it is a good idea to change one item at a time and then measure possible changes. This is made somewhat more difficult when changing options on SQL Server because a change to a single setting can impact more than one item. If you want to increase the size of the procedure cache while maintaining the same data cache size, you must increase both the memory and the procedure cache settings.

User Connections

SQL Server allocates approximately 37K for each defined user connection. This space is allocated even if some connections are unused. Although there is always a tendency to allocate a few extra connections to handle unusual situations, it is important to limit the number of user connections as much as possible.

Increasing user connections decreases the total cache size (and hence the size of both the data and procedure cache) unless you increase the memory option. To maintain the same total cache size, you would need to add approximately 19 (2K) pages for each user connection.

Using Performance Monitor

Fig. 19.46 - This figure depicts Performance Monitor creating a chart with selected SQL Server counters.

You can create your own predefined set of counters to monitor and save it in a file so that you can easily use this configuration again. You can even define an icon to automatically launch Performance Monitor and load this predefined configuration. If you are doing benchmark testing, you may want to explore the Performance Monitor in detail. You can also log measurements made with Performance Monitor in addition to displaying them in a real-time graph. The Windows NT Resource Kit includes a manual entirely devoted to this powerful tool.

Remember that as soon as you measure something, you have changed it somewhat. To minimize the overhead associated with performance monitoring activities, it is suggested that you connect to the SQL Server under investigation from a Windows NT workstation that contains the SQL Server administration tools and run Performance Monitor from there. In addition, you should only turn on the disk performance counters while you are actively benchmarking your server. Remember to turn them back off when you are finished.

In addition to the predefined settings provided with SQL Server, some important counters are outlined in the following sections.

RAM

Memory management is one of the most important performance improvement areas you can explore. Ideally, you want to maximize the use of cache and minimize the amount of physical I/O required. The primary counter to monitor for cache utilization is the Cache Hit Ratio. This counter measures the percentage of time that a request was resolved using information (pages) in the data cache avoiding the need to physically read information from disk. This counter monitors the use of the data cache, not the procedure cache. You want this counter to be high.

New in SQL Server 6.5, is the Procedure Cache object. Some objects that might warrant particular scrutiny are the Max Procedure Cache Used % and the Procedure Cache Used %. By watching the Procedure Cache Used % over time, you can tell if you have allocated too much space to the procedure cache. The Max Procedure Cache Used % tells you what was needed at peak usage. If this represents a single spike during a short period of unusual activity, you may want to try a smaller procedure cache percentage setting.

DiskPerf

To measure physical disk activity, you must first activate the counters associated with the Physical Disk object. The counters are turned off by default, because they slow down disk access. The Windows NT Server System Guide lists the overhead as 1.5 percent on a 386-class computer. You must be a Windows NT administrator (not a SQL Server SA) for the computer you want to monitor. At a command prompt, type the command diskperf to see if these counters are on or off and view a help screen for additional details about the command.

After these counters have been turned on, you can view a number of key counters related to SQL Server. Of particular interest are I/O-Page Reads/sec and I/O-Outstanding Writes. The first of these counters represents the number of physical page reads per second. Ideally, you want this number to stay low and the Cache Hit Ratio to be high. Many factors impact this counter, including the size of the data cache, the use of indexes, the formulation of queries, and the overall design of your databases. No amount of tuning can make up for a poor design.

The I/O-Outstanding Writes counter can indicate whether your disk subsystems are up to the job of running SQL Server with the load that you are placing it under. If this counter remains high over a long period of time, it indicates that the I/O subsystem on this computer is not able to keep up with the demands being made. A faster subsystem, such as a high-performance disk array, might have a significant impact on performance.

From Here...

• For an overview of data replication and distributed transaction management, see Chapter 20, "SQL Server Advanced Topics."

• For information on the host connectivity component of BackOffice, SNA Server, see Chapter 21, "SNA Server Preparation and Installation."

• For information on the systems management component of BackOffice, Systems Management Server (SMS), see Chapter 25, "Preparing for SMS."

Table of Contents

18 - Building Your SQL Server

20 - SQL Server Advanced Topics