Monitoring and Enhancing MS-SQL Server Performance

Understanding MS SQL Server Multi tasking and Multi threading on a Multi processor Windows NT System

When considering the scalability and performance of an MS-SQL Server running in a Windows NT environment, it is important to understand the differences among the impacts of multi-tasking, multi-threading, and multi-processing on MS-SQL Server performance.

Multi-tasking refers to the operating system's ability to run multiple programs at the same time. Although only one application can have control of the central processing unit (CPU) at a time, the operating system creates the illusion that several programs are executing simultaneously, by letting each of them use the CPU for a period of time. At the end of the program's CPU time slice, the system notes where the program left off so that it can restart the program at the same point the next time it gives the application control of the CPU. Then the system switches to another application and lets that program control use the CPU for a time, and so on. It takes the operating system only a few hundred milliseconds to switch from one application to another. However, each of the programs will appear to run more slowly when the time between CPU time slices allotted to the program increases as the server shares the CPU among an increasing number of programs.

Each program running on an NT Server is called a process. Figure 501.1 shows statistics on several of the 35 processes (including 2 instances of MS-SQL Server) running on a Windows NT Server.

Figure 501.1: The Windows NT Task Manager showing statistics on processes running on an NT Server

The operating system assigns each process a priority from 1 to 31 (which increases the longer the process waits for its CPU time slice) and grants use of the CPU to the process with the highest priority. Each instance of MS-SQL Server, for example, defaults to an initial (base) priority of 7. (Processes that run at a base priority of 7 are said to run at normal priority because the majority of applications start themselves at a base priority of 7.) In Tip 502, "Using the MS-SQL Server PRIORITY BOOST Configuration Option to Increase Server Thread Priority from 7 to 13," you will learn how to use the PRIORITY BOOST configuration option to increase the MS-SQL Server's base priority to 13, which will cause the MS-SQL Server process to run at high priority.

Multi-threading relates to multi-processing in that each program running on an NT Server may be written in sections so that each section or thread in an application can operate on the CPU independently. Thus, when the NT operating system gives a CPU time slice to a multithreaded process, the system actually starts (or restarts) the highest-priority thread (or section of code within the process) at the point at which the thread halted the last time it had to give up the CPU to another process or thread. One of the instances of MS-SQL Server (shown in Figure 501), for example, has 30 threads, while the other has 31. Therefore, when Windows NT gives control of the CPU to one of the two instances of MS-SQL Server, it is actually giving the CPU time slice to the MS-SQL Server thread with the highest priority that is waiting to execute.

Each instance of the MS-SQL Server always runs several threads: one for each network protocol used to communicate with workstations and other servers, one to handle login requests, another to communicate with the server's service control manager, and several others to execute the individual SQL and Transact-SQL statements and statement batches users send to the MS-SQL Server for execution. Multi-tasking with multiple threads is most advantageous when the NT Server has multiple processors because a single CPU (or processor) can execute only one thread at a time.

Multi-processing refers to the Windows NT operating system's ability to spread processes and threads across multiple CPUs located in a single server. Thus, a Windows NT Server with five CPUs can execute five threads or processes simultaneously. A multi-CPU NT Server is particularly advantageous when used to host an MS-SQL Server because the MS-SQL Server can use one of the CPUs to execute database I/O requests while at the same time using another CPU to execute a login thread to allow another user to open a connection to the SQL server and executing a query for another user on a third CPU, and so on.

Using the MS SQL Server PRIORITY BOOST Configuration Option to Increase Server Thread Priority from 7 to 13

As you learned in Tip 501, "Understanding MS-SQL Server Multi-tasking and Multi-threading on a Multi-processor Windows NT System," Windows NT assigns a priority from 1 to 31 to each process and thread waiting to execute on one of the server's CPUs. If more than one process or thread is waiting to execute, the operating system will give the available CPU time slice to the thread with the highest priority. Each MS-SQL Server thread defaults to the normal priority of 7. However, you can use the PRIORITY BOOST option to increase the thread base priority to 13. Since doing so will give MS-SQL Server threads a higher priority than other processes and threads waiting to execute on the server, MS-SQL Server will tend to execute queries and other DBMS commands more quickly. In fact, at a (high) base priority of 13, the NT Server will tend to execute its threads whenever they are ready to run (since they will tend to have a higher priority than other threads and processes). Moreover, the high-priority MS-SQL Server threads will not be preempted by threads from other processes—again because they will tend to have a higher priority than other processes and threads waiting to use the CPU. (A thread is preempted when it is forced to relinquish control of the CPU to another waiting thread with a higher priority.)

To boost the base priority of MS-SQL Server threads from 7 (normal) to 13 (high), perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Microsoft SQL Server 2000 option, and click your mouse pointer on Enterprise Manager. Windows will start Enterprise Manager in the SQL Server Enterprise Manager application window.
3. Click your mouse pointer on the plus (+) to the left of Microsoft SQL Servers and then on the plus (+) to the left of SQL Server Group to display the list of MS-SQL Servers available on your network.
4. Right-click your mouse pointer on the name of the SQL server whose base priority you want to boost, and then select Properties from the pop-up menu. The Enterprise manager will display the SQL Server Properties (Configure) dialog box.
5. Click your mouse pointer on the Processor tab.
6. Click your mouse pointer on the check box to the left of Boost SQL Server Priority on Windows until a check mark appears.
7. Click your mouse pointer on the OK button to update the MS-SQL Server configuration and return to the Enterprise Manager main application window.

After you complete Step 7, you must stop and restart the MS-SQL Server in order for the priority boost to take effect.

Understanding the NT Server Performance Monitor Chart View

Windows NT Server includes a Performance Monitor that you can use to check the performance of one or more computers (both workstations and servers) on a network. Items that the Performance Monitor can monitor (referred to as objects by the application) include processors (CPUs), executing programs (processes), threads, hard drives, network resources, Internet services, and memory. To start the Windows NT Performance Monitor and generate a graphical display of MS-SQL Server statistics, perform the following steps:

1. Click your mouse pointer on the Windows NT Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Performance Monitor. Windows will start the Performance Monitor in Chart View similar to that shown in Figure 503.1.

   Figure 503.1: The Windows NT Performance Monitor Chart View

3. To add objects (items to monitor) to the chart displayed in Chart View, select the Edit menu Add to Chart option, or click your mouse pointer on the Add counter button (the button with the plus [+]) on the Standard toolbar. The Performance Monitor will display an Add to Chart dialog box similar to that shown in Figure 503.2.

   Figure 503.2: The Windows NT Performance Monitor Add to Chart dialog box

4. Enter the network path of the NT Server on which the SQL Server you wish to monitor is running into the Computer field. If you started Performance Monitor on the NT Server running the SQL Server you want to monitor, you can accept the default. If you do not know the server's path, click your mouse pointer on the Search button (labeled with the three dots [...]) to the right of the Computer field, and then select the NT Server you want from the drop-down list in the Select Computer dialog box.
5. To select the type of resource you want to monitor, click your mouse pointer on the drop-down list button to the right of the Object field, and then select one of the objects from the drop-down list. For the current project, select SQLServer:Buffer Manager from the drop-down list of objects you can monitor.

   Note

   The name of the MS-SQL Server may be something other than SQLServer on your system. For example, MS-SQL Server 2000 will be labeled as MSSQL$. Therefore, if you installed MS-SQL Server 2000 into the MSSQL2000 folder, you would select MSSQL$MSSQL2000:Buffer Manager in Step 5.

6. Click your mouse pointer on the scroll bar to the right of the Counter list box to find the specific counter value you want to display for the object you selected in Step 5. For the current project, select Page Reads/sec in the Counter list box.
7. Click your mouse pointer on the Add button to add the Counter you selected in Step 6 for the Object you selected in Step 5 to the Performance Monitor Chart View display.
8. Repeat Steps 4 through 7 for each Object/Counter you want to add to the Performance Monitor real-time chart. Each time you add a new Object/Counter, Performance Monitor will automatically change the line color it will use to draw the selected counter's statistics on the chart.
9. To exit the Add to Chart dialog box and return to the Performance Monitor Chart View of the Object/Counter items you selected (in Steps 4–8), click your mouse pointer on the Done button.

If you decide that you want to add additional items to the chart, you can repeat the preceding procedure from Step 3. Conversely, to remove an Object/Counter, click your mouse pointer on the item you no longer want displayed in the list of Object/Counter items at the bottom of the Performance Monitor application window, and then select the Edit menu's Delete from Chart option, or click your mouse pointer on the Delete Selected Counter button (labeled with an X) on the Standard toolbar.

To clear all Object/Counter items, select the File menu's New Chart option.

Understanding the NT Server Performance Monitor Report View

In Tip 503, "Understanding the NT Server Performance Monitor Chart View," you learned how to use Performance Monitor to display Object/Counter information in graphical form. Charts work well when you want to view the change in an Object/Counter item's value over time and when you want to compare the values of two or more Object/Counter items. However, there are times when you want to see the Performance Monitor statistics in tabular (vs. graphical) form. The Performance Monitor's Report View will display the numeric values for the Object/Counter items you select. In Chart View, you must interpret the values of the items by the position of the line(s) on the graphic relative to the data point's height on the chart's vertical axis.

To display Performance Monitor Object/Counter items in Report View, perform the following steps:

1. Click your mouse pointer on the Windows Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Performance Monitor. Windows will start the Performance Monitor in Chart View similar to that you saw in Figure 503.1 in Tip 503.
3. Select the View menu Report option, or click your mouse pointer on the View Report data button (fourth button from the left) on the Standard toolbar.
4. To add objects (items to monitor) to the report, select the Edit menu's Add to Report option, or click your mouse pointer on the Add counter button (the button with the plus [+]) on the Standard toolbar. Performance Monitor will display the Add to Report dialog box (similar to the Add to Chart dialog box you saw in Figure 503.2 in Tip 503).
5. Enter the network path of the NT Server on which the SQL Server you wish to monitor is running into the Computer field.
6. To select the type of resource you want to monitor, click your mouse pointer on the drop-down list button to the right of the Object field. Then select one of the objects from the drop-down list. For the current project, select SQLServer:Databases.
7. Click your mouse pointer on the scroll bar to the right of the Counter list box to find the specific fact (or counter) you want to display about the object you selected in Step 6. For the current project, select Data File(s) Size (KB) in the Counter list box.
8. Select the database whose file size(s) you want to monitor in the for those listed in the Instance list box. For the current project, select SQLTips. (If you do not have the SQLTips database on your MS-SQL Server, select any one of the databases listed in the Instance list box.)

   Note

   Performance Monitor will display items in the Instance list box whenever you select a counter that pertains to more than one object (or "instance"). Conversely, if you select an Object/Counter item such as the Cache Size (pages) counter of the SQLServer:Buffer Manager object, the Performance Monitor does give you a choice of Instance selections, since the Object/Counter can pertain to only a single item—the MS-SQL Server.

9. Click your mouse pointer on the Add button to add the Counter you selected in Steps 7 and 8 about the Object you selected in Step 6 to the Performance Monitor Report View display.
10. Repeat Steps 5 through 9 for each Object/Counter/Instance you want to add to the Performance Monitor tabular report. For the current project, perform Steps 5 through 9 once more, selecting Log File(s) Size (KB) in Step 7 the second time through.
11. To exit the Add to Report dialog box and view the Performance Monitor Report View of the Object/Counter/Instance items you selected (in Steps 5–10), click your mouse pointer on the Done button. Performance Monitor will display a tabular report similar to that shown in Figure 504.1.

    Figure 504.1: The Windows NT Performance Monitor Report View

If you decide you want to add additional items to the tabular report, you can repeat the preceding procedure from Step 4. Conversely, to remove an Object/Counter/Instance item, click your mouse pointer on the item (in the body of the report) that you want to remove, and then select the Edit menu's Delete from Report option, or click your mouse pointer on the Delete Selected Counter button (labeled with an X) on the Standard toolbar.

To clear all Object/Counter/Instance items, select the File menu's New Report Settings option.

Understanding the NT Server Performance Monitor Alert View

Tip 503, "Understanding the NT Server Performance Monitor Chart View," and Tip 504, "Understanding the NT Server Performance Monitor Report View," showed you how to use Performance Monitor to display MS-SQL Server data on an ongoing basis. However, there are certain conditions you hope will never or rarely occur. Rather than watch a graphical or tabular display for a spike or dip in a counter value that corresponds to one or more of these events, you can tell Performance Monitor to display an alert only when a specific event occurs.

To have Performance Monitor display an alert whenever an Object/Counter item value or Object/Counter/Instance item value falls below or exceeds a specific threshold, perform the following steps:

1. Click your mouse pointer on the Windows Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Performance Monitor. Windows will start Performance Monitor in Chart View, similar to what you saw in Figure 503.1 in Tip 503.
3. Select the View menu Alert option, or click your mouse pointer on the View the Alerts button (second button from the left) on the Standard toolbar.
4. To add objects (items to monitor) to the alerts report, select the Edit menu's Add to Alert option, or click your mouse pointer on the Add counter button (the button with the plus [+]) on the Standard Toolbar. The Performance Monitor will display an Add to Alert dialog box similar to that shown in Figure 505.1.

   Figure 505.1: The Windows NT Performance Monitor Add to Alert dialog box

5. Enter the network path of the NT Server on which the SQL Server you wish to monitor is running into the Computer field.
6. To select the type of resource you want to monitor, click your mouse pointer on the drop-down list button to the right of the Object field, and then select one of the objects from the drop-down list. For the current project, select SQLServer:Locks.
7. Click your mouse pointer on the scroll bar to the right of the Counter list box to find the specific fact (or counter) you want to watch about the object you selected in Step 6. For the current project, select Number of Deadlocks/Sec in the Counter list box.
8. When the same counter is used for more than one item, Performance Monitor will list the available items (instances of the counter) in the Instance list box. As such, whenever it is not empty, select the instance of the counter you want to monitor in the Instance list box. For the current project, select Database in the Instance list box.
9. Into the Alert If field, enter the value against which you wish Performance Monitor to test the current value of the Object/Counter/Instance item you selected in Steps 6 through 9. For the current project, enter 0 in the Alert If field.
10. If you want Performance Monitor to alert you if the value of the Object/Counter/Instance value is above the value you entered in the Alert If field, click your mouse pointer on the radio button to the left of Over. Otherwise, click your mouse pointer on the radio button to the left of Under. For the current project, click your mouse pointer on the radio button to the left of Over.
11. If you want Performance Monitor to execute a program whenever it raises the alert (by displaying a notification message in the Alert View display), enter the name of the program into the Run Program on Alert field. Then click your mouse pointer on the radio button the left of First Time if you want Performance Monitor to execute the program only the first time it raises the alert, or click on the radio button the left of Every Time if you want the program executed every time Performance Monitor raises the alert.
12. Click your mouse pointer on the Add button to add the alert for the Counter you selected in Steps 7 and 8 about the Object you selected in Step 6 to the Performance Monitor Alert View.
13. Repeat Steps 5 through 12 for each Object/Counter/Instance about which you want Performance Monitor to post an alert (and, optionally, execute a program) if its value satisfies the alert criteria you specified in Steps 9 and 10. Each time you add a new alert, Performance Monitor will change the color of the "next" alert, so you will be able to distinguish one alert from another easily on the Performance Monitor Alert View display.
14. To exit the Add to Alert dialog box and, click your mouse pointer on the Done button.

The Performance Monitor Alert View display will remain empty until one of the Object/Counter/Instance values satisfies the alert criteria. Thereafter, Performance Monitor will add each alert raised to the display similar to that shown in Figure 505.2.

Figure 505.2: The Windows NT Performance Monitor Alert View

If you decide that you want to add additional alerts, repeat the preceding procedure from Step 4. Conversely, to remove an alert, click your mouse pointer on the alert that you no longer want in the list of alerts at the bottom of the Performance Monitor application window, and then select the Edit menu's Delete Alert option, or click your mouse pointer on the Delete Selected Counter button (labeled with an X) on the Standard toolbar.

To clear all alerts, select the File menu's New Alert Settings option.

Using the CREATE SCHEMA Statement to Create Tables and Grant Access to Those Tables

The SQL-89 standard made a strong distinction between the SQL data manipulation language (DML) statements and data definition language (DDL) statements. While the standard required that the DBMS be able to execute DML statements during its normal operation, it made no such demand with regard to the ability to execute DML statements. In fact, the SQL-89 standard permits an SQL database to have a static structure like that used by the older hierarchical and network database models (which you learned about in Tip 3, "Understanding the Hierarchical Database Model," and Tip 4, "Understanding the Network Database Model").

If an SQL-89-compliant DBMS were implemented using a static database structure, the database administrator (DBA) would use DDL statements to create a database schema-a map of the database that shows its structure including tables, views, users, and access privileges. The DBA would then submit the database schema to a "builder" utility that creates the database according to the specifications in the schema. Once created (by the "builder" utility), the database objects and security scheme could not be changed. DML statements could add, change, remove, and retrieve data. However, to add a new table or user to the database-which requires the execution of DDL statements-the DBA would have to stop all access to the database, unload all of its data, use the DDL to create a revised schema, submit the new schema to the "builder" utility, and then reload the database data.

Although allowed by the SQL-89 standard, no database product actually used a static database structure. In fact, the later SQL-92 standard includes DROP (TABLE, VIEW, USER, and so on) and ALTER (TABLE, VIEW, USER, and so on) statements that effectively require an SQL-92-compliant database to support dynamic database object definition and modification. However, the concept of using a database schema to create a set of tables, views, and permissions (in effect, a database) is still supported by many DBMS products-despite their ability to create, drop, and alter individual database objects on the fly.

The CREATE SCHEMA statement lets you create a conceptual database object that contains the definitions of tables and views and to grant access privileges on those tables and views to DBMS users and roles. The syntax of the CREATE SCHEMA statement is:

CREATE SCHEMA AUTHORIZATION [|| ]

To create a set of tables and views, you can execute a CREATE SCHEMA statement such as:

CREATE SCHEMA AUTHORIZATION frank CREATE VIEW vw_offices AS SELECT offices.office_ID, manager_ID, f_name + l_name manager_name FROM offices, employees WHERE manager_ID = emp_ID CREATE TABLE employees (emp_ID INTEGER, f_name VARCHAR(15), l_name VARCHAR(15), total_sales MONEY, office_ID SMALLINT) CREATE TABLE offices (office_ID SMALLINT, street_address VARCHAR(30), manager_ID INTEGER) GRANT SELECT ON vw_offices TO PUBLIC GRANT ALL PRIVILEGES ON offices TO sally GRANT ALL PRIVILEGES ON employees TO sally

In short, the CREATE SCHEMA does not add any real functionality to the DDL. However, the CREATE SCHEMA statement does give you a single SQL statement that you can use to create one or more tables and views and to grant access permissions to those tables and views.

With the exception of views that are dependant on other views, the objects created in the CREATE SCHEMA statement need not appear in any specific order. As such, you can grant permissions or create a view based on a table created later in the CREATE SCHEMA statement. Moreover, view definitions and foreign keys created in one part of the statement can refer to columns in tables created later in the CREATE SCHEMA statement. However, if a view definition references columns in another view (vs. columns in a table), the view whose columns are being referenced must be created before the view that references its columns.

As is the case with any single SQL statement, the DBMS will roll back (undo) the CREATE SCHEMA statement's work if any part of the statement fails to execute successfully. Therefore, if any one of the CREATE SCHEMA statement's CREATE or GRANT statements fails, the DBMS will not create any of schema's tables or views, nor will the DBMS grant any of the privileges specified in the schema.

Seating Up the NT Server Performance Monitor Log to Help in Optimizing the MS SQL Server

Tip 503, "Understanding the NT Server Performance Monitor Chart View," and Tip 504, "Understanding the NT Server Performance Monitor Report View," showed you how to use Performance Monitor to display real-time object/counter values in chart and tabular form, respectively. If you want to store the values of the counters for one or more Performance Monitor objects so that you can display the counter values and analyze them later, use the Performance Monitor Log View. The Performance Monitor's Log View will write counter values to a disk file at set intervals. As you will learn in Tip 508, "Using the NT Performance Monitor to View a Performance Log File," you can tell the Performance Monitor to retrieve object/counter values from its log files and display them in chart or graphical form.

To have Performance Monitor log the values of the counters for one or more objects for later review, perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Performance Monitor. Windows will start the Performance Monitor in Chart View similar to that you saw in Figure 503.1 in Tip 503.
3. Select the View menu's Log option, or click your mouse pointer on the View output Log file status button (third button from the left) on the Standard toolbar.
4. To add objects (sets of counters) whose values you want recorded in a Performance Monitor log file, select the Edit menu's Add to Log option, or click your mouse pointer on the Add counter button (the button with the plus [+] on the Standard toolbar). Performance Monitor will display an Add to Log dialog box similar to that shown in Figure 507.1.

   Figure 507.1: The Windows NT Performance Monitor Add to Log dialog box

5. Enter the network path of the NT Server on which the SQL Server you wish to monitor is running into the Computer field. (If you do not know the server's path, click your mouse pointer on the Select button to the right of the Computer field so that you can select the computer you want from the list of servers in a Select Computer dialog box.)
6. Click your mouse pointer on the category of counters you want to log in the list of objects in the Objects section of the Add to Log dialog box. For example, if you plan to generate a chart of page reads/sec values (as you did for the project in Tip 503), select the Buffer Manager object, which includes the page reads/sec counter.
7. Click your mouse pointer on the Add button, to add the object you selected in Step 6 to the list of objects whose sets of counter values Performance Monitor will write its log file.
8. Repeat Steps 6 and 7 until you've selected all of the objects whose counter values you want to review later. Then click your mouse pointer on the Done button. Performance Monitor will close the Add to Log dialog box and return to its Log View. (If you selected any object you no longer want, click your mouse pointer on it and then press the Delete key on your keyboard.)
9. Select the Options menu's Log option. Performance Monitor will display a Log Options dialog box similar to that shown in Figure 507.2.

   Figure 507.2: The Windows NT Performance Monitor Log Options dialog box

10. Enter the pathname of the file into which you want Performance Monitor to store the objects' counter values into the File Name field.
11. In the Periodic Update field (near the bottom-left corner of the dialog box), enter how often, in seconds, you want Performance Monitor to record values in its log file.
12. Click your mouse pointer on the Start Log button.

When you complete Step 12, Performance Monitor will close the Log Options dialog box and begin writing the object/counter values (which you selected in Steps 6-8) into the log file (which you specified in Step 10), at the interval you entered in Step 11. Click your mouse pointer on the dash (-) (third button from the right in the upper-right corner of the Performance Monitor application window), and shrink the application to an icon on the task bar at the bottom of your computer screen.

After Performance Monitor (running as a background task on your computer) has collected data for a desired time period, click you mouse pointer on its icon on the task bar at the bottom of your screen to display its application window. Next, select the Options menu's Log option to again display the Log Options dialog box. To stop the application from logging counter values, click your mouse pointer on the Stop Log button (in the lower-right corner of the dialog box).

You will learn how to use Performance Monitor to display the contents of its log files in Tip 508.

Using the NT Performance Monitor to View a Performance Log File

After you create Performance Monitor log files (by performing the steps in the procedure in Tip 507, "Setting Up the NT Server Performance Monitor Log to Help in Optimizing the MS-SQL Server"), you can use Performance Monitor to display the contents of the log files in chart (graphical) or tabular form. The process is very simple. Instead of having Performance Monitor retrieve the counter values it is to display directly from the NT Server hosting the MS-SQL Server, you tell the program to use the counter values in the log file (as if they were coming directly from the server).

For example, to use Performance Monitor to display previously logged counter values in chart form, perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Performance Monitor. Windows will start Performance Monitor in Chart View similar to that you saw in Figure 503.1 (see Tip 503, "Understanding the NT Server Performance Monitor Chart View").
3. Select the View menu's Chart option.
4. Select the Options menu's Data From option. Performance Monitor will display a Data From dialog box similar to that shown in Figure 508.1.

   Figure 508.1: The Windows NT Performance Monitor Data From dialog box

5. To have Performance Monitor collect and display counter values from a log file, click your mouse pointer on the Log File radio button, and then enter the pathname of the log file in the field at the bottom of the Data From dialog box. (If you click your mouse pointer on the Current Activity radio button, Performance Monitor will collect the counter values it is to display directly from the server.)
6. Click your mouse pointer on the OK button.
7. To select objects/counter values you want to display in Chart View, select the Edit menu's Add to Chart option, or click your mouse pointer on the Add counter button (the button with the plus [+]) on the Standard toolbar. Performance Monitor will display an Add to Chart dialog box similar to the one you saw in Figure 503.2, in Tip 503.
8. Use the Select button (labeled with the three dots [...]) to the right of the Computer field, and then select the NT Server whose data values you want to display. (Only servers with data in the log file that you selected in Steps 5 and 6 will appear in the Search button's drop-down list of servers.)
9. To select the object whose logged counter values you want to display, click your mouse pointer on the drop-down list button to the right of the Object field, and then select one of the objects from the drop-down list. Only those objects whose counter values were written to the current log file will appear on the drop-down list of objects you can select.
10. Click your mouse pointer on the scroll bar to the right of the Counter list box to find the specific counter value that you want to display for the object you selected in Step 9.
11. Click your mouse pointer on the Add button to add the counter you selected in Step 10 for the object you selected in Step 9 to the Performance Monitor Chart View display.
12. Repeat Steps 8 through 11 for each Object/Counter you want to add to the Performance Monitor chart. Each time you add a new object/counter, Performance Monitor will automatically change the line color that it will use to draw the selected counter's statistics on the chart.
13. To exit the Add to Chart dialog box and return to the Performance Monitor Chart View, click your mouse pointer on the Done button.

After you complete Step 13, Performance Monitor will display the entire contents of the log file on a single chart. To view, "zoom in" on the values logged during a specific period of time, select the Edit menu's Time Window, and specify the time period of interest on the Input Log File Timeframe dialog box.

Configuring the Windows NT Application Event Log

In Tip 505, "Understanding the NT Server Performance Monitor Alert View," you learned how to use Performance Monitor to alert you to potential problems with the MS-SQL Server's operation by displaying a message in Alert View whenever object/counter values either exceeded or fell below certain limits. The Performance Monitor, however, is not the only tool you can use to monitor the health of your MS-SQL Server. Windows NT includes an integrated logging tool that maintains a log of application, security, and system operations called events. By reviewing the application log events generated by your MS-SQL Server, you can learn how often the SQL Server/Agent was stopped and started, see how often it encounters file (table, log, and index) errors, and review warnings and error messages generated by various other database operations such as transaction rollbacks and roll-forwards executed when the SQL server is restarted after an abnormal shutdown.

Windows NT enables the event-logging service automatically when you start the server. To avoid losing event notifications due to a full event log, you should use the Event Viewer to configure the event logs after you install the MS-SQL Server on the server. To use the Event Viewer to set the maximum log file size in kilobytes, the length of time events remain in the log, and whether or not the event logging service will overwrite events if the log file is full, perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Administrative Tools (Common) option, and click your mouse pointer on Event Viewer.
3. Select the Log menu's Log Settings option. The Event Viewer will display an Event Log Settings dialog box similar to that shown in Figure 509.1.

   Figure 509.1: The Windows NT Event Viewer Event Log settings

4. Click your mouse pointer on the drop-down list button to the right of the Change Setting For field (near the top of the dialog box), and select Application from the drop-down list.
5. Enter the maximum size for the event log into the Maximum Log Size field. If you increase the maximum size of the log file beyond the 512Kb default, you will be able to keep more events in the log file for a longer time in order to see a trend among the events reported by the MS-SQL Server.
6. Choose the action you want the event-logging service to take if the log file gets full. To avoid losing new events, click your mouse pointer on the Overwrite Events as Needed radio button. By doing so, the event-logging service will overwrite the oldest events as new events are added to a full log. (Selecting either of the other two settings could cause the loss of new event notifications.)
7. Click your mouse pointer on the OK button to save your configuration changes and return to the Event Viewer application window.

While you are still in the Event View, you should go ahead and set the configuration settings for the system and security logs as well. To do so, start at Step 3 of the preceding procedure and select System in Step 4. Then, after completing Steps 5 and 6, repeat the procedure again, starting at Step 3, and select Security at Step 4 the third time through.

Displaying Windows NT Application Event Details and Clearing the Application Event Log

The Event Viewer lets you view application (system and security) log information recorded by the Windows NT server's event-logging service. As shown in Figure 510.1, each line in an event log shows the date and time the event occurred, the source of the event, and the event's category, ID number, and username (if applicable) of the user that launched the transaction.

Figure 510.1: A Windows NT Event Viewer application event log

To display more detailed information on an event, double-click your mouse pointer on the event (or click your mouse pointer on the event, and then select the View menu's Detail option). The Event Viewer will display an Event Detail message box similar to that shown in Figure 510.2.

Figure 510.2: The Windows NT Event Viewer Event Detail message box

After you invoke the Event Detail dialog box to display the detail for one event, you can click your mouse pointer on its Next button to display the next event (toward the bottom of the event list) or on the Previous button to display event detail for the next event toward the top of the list. When you are finished viewing event detail, click your mouse pointer on the Close button to close the Event Detail dialog box and return to the Event View application window.

If you noticed a trend in the application log's event messages, made a correction to the application's settings or hardware, and now want to monitor the server for the reoccurrence of the trend, you will want start with an empty event log. To clear all events from a log, select the Log menu's Clear All Events option. The Event Monitor will display a Clear Event Log confirmation textbox. Click your mouse pointer on the Yes button if you want to write the event log to a disk file before clearing it, click on the No button to clear the event log before saving its contents to another file on disk, or click on the Cancel button to leave the event log unchanged.

Using the MS SQL Server Service Manager to Start the MS SQL Server

There are several ways you can start the MS-SQL Server on a Windows NT Server. When you install the MS-SQL Server (as you will learn to do in Tip 527, "Installing the MS-SQL Server"), you can select an option on one of the setup screens to have Windows NT start the MS-SQL Server as a service automatically whenever you boot the operating system. Conversely, you can choose to start the MS-SQL Server manually with the MS-SQL Server Service Manager or by entering a command at the command prompt in the MS-SQL Server's BINN directory.

To start the MS-SQL Server using the MS-SQL Server Service Manager, perform the following steps:

1. Click your mouse pointer on the Start button on the Windows NT Server on which you installed the MS-SQL Server. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu. Select the Microsoft SQL Server program group, and click your mouse on Service Manager. Windows NT will start the MS-SQL Server Service Manager which will display a dialog box similar to that shown in Figure 511.1.

   Figure 511.1: The MS-SQL Server Service Manager dialog box

3. If you have more than one MS-SQL Server installed on your system, click your mouse pointer on the drop-down list button to the right of the Server field to display the list of installed MS-SQL Servers, and then select the MS-SQL Server that you want to start.
4. Click your mouse pointer on the drop-down list button to the right of the Services field to display the list of services that the MS-SQL Service Manager controls, and select SQL Server.
5. To start the MS-SQL Server you selected in Step 3, click your mouse pointer on the Start/Continue button in the lower half of the dialog box. The MS-SQL Server Service Manager will attempt to start the MS-SQL Server. If successful, the Server Service Manager will change the red block on the server icon in the dialog box to a green arrow to indicate a running service, the MS-SQL Server.

   Note

   If you want the Windows NT Server to start the MS-SQL Server (service) automatically each time you boot the operating system, click your mouse pointer on the check box to the left of Auto-start Service When OS Starts until a check mark appears.

6. To exit the MS-SQL Server Service Manager, click your mouse pointer on the close button (the X) in the upper-right corner of the dialog box.

In addition to starting the MS-SQL Server, you can use the MS-SQL Server Service Manager to start four other SQL related services:

• Microsoft Search— Service that builds indexes for and supports advanced queries on character and text based columns
• Microsoft Distributed Transaction Coordinator (MSDTC)— Service that manages transactions to allow applications to combine data from several different data sources (including multiple instances of MS-SQL Server) running on the same or different Windows NT servers
• MSSQLServerOLAPService— Online analytical processing services used to maintain data warehouse data cubes and provide rapid analytical access to data warehouse data
• SQL Server Agent— Application that runs scheduled MS-SQL Server administrative tasks such as backups, index updates, and stored procedures scheduled for execution at specific dates and times

To start any of these MS-SQL Server support services, select the service you want to start instead of SQL Server in Step 4 of the preceding procedure.

Starting the MS SQL Server from a Command Prompt

As mentioned in Tip 511, "Using the MS-SQL Server Service Manager to Start the MS-SQL Server," you can start the MS-SQL Server from the command prompt. To start the MS-SQL Server from a command prompt, perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu, and select Command Prompt.
3. At the command prompt, enter the CD (change directory) command and move to the MS-SQL Server BINN directory. For example, if you are starting MS-SQL Server Version 7 installed to the default directories, you enter CD MSSQL7BINN at the command prompt and then press the Enter key.
4. Enter the command to start the MS-SQL Server as:

   sqlservr - :\master.mdf

   For example, if you are starting MS-SQL Server Version 7 installed to the default directories, with database files in the C:MSSQL7DATA directory, enter sqlserver -master c:mssql7datamaster.mdf at the command prompt and then press the Enter key.

5. Type EXIT at the command prompt and then press the Enter key to return to the Windows desktop.

If you used something other than MASTER for the name of the master database when you installed the MS-SQL Server, substitute that name for and for the "master" in master.mdf in Step 4.

Using the Windows Control Panel to Enable Automatic MS SQL Server Startup

In Tip 527, "Installing the MS-SQL Server," you will learn how to install the MS-SQL Server on a Windows NT server. As mentioned in Tip 511, "Using the MS-SQL Server Service Manager to Start the MS-SQL Server," the installation process gives you the option of having the operating system (OS) start the MS-SQL Server automatically during the OS boot process. If you select the installation option to force a manual restart of the MS-SQL Server each time you restart the Windows NT server, you can change the "manual" MS-SQL Server startup election later.

As you learned from Tip 511, you can make the election to automatically start the server in the future by clicking a check mark into the Auto-start Services When OS Starts check box on the MS-SQL Server Service Manager dialog box. Alternatively, to tell Windows NT to start the MS-SQL Server automatically as part of the boot process (vs. manually after the Windows NT server is running), through the Windows NT server's Control Panel, perform the following steps:

1. Double-click your mouse pointer on the My Computer icon on the Windows NT server desktop.
2. In the My Computer window, double-click your mouse pointer on Control Panel.
3. In the Control Panel window, double-click your mouse pointer on Services. Windows NT will display the Services dialog box, which shows all services (both running and stopped) defined to the operating system.
4. To select the MS-SQL Server whose startup option you want to change from manual to automatic (or from automatic to manual), click your mouse pointer on its name in the Service list box of the Services dialog box. For example, if you accepted the defaults when you installed MS-SQL Server Version 7, look for the MSSQLServer entry in the Service list box of the Services dialog box. Similarly, if you assigned the name MSSQL2000 to the SQL server during the MS-SQL Server 2000 installation process, click your mouse pointer on the MSSQL$MSSQL2000 entry in the Service list box.
5. Click your mouse pointer on the Startup button. Windows NT will display a Service (startup) dialog box similar to that shown in Figure 513.1.

   Figure 513.1: The Windows NT Control Panel Services window's Service (startup) dialog box

6. To have Windows NT start the MS-SQL Server service automatically at startup, click your mouse pointer on the Automatic radio button in the Startup Type section of the dialog box.
7. Click your mouse pointer on the OK button. Windows NT will close the Service dialog box and change the value in the MS-SQL Server Startup column from manual to automatic.
8. Click your mouse pointer on the Close button to exit the Services dialog box and return to the Control Panel.

In addition to enabling (or disabling) automatic startup of the MS-SQL Server, you can manually start (or stop) the MS-SQL Server on the Control Panel's Services dialog box. If the MS-SQL Server is not running at Step 4 (or after Step 7), you will see nothing in the MS-SQL Server's Status column. To start the MS-SQL Server, click your mouse pointer on the Start button in Step 5 (vs. the Startup button). Windows NT will start the MS-SQL Server and display Started in the Status column of the line for the MS-SQL Server that you selected in Step 4. Conversely, if you want to shut down a running MS-SQL Server, at Step 4 (or after Step 7), click your mouse pointer on the Stop button. Windows NT will stop the MS-SQL Server and remove Started from the Status column of the affected MS-SQL Server's line in the Service list box of the Services dialog box.

Understanding MS SQL Server Client Software

When you run the MS-SQL Server installation program, the application will install both the MS-SQL Server and the tools you need to manage it and to modify and query its data. As such, if the computer on which you installed the MS-SQL Server is also the workstation you are using to work with the SQL server, you need not install any additional client software. However, as is most likely the case, when you are using one computer to manage or work with data on the MS-SQL Server installed on another computer, you need to install some (if not all) of the MS-SQL Server client software shown in Table 514.1 on your workstation.

If you want to install all of the client software, books online (documentation), development tools, and code samples, you will need approximately 180MB of free space on your computer. At a minimum, you will need to install the management tools (which let you manage the MS-SQL Server and work with its data) and the client connectivity tools (which allow the clients to communicate with the server through DBLIB, ODBC, and OLEDB drivers). If you plan to write applications, you will also need the headers and libraries, MDAC SDK's subcomponents of the development tools component, and the subcomponents of the code samples component relevant to your planned development environment.

In Tip 515, "Installing MS-SQL Server Client Software," you will learn how to install the MS-SQL Server client software. For now, the important thing to understand is that the MS-SQL Server client software lets you use any workstation on the network to manage the MS-SQL Server and work with its data—as long as the DBA and the network administrator have given your user ID the required access rights. Although there are still some 16-bit clients available, the latest clients (released with MS-SQL Server 7.0 and above) require that you install and run them on a 32-bit operating system (such as Windows 95, 98, ME, NT, 2000, UNIX, Apple Macintosh, OS/2, and so on).

Installing MS SQL Server Client Software

As mentioned in Tip 514, "Understanding MS-SQL Server Client Software," you will need to install MS-SQL Server client software on a workstation when you want to work with the data or manage the MS-SQL Server installed on another computer on the network. You will find the MS-SQL Server client software on the MS-SQL Server installation CD. Please refer to table 514 in Tip 514 for a complete list of the client applications, documentation, and code samples available for installation.

Before starting the installation process, close all applications running on your workstation (be sure to save any unsaved work first) because you will be prompted to reboot your computer to finish the setup process. Then, to install the MS-SQL Server client software, documentation, or code samples on your workstation, perform the following steps:

1. Insert the MS-SQL Server installation CD in your CD-ROM drive. Windows will start the MS-SQL Server setup program. (If the MS-SQL Server installation does not begin momentarily after you insert the CD, double-click your mouse pointer on CD-ROM drive in the My Computer window.
2. After the MS-SQL Server installation program displays the initial installation screen, click your mouse pointer on SQL Server 2000 Components. The installation program will display the Install Components screen.
3. Click your mouse pointer on Install Database Server. The setup program will check your system hardware and operating system to see if the MS-SQL Server will run on your system. If your system does not meet the requirements to run MS-SQL Server, the installation program will display a warning message in a textbox, indicating that setup will only make the MS-SQL Server client components available for installation, since the MS-SQL Server is not supported for your operating system/hardware setup. If you see the warning message, do not worry; just click your mouse pointer on the OK button.
4. After the installation program displays its Welcome screen, click your mouse pointer on the Next button.
5. On the Computer Name screen, click your mouse pointer on the Local Computer radio button and then on the Next button.
6. On the Installation Selection screen, click your mouse pointer on the Create a New Instance of SQL Server, or Install Client Tools radio button and then click on the Next button. The setup program will display the User Information dialog box.
7. Enter your name into the Name field and your company's name into the Company field, and then click your mouse pointer on the Next button.
8. When the setup program displays the Software License Agreement, read it and, if you agree to its terms, click your mouse pointer on the Yes button near the bottom-right corner of the dialog box.
9. On the Installation Definition screen, click your mouse pointer on the Client Tools Only radio button and then on the Next button. The installation program, will display the Select Components dialog box.
10. To select a component and all of its subcomponents for installation, click your mouse pointer on the checkbox to the left of the component name until a check mark appears. (If you want to install only some of the subcomponents for a component, clear the check box next to the component, and click your mouse pointer on the check box to the left of each subcomponent you want to install until a checkmark appears.)
11. Click your mouse pointer on the Next button (in the lower-right corner of the Select Components dialog box). The Installer will display the Start Copying Files screen.
12. To have the setup program start copying files from the MS-SQL Server CD-ROM onto your workstation, click your mouse pointer on the Next button.

After you complete Step 12, the MS-SQL Server installation program will begin copying the documentation and code sample files, and will install the client software applications that you selected in Step 10. Once it has finished the installation process, the setup program will display the Setup Complete screen. When you see it, click your mouse pointer on the Finish button to restart your computer.

To use an MS-SQL Server client, select the client from those listed in the MS-SQL Server group under Programs on the Windows start menu. If you installed code samples as well, you can find them in folders subordinate to the C:PROGRAM FILESMICROSOFT SQL SERVER folder.

Understanding the MS SQL Server System Catalog

MS-SQL Server uses tables to store both data and metadata. (Metadata consists of the fields and the value of the fields that describe the overall structure of the database and the objects such as tables, views, constraints, indexes, and keys, in it.) While users create and maintain the data tables that model real-world objects or events, the server's installation program creates the tables in the System Catalog and the SQL server maintains the metadata stored in the tables. Thus, the System Catalog consists of a set of tables whose data values describe everything about the database itself. By querying the System Catalogs, then, you can retrieve information such as:

• The number and names of all database tables and views
• The number of columns in a table or view, along with the name, data type, scale, and precision of each column
• The constraints defined on each table in the database
• The indexes and keys created for each table

Tables 516.1-516.4 provide the name, database location, and a brief description for each of tables in the MS-SQL Server system catalog. Although you can query the tables in the System Catalog directly, Microsoft recommends against this practice because the structure of the tables in the System Catalog changes from one version of the MS-SQL Server to another. Therefore, issue SELECT statements against System Catalog tables only if you cannot retrieve the information you need by using an information schema view (discussed in Tips 472-493), metadata function, or system stored procedure (discussed in Tips 606-640).

The following System Catalog tables are stored only in the MS-SQL Server's MASTER database.

The following System Catalog tables are stored in each of the databases managed by MS-SQL Server.

The following System Catalog tables are stored in the MSDB database for use by the SQL Server Agent.

The following System Catalog tables are stored in the MSDB database for use by backup and restore operations.

Understanding SQL Server Backups

If you have not backed up your computer in a while, the last question you want to hear when you call the support department for an application you are running is, "When was your last backup?" While painful when it happens to you at home on your personal computer, being caught without a recent backup for a database used by multiple departments in a company can cause severe repercussions. Having to reenter a huge amount of data can be costly in terms of employee time and salaries, and it also could cost the company business as customers unable to place orders or access accurate account information take their business elsewhere.

Fortunately, all commercially available SQL servers provide several different methods you can use to protect against (or at least minimize) data loss and downtime due to equipment failure or user/application program error. These techniques range from "hot backups" that duplicate the information in the database on mirrored disks, to manual or scheduled backups that copy both database data files and transaction log files to a named backup device (either a tape drive or a disk drive file) at specific intervals during the day.

When implementing an SQL server backup scheme, keep in mind that you need to back up two different (and equally important) components of the database. First, you need to back up the database data and its objects-that is, you need to back up the physical disk file(s) in which the SQL server stores its tables, views, stored procedures, indexes, defaults, constraints, and so on. Second, you need to back up the transaction log file(s) that contains the "before" and "after" pictures of the database, stored by the SQL server as it executes transactions that change the structure of database objects or the data stored in them.

The most important things to understand when designing a backup strategy are that a backup is a snapshot of the database taken at a specific point in time, and the database's transaction log contains all of the changes made after the snapshot was taken since the last backup. Therefore, if the physical database file (the file with all of the database objects) is that large and you cannot back it up on a daily basis, then, at a minimum, back up the database file once per week and back up the transaction logs daily. In doing so, you can restore the database exactly as it was as on a specific date and time, and then restore and apply the transaction logs from the backup device, one at a time, for each day between the last backup and the day of the system failure that required you to restore the database.

One other consideration to keep in mind is that not all restore operations follow a catastrophic hardware failure. Sometimes a new program or stored procedure installed on the system causes data loss or stores invalid data values. In such instances, the problem may go unnoticed for several days (or weeks). As a result, you may want to restore the database to the way it was just before a user executed the erroneous program or stored procedure for the first time, instead of as it was when the data loss was detected. Therefore, a good backup system keeps a month (or at least two weeks) of backup sets available for restoration. By selecting the appropriate full backup and associated transaction log backups, you can then restore the database as it was at any point during the month (or the past two weeks).

Creating an MS SQL Server Backup (Dump) Device

When you back up a database file or its transaction log file, you copy its contents to a dump device. The dump device can be either a disk file on the local system, a disk file in a folder on a network drive, or a tape device. If you use a disk file as the dump device for the backup operation, you then set up the system-wide non-SQL server backup program to copy the database dump file to tape, to another hard drive, or to whatever location you use to store and manage your system backups.

When creating a backup file for SQL server data, the most important thing to understand is that you must let the SQL server create the backup file if you want the SQL server to be able to restore the data from the backup file later. If you simply rely on your system-wide backup program to back up the SQL server files (along with the other files it backs up), you must shut down the SQL server while the backup is running. (If you run an external backup program without shutting down the SQL server, the database and transaction log files will remain "in use" and the backup file will not be able to save them to the backup device. Moreover, if you use a non-SQL server backup program to create a backup, you cannot restore the backup and apply changes from the transaction log-you will have to use the snapshot of the database as is.)

To set up an MS-SQL Server backup (dump) device, perform the following steps:

1. Start Enterprise Manager by clicking your mouse on the Windows Start button. When Windows displays the Start menu, select Programs, move your mouse pointer to the Microsoft SQL Server program group, and then click your mouse on Enterprise Manager.
2. To display the list of SQL servers, click your mouse pointer on the plus (+) to the left of Microsoft SQL Servers and then on the plus (+) to the left of SQL Server Group.
3. Click your mouse on the plus (+) to the left of the icon for the SQL server for which you want to create the backup device. For example, if you want to create a dump device for a server named MSSQL2000 running on an NT server name NVBIZNET2, click your mouse on the plus (+) to the left of the icon for NVBIZNET2MSSQL2000. Enterprise Manager will display folders containing databases and services available on the MS-SQL Server you selected.
4. Click your mouse on the plus (+) to the left of the Management folder.
5. Right-click your mouse pointer on Backup and select New Backup Device from the popup menu. Enterprise Manager will display a Backup Device Properties-New Device dialog box similar to that shown in Figure 518.1

   Figure 518.1: The MS-SQL Server Enterprise Manager Backup Device Properties-New Device dialog box

6. In the Name field, enter a name for the backup device. For example, if you are creating a dump device on disk so that you can use it to back up the SQLTips database every Monday, enter a file name such as SQLTIPS-MONDAY into the Name field.
7. To back up the database to a tape drive, click the mouse pointer on the Tape Drive Name radio button and enter the name of the tape device in the field to the right of the button. (If you do not know the name of the tape device installed on your system, click the mouse pointer on the drop-down list button to the right of the Tape Drive Name field and select the tape drive you want to use from the drop-down list of available tape drives.) Otherwise, click the mouse pointer on the File Name radio button, and enter the full pathname of the file to which you want MS-SQL Server to copy the database data during the backup operation. For example, to tell MS-SQL Server to create the SQLTIPS-MONDAY.BAK SQL Server backup file in the BACKUP subfolder of the MSSQL2000 folder on the G: drive, enter G:MSSQL2000BACKUPSQLTIPS-MONDAY.BAK in the File Name field.

   Note

   If any of the folders listed in the backup file's path name do not exist, Enterprise Manager will display a warning message box similar to that shown in Figure 518.2 when you click the mouse pointer on the OK button in Step 8. Figure 518.2: The MS-SQL Server Enterprise Manager message warning that folders in the backup device pathname do not exist or are not currently accessible

   Note

   If you receive this error message, verify the path name that you entered. If the path name is correct, click the mouse pointer on the Yes button near the bottom center of the message box. Enterprise Manager will then go ahead and create the backup device. However, before you back up the database to the device (as you will learn to do in the next tip), you must create the folder path that you entered in Step 7. If you do not, the backup will fail and MS-SQL Server will display an error message similar to that shown in Figure 518.3. Figure 518.3: The MS-SQL Server error message displayed if the folders in the backup device is not accessible when attempting to execute an SQL file backup

   Note

   If you receive this error message during the backup process, click the mouse pointer on the OK button at the bottom of the message box, use the Windows Explorer (or DOS command prompt) to create the folder structure you need, and then repeat the backup procedure (in the next tip).

8. Click your mouse pointer on the OK button near the bottom center of the Backup Device Properties-New Device dialog box. Enterprise Manager will create and display an icon along with the name and physical location of the backup device in the right pane of the Enterprise Manager application window.

The important thing to understand now is that you must perform the steps in the preceding procedure to create a named backup (dump) device. To perform the actual SQL server backup operation, you will select the dump device that you learned to create in this tip while performing a manual SQL server backup (as you will learn in the next tip) or scheduling an automated backup (as you will learn to do in Tip 520, "Scheduling Automatic Backups of an MS-SQL Server"). As such, if you plan to make weekly full backups and daily transaction log backups, repeat the preceding procedure and then create a separate backup device for each backup that you plan to perform. For example, you would create the transaction log backup devices SQLTIPS-LOG-TUESDAY, SQLTIPS-LOG-WEDNESDAY, SQLTIPS-LOG-THURSDAY, SQLTIPS-LOG-FRIDAY, SQLTIPS-LOG-SATURDAY, and SQLTIPS-LOG-SUNDAY to be used in conjunction with the SQLTIPS-MONDAY full backup. You would then be able to back up to a different device (backup file) each day of the week.

Executing a Manual Backup of an MS SQL Server Database

After you create the backup (dump) devices needed to support your backups (as you learned to do in Tip 518, "Creating an MS-SQL Server Backup [Dump] Device"), you can use the devices to perform either manual or automatic/scheduled backups. However, because business emergencies, unexpected meetings, and the demands of life in general have a way of making even the most conscientious worker forget to perform a manual backup now and again, use the manual backup process as a "backup on demand." For example, prior to installing a new application or upgrade to an existing one, perform a manual backup so that you can restore the database to the state it was in immediately before problems caused by the new software. To protect database data against accidental loss on an ongoing basis, implement an automated backup scheme (which you will learn about in Tip 520, "Scheduling Automatic Backups of an MS-SQL Server").

To execute a manual backup, perform the following steps:

1. Start the Enterprise Manager by clicking the mouse pointer on the Windows Start button and selecting Programs from the Start menu. Then move your mouse pointer onto the Microsoft SQL Server programs group, and click the mouse pointer on Enterprise Manager.
2. To display the list of SQL Servers under the Enterprise Manager's control, click your mouse pointer on the plus (+) to the left of Microsoft SQL Servers and then on the plus (+) to the left of SQL Server Group.
3. Click the mouse pointer on the icon for the SQL server with the database (or transaction log) that you want to back up. For example, if you want to back up a database on an SQL server named MSSQL2000 running on an NT Server named NVBIZNET2, click your mouse pointer on the icon to the left of NVBIZNET2MSSQL2000. The Enterprise Manager will use its right pane to display icons for folders that contain databases and services available on the MS-SQL Server you selected.
4. Select the Tools menu Backup Database option. The Enterprise Manager will display an SQL Server Backup dialog box similar to that shown in Figure 519.1.

   Figure 519.1: The MS-SQL Server Enterprise Manager SQL Server Backup dialog box

5. Click your mouse pointer on the drop-down list button to the right of the Database field, and select the database that you want to back up from the drop-down list. The Enterprise Manager will enter the name of the database you selected, followed by the word Backup, into the Name field. For example, if you select SQLTips from the drop-down list box below the Database field, the Enterprise Manager will enter SQLTips Backup into the Name field.
6. If you do not like the system-generated name for the backup setup, enter a new name into the Name field.
7. In the Description field, enter a description of the backup. For example, if you plan to perform a complete database backup, you might enter Complete Backup into the Description field. Conversely, if you are planning to back up only the database transaction log, enter a description such as Transaction Log Backup.
8. Click the mouse pointer on the radio button for the type of backup you want to perform. The backup options are:
   • Database-complete: To back up the entire database file and enough of the transaction log file to produce a consistent database if you were to restore the database from the complete backup
   • Database-differential: To back up only the parts of the database that have changed since the last complete backup and enough of the transaction log file to produce a consistent database if you were to restore the database using the differential backup
   • Transaction log: To back up only the transaction log
   • File and file group: To use the Browse button to the right of the File and Filegroup field to select the logical filegroup(s) or physical database file(s) that you want to back up

9. Use the Add and Remove buttons to the right of the Backup To list box to select the backup (dump) device that you want use. For example, if the Backup To list box contains undesired backup device(s), click the mouse pointer on the device(s) and then on the Remove button. To select the backup device that you want to use, click the mouse pointer on the Add button. The Enterprise Manager will display a Select Backup Destination dialog box similar to that shown in Figure 519.2.

   Figure 519.2: The MS-SQL Server Enterprise Manager Select Backup Destination dialog box

   Either click the mouse pointer on the File Name radio button and enter that pathname of the disk file to which you want the Enterprise Manager to back up the database, or click your mouse pointer on the Backup Device radio button and select a backup (dump) device that you defined previously.

   Then, click your mouse pointer on the OK button near the bottom-right corner of the Select Backup Destination dialog box.

10. If you want to keep the current contents on the backup device intact and add the new backup data to it, click the mouse pointer on the Append to Media radio button. Conversely, if you want to overwrite the existing contents on the backup device (if any) with the new backup, click the mouse pointer on the Overwrite Existing Media radio button.

    Note

    Only use the "Append to media" option if you are performing a multiple database backup during a single backup session. For example, if you start the backup session with a backup of the SQLTips database, select the "Overwrite existing media" option. If you then decide to backup the MASTER database and the MSDB database to the same backup device, select the "Append to media" option when you perform the manual backup procedure for the second and third time.

11. When you are satisfied with the backup specifications on the General tab, click the mouse pointer on the OK button near the bottom center of the SQL Server Backup dialog box to start the backup process.

After you complete Step 11, the Enterprise Manager will display a Backup Progress message box with a status bar that shows you how close the backup process is to completion. When the Enterprise Manager completes the backup process, it will display a "Backup operation completed successfully" message box. Click the mouse pointer on the OK button at the bottom center of the message box to return to the Enterprise Manager application window.

A manual backup provides a convenient way to see how the MS-SQL Server backup process works, how long it takes to back up a particular database (or transaction log), and the impact on performance of executing a backup while users are logged in and using the database. For a greater degree of protection from accidental data loss (because someone forgot to run the backup the day before you need it for a database restore), set up a schedule of automatic backups, as you will learn to do in the next tip.

Scheduling Automatic Backups of an MS SQL Server

As mentioned in Tip 519, "Executing a Manual Backup of an MS-SQL Server Database," scheduled/automatic backups are your strongest defense against data loss due to equipment failure or the actions of errant applications or stored procedures. While the manual backup process is simple to execute, it is too often forgotten or put off "until tomorrow" because hardware has become so reliable. However, you need only experience the loss of several months worth of data one time to understand the importance of implementing a comprehensive and reliable database backup plan.

Fortunately, MS-SQL Server provides an easy way to make database backups occur automatically. After you schedule the backup jobs that you want done on a daily basis, you can rely on the server not to forget to execute a backup due to an upheaval in its "normal" workday.

Before you create the schedule of automatic backups, perform the procedure that you learned in Tip 518, "Creating an MS-SQL Server Backup [Dump] Device," as many times as necessary to create a different backup (dump) device for each of the backups that you want to schedule. If you plan to perform backups to tape, you need only a single backup device, since all backups will use to the same tape drive. (You can have the backup program vary the backup set's name to indicate the day of the week on which the MS-SQL Server Agent started it.) If you plan to perform backups to disk, however, you will want to use a different disk file for each backup session. As such, create at least seven backup (dump) devices similar to those shown in the right pane of the Enterprise Manager application window in Figure 520.1.

Figure 520.1: The MS-SQL Server Enterprise Manager application window with seven (daily) backup devices shown in its right pane

To create a queue of scheduled/automatic backups, perform the following steps:

1. Perform Steps 1-10 of the procedure for performing a manual backup (as detailed in Tip 519, "Executing a Manual Backup of an MS-SQL Server Database").
2. Click your mouse pointer on the Schedule check box in the Schedule section at the bottom of the SQL Server Backup dialog box until a check mark appears. Then click the mouse pointer on the Browse button to the right of the check box to display an Edit Schedule dialog box similar to that shown in Figure 520.2.

   Figure 520.2: The MS-SQL Server Enterprise Manager Edit Schedule dialog box

3. Into the Name field at the top of the Edit Schedule dialog box, enter a descriptive name for the backup job. For example, if you selected the SQLTIPS-MONDAY backup device (in Step 9 of the procedure in Tip 519, "Executing a Manual Backup of an MS-SQL Server Database") and are scheduling a complete backup of the SQLTips database and transaction log files to start every Tuesday morning at 12:00 a.m., enter Monday SQLTips Full Backup to Disk into the Name field. Similarly, if you selected SQLTIPS-LOG-TUESDAY as the backup device (in Step 9 of the procedure in Tip 519) and are scheduling a backup of the SQLTips transaction log file(s) to start every Wednesday morning at 12:00 a.m., you might enter Tuesday SQLTips Transaction Log Backup to Disk into the Name field.
4. Click your mouse pointer on the Recurring radio button and then on the Change button at the lower-right corner of the Schedule Type area of the dialog box. The Enterprise Manager will display the Edit Recurring Job Schedule dialog box shown in Figure 520.3.

   Figure 520.3: The MS-SQL Server Enterprise Manager Edit Recurring Job Schedule dialog box

5. To have the MS-SQL Server Agent execute the backup job each week, click your mouse pointer on the Weekly radio button and enter 1 into the Every Week(s) field (in the Weekly section at the top of the dialog box and to the right of the Occurs section).
6. Click the mouse pointer on the day of week that you want the backup to occur. For example, to schedule a backup to occur at 12:00 a.m. on Sunday each week, click your mouse pointer on the Sun check box until a check mark appears.
7. Click your mouse pointer on the OK button near the bottom center of the Edit Recurring Job Schedule dialog box to save the recurring schedule settings you entered and return to the Edit Schedule dialog box.
8. Click your mouse pointer on the OK button near the bottom center of the Edit Schedule dialog box to return to the SQL Server Backup dialog box.
9. Click your mouse pointer on the OK button near the bottom center of the SQL Server Backup dialog box to schedule the automatic backup by adding it to the MS-SQL Server Agent's Jobs queue and return to the Enterprise Manager's application window.

Repeat Steps 1-9 six additional times to create weekly backup jobs for each of the remaining days of the week. Each time you perform the steps in the procedure, select a different day's backup (dump) device (in Step 9 of the procedure in Tip 519) and enter an appropriate description of the backup performed into the Name field in Step 3. Be sure to click the mouse pointer on the check box next to the correct day of the week in Step 6.

Understanding How to Restore an MS SQL Server Database

No one looks forward to a hardware or software failure. However, failures are bound to occur. Fortunately, having designed and implemented a comprehensive backup plan, you will be able to restore your SQL data and get the database back online in the shortest time possible.

Typically, restoring an MS-SQL Server to full functionality involves the following steps:

1. In case of a hard drive failure, you may have to reinstall the MS-SQL Server itself. (If you do have to install the MS-SQL Server again, you do not have to re-create its database(s)-let MS-SQL Server's Restore Database process do it for you.)
2. If it is damaged or lost, restore the Master database, which contains information about all of the other databases and database objects available on MS-SQL Server at the time of the backup.
3. If it is damaged or lost, restore the MSDB database, which contains information about alerts, backups, tasks, and the database replication scheme (if any).
4. For each database lost or damaged, restore the last complete (full) backup.
5. For each database restored in Step 4, restore the transaction log backups made since the last full backup, in sequential order.

Prior to starting a database restore operation, you must have a functional MS-SQL Server installed and running on your fileserver. Therefore, execute the MS-SQL Server installation procedure (detailed in Tip 527, "Installing the MS-SQL Server"), if necessary.

Next, make sure that the most recent complete (full) database backup file and any subsequent transaction log backup files are available. If you used a disk-based backup scheme to back up the MS-SQL Server to disk, restore the server's backup files from the network-wide backup system's disks as necessary. Conversely, if you used a tape-based backup scheme for MS-SQL Server backups, make sure that the tape device is available, and retrieve the MS-SQL Server backup tapes you need from storage.

After MS-SQL Server is up and running and you have the full database backup file and transaction log backup file(s) that you want to restore either on disk or on tape, perform the following steps to restore a database from a complete (full) backup file:

1. Start the Enterprise Manager by clicking the mouse pointer on the Windows Start button and selecting Programs from the Start menu. Then move your mouse pointer onto the Microsoft SQL Server programs group and click the mouse pointer on Enterprise Manager.
2. To display the list of SQL servers, click your mouse pointer on the plus (+) to the left of Microsoft SQL Servers and then on the plus (+) to the left of SQL Server Group.
3. Click the mouse pointer on the icon for the SQL server whose database(s) you want to restore. For example, if you want to restore the COMPANY_DB database on an SQL server named MSSQL2000 running on an NT server named NVBIZNET2, click your mouse pointer on the icon to the left of NVBIZNET2MSSQL2000 in the Enterprise Manager's left pane. The Enterprise Manager will use its right pane to display icons of folders that contain a list of the databases and services available on the MS-SQL Server you selected.
4. Select the Tools menu Restore Database option. The Enterprise Manager will display a Restore Database dialog box similar to that shown in Figure 521.1.

   Figure 521.1: The MS-SQL Server Enterprise Manager Restore Database dialog box's General tab

5. Into the Restore As database: field, enter the logical name of the database you want to restore. Alternatively, if the database that you are planning to restore is already present on the MS-SQL Server, click the mouse pointer on the drop-down list button to the right of the Restore as Database field, and select the name of the database from the drop-down list.

   Note

   If you are restoring all of the databases on an MS-SQL Server, make sure that you restore the MASTER database first. Next, repeat the steps in the current full backup restore procedure to restore the MSDB database. Then install any user-created databases. Be careful that you do not restore a user-created database such as SQLTips or COMPANY_DB as the MASTER database by accepting the default database name in the Restore to Database field.

6. Click the mouse pointer on the From Device radio button in the Restore section of the dialog box. The Enterprise Manager will display the Restore from Device options in the Parameters section of the dialog box, similar to that shown in Figure 521.2.

   Figure 521.2: The MS-SQL Server Enterprise Manager Restore Database dialog box's General tab, with Restore from Device options displayed

7. Click the mouse pointer on the Select Devices button to the right of the Device scroll box in the middle of the dialog box. The Enterprise Manager will display a Choose Restore Devices dialog box similar to that shown in Figure 521.3.

   Figure 521.3: The MS-SQL Server Enterprise Manager Choose Restore Devices dialog box

8. Click your mouse pointer on the Disk radio button and then on the Add button in the Restore from Set section in the middle of the dialog box. The Enterprise Manager will display a Choose Restore Destination dialog box similar to that shown in Figure 521.4.

   Figure 521.4: The MS-SQL Server Enterprise Manager Choose Restore Destination dialog box

9. Click your mouse pointer on the File Name radio button. Then enter the full pathname of the MS-SQL Server backup file into the File Name field. Alternatively, use the Browse button to the right of the File Name field to navigate to the folder with the backup file and then double-click the mouse pointer on the backup file's name.
10. Click the mouse pointer on the OK button near the bottom-right corner of the Choose Restore Destination dialog box to return to the Choose Restore Devices dialog box.
11. If the backup that you want to restore is stored in multiple files, repeat Steps 8-10 as necessary.
12. Click your mouse pointer on the OK button near the bottom-right side of the Choose Restore Devices dialog box to return to the Restore Database dialog box.
13. Click your mouse pointer on the Restore Backup Set radio button and then on the Database-Complete radio button in the lower half of the dialog box.
14. Click your mouse pointer on the Options tab to expose the fields shown in Figure 521.5.

    Figure 521.5: The MS-SQL Server Enterprise Manager Restore Database dialog box's Options tab

15. Review the file locations to which the restore process will write the physical database base and transaction log files in the Restore Database Files As scroll box in the center of the Options tab. By default, the Enterprise Manager will restore the backup file's contents to the same folders they were in when the Enterprise Manager executed the complete (full) database backup procedure. If you need to change the physical location of any of the files in the database, click your mouse pointer on the file's current location in the Move to Physical File Name column of the scroll box, and replace the existing file location with the one you want to use.
16. If you have transaction log files that you need to restore after you restore the full backup file, click your mouse pointer on the Leave Database Nonoperational but Able to Restore Additional Transaction Logs radio button. This will prevent users from logging into the database before you have finished restoring it from the full backup file and the transaction log file backup file(s) made subsequent to the full backup.
17. Click your mouse pointer on the OK button near the bottom center of the Restore Database dialog box. The Enterprise Manager will start the complete database backup file restoration process and display a Restore Progress message box shown in Figure 521.6 to keep you informed as to the status of the restore operation.

    Figure 521.6: The MS-SQL Server Enterprise Manager Restore Progress message box

When the Enterprise manager is finished restoring the database from the backup file, the program will display a "Restore Completed Successfully" message box. When you see the message box, click the mouse pointer on its OK button.

After you restore the database from the complete (full) backup file, you need to apply the changes to the database stored in the transaction log backup files created subsequent to the full database backup file. To restore and apply changes from a transaction log backup file, perform the following steps:

1. Click the mouse pointer on the icon for the SQL server with the database whose transaction log file backup(s) you want to restore. For example, if you want to restore transaction log backup files for the COMPANY_DB database on an SQL server named MSSQL2000 running on an NT server named NVBIZNET2, click your mouse pointer on the icon to the left of NVBIZNET2MSSQL2000 in the Enterprise Manager's left pane. The Enterprise Manager will use its right pane to display folders containing databases and services available on the MS-SQL Server that you selected.
2. Select the Tools menu's Restore Database option. The Enterprise Manager will display a Restore Database dialog box similar to that shown (previously) in Figure 521.1.
3. Click the mouse pointer on the drop-down list button to the right of the Restore as Database field, and select the name of the database whose transaction log you want to restore from drop-down list.
4. Click the mouse pointer on the From Device radio button in the Restore section of the dialog box. The Enterprise Manager will display the Restore from Device options in the Parameters section of the dialog box, similar to that shown (previously) in Figure 521.2.
5. Click the mouse pointer on the Select Devices button to the right of the Device scroll box in the middle of the dialog box. The Enterprise Manager will display a Choose Restore Devices dialog box similar to that shown (previously) in Figure 521.3.
6. Click your mouse pointer on the Disk radio button and then on the Add button in the Restore from Set section in the middle of the dialog box. The Enterprise Manager will display a Choose Restore Destination dialog box similar to that shown (previously) in Figure 521.4.
7. Click your mouse pointer on the Backup Device radio button. Then click the mouse pointer on the drop-down list button to the right of the Backup Device field, and select the backup (dump) device that you used to create the transaction log backup file.
8. Click the mouse pointer on the OK button near the bottom-right corner of the Choose Restore Destination dialog box to return to the Choose Restore Devices dialog box.
9. If the transaction log backup that you want to restore is stored in multiple files, repeat Steps 6-8 as necessary.
10. Click your mouse pointer on the OK button near the bottom-right side of the Choose Restore Devices dialog box to return to the Restore Database dialog box.
11. Click your mouse pointer on the Restore Backup Set radio button and then on the Transaction Log radio button in the lower half of the dialog box.
12. Click your mouse pointer on the Options tab to expose the fields shown (previously) in Figure 521.5.
13. Look at the contents of the Restore Database Files As scroll box in the center of the Options tab to verify the validity of the file locations in which the restore process will look for the existing physical database and transaction log files that it is to update. By default, the Enterprise Manager will restore the transaction log backup file(s) to the same folder(s) they were in when the Enterprise Manager executed the transaction log backup. If you changed the physical location of the database or transaction log files when you restored the complete database backup file, make the same changes in the Move to Physical File Name column now.
14. If you have additional transaction log files that you need to restore after restoring the current transaction log backup file, click the mouse pointer on the Leave Database Nonoperational but Able to Restore Additional Transaction Logs radio button.
15. Click your mouse pointer on the OK button near the bottom center of the Restore Database dialog box. The Enterprise Manager will start the transaction log backup file restoration process and display a Restore Progress message box shown (previously) in Figure 521.6 to keep you informed as to the status of the restore operation.

After the Enterprise Manager has finished restoring the transaction log from the backup file, it will display a "Restore completed successfully" message box. When you see the message box, click the mouse pointer on its OK button.

In addition to restoring the physical transaction log file, the restore process updates the database by executing the transactions stored in the transaction log file. As such, when you finish the transaction log backup file-restoration process, the database will look as it did when MS-SQL Server performed the transaction log backup.

Repeat the transaction log restore procedure as often as necessary in order to restore all of the transaction log backup files created subsequent to the full database backup. When you perform the steps in the procedure to restore the final transaction log backup file, be sure to select the Leave Database Operational, No Additional Transaction Logs Can Be Restored radio button in Step 14.

Understanding MS SQL Server Optimizer Hints

When you submit an SQL statement to the MS-SQL Server for execution, the database management system (DBMS) sends the statement to its query optimizer. The query optimizer analyzes the statement and generates the execution plan (i.e., the sequence of steps) the DBMS will perform in order to execute the statement. By adding one or more optimizer hints to an SQL statement, you can change the data retrieval methods and locking mechanisms the query optimizer will include in the statement's execution plan. In short, when you add optimizer hints to a statement, you are doing a portion of the query optimizer's job by deciding the most efficient way to retrieve data from the database or to issue locks in order to prevent deletion and modification anomalies.

For example, without optimizer hints, the query optimizer might decide against using an index when executing a SELECT statement, because the query only returns a small number of rows. It would be faster to retrieve the data only from the table (vs. the table and the index). Similarly, if a table has more than one index and the query optimizer decides it should use one of them when retrieving data from the table, the optimizer will select the index that it thinks will result in the fastest retrieval of data to satisfy the query. In both cases, you could add an optimizer hint to the query to force the query optimizer to use a particular index in its execution plan, thereby return a set of rows sorted in ascending order by the columns in the index you select.

You can also use optimizer hints to control the system's lock behavior when executing a DELETE, INSERT, SELECT, or UPDATE statement. For example, adding the WITH NOLOCK hint to a SELECT statement tells the DBMS to read data currently locked by other users (including inserted or updated and yet uncommitted data). Similarly, adding the WITH HOLDLOCK optimizer hint tells the DBMS to prevent others from modifying data in rows returned by your SELECT statement until the end of the current transaction.

Table 522.1 lists the optimizer hints available on an MS-SQL Server and a description of each hint.

Use a WITH or OPTION clause to introduce one or more optimizer hints into SQL statements as follows:

DELETE [FROM] WITH () ... or DELETE [FROM]

[] OPTION () INSERT [INTO]

WITH () ... SELECT [] [] [] OPTION () UPDATE

WITH () ... or UPDATE

[] OPTION ()

When choosing optimizer hints in an effort to increase the efficiency of a query, you will find it helpful to review the list of instructions in the execution plan that the query optimizer generates. If you find a costly step such as full table scans, you can create new INDEXES or perhaps use an optimizer hint that directs the optimizer to use an existing index to eliminate the step.

If you execute a query after turning on the MS-SQL Server's SHOWPLAN_TEXT option (which you will learn about in Tip 523), the DBMS will display the steps that the query optimizer generated for the SELECT statement's execution plan. After you look through the plan, you can try executing the same query with different sets of optimizer hints until the query optimizer generates an execution plan more to your liking.

Using the MS SQL Server SHOWPLAN_TEXT Option to Display a Statement s Execution Plan

As mentioned in Tip 522 "Understanding MS-SQL Server Optimizer Hints," when you submit an SQL statement to the MS-SQL Server for execution, the DBMS sends the statement to the query optimizer, which, in turn, generates the statement's execution plan. (An execution plan is the sequence of steps the query optimizer tells the DBMS is to perform in order to execute a statement.)

When you tell the MS-SQL Server to execute a statement, you are normally interested only in the statement's results and not in how the DBMS produced them. For example, when you submit the query

SELECT a.au_lname, a.au_fname, t.title, t.ytd_sales FROM authors a, titles t, titleauthor ta WHERE a.au_id = t.au_ID AND t.title_ID = ta.title_ID

to the DBMS, you want the SQL server to return a list of author names, book titles, and sales figures. However, you really do not care how the DBMS retrieves the information from its tables. As such, the DBMS hides the execution plan from view.

However, if you are trying to improve a statement's performance, you will want to see the steps the DBMS must perform in order to execute it. When optimizing a SELECT statement, for example, you definitely want to know if the DBMS must perform a full-table scan when executing the query. Because reading every row in a large table usually makes executing a query take an unacceptably long time, the DBMS only performs a full-table scan if it cannot use any existing indexes to retrieve the data in the manner requested. Therefore, seeing a full-table scan in the query's execution plan would tell you that it might be possible to create an INDEX that will reduce the query's execution time.

You can use the MS-SQL Server's SHOWPLAN_TEXT option (and the SHOWPLAN_ALL option discussed in Tip 524), to tell the DBMS to show you what it plans to do "behind the scenes" when executing the SQL statements in a statement batch. One important thing to understand is that after you turn on the SHOWPLAN_TEXT option by submitting the SET statement

SET SHOWPLAN_TEXT ON

to the MS-SQL Server for execution, the DBMS will display the execution plan for each of the statements you submit to the DBMS. However, the DBMS will not actually execute any of the statements you submit until you turn off the SHOWPLAN_TEXT option by executing the SET statement

SET SHOWPLAN_TEXT OFF

For example, if you execute the SET statement

SET SHOWPLAN_TEXT ON

and then submit the statement batch shown in the upper pane in Figure 523.1, the DBMS will return a results set with the text of the statements in the batch, followed by the results set shown in the results (lower) pane in Figure 523.1.

Figure 523.1: The MS-SQL Server SQL Query Analyzer results after submitting a query with SHOWPLAN_TEXT set on

When you are finished reviewing execution plans for SQL statements you are trying to optimize, execute the SET statement

SET SHOWPLAN_TEXT OFF

to tell the DBMS to stop displaying execution plans and resume the normal execution of statements you submit to the server.

Understanding the MS SQL Server SHOWPLAN_ALL Option for Displaying Statement Execution Plans and Statistics

To construct a statement's execution plan, the MS-SQL Server's query optimizer uses statistical information that the server stores with database tables and indexes to evaluate alternative ways in which to execute the statement. For example, when you submit a SELECT statement with an ORDER BY clause, the query optimizer will check to see if the database has an index it can use to satisfy the sort requirements of the clause, or if it must add the step of physically sorting the rows it retrieves them from the table(s) involved in the query. In short, the query optimizer attempts to create a "least cost" (i.e., most efficient) execution plan for each SQL statement you submit to the MS-SQL Server for execution.

In Tip 523 "Using the MS-SQL Server SHOWPLAN_TEXT Option to Display a Statement's Execution Plan," you learned how to set the SHOWPLAN_TEXT option to "on" so that the DBMS would show you the execution plan for statements you told it to execute. Setting the MS-SQL Server's SHOWPLAN_ALL option to "on" also tells the DBMS to display statement execution plans rather than actually executing the statements. However, in addition to displaying the steps in an execution plan, the SHOWPLAN_ALL option also provides the data shown in Table 524.1 (as applicable) for each step.

As was the case with the SHOWPLAN_TEXT option, you must execute the SET statement:

SET SHOWPLAN_TEXT OFF

when you are finished reviewing execution plans for SQL statements and want the DBMS to stop displaying execution plans and resume the normal execution of SQL statements you submit.

Using the MS SQL Server SQL Query Analyzer SHOWPLAN Options

In addition to generating a text-based results sets with execution-plan information, the MS-SQL Server SQL Query Analyzer also lets you display execution plans graphically. In Tips 523 and 524, you learned how to use the statements SET SHOWPLAN_TEXT ON and SET SHOWPLAN_ALL ON. These statements are used so the DBMS returns the query optimizer-generated execution plans for SQL statements instead of executing the statements and returning their results sets.

When you submit an SQL statement with SHOWPLAN_TEXT ON or SHOWPLAN_ALL ON to the MS-SQL Server for execution, the DBMS returns an execution plan which lists the steps it would perform if it were to execute the statement. The DBMS returns the execution plan as rows of column values in a table—just like it would return the results set generated by a statement executed by the server. As such, with SHOWPLAN_TEXT or SHOWPLAN_ALL set to on, you can type an SQL statement batch in the SQL Query Analyzer's query (top) pane, select the Query menu Execute option, and review the statement's execution plan in the SQL Query Analyzer's results (bottom) pane.

You can also use the MS-SQL Server's SQL Query Analyzer to display a statement's execution plan graphically by performing the following steps:

1. Start the SQL Query Analyzer by clicking your mouse pointer on the Windows Start button. When Windows displays the Start menu, move your mouse pointer to Programs, select Microsoft SQL Server 2000, then click your mouse pointer on Query Analyzer. Windows, in turn, will start the SQL Query Analyzer, which will display the Connect to SQL Server dialog box.
2. Use the drop-down list button to the right of the SQL Server field at the top of the dialog box to select the SQL Server that you wish to login. Enter your username and password into the Login name and Password fields. Then, click the mouse pointer on the OK button near the bottom center of the dialog box to complete the login process.
3. Use the drop-down list button (sixth button from the right) on the SQL Query Analyzer's Standard toolbar to select the database with which you wish to work. For the current project, select PUBS.
4. Enter the SQL statement whose execution plan you want to see into the query pane at the top of the SQL Query Analyzer's application window. For the current project, enter SELECT * FROM authors ORDER BY au_lname.

   Note

   If you type more than one statement into the query pane, you can generate an execution plan for all of the statements as a "statement batch." Conversely, if you only want to generate the execution plan for one of several statements in the query pane, use the mouse pointer to select the statement whose execution plan you want to see before you perform Step 6.

5. To display the execution plan for the statement (or statement batch) in the SQL Query Analyzer's query pane without executing the statement, select the Query menu Display Estimated Execution Plan option. The SQL Query Analyzer will use its results (bottom) pane to display the execution plan for the statement (or statement batch) in query (top) pane, similar to that shown in Figure 525.1.

   Figure 525.1: The SQL Query Analyzer's graphical display of a statement execution plan

   Note

   If you want the SQL Query Analyzer to submit both the statement (or statement batch) in the query pane to the DBMS for execution and display the execution plan, select the Query menu Show Execution Plan option. Unfortunately, the SQL Query Analyzer will not open a second results pane so you can see query results as well as the execution plan. Therefore, if you enter a SELECT statement into the query pane and select the Query menu Show Execution Plan option, the SQL Query Analyzer will ask the DBMS to execute the query. Keep in mind, however, that you will only see an execution plan (and not the query's results set) onscreen.

Each of the icons displayed in the graphics-execution plan in the results pane represents a step in the plan. When you move your mouse pointer over an icon, the SQL Query Analyzer will display the values for several of the SHOWPLAN_ALL columns (explained in Table 524.1) for that step in the plan, similar to that shown in Figure 525.2.

Figure 525.2: The SQL Query Analyzer's graphical execution plan detail

Similarly, if you move the mouse pointer over an arrow between icons, the SQL Query Analyzer will display the estimated number or rows passed from one step to the next along with the estimated average byte length of each row passed.

Understanding the MS SQL Server 2000 Hardware and Operating System Requirements

Before you install the MS-SQL Server 2000 on your computer, make sure your system meets the minimum hardware requirements for the edition of the MS-SQL Server you are about to install.

At a minimum, to install and run the MS-SQL Server 2000, you need:

• A personal computer (PC) with an Intel Pentium or compatible processor running at 166-Megahertz (or higher).
• 64 megabytes of memory (minimum); 128 megabytes of memory (recommended). You will need a minimum of 32 megabytes to run the Personal Edition or Desktop Engine under any version of Windows other than Windows 2000, which requires at least 64 megabytes of memory.
• 95 to 270 megabytes of available disk space for the core MS-SQL Server software with a typical installation using 250 megabytes. If you are installing the Desktop Engine, you will need 44 megabytes of available disk space.
• 50 megabytes of available disk space for the Analysis Services with a typical installation using 130 megabytes. Installing the Analysis Services is optional.
• 80 megabytes of available disk space for the Microsoft English Query support. Installing the Microsoft English Query support is optional.
• A CD-ROM drive.
• VGA or higher resolution monitor.
• You can run the MS-SQL Server 2000 Enterprise Edition and Standard Edition under Windows NT Server version 4.0 with Service Pack 5 (SP5) or later; Windows NT Server 4.0 Enterprise Edition with Service Pack 5 (SP5) or later; Windows 2000 Server; Windows 2000 Advanced Server; or Windows 2000 Datacenter Server.
• You can run the MS-SQL Server 2000 Evaluation Edition and Developer Edition under the same operating systems as the Enterprise and Standard Editions plus Windows 2000 Professional and Windows NT Workstation 4.0 with Service Pack 5 (SP5) or later.
• You can run the MS-SQL Server 2000 Personal Edition and the Desktop Engine under the same operating systems as the Enterprise and Standard Editions plus Windows 98, Windows ME, Windows 2000 Professional, and Windows NT Workstation 4.0 with Service Pack (SP5) or later.
• To install and use the MS-SQL Server 2000's Internet tools, you will need to install Microsoft Internet Explorer (IE) version 5.0 or later

  Note

  The MS-SQL is available in several editions: Enterprise Edition, Standard Edition, Evaluation Edition, Developer Edition, Personal Edition, and Desktop Engine. Please visit the MS-SQL Server 2000 area on the Microsoft Web site (www.microsoft.com/sql) for detailed product information about each edition. Then you can select the one that best satisfies your needs.

Installing the MS SQL Server

As is the case with almost all Microsoft products, the MS-SQL Server 2000 has an installation wizard that will guide you through the process of installing the core (required) DBMS software and its optional components (such as the Microsoft English Query and Data Analysis Services) on your computer. Before installing the MS-SQL Server, make sure you computer meets the minimum requirements (detailed in Tip 526).

To install the MS-SQL Server, perform the following steps:

1. Insert the MS-SQL Server 2000 installation CD-ROM. Windows starts the installation program, which displays a Welcome screen, similar to that shown in Figure 527.1. If you do not have the CD-ROM AutoPlay enabled on your system, use the Windows explorer to navigate to your CD-ROM drive and double-click the mouse pointer on AUTORUN.EXE.

   Figure 527.1: The MS-SQL Server Installation Welcome screen

2. Click the mouse pointer on SQL Server 2000 Prerequisites. The installation program checks for operating system (OS) patches, external support programs (such as Internet Explorer), and other DLL files you need to install before MS-SQL Server will run successfully on your computer. Follow the installation program's instructions for installing any required software or OS patches. The install program may ask you to reboot the computer before allowing you to continue at Step 3.
3. Click the mouse pointer on Select SQL Server 2000 Components. The installation program will ask you which of three MS-SQL Server components you want to install. Your choices are:
   • Install Database Server— The MS-SQL Server 2000 DBMS.
   • Install Analysis Services— OLAP (online analytical processing) and data mining tools.
   • Install English Query— Tools to develop applications that let users query by posing questions in English instead of putting the questions in the form of SQL statements.

4. Click the mouse pointer on Install Database Server to install the MS-SQL Server 2000 DBMS. The installation program will start the MS-SQL Server 2000 installation wizard, which displays a Welcome screen.
5. Click the mouse pointer on the Next button near the lower right corner of the installation wizard's Welcome screen. The installation wizard displays a Computer Name dialog box similar to that shown in Figure 527.2.

   Figure 527.2: The MS-SQL Server 2000 installation wizard Computer Name dialog box

6. If you want to install the MS-SQL Server 2000 on the local computer, click the mouse pointer on the Local Computer radio button and then on the Next button near the lower right corner of the Computer Name dialog box. Conversely, if you want to install the DBMS on another computer connected to your local area network, click the mouse pointer on the Remote Computer radio button and enter the name of the computer into the field above the radio buttons. Then, click the mouse pointer on the Next button near the lower right corner of the Computer Name dialog box. The installation wizard will display the Installation Selection dialog box shown in Figure 527.3.

   Figure 527.3: The MS-SQL Server 2000 installation wizard Installation Selection dialog box

7. Click the mouse pointer on the Create a new instance of SQL Server, or install Client Tools radio button and then on the Next button near the lower right corner of the dialog box. The installation wizard will display the User Information dialog box.
8. Into the Name field of the User Information dialog box, enter your first and last name. Then, into the Company field, enter the name of the company for whom you are installing the MS-SQL Server. If there is no company name, leave the Company field blank. Next, click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display the MS-SQL Server 2000 Software License Agreement dialog box.
9. After you read through DBMS software license agreement, click the mouse pointer on the Yes button (near the lower right corner of the dialog box) to accept the terms of the agreement and continue with the installation process. The installation wizard will display the Installation Definition dialog box shown in Figure 527.4.

   Figure 527.4: The MS-SQL Server 2000 installation wizard Installation Definition dialog box

10. Click the mouse pointer on the Server and Client Tools radio button and then on the Next button near the lower right corner of the dialog box. The installation wizard will display the Instance Name dialog box shown in Figure 527.5.

    Figure 527.5: The MS-SQL Server 2000 installation wizard Instance Name dialog box

11. You can run multiple instances (copies) of the MS-SQL Server on the same computer. However, each instance of the DBMS running on the computer must have a unique name. If you are installing a single instance of the DBMS on the server, click the mouse pointer on the Default checkbox until a checkmark appears and then on the Next button near the lower right corner of the dialog box. Conversely, if you are installing an additional copy of the DBMS, or if you plan to run more than one instance of the DBMS on the computer, clear the Default checkbox and enter an unique name for the MS-SQL Server into the Instance name field near the bottom of the dialog box. Then, click the mouse pointer on the Next button near the lower right corner of the dialog box. The install wizard will display the Setup Type dialog box shown in Figure 527.6.

    Figure 527.6: The MS-SQL Server 2000 installation wizard Setup Type dialog box

12. To install SQL programming tools and sample source code as part of the MS-SQL Server installation process, click the mouse pointer on the Custom radio button so the installation wizard will prompt you to install the sample code and SQL programming support tools on the next screen. The installation wizard will display the Select Components dialog box.

    Conversely, if you previously installed the SQL tools and code samples on this computer (and therefore do not want to install them again), click the mouse pointer on either the Typical or on the Minimum radio button—depending on the type of installation you want to perform. Then, click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display the Services Accounts screen of Step 14.

13. Scroll to the bottom of the Components scroll box on the left side of the Select Components dialog box and click the mouse pointer on the checkbox next to Code Samples until a checkmark appears. Then click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display the Services Accounts dialog box shown in Figure 527.7.

    Figure 527.7: The MS-SQL Server 2000 installation wizard Services Accounts dialog box

14. To have Windows start the MS-SQL Server and the MS-SQL Server Agent automatically each time you boot the server, click the mouse pointer on the "Use the same account for each service. Auto start SQL Server Service" radio button, and on the "Use a Domain User Account" radio button. Then, into the Username and Password fields, enter the username and password you want the MS-SQL Server to use when logging into the server. Make sure that you enter the username and password of an account with system administrator access. Click your mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display an Authentication Mode dialog box similar to that shown in Figure 527.8.

    Figure 527.8: The MS-SQL Server 2000 installation wizard Authentication Mode dialog box

    Note

    Rather than using the system administrator's username and password, set up a separate username such as "SQLEXEC" (with a password, of course) and assign the user-name to the system administrators group. This will allow for administrator access to the DBMS if the system administrator changes.

    Note

    To explore the other DBMS startup options, click the mouse pointer on the Help button near the bottom center of the dialog box. The dialog box help screen gives examples of several different startup options for the MS-SQL Server. Moreover, the help screen lists the restrictions the operating system imposes on the MS-SQL Server's access rights. Keep in mind that if you choose to have the MS-SQL Server login to the local system account on startup, the MS-SQL Server will be unable to start if the system administrator later changes the password on the account.

15. Click the mouse pointer on the Mixed Mode (Windows Authentication and SQL Server Authentication) radio button and enter a password for the MS-SQL Server sa (system administrator) account into the Enter password field and again into the Confirm password field. Click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display the Collation Settings dialog box.

    Note

    The security mechanism is the same whether you select Windows Authentication Mode or Mixed Mode. However, only Mixed Mode lets you set the sa account's password. Given the importance of not leaving the sa account's password blank, now is as good of a time as any to select a password to protect the account that has all access privileges to all objects in the DBMS.

16. Unless you need to match the collation settings of another instance of the MS-SQL Server installed on this computer, accept the default collation settings and sort order. Click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display a Network Libraries dialog box similar to that shown in Figure 527.9.

    Figure 527.9: The MS-SQL Server 2000 installation wizard Network Libraries dialog box

17. The Network Libraries dialog box allows you to set up the interprocess communication (IPC) mechanism that enables MS-SQL Server client applications running on network workstations communicate with the MS-SQL Server (DBMS) running on the network server. If you are using only Windows networking, accept the dialog box defaults and click the mouse pointer on the Next button near the lower right corner of the dialog box. On the other hand, if you have a multi-protocol network, click the mouse pointer on the checkboxes next to the network libraries you want to setup and enter the information required into the field below the checkboxes you checkmark. (See your network administrator for help with service/object names and address, as necessary.) Click the mouse pointer on the Next button near the lower right corner of the dialog box. The installation wizard will display the Start Copying Files message box.

    Note

    The installation program will install all of the network libraries onto the server. Therefore, if you do not see the network library you need, or if the network administrator is not available to help you with the library setup right now, you can always configure the libraries any time after you install the MS-SQL Server. See MS-SQL Server 2000's Books Online help system for additional details.

18. Click the mouse pointer on the Next button near the lower right corner of the Start Copying Files message box.

After you complete Step 18, the MS-SQL Server 2000 installation wizard will start copying the MS-SQL Server 2000's files onto your computer and setup the MS-SQL Server DBMS software as you specified through the installation wizard's dialog boxes. Once the installation wizard starts copying files, the MS-SQL Server 2000 installation will run to completion without further intervention on your part. The installation program will display a message box and a series of status bars that tell you the name of the file it is copying and how close the program is to completing its work.

When the installation program has finished installing the MS-SQL Server 2000 on your computer, the program will display a Setup Complete dialog box. Click the mouse pointer on the Finish button at bottom of the dialog box to reboot the computer so you can start using the MS-SQL Server. Unless you specified otherwise on the Services Account screen in Step 14, the MS-SQL Server 2000 will login as a system service and startup automatically during the computer's boot process.

Using the MS SQL Server SETUSER Statement to Test a User s Access to Database Objects

When you implement database security, you will give some users access privileges on certain database objects and not on others. To test a user's access rights, you can login to the user's account, or if you are the system administrator (sa) or the database owner (DBO), you can use the SETUSER statement to impersonate any database user.

The syntax of the SETUSER STATEMENT is:

SETUSER [WITH NORESET]

Therefore, while logged in to the sa account you could execute the statement:

SETUSER MARY

for example, and test user MARY's access privileges on various database objects. Moreover, username MARY will own any database objects you create while the DBMS "thinks" you are MARY. As such, the SETUSER statement gives you an easy way to create new database objects and give a specific user all access privileges (as the database object owner (DBOO)) on those objects.

When you want to revert back to being the sa or DBO, execute the SETUSER statement without a username, or execute a USE statement.

If you include the WITH NORESET clause in the SETUSER statement, such as

SETUSER MARY NORESET

to impersonate another user, the DBMS will not change your identity back to the account you originally logged in on (either sa or DBO). If you include the NORESET option when you execute a SETUSER statement, the only way to get your original identity back is to logoff and then login as sa or DBO.

Understanding the MS SQL Server MODEL Database

The MS-SQL Server uses the MODEL database as a template whenever you tell it to create a new database. Any new database the MS-SQL Server creates will have option settings identical to the database options of the MODEL database. Moreover, the new database will contain a copy of all objects that exist in the MODEL database when you execute the CREATE DATABASE statement. Therefore, the MODEL database provides the ideal starting place to create such things as rules, constraints, defaults, and tables that you want to exist in every database you create.

For example, if you want the rule created by

CREATE RULE valid_empnum AS @employee_number BETWEEN 1000 AND 1999

to be available in all databases created in the future, login to the MODEL database using the system administrator (sa) account and create the VALID_EMPNUM rule. MS-SQL Server will create the VALID_EMPNUM rule in each database you create subsequent to adding the rule as an object in the MODEL database. MS-SQL Server will, of course, stop creating the VALID_EMPNUM rule in new databases if you later drop the VALID_EMPNUM object from the MODEL database.