MCSE Microsoft SQL Server 2000 Administration Readiness Review: Exam 70-228 by Irfan Chaudhry, Dean Bartholo

From the Publisher

Test your readiness for the MCP exam.

If you took Microsoft Certified Professional (MCP) Exam 70-228 today, would you pass? With the Readiness Review MCP exam simulation on CD-ROM, you get a low-risk, low-cost way to find out! This next-generation test engine delivers a set of randomly generated, 50-question practice exams covering real MCP objectives. You can test and retest with different question sets each time—and with automated scoring, you get immediate Pass/Fail feedback. More important, you get answers to these four critical questions:

• What do MCP exam questions look like?
• In what topic and skill areas am I proficient/deficient?
• How should I focus my studies?
• Am I ready for the real exam?

• Installing and upgrading
• Creating databases
• Extracting and transforming data
• Optimizing performance
• Managing security
• Troubleshooting system activity

Product Details

• Table of Contents
• Read an Excerpt
• Read a Sample Chapter

Table of Contents

Read an Excerpt

Obective Domain 3: Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases

• Creating a database and ensuring that the database performs well

When creating a database, administrators must take into consideration certain SQL Server configuration settings, such as database and log file location. These settings will ultimately affect the performance of the database.

• Altering the database schema after it has been created

The schema design for the database is possibly the most important stage in planning the deployment of the database. Poor schema design not only affects the performance and scalability of the database, but it also governs how SQL Server interacts with it. Understanding how to successfully alter the schema plays an important role in the day-to-day administration of any database.

• Utilizing built-in tools for administrating SQL Server 2000

To accomplish tasks such as creating, altering, and managing a database, administrators must be aware of every available tool. With a better knowledge of these tools and how they work, administrators can more easily accomplish their tasks.

• Performing database and table integrity checks to avoid future problems with database reliability

Constantly checking on the status of a database's integrity is tantamount to the successful operation of a database. For example, a corrupt index that goes unnoticed will have a direct effect on the performance of the database.

• Executing procedures to ensure quick and reliable data recovery

Understanding the most efficient method of recovering lost or corrupted data allows an administrator to get downed systems up and running quickly.

Tested Skills and Suggested Practices

• Optimizing database performance.
• Practice 1: Using Transact-SQL (T-SQL) commands, configure the Max Worker Threads value to be 128 for the default instance of SQL Server.
• Practice 2: Create a stored procedure that queries data from several tables within the Northwind database. Execute the stored procedure within a loop so that you can monitor it using the SQL Server 2000 Profiler utility. Configure the Profiler to monitor for table scans, locks, and other objects that could hinder the queries' performance.
• Practice 3: Execute the stored procedure you used in Practice 2. Run it through a loop again, but this time monitor SQL Server's performance using Performance Monitor. You should monitor the server for processor and memory utilization along with SQL Server specific performance objects, such as the number of lock requests.

• Optimizing data storage.
• Practice 1: Using Enterprise Manager, create a new filegroup in the Northwind database. Then create a new file (preferably on a separate disk drive), and assign it to the new filegroup.
• Practice 2: Assign an existing table to a new filegroup. Right-click on the Customers table, select Design Table, right-click on any column, and select Properties. From the Tables tab, select Table Filegroup, and select the new filegroup created in Practice 1 from the drop-down list.
• Practice 3: Assign an existing index to a new filegroup. Right-click on the Customers table, select Design Table, right-click on any column, and select Properties. From the Index/Keys tab, select the index box and the index you want to move. In the Index Filegroup, select the new filegroup created in Practice 1.

• Modifying the database schema.
• Practice 1: Using the Northwind database, change the presentation format of the OrderDate column located in the Orders table. Change the date to the European format in which the day comes before the month.
• Practice 2: Alter the table so that it displays the time along with the date. Have the time also show the number of milliseconds.

• Performing disaster recovery operations.
• Practice 1: Set the Northwind database to BULK_LOGGED recovery using the T- SQL command ALTER DATABASE NORTHWIND SET RECOVERY BULK_LOGGED. Using Enterprise Manager, view the database Options tab found under Properties for the Northwind database. Verify that the database is set to the BULK_LOGGED recovery model. Set it to FULL using Enterprise Manager.
• Practice 2: Using Enterprise Manager, back up the Northwind database to a file named C:\NORTHWIND.BAK. Select Overwrite Existing Media. Issue the same command using the T-SQL command BACKUP DATABASE NORTHWIND TO DISK = 'C:\NORTHWIND.BAK' WITH INIT.
• Practice 3: Back up the Northwind database transaction log using the T-SQL command BACKUP LOG NORTHWIND TO DISK = 'C:\NORTHWINDLOG.BAK' WITH INIT.
• Practice 4: Using Enterprise Manager, restore the Northwind database and transaction log from Practices 2 and 3.

• Performing integrity checks.
• Practice 1: Using T-SQL commands, run an integrity check on the Northwind database. Specify the NOINDEX argument when executing the DBCC command.
• Practice 2: Create a scheduled job that will run an integrity check on the Employees table located in the Northwind database.
• Practice 3: Execute the appropriate DBCC statement to monitor fragmentation of data and indexes within the Northwind database.

• Troubleshooting transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or T-SQL.
• Practice 1: Using the Query Analyzer, connect to SQL Server and use the Northwind database to issue SELECT * FROM ORDERS. Use the Current Activity window in Enterprise Manager to find the system process ID (SPID) that issued the command, and review the information tracked.
• Practice 2: Using the Query Analyzer, run sp_who. Find the SPID that issued the command, and review the information tracked. Compare it to the information tracked in Practice 1.
• Practice 3: Using the SQL Profiler, create a new trace. Track Security Audit events of Audit Login and Audit Logout. Add data columns for LoginName and StartTime. Filter where LoginName is Like sa. Start the trace. Use the Query Analyzer to connect and disconnect as user sa, and view the results of the trace.
• Practice 4: Using the Query Analyzer, run sp_lock. Review the types of locks reported.

Further Reading

Objective 3.1

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 14, "Monitoring SQL Server," covers the topic of detecting and resolving performance issues with SQL Server and databases created on the server. Chapter 12, "Performing Administrative Tasks," Lesson 7 also covers configuration parameters that you alter to optimize database performance.

Objective 3.2

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Optimizing Database Performance, then Database Design, then Physical Database Design, and then RAID. This volume covers the use of RAID systems with SQL Server 2000.

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 6, "Creating and Configuring User Databases," Lesson 4 discusses the use of multiple disks and RAID systems.

Objective 3.3

Microsoft Corporation. Microsoft SQL Server 2000 Administrator's Companion. Redmond, Washington: Microsoft Press, 2000. Chapter 15, "Managing Tables Using Transact-SQL and Enterprise Manager," details altering schemas using the Enterprise Manager and using T-SQL.

Objective 3.4

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 8, "Selecting a Backup and Restore Strategy," Lessons 3 and 4 deal with selecting and designing a backup and restore strategy.

Objective 3.5

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand SQL Server Architecture, and then Administration Architecture, and then select "Data Integrity Validation." This article discusses the use of DBCC and its features in SQL Server 2000.

Objective 3.6

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Review Chapter 14, "Monitoring SQL Server Performance and Activity," Lesson 3 for a full discussion of all of the monitoring options available with SQL Server 2000.

3.1 Optimize database performance.

One of the first steps to successfully tune your database is to pinpoint the cause of the performance bottleneck. Is it the server or is it the database design? Running performance tools, such as Windows 2000 Performance Monitor, and monitoring for SQL specific counters is one method you can use to help determine causes for poor database performance. However, unless you know how to interpret this data, you will be unable to diagnose the cause of the problem. For example, you monitor your SQL server and notice high CPU utilization. Without further investigation as well as an understanding of what the server is doing at that time, you do not have a solid path to follow to help diagnose the issue.

Another useful tool for monitoring database performance is the SQL Server 2000 Profiler application. Profiler enables you to record database activity, which you can then analyze with built-in tools, such as the Index Tuning Wizard. Configure the Profiler to record all database and server activity, such as the stored procedures being executed on the server, or specific activities, such as T-SQL transactions. When used in conjunction, Performance Monitor and SQL Server 2000 Profiler can help you quickly pinpoint many SQL Server specific bottlenecks.

One such bottleneck can be the cause of a high number of table scans. Table scans occur when SQL Server searches through all of the rows in a table to find the requested data rather than searching a single row. By searching all of the rows, the server inefficiently uses memory and CPU time. Table scans indicate poorly indexed tables within a database. By creating an index, such as a clustered index, you supply the server with a specified row that it can use to efficiently search for data in the table.

Creating indexes in the appropriate places is part of good database design. Other design features include optimizing queries, denormalizing the database to simplify complex processes, and identifying and optimizing the larger tables within the database. For example, if you are running a report against a large table, you might consider creating a column or columns within that table that will help propagate data, and therefore help to retrieve data faster for that report.

The type of storage system the database resides on can also affect its performance, particularly if the database is large (more than a gigabyte). To help increase the performance of a database, consider placing data files for the database on the fastest drives within the server. Additionally, you should create the data files so they reside on a RAID device. For example, RAID 5 enables SQL Server to read from the drives almost 50 percent faster than if they were on a non-RAID device. RAID devices also have the advantage of offering redundancy when storing data.

SQL Server settings also have a major effect on database performance. Before changing any of these settings, be sure you thoroughly understand what effects they will have, not only on SQL Server, but also on other applications that could be running on the server. For example, some administrators will change the Max Worker Threads value without considering its effects on the server. Max Worker Threads determines the number of worker threads allocated for SQL Server. The more threads you have, the more system resources are required to create and maintain the threads. Because worker threads can be costly, you should only specify the amount of threads that makes sense. By default, the Max Worker Threads value under SQL Server 2000 is set to 255. When determining the number of worker threads required for SQL Server, be aware of the number of concurrent users your server will need to host at any given time. The worker thread value is pooled, so in case the number of connections to the SQL server goes beyond the Max Worker Thread value, SQL Server will begin to share the threads across connections.

There are other performance issues that are not covered in this overview. Refer to the Further Reading section to learn about other sources of information.

MCS

70-228.03.01.001

B

Administrators in the HR department are complaining about the time it takes to retrieve a list of retired employees from the database. After looking into the situation, you do not find any significant problems with the query they are running that could cause a large lag time in returning the results. You run SQL Server Profiler and find that when the query is executed, a larger number of table scans appear in the trace. Which of the following is the best method to reduce query execution time in this case?

A. Increase the amount of memory in the server.

Incorrect

A. Increasing the amount of memory would be a possible resolution if SQL Server were showing signs of increased memory usage during query execution. In this case, there are a large amount of table scans, which indicates a poorly designed database schema.

B. Create or reindex tables in the database.

Correct

B. High table scans are a sign of a poorly indexed table. When SQL Server has to search for data in a table that is not indexed, it must do so by searching through each row of the table; therefore, it ends up scanning the entire table.

C. Increase the number of CPUs in the server.

Incorrect

C. Increasing the number of CPUs may help increase the response time of the server, but it is an inefficient method of resolving the problem.

D. Configure the SQL server to use Windows NT Fibers.

Incorrect

D. Selecting to use Windows NT Fibers enables the Windows 2000 operating system to avoid the transition between the user mode of the application and the kernel mode of the thread. This transition can be a costly operation. By reducing this transition, a server can be more efficient. However, in the case of high table scans, the problem must be addressed at the schema level for the database.

MCM

70-228.03.01.002

A, B, and C

You are creating a SQL Server database on a server configured with a RAID array controller. The server has a total of five 18 GB drives installed.

You want to accomplish the following goals:

• Recover the data even after a single drive failure.
• Allow for growth of the database beyond 18 GB.
• Configure the drives so that SQL Server performs optimally.

You create a RAID 5 disk array and store the database and transaction log files on the logical drive. You specify all five drives to be part of the array.

Which of the following does your solution achieve? (Choose all that apply.)

A. If a drive were to fail, users could continue to access SQL Server data.

Correct

A. Level 5 RAID stripes data and parity information across the set of disk drives making up the drive array. In case a single drive were to fail, information stored on the array would still be accessible because of the parity information written to the drives.

B. Disk drive capacity will not be an issue when the database grows beyond 18 GB.

Correct

B. By creating a disk array, you create a single logical drive. The size of this logical drive is approximately the sum of all of the drives within the array less approximately 20 percent used for overhead when creating the array.

C. Creating a RAID 5 array increases the read performance of the server.

Correct

C. Striping data with parity increases read performance compared to other RAID levels, such as disk mirroring.

D. None of the goals are achieved.

Incorrect

D. All three goals are achieved.

MCS

70-228.03.01.003

B

Your SQL 2000 server has 512 users created in the Logins directory. You have been monitoring the system for usage and have noticed that there are never more than 281 users connected to the server at a single time. Realizing this, you want to ensure that server resources are being allocated correctly. A coworker suggests that you look into changing the Max Worker Thread setting, which is currently set to 512. What value should you change it to?

A. Change it to 128.

Incorrect

A. See the explanation for answer B.

B. Change it to 300.

Correct

B. For each client connection to SQL Server, an operating system thread is created, which can be extremely costly to system resources. To improve overall system performance, SQL Server implements thread pooling: one thread handles each user connection. However, if the number of worker threads is less than the number of user connections, SQL Server pools the worker threads, reducing the number of threads that need to be created. You must specify a Max Worker Thread value that is capable of supporting the expected users to the system. Creating a large thread value forces more system resources to be used.

C. Leave it at 512.

Incorrect

C. See the explanation for answer B.

D. Change it to 256.

Incorrect

D. See the explanation for answer B.

MCS

70-228.03.01.004

A

You recently deployed a Web-based application that stores data on a SQL Server 2000 machine. The application is accessible to users both internal and external to your organization. Even though the site has only been up and running for a week, complaints are coming in regarding the response time of the application, specifically when users must retrieve a list containing available vendors. You gather performance monitoring data for the site and find that the Processor Queue Length for the CPUs on the SQL server are extremely high. What should you do to resolve the problem?

A. Increase the number of processors in the machine.

Correct

A. The Processor Queue Length counter denotes the number of threads waiting for processor time. If the queue is large, you must increase the number of processors in the server, replace the processors with faster ones, optimize the queries, or improve the indexes.

B. Increase the amount of RAM.

Incorrect

B. See the explanation for answer A.

C. Insert a faster SCSI board.

Incorrect

C. See the explanation for answer A.

D. Increase the size of the temp database.

Incorrect

D. See the explanation for answer A.

MCM

70-228.03.01.005

A and B

You have a SQL Server 2000 machine that is extremely slow when accessed. You run some basic Performance Monitor sessions and find that a deadlock is occurring in the Marketing database that's configured on the server.

You want to accomplish the following goals:

• Find the cause of the deadlock that is occurring.
• Monitor the database for excessive table scans.
• Apply the optimal indexes to the database for maximum performance.

You run the SQL Profiler application and save the trace file. While running the Profiler, you configure the Profiler to use the following counters: Stored Procedures/RPC:Starting, TSQL/SQL:BatchStarting, Locks/Lock:Deadlock, and Locks/Lock:Deadlock Chain. After the trace is complete, you run the Index Tuning Wizard and create the indexes it recommends for the database.

Which of the following does your solution achieve? (Choose all that apply.)

A. You can find the cause of the deadlock.

Correct

A. SQL Server Profiler is an excellent tool for monitoring SQL Server activity. Unlike Windows 2000's Performance Monitor, SQL Server Profiler enables you to monitor detailed SQL Server transactions including monitoring for deadlocks and their causes. By specifying the Profiler to monitor for specific counters, you can better determine the cause of the deadlock.

B. Indexes are applied optimally to the database.

Correct

B. The Index Tuning Wizard analyzes the trace file and recommends indexes that should be created.

C. SQL Server Profiler displays table scans that are occurring.

Incorrect

C. You must configure SQL Server Profiler to monitor for table scans.

D. None of the goals are accomplished.

Incorrect

D. Two of the goals are accomplished....

Read a Sample Chapter

• Tested Skills and Suggested Practices
• Further Reading
• Objective 3.1
• Objective 3.2
• Objective 3.3
• Objective 3.4
• Objective 3.5
• Objective 3.6
• 3.1 Optimize database performance.
• 3.2 Optimize data storage.
• 3.3 Modify the database schema.
• 3.4 Perform disaster recovery operations.
• 3.5 Perform integrity checks.
• 3.6 Troubleshoot transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or Transact-SQL.

The Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases domain examines the tasks administrators will face on a day-to-day basis. These tasks include:

• Creating a database and ensuring that the database performs well

When creating a database, administrators must take into consideration certain SQL Server configuration settings, such as database and log file location. These settings will ultimately affect the performance of the database.

• Altering the database schema after it has been created

The schema design for the database is possibly the most important stage in planning the deployment of the database. Poor schema design not only affects the performance and scalability of the database, but it also governs how SQL Server interacts with it. Understanding how to successfully alter the schema plays an important role in the day-to-day administration of any database.

• Utilizing built-in tools for administrating SQL Server 2000

To accomplish tasks such as creating, altering, and managing a database, administrators must be aware of every available tool. With a better knowledge of these tools and how they work, administrators can more easily accomplish their tasks.

• Performing database and table integrity checks to avoid future problems with database reliability

Constantly checking on the status of a database’s integrity is tantamount to the successful operation of a database. For example, a corrupt index that goes unnoticed will have a direct effect on the performance of the database.

• Executing procedures to ensure quick and reliable data recovery

Understanding the most efficient method of recovering lost or corrupted data allows an administrator to get downed systems up and running quickly.

Tested Skills and Suggested Practices

The skills that you need to successfully master the Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases objective domain on the Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition exam include:

• Optimizing database performance.
• Practice 1: Using Transact-SQL (T-SQL) commands, configure the Max Worker Threads value to be 128 for the default instance of SQL Server.
• Practice 2: Create a stored procedure that queries data from several tables within the Northwind database. Execute the stored procedure within a loop so that you can monitor it using the SQL Server 2000 Profiler utility. Configure the Profiler to monitor for table scans, locks, and other objects that could hinder the queries’ performance.
• Practice 3: Execute the stored procedure you used in Practice 2. Run it through a loop again, but this time monitor SQL Server’s performance using Performance Monitor. You should monitor the server for processor and memory utilization along with SQL Server specific performance objects, such as the number of lock requests.

• Optimizing data storage.
• Practice 1: Using Enterprise Manager, create a new filegroup in the Northwind database. Then create a new file (preferably on a separate disk drive), and assign it to the new filegroup.
• Practice 2: Assign an existing table to a new filegroup. Right-click on the Customers table, select Design Table, right-click on any column, and select Properties. From the Tables tab, select Table Filegroup, and select the new filegroup created in Practice 1 from the drop-down list.
• Practice 3: Assign an existing index to a new filegroup. Right-click on the Customers table, select Design Table, right-click on any column, and select Properties. From the Index/Keys tab, select the index box and the index you want to move. In the Index Filegroup, select the new filegroup created in Practice 1.

• Modifying the database schema.
• Practice 1: Using the Northwind database, change the presentation format of the OrderDate column located in the Orders table. Change the date to the European format in which the day comes before the month.
• Practice 2: Alter the table so that it displays the time along with the date. Have the time also show the number of milliseconds.

• Performing disaster recovery operations.
• Practice 1: Set the Northwind database to BULK_LOGGED recovery using the T- SQL command ALTER DATABASE NORTHWIND SET RECOVERY BULK_LOGGED. Using Enterprise Manager, view the database Options tab found under Properties for the Northwind database. Verify that the database is set to the BULK_LOGGED recovery model. Set it to FULL using Enterprise Manager.
• Practice 2: Using Enterprise Manager, back up the Northwind database to a file named C:\NORTHWIND.BAK. Select Overwrite Existing Media. Issue the same command using the T-SQL command BACKUP DATABASE NORTHWIND TO DISK = 'C:\NORTHWIND.BAK' WITH INIT.
• Practice 3: Back up the Northwind database transaction log using the T-SQL command BACKUP LOG NORTHWIND TO DISK = 'C:\NORTHWINDLOG.BAK' WITH INIT.
• Practice 4: Using Enterprise Manager, restore the Northwind database and transaction log from Practices 2 and 3.

• Performing integrity checks.
• Practice 1: Using T-SQL commands, run an integrity check on the Northwind database. Specify the NOINDEX argument when executing the DBCC command.
• Practice 2: Create a scheduled job that will run an integrity check on the Employees table located in the Northwind database.
• Practice 3: Execute the appropriate DBCC statement to monitor fragmentation of data and indexes within the Northwind database.

• Troubleshooting transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or T-SQL.
• Practice 1: Using the Query Analyzer, connect to SQL Server and use the Northwind database to issue SELECT * FROM ORDERS. Use the Current Activity window in Enterprise Manager to find the system process ID (SPID) that issued the command, and review the information tracked.
• Practice 2: Using the Query Analyzer, run sp_who. Find the SPID that issued the command, and review the information tracked. Compare it to the information tracked in Practice 1.
• Practice 3: Using the SQL Profiler, create a new trace. Track Security Audit events of Audit Login and Audit Logout. Add data columns for LoginName and StartTime. Filter where LoginName is Like sa. Start the trace. Use the Query Analyzer to connect and disconnect as user sa, and view the results of the trace.
• Practice 4: Using the Query Analyzer, run sp_lock. Review the types of locks reported.

Further Reading

This section lists supplemental readings by objective. We recommend that you study these sources thoroughly before taking exam 70-228.

Objective 3.1

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab, expand Optimizing Database Performance, and select "Optimizing Database Performance Overview." This article provides links to other topics that cover performance tuning and database performance.

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 14, "Monitoring SQL Server," covers the topic of detecting and resolving performance issues with SQL Server and databases created on the server. Chapter 12, "Performing Administrative Tasks," Lesson 7 also covers configuration parameters that you alter to optimize database performance.

Objective 3.2

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Optimizing Database Performance, then Database Design, then Physical Database Design, and then Data Placement Using Filegroups. This volume contains articles on placing tables and indexes on filegroups.

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Optimizing Database Performance, then Database Design, then Physical Database Design, and then RAID. This volume covers the use of RAID systems with SQL Server 2000.

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 6, "Creating and Configuring User Databases," Lesson 4 discusses the use of multiple disks and RAID systems.

Objective 3.3

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab, expand Transact-SQL Reference, and then select "ALTER DATABASE." Also expand Replication, then Implementing Replication, and then select "Schema Changes on Publication Databases." These articles cover modifying the database schema.

Microsoft Corporation. Microsoft SQL Server 2000 Administrator’s Companion. Redmond, Washington: Microsoft Press, 2000. Chapter 15, "Managing Tables Using Transact-SQL and Enterprise Manager," details altering schemas using the Enterprise Manager and using T-SQL.

Objective 3.4

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Administering SQL Server, and then Backing Up and Restoring Databases. This volume contains sections that cover disaster recovery.

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Chapter 8, "Selecting a Backup and Restore Strategy," Lessons 3 and 4 deal with selecting and designing a backup and restore strategy.

Objective 3.5

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Using SQL Server Tools, then Database Maintenance Plan Wizard Help. This topic describes how to use the Database Maintenance Plan wizard with an article on database integrity checks.

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand SQL Server Architecture, and then Administration Architecture, and then select "Data Integrity Validation." This article discusses the use of DBCC and its features in SQL Server 2000.

Objective 3.6

Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, point to Programs, then Microsoft SQL Server, and then select Books Online. Choose the Contents tab and expand Administering SQL Server, then Monitoring Server Performance and Activity, and then select "Monitoring with SQL Profiler." This article covers the use of SQL Profiler for such actions as troubleshooting transactions and debugging T-SQL statements.

Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 System Administration. Redmond, Washington: Microsoft Press, 2001. Review Chapter 14, "Monitoring SQL Server Performance and Activity," Lesson 3 for a full discussion of all of the monitoring options available with SQL Server 2000.

3.1 Optimize database performance.

Both the SQL Server configuration and the database design affect database performance. In fact, most database performance issues are the result of poor planning and design when creating a database.

One of the first steps to successfully tune your database is to pinpoint the cause of the performance bottleneck. Is it the server or is it the database design? Running performance tools, such as Windows 2000 Performance Monitor, and monitoring for SQL specific counters is one method you can use to help determine causes for poor database performance. However, unless you know how to interpret this data, you will be unable to diagnose the cause of the problem. For example, you monitor your SQL server and notice high CPU utilization. Without further investigation as well as an understanding of what the server is doing at that time, you do not have a solid path to follow to help diagnose the issue.

Another useful tool for monitoring database performance is the SQL Server 2000 Profiler application. Profiler enables you to record database activity, which you can then analyze with built-in tools, such as the Index Tuning Wizard. Configure the Profiler to record all database and server activity, such as the stored procedures being executed on the server, or specific activities, such as T-SQL transactions. When used in conjunction, Performance Monitor and SQL Server 2000 Profiler can help you quickly pinpoint many SQL Server specific bottlenecks.

One such bottleneck can be the cause of a high number of table scans. Table scans occur when SQL Server searches through all of the rows in a table to find the requested data rather than searching a single row. By searching all of the rows, the server inefficiently uses memory and CPU time. Table scans indicate poorly indexed tables within a database. By creating an index, such as a clustered index, you supply the server with a specified row that it can use to efficiently search for data in the table.

Creating indexes in the appropriate places is part of good database design. Other design features include optimizing queries, denormalizing the database to simplify complex processes, and identifying and optimizing the larger tables within the database. For example, if you are running a report against a large table, you might consider creating a column or columns within that table that will help propagate data, and therefore help to retrieve data faster for that report.

The type of storage system the database resides on can also affect its performance, particularly if the database is large (more than a gigabyte). To help increase the performance of a database, consider placing data files for the database on the fastest drives within the server. Additionally, you should create the data files so they reside on a RAID device. For example, RAID 5 enables SQL Server to read from the drives almost 50 percent faster than if they were on a non-RAID device. RAID devices also have the advantage of offering redundancy when storing data.

SQL Server settings also have a major effect on database performance. Before changing any of these settings, be sure you thoroughly understand what effects they will have, not only on SQL Server, but also on other applications that could be running on the server. For example, some administrators will change the Max Worker Threads value without considering its effects on the server. Max Worker Threads determines the number of worker threads allocated for SQL Server. The more threads you have, the more system resources are required to create and maintain the threads. Because worker threads can be costly, you should only specify the amount of threads that makes sense. By default, the Max Worker Threads value under SQL Server 2000 is set to 255. When determining the number of worker threads required for SQL Server, be aware of the number of concurrent users your server will need to host at any given time. The worker thread value is pooled, so in case the number of connections to the SQL server goes beyond the Max Worker Thread value, SQL Server will begin to share the threads across connections.

There are other performance issues that are not covered in this overview. Refer to the Further Reading section to learn about other sources of information.

MCS

70-228.03.01.001

B

Administrators in the HR department are complaining about the time it takes to retrieve a list of retired employees from the database. After looking into the situation, you do not find any significant problems with the query they are running that could cause a large lag time in returning the results. You run SQL Server Profiler and find that when the query is executed, a larger number of table scans appear in the trace. Which of the following is the best method to reduce query execution time in this case?

A. Increase the amount of memory in the server.

Incorrect

A. Increasing the amount of memory would be a possible resolution if SQL Server were showing signs of increased memory usage during query execution. In this case, there are a large amount of table scans, which indicates a poorly designed database schema.

B. Create or reindex tables in the database.

Correct

B. High table scans are a sign of a poorly indexed table. When SQL Server has to search for data in a table that is not indexed, it must do so by searching through each row of the table; therefore, it ends up scanning the entire table.

C. Increase the number of CPUs in the server.

Incorrect

C. Increasing the number of CPUs may help increase the response time of the server, but it is an inefficient method of resolving the problem.

D. Configure the SQL server to use Windows NT Fibers.

Incorrect

D. Selecting to use Windows NT Fibers enables the Windows 2000 operating system to avoid the transition between the user mode of the application and the kernel mode of the thread. This transition can be a costly operation. By reducing this transition, a server can be more efficient. However, in the case of high table scans, the problem must be addressed at the schema level for the database.

MCM

70-228.03.01.002

A, B, and C

You are creating a SQL Server database on a server configured with a RAID array controller. The server has a total of five 18 GB drives installed.

You want to accomplish the following goals:

• Recover the data even after a single drive failure.
• Allow for growth of the database beyond 18 GB.
• Configure the drives so that SQL Server performs optimally.

You create a RAID 5 disk array and store the database and transaction log files on the logical drive. You specify all five drives to be part of the array.

Which of the following does your solution achieve? (Choose all that apply.)

A. If a drive were to fail, users could continue to access SQL Server data.

Correct

A. Level 5 RAID stripes data and parity information across the set of disk drives making up the drive array. In case a single drive were to fail, information stored on the array would still be accessible because of the parity information written to the drives.

B. Disk drive capacity will not be an issue when the database grows beyond 18 GB.

Correct

B. By creating a disk array, you create a single logical drive. The size of this logical drive is approximately the sum of all of the drives within the array less approximately 20 percent used for overhead when creating the array.

C. Creating a RAID 5 array increases the read performance of the server.

Correct

C. Striping data with parity increases read performance compared to other RAID levels, such as disk mirroring.

D. None of the goals are achieved.

Incorrect

D. All three goals are achieved.

MCS

70-228.03.01.003

B

Your SQL 2000 server has 512 users created in the Logins directory. You have been monitoring the system for usage and have noticed that there are never more than 281 users connected to the server at a single time. Realizing this, you want to ensure that server resources are being allocated correctly. A coworker suggests that you look into changing the Max Worker Thread setting, which is currently set to 512. What value should you change it to?

A. Change it to 128.

Incorrect

A. See the explanation for answer B.

B. Change it to 300.

Correct

B. For each client connection to SQL Server, an operating system thread is created, which can be extremely costly to system resources. To improve overall system performance, SQL Server implements thread pooling: one thread handles each user connection. However, if the number of worker threads is less than the number of user connections, SQL Server pools the worker threads, reducing the number of threads that need to be created. You must specify a Max Worker Thread value that is capable of supporting the expected users to the system. Creating a large thread value forces more system resources to be used.

C. Leave it at 512.

Incorrect

C. See the explanation for answer B.

D. Change it to 256.

Incorrect

D. See the explanation for answer B.

MCS

70-228.03.01.004

A

You recently deployed a Web-based application that stores data on a SQL Server 2000 machine. The application is accessible to users both internal and external to your organization. Even though the site has only been up and running for a week, complaints are coming in regarding the response time of the application, specifically when users must retrieve a list containing available vendors. You gather performance monitoring data for the site and find that the Processor Queue Length for the CPUs on the SQL server are extremely high. What should you do to resolve the problem?

A. Increase the number of processors in the machine.

Correct

A. The Processor Queue Length counter denotes the number of threads waiting for processor time. If the queue is large, you must increase the number of processors in the server, replace the processors with faster ones, optimize the queries, or improve the indexes.

B. Increase the amount of RAM.

Incorrect

B. See the explanation for answer A.

C. Insert a faster SCSI board.

Incorrect

C. See the explanation for answer A.

D. Increase the size of the temp database.

Incorrect

D. See the explanation for answer A.

MCM

70-228.03.01.005

A and B

You have a SQL Server 2000 machine that is extremely slow when accessed. You run some basic Performance Monitor sessions and find that a deadlock is occurring in the Marketing database that’s configured on the server.

You want to accomplish the following goals:

• Find the cause of the deadlock that is occurring.
• Monitor the database for excessive table scans.
• Apply the optimal indexes to the database for maximum performance.

You run the SQL Profiler application and save the trace file. While running the Profiler, you configure the Profiler to use the following counters: Stored Procedures/RPC:Starting, TSQL/SQL:BatchStarting, Locks/Lock:Deadlock, and Locks/Lock:Deadlock Chain. After the trace is complete, you run the Index Tuning Wizard and create the indexes it recommends for the database.

Which of the following does your solution achieve? (Choose all that apply.)

A. You can find the cause of the deadlock.

Correct

A. SQL Server Profiler is an excellent tool for monitoring SQL Server activity. Unlike Windows 2000’s Performance Monitor, SQL Server Profiler enables you to monitor detailed SQL Server transactions including monitoring for deadlocks and their causes. By specifying the Profiler to monitor for specific counters, you can better determine the cause of the deadlock.

B. Indexes are applied optimally to the database.

Correct

B. The Index Tuning Wizard analyzes the trace file and recommends indexes that should be created.

C. SQL Server Profiler displays table scans that are occurring.

Incorrect

C. You must configure SQL Server Profiler to monitor for table scans.

D. None of the goals are accomplished.

Incorrect

D. Two of the goals are accomplished.

3.2 Optimize data storage.

Although SQL Server 2000 is designed to store large amounts of data and support large numbers of users, it benefits both large and small companies by providing them with easy-to-use data storage options. Optimizing data storage requires early planning of your organization’s storage needs as well as an understanding of how SQL Server utilizes I/O subsystems and the placement of database objects on disks to maximize performance.

Hardware and software implementations of RAID can directly affect the performance of SQL Server. Hardware disk arrays provide better performance than those implemented through the operating system’s software because I/O functions are handled directly through the controller.

Various levels of RAID configurations designate differences in performance, redundancy, storage capacity, and cost. With RAID 0, data is striped in a fixed order across all disks in the array. Read/write operations can be performed independently and simultaneously, thereby improving performance. RAID 0 provides no fault tolerance, so if one disk in the array fails, data that is striped across all other disks becomes unavailable. RAID 5 also stripes data across a disk array but writes parity information in such a way that data and parity data are always on different disks. RAID 5 offers lower performance than RAID 0; however, it has higher reliability because a single disk can fail and all data remains accessible.

The placement of database objects within a database can also improve performance. You can create database objects, such as tables and indexes, on specific filegroups within a database, and you can create these filegroups on specific disks. By partitioning tables and indexes correctly, you can improve query performance and process queries in parallel.

MCS

70-228.03.02.001

C

You are building a SQL server with five physical disks. You want to achieve the best performance possible, but you also want to achieve a high level of redundancy. Which RAID level best meets your requirements?

A. Level 10 (1 + 0)

Incorrect

A. This RAID level is not possible with five disks. Because each disk drive requires a mirrored disk, RAID 10 always requires an even number of disks. RAID 10 requires a minimum of four disks. If a sixth disk were available, this configuration would provide the best combination of performance and fault tolerance of any RAID level. Three disks would be used to configure a striped array with parity, which would then be mirrored to an identical set of striped disks.

B. Level 1

Incorrect

B. RAID 1 uses disk mirroring to provide redundancy. All data written to the primary disk is also written to the mirror disk. RAID 1 provides improved read performance; however, write performance is less than that achieved by RAID 5. Because each disk drive requires a mirrored disk, RAID 1 always requires an even number of disks.

C. Level 5

Correct

C. By ensuring that data and parity data are always written to separate disks, RAID 5 achieves a high level of fault tolerance because a failed disk does not affect data retrieval. Optimal read/write performance is achieved because data is spread across multiple disks and can be accessed in parallel.

D. Level 0

Incorrect

D. RAID 0 offers the same performance benefits as RAID 5, but it does not offer any redundancy.

MCM

70-228.03.02.002

A and B

Which of the following data storage options use disk striping to spread data across multiple disks in order to achieve better performance? (Choose all that apply.)

A. Creating database files on multiple physical disks and assigning filegroups to each file

Correct

A. SQL Server uses proportional fill to evenly distribute data across all of the files and all of the disks.

B. RAID 3

Correct

B. RAID 3 employs disk striping to improve performance.

C. Hardware-based disk duplexing

Incorrect

C. Hardware-based disk duplexing does not use disk striping technology to spread data across multiple disks. Disk duplexing is a form of disk mirroring that also provides protection against disk controller failure.

D. Software-based disk mirroring

Incorrect

D. Disk mirroring does not stripe data across multiple drives. Instead it provides a mirror of the data on another drive.

MCM

70-228.03.02.003

A and D

Placing SQL Server data files on which of the following will provide the best performance benefits when reading data? (Choose all that apply.)

A. A Windows NT volume formatted using NTFS with a 64-KB extent size

Correct

A. SQL Server performance can be improved when databases are created on NTFS volumes with a 64-KB extent size.

B. A Windows NT volume formatted using FAT

Incorrect

B. Performance is not improved by the FAT file system. Databases stored on volumes formatted using NTFS with a 64-KB extent size will have improved performance over those stored on volumes formatted using the FAT file system.

C. A Windows NT compressed drive

Incorrect

C. SQL Server data and transaction log files should never be placed on compressed drives.

D. A Windows NT disk mirror

Correct

D. Windows NT disk mirroring will improve data reads.

MCS

70-228.03.02.004

B

Assuming you are planning your data storage requirements for optimal performance, which of the following is the best option for improving performance?

A. Create the transaction log in the primary filegroup.

Incorrect

A. Transaction logs are separate files that cannot be part of a filegroup.

B. Create files or filegroups on as many different physical disks as possible.

Correct

B. Spreading data files across as many different physical disks as possible is beneficial because I/O performance is improved due to multiple disk heads reading data at the same time.

C. Place frequently accessed tables and clustered indexes belonging to those tables on different filegroups spread across as many different physical disks as possible.

Incorrect

C. The only type of index that can be placed in a filegroup not containing its base table is a nonclustered index. Placing nonclustered indexes on different filegroups spread across as many different physical disks as possible improves performance because it allows multiple disk heads to read data simultaneously.

D. Place different tables used in the same join queries in the same filegroup.

Incorrect

D. Performance would be improved if the tables used in the same join query were placed in different filegroups spread across as many different physical disks as possible because it allows multiple disk heads to read data simultaneously.

MCS

70-228.03.02.005

D

What is the primary advantage of a software-based implementation of RAID 5 versus a hardware-based implementation?

A. Performance

Incorrect

A. Hardware-based RAID 5 offers better performance because no processor cycles are required.

B. Redundancy

Incorrect

B. Both implementations offer the same level of redundancy.

C. Ease of installation

Incorrect

C. Both implementations are comparable in terms of ease of installation.

D. Cost

Correct

D. Because it is built into the operating system, software-based RAID 5 does not require any additional cost for specialized hardware.

3.3 Modify the database schema.

As discussed in the previous objective, the design of a database affects overall performance of the database. Keeping that in mind, this objective discusses ways to edit the schema not only for the purposes of improving performance, but also for managing users’ needs and requirements.

There are two major methods used to edit an existing database schema: SQL Server 2000 Enterprise Manager and SQL Server 2000 Query Analyzer. By utilizing the schema manipulation tools within Enterprise Manager, administrators can easily add, delete, or modify tables from databases. They can also get more specific by modifying views, triggers, indexes, and relationships attributed to the table.

Many tasks that administrators perform when modifying the schema are repetitive. For that reason, most administrators turn to T-SQL. Creating T-SQL statements enables administrators to have greater control over their modifications. It also allows them to easily complete tasks that may require executing repeated steps, for example, selecting columns from a table according to a predetermined search query and then deleting those columns from the table.

When manipulating schema, keep in mind that specific restrictions may prevent you from changing certain information. These restrictions could be in the form of security restrictions or may be some constraint enforced by SQL Server logic. For example, edits to the schema of a database that is published to Subscriber servers will not be applied unless the schema was changed using Enterprise Manager or replication stored procedures.

T-SQL has a set of commands that you can use to edit the schema. As previously mentioned, these commands will fail if they do not pass security or other requirements. Some of the commands that you should become familiar with include:

• ALTER TABLE Execute the ALTER TABLE command before manipulating an existing table. You must execute the ALTER command when adding or dropping columns and constraints, or disabling or enabling constraints and triggers.
• ALTER VIEW Use the ALTER VIEW command to change previously created views. Also use it to modify indexed views.
• ALTER TRIGGER Use the ALTER TRIGGER command to modify previously created triggers.

MCS

70-228.03.03.001

C

All customer information is stored on a SQL server named Sales. The Contracts table, which is part of the Sales database, stores customer contract information, such as the start and end date of each contract and any specific discounts specified in the contract. The Contracts table has a column named Titles, which contains the title of each contract. Because of some changes in the way contracts are written, you must delete the Titles column. However, SQL Server is not allowing you to do so. You have been assigned the role of system administrator and dbo for that table. Which of the following represents the best explanation for why you can’t delete the column?

A. There is not enough room on the temp database.

Incorrect

A. The size of the temp database does not affect your ability to delete a single column from a table.

B. The column must be null before you can delete it.

Incorrect

B. A column can be deleted even if it contains data. Situations that prevent a column from being deleted include the column being involved in replication or having an index defined on the column.

C. A rule has been bound to the column.

Correct

C. A column that has a rule bound to it cannot be deleted. The rule must be dropped first, and as long as other constraints, such as indexes, primary keys, or foreign keys, are not associated with the column, the column can be deleted.

D. The column contains a default value.

Incorrect

D. See the explanation for answer C.

MCS

70-228.03.03.002

D

The sales office in Chicago publishes data to the West Coast sales office in Los Angeles. The marketing group in Chicago has given you a list of vendors who are no longer associated with your organization, so you must change some of the tables in the database. Using T-SQL commands, you execute a series of ALTER TABLE commands on the required tables. The next morning you receive an e-mail from the marketing group informing you that the Los Angeles office can still view vendor information that was supposed to be deleted. You run a query on the Chicago database and find that the information is correct. What could be the reason for the incorrect information at the Los Angeles office?

A. When servers are involved in a Publisher/Subscriber relationship, you must manually update any schema changes to a database on both the Publisher and Subscriber.

Incorrect

A. See the explanation for answer D.

B. You need to create a Distributor as part of the replication so that schema changes are successfully carried over to the Subscriber server.

Incorrect

B. See the explanation for answer D.

C. Schema changes cannot be overwritten on a Subscriber server unless the subscriber’s database is first dropped.

Incorrect

C. See the explanation for answer D.

D. You cannot edit the schema of a database involved in replication using an ALTER TABLE statement.

Correct

D. You can only edit a database’s schema on a Publisher and have it replicate successfully to the Subscriber if the changes are done through the replication publication properties of Enterprise Manager or by using replication stored procedures.

MCM

70-228.03.03.003

C and D

You want to create a table within the Patient_Records table. You also want to enable full-text indexing for the table. Which tools can you use to do so? (Choose all that apply.)

A. Database Designer

Incorrect

A. You cannot use Database Designer to create a full-text index.

B. Table Designer

Incorrect

B. You cannot use Table Designer to create a full-text index.

C. SQL Server Enterprise Manager

Correct

C. You can right-click on a table in Enterprise Manager and choose to enable Full-Text Indexing.

D. SQL Server Query Analyzer

Correct

D. You can execute the sp_fulltext_table command to mark or unmark a table for full-text indexing.

MCS

70-228.03.03.004

A

A user in the Marketing department is having trouble updating the Distributors table in the Marketing database. Which of the following stored procedures can you execute to display a list of permissions for that table?

A. sp_table_privileges

Correct

A. INSERT, UPDATE, DELETE, and SELECT permissions are just some of the permissions returned by the sp_table_privileges stored procedure.

B. sp_srvrolepermission

Incorrect

B. Executing sp_srvrolepermission returns the list of fixed server roles.

C. sp_grantdbaccess

Incorrect

C. You can add a security account in the current database for Microsoft SQL Server using sp_grantdbaccess.

D. sp_table_privileges_ex

Incorrect

D. The stored procedure sp_table_privileges_ex is used to return privilege information for tables located on a linked server.

3.4 Perform disaster recovery operations.

The recovery model you select for a database dictates backup and recovery procedures. Objective 2.1 discusses SIMPLE, FULL, and BULK_LOGGED recovery models in relation to database performance. However, database performance is just one factor to consider when analyzing recovery requirements. Selecting the appropriate recovery model for a database and creating a disaster recovery strategy requires an understanding of the availability requirements and the acceptable level of exposure to data loss within your environment.

All of the recovery models can utilize database backups, which capture an exact duplicate of the data in the database, and differential database backups, which capture only the data that has changed since the last database backup. You can create database backups and differential database backups by using the BACKUP DATABASE T-SQL command or by using Enterprise Manager.

The recovery models differ in their ability to use transaction logs to recover a database up to the point of failure. The SIMPLE recovery model does not use transaction logging, so recovery is only available to the point of the last good database or differential backup. The BULK_LOGGED recovery model minimally logs bulk copy operations, so it provides recovery to the end of a transaction log backup when the log backup contains bulk changes. Therefore, it is possible that you may need to manually redo any transactions occurring after the last transaction log backup. Under the FULL recovery model, all transactions are logged and recovery is available to the point of failure or to a specific point in time. You can create transaction log backups via the BACKUP LOG T-SQL command or through Enterprise Manager.

Restore database and differential backups by using the RESTORE DATABASE T-SQL command or through Enterprise Manager. Restore transaction logs by using the RESTORE LOG T- SQL command or through Enterprise Manager.

You can switch the recovery model for a database to meet the technical and business needs of an organization. For example, during bulk load operations, it may be wise to switch the recovery model to BULK_LOGGED for increased performance, and then switch it back to FULL for superior recovery when the load operation is complete.

MCM

70-228.03.04.001

A and C

Your company currently backs up its SQL Server 2000 master database once a week. You suspect that this needs to be increased based on the activity occurring. Which of the following operations will update the master database, thereby requiring a backup? (Choose all that apply.)

A. Changing a database configuration option

Correct

A. Database configuration options as well as serverwide settings will update the master database.

B. Deleting a filegroup from a database

Incorrect

B. This operation does not affect the master database.

C. Creating a new user database

Correct

C. Creating a new user database will update the master database.

D. Changing security for a user on a user database

Incorrect

D. This operation does not affect the master database.

MCS

70-228.03.04.002

B

You have a 10 GB database named Litigation running on SQL Server 2000 for your company’s Legal department. A large case is underway, and the database is used around the clock from Monday through Friday. The database is set up for SIMPLE recovery. A database backup occurs every Sunday evening at 11:00 P.M.

You must meet the following goals:

• Estimate the amount of space required for a database backup.
• Increase the frequency of backups throughout the week.
• Minimize the time required to perform backups that occur during the week.
• Maximize database performance and still be able to restore up to the last database backup.

You issue the sp_helpdb command on the Litigation database to determine the amount of space a database backup requires. You schedule database backups to occur Mondays, Wednesdays, and Fridays. You switch the database to the FULL recovery model.

Which of the goals have you accomplished?

A. You have estimated the space required for a database backup.

Incorrect

A. The sp_helpdb command shows you the total size of the database including unused space. The sp_spaceused system stored procedure provides the size of only the data that is used in a database, which is a good estimate of the size of the database backup.

B. You have increased the frequency of backups throughout the week.

Correct

B. You have increased the number of backups by scheduling backups to occur on Mondays, Wednesdays, and Fridays.

C. You have minimized the time required to perform backups during the week.

Incorrect

C. Database backups take more time than differential database backups. To minimize the required time, differential database backups should be scheduled during the week with a database backup occurring on Sunday.

D. You have maximized database performance and are still able to restore up to the last database backup.

Incorrect

D. By switching the database recovery model to FULL, you have added additional database overhead for transaction logging. If the recovery requirement is to be able to restore to the last database backup, the SIMPLE recovery model is adequate and provides better database performance because there is no transaction logging.

MCS

70-228.03.04.003

B

Your company’s SQL Server 2000 Accounting database experienced a critical failure and needs to be restored. The database uses the FULL recovery model with database backups occurring nightly at 11:00 P.M. Transaction log backups occur daily at 10:00 A.M. and 2:00 P.M. The critical failure occurred at 8:00 P.M. You expect to be able to recover up to the point of failure. You create a backup of the currently active transaction log. Which of the following must be done before this transaction log can be applied?

A. Restore the 11:00 P.M. database backup from the previous night, and issue the T-SQL command RESTORE DATABASE ACCOUNTING WITH RECOVERY to recover the database.

Incorrect

A. If you recover the database without first applying the transaction log backups, you cannot restore past that point without restarting the entire restore operation, starting with the database backup. All transaction logs should be applied before the database is recovered.

B. Restore the 11:00 P.M. database backup from the previous night, and apply the 10:00 A.M. and 2:00 P.M. transaction logs created since the database backup.

Correct

B. This is the correct sequence of events. Once all preceding transaction logs have been applied to the restored database, the final transaction log can be restored.

C. Stop and restart the SQL Server Service.

Incorrect

C. This is not a step used in recovery.

D. Issue a checkpoint in the database prior to restoring the final transaction log.

Incorrect

D. This is not a step used in recovery.

MCM

70-228.03.04.004

B and C

Your company is planning the rollout of a very large, mission-critical intranet database. The database has two filegroups: a primary filegroup and a secondary filegroup named Contacts, which isolates a single table. The company policy requires point of failure recovery. Due to its large size, the database takes a long time to back up, and it is not feasible to do more than one database backup each night. The SQL Server 2000 machine has several physical disk drives (assigned to drive letters F:, G:, and H:) that are dedicated for backup purposes.

You must design the backup strategy to accomplish the following:

• Maximize the speed of the nightly database backup.
• Enable the Contacts filegroup to be restored independently of the primary filegroup.
• Minimize the amount of time it takes to recover the database to a point of failure following a database restore.
• Select the most appropriate recovery model.

You run the system stored procedure sp_spaceused and determine that the nightly database backup will fit on any of the three physical disks dedicated for backup. Your plan is to back up the database to a backup device you create on drive F:. You plan to implement differential backups at hourly intervals and transaction log backups every 15 minutes to devices you create on drive G:. You plan to set the database recovery model to SIMPLE.

Which of the following requirements are met? (Choose all that apply.)

A. You have maximized the speed of the nightly database backup.

Incorrect

A. To increase the speed of the nightly backups, you should create multiple backup devices on the three physical drives. SQL Server 2000 can use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time.

B. The Contacts filegroup can be restored independently of the primary filegroup.

Correct

B. Database backups allow for individual files and filegroups to be restored. There is also a separate file backup operation that can capture just the database files that you are interested in backing up. Note that files and filegroups must be restored in conjunction with transaction logs to ensure the integrity of the database.

C. Following a database restore, you have minimized the time it would take to recover a database to the point of failure.

Correct

C. By implementing differential and transaction log backups, you have reduced the time it takes to recover a database to the point of failure because only the transaction log backups created since the last differential backup need to be applied. If differential backups were not implemented, every transaction log would need to be applied to the restored database to recover to the point of failure, which would take much longer.

D. You have set the database to the appropriate recovery model.

Incorrect

D. SIMPLE recovery does not allow for transaction logging. In order to accomplish point of failure recovery, the database should be set to use the FULL recovery model.

MCS

70-228.03.04.005

D

The Human Resources director contacts you to restore the department’s SQL Server 2000 Personnel database. One of the users has accidentally processed benefit statements for the wrong group of employees. The processing took place at 11:00 A.M. on Friday, October 13, 2000. The director would like the database restored to the condition it was in at 10:30 A.M. and will figure out what transactions need to be reposted from that time. The last database backup, PERS_FULL, was at 11:00 P.M. the previous night. The last transaction log backup, PERS_LOG1, was at 10:00 A.M. that morning. You back up the currently active transaction log to PERS_LOG2. Which of the following commands would you use to perform the restore?

RESTORE DATABASE PERSONNEL FROM PERS_FULL WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG1 WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG2 WITH RECOVERY, AFTER = 'OCT 13, 2000 10:30 AM'

GO

Incorrect

A. The first two steps are correct. You need to restore the database as well as the transaction log from 10:00 A.M. The syntax is wrong in the final command. AFTER is not a valid clause. The AFTER clause is used in conjunction with the STOPATMARK or STOPBEFOREMARK clause when recovering a database to a mark in a log file.

RESTORE DATABASE PERSONNEL FROM PERS_FULL WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG2 WITH RECOVERY

GO

Incorrect

B. The first step is correct. However, the 10:00 A.M. transaction log has not been applied, and the second command would restore the entire PERS_LOG2 transaction log and would not recover to a specific point in time.

RESTORE DATABASE PERSONNEL FROM PERS_FULL WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG2 WITH RECOVERY, STOPAT = 'OCT 13, 2000 10:30 AM'

GO

Incorrect

The 10:00 A.M. transaction log restore is missing from this solution. The second step should be RESTORE LOG PERSONNEL FROM PERS_LOG1 WITH NORECOVERY.

RESTORE DATABASE PERSONNEL FROM PERS_FULL WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG1 WITH NORECOVERY

GO

RESTORE LOG PERSONNEL FROM PERS_LOG2 WITH RECOVERY, STOPAT = 'OCT 13, 2000 10:30 AM'

GO

Correct

C. This solution would correctly restore the database, apply the 10:00 A.M. transaction log, and then recover the most current transaction log up to the point of 10:30 A.M., which is prior to the user error occurring.

3.5 Perform integrity checks.

Integrity checks are imperative to the upkeep of any database that is critical to the day-to-day operations of a business. You can perform database integrity checks by using built-in Database Consistency Checker (DBCC) utilities, which are included with SQL Server 2000. These utilities perform the same function as the check disk utilities under the Windows operating systems. Basically, as users continuously access data, corruption of that data will inevitably occur over a period of time. By scheduling integrity checks on a regular basis, you can avoid permanent data destruction before it occurs.

Like many of the administrative tasks that you execute in SQL Server 2000, you can launch an integrity check using SQL Server Enterprise Manager or by executing T-SQL commands. Before you can actually run an integrity check on a database, all users must be disconnected from that specific database.

Using Enterprise Manger, you launch an integrity check via the Database Maintenance Plan Wizard. The wizard enables you to schedule and launch many of the day-to-day administrative tasks you normally perform when running Microsoft SQL Server. The following options are available to you when executing an integrity check using the Maintenance Plan Wizard:

• Check database integrity. Without selecting this option, the Include indexes, Attempt to repair any minor problems, Exclude indexes, and Perform these checks before doing backups options will not be available. This prevents the administrator from having the wizard run any integrity checks on the database.
• Include indexes. This option forces each data and index page of a database to be checked. Selecting this option offers a more thorough integrity check, thereby increasing its duration.
• Attempt to repair any minor problems. This option is only available if the Include Indexes check box has been selected. By selecting the Attempt to repair any minor problems option, SQL Server automatically corrects easily correctable problems.
• Exclude indexes. Selecting this option decreases the duration of the integrity check. However, corrupt indexes are usually the reason that integrity checks on a database fail. If you do not select this option, you will not know whether the database is truly consistent.
• Perform these tests before doing backups. If you continually back up a database with potential problems that have not been corrected by running an integrity check or by some other means, you may face a catastrophe when you need to recover data from the backup source. Selecting this option assures you that the data being backed up has passed the integrity check according to your specified parameters.

Executing DBCC commands via T-SQL statements allows you greater flexibility. For example, using the DBCC CHECKTABLE command, you can run an integrity check on the data, index, text, ntext, and image pages for the specified table or indexed view. Other DBCC commands that you can execute using T-SQL include:

• DBCC CHECKALLOC
• DBCC CHECKCONSTRAINTS
• DBCC NEWALLOC
• DBCC CHECKDB

The following commands are used to correct known problems with specific objects of a database.

• DBCC DBREINDEX
• DBCC INDEXDEFRAG

MCS

70-228.03.05.001

B

The database storing data from a recent marketing survey conducted by your company is showing signs of possible corruption. You want to run an integrity check on the tables in the database. Which of the following must be done before you can run the integrity check?

A. Users must close the data entry program they are using to input survey results.

Incorrect

A. In order to run an integrity check on a database, the database must be in single user mode. Closing client/ server applications does not guarantee that the database is in single user mode.

B. The database must be in single user mode.

Correct

B. Before running an integrity check, the database must be in single user mode.

C. Permissions for the data files must be set to Full Control for Everyone.

Incorrect

C. Permissions for the database files should never have to be changed for any SQL server task, such as running an integrity check.

D. You must run an integrity check of the database filegroups.

Incorrect

D. You do not have to run an integrity check of the filegroups independent of or before an integrity check of the tables.

MCS

70-228.03.05.002

A

You want to monitor fragmentation of data and indexes for a specified table using T-SQL. Which DBCC statement should you execute to do this?

A. SHOWCONTIG

Correct

A. The DBCC statement SHOWCONTIG displays the fragmentation information for the data and indexes of the specified table.

B. CHECKALLOC

Incorrect

B. The DBCC statement CHECKALLOC checks the allocation and use of all pages in the specified database.

C. SQLPERF

Incorrect

C. The DBCC statement SQLPERF provides statistics about the use of transaction log space in all databases.

D. SHOW_STATISTICS

Incorrect

D. The DBCC statement SHOW_STATISTICS displays the current distribution statistics for the specified target on the specified table.

MCM

70-228.03.05.003

A, B, and C

A SQL Server severity error appears stating that the integrity of an index in a table is corrupted.

You want to accomplish the following goals:

• Confirm the integrity of other objects in the database.
• Determine if the problem is being caused by a disk error or cache.
• Ensure that the index works correctly.

You run DBCC CHECKDB to determine if other objects, such as tables, in the database are also corrupted. You then reboot the server to clear the cache and check the status of the index. After rebooting, you still find a problem with the index. You then delete the index, and re-create it.

Which of the following goals are accomplished? (Choose all that apply.)

A. You are able to determine if any other object in the database is corrupt.

Correct

A. Running the DBCC CHECKDB command will check the integrity of all objects within a database.

B. By rebooting the server, you can determine whether cache is causing the problem.

Correct

B. Rebooting the server and checking the status of the index allows you to deter mine if the server’s cache is the cause of the problem. Rebooting the server forces the cache for the system to clear along with any corrupt data that may be present.

C. Re-creating the index ensures that the index will work correctly.

Correct

C. If cache is not the cause of the problem, you have to re-create the index.

D. None of the goals are accomplished.

Incorrect

D. All of the goals are accomplished.

MCS

70-228.03.05.004

B

You’re hosting your Web site’s data on a SQL Server 2000 machine. As part of your daily maintenance tasks, you back up the database and run an integrity check on the database. You want to keep the downtime for the system to a minimum. Which of the following should you do to increase the speed of the DBCC checks?

A. Run the Index Tuning Wizard before running the integrity check.

Incorrect

A. A well-indexed database does nothing to increase the speed of the DBCC check against the database.

B. Move the temp table to a faster disk, such as a RAID device.

Correct

B. The DBCC operation utilizes the tempdb for spooling, particularly when dealing with a large database. If transactions to and from the tempdb are faster, it also helps DBCC operations to proceed faster.

C. Increase the size of the master database.

Incorrect

C. See the explanation for answer B.

D. Increase the size of the database’s transaction log.

Incorrect

D. See the explanation for answer B.

3.6 Troubleshoot transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or Transact-SQL.

You can monitor SQL Server 2000 transactions by using a number of built-in tools including SQL Server Profiler, the Current Activity window available within Enterprise Manager, T-SQL statements, SQL and Windows NT or 2000 server error logs, and Windows NT or 2000 Performance Monitor.

SQL Server Profiler is a graphical tool that captures information about events. You can use it to save a sequence of events to a file, which you can later replay step-by-step to determine where a problem is occurring. You can set SQL Server Profiler to monitor only the events you want to view. Use SQL Server Profiler to monitor performance, debug T-SQL statements and stored procedures, troubleshoot poorly performing queries, and review security auditing events. Start SQL Server Profiler from within Enterprise Manager or from the SQL Server Profiler icon within the Microsoft SQL Server program group.

You can use the Current Activity window within Enterprise Manager as a monitoring tool to view currently blocked or blocking transactions. You can also view current user connections, the status of commands that active users are running, and any locked objects. System administrators can terminate processes from within this tool.

You can also use T-SQL commands to view and troubleshoot transactions. Use DBCC OPENTRAN to determine if an open transaction exists within a log. Use DBCC INPUTBUFFER to display the last statement sent from a client to the SQL server. To view general statistics about SQL Server activity, use sp_monitor. Use sp_lock to show current object locks, and sp_who to show current users and processes.

Windows NT Performance Monitor or Windows 2000 System Monitor allows you to view SQL Server objects and performance counters in conjunction with other objects, such as memory, cache, threads, and other processes. This information, as well as the SQL Server and Windows NT or 2000 error logs, can assist in troubleshooting events occurring with SQL Server 2000.

MCM

70-228.03.06.001

A and B

Which of the following tools allows you to see a list of currently connected users and the processes they are running on an instance of SQL Server? (Choose all that apply.)

A. sp_who

Correct

A. The sp_who stored procedure displays a list of current users and processes that they are running.

B. The Current Activity window in Enterprise Manager

Correct

B. The Current Activity window displays currently connected users and the processes that they are running.

C. sp_monitor

Incorrect

C. The sp_monitor stored procedure displays statistics about how busy the SQL Server is. Statistics reported include the number of seconds the server’s CPU has been busy performing SQL Server tasks, the number of seconds SQL Server has spent performing I/O operations, and the number of packets read and written by SQL Server. The sp_monitor stored procedure displays current values for these functions as well as how much the values have changed since the last time the procedure was run.

D. Windows NT Performance Monitor/Windows 2000 System Monitor

Incorrect

D. The Performance Monitor in Windows NT or the System Monitor in Windows 2000 allow you to see the performance statistics and activity of SQL Server, but do not display details, such as a list of users and the processes they are running.

MCS

70-228.03.06.002

B

The transaction log file for your company’s Medical Records database is set to autogrow. You suspect that there has been a significant increase in the number of transactions posted to the database, and you would like to evaluate whether the autogrow percentage increment should be increased. Which of the following SQL Profiler event categories would you use to monitor the frequency of growth of the transaction log file?

A. Object event category

Incorrect

A. The Object event category monitors when an object is opened, created, deleted, or used.

B. Database event category

Correct

B. The Database event category monitors when a data or log file automatically grows or shrinks.

C. Performance event category

Incorrect

C. The Performance event category monitors the execution of SQL data manipulation language operations.

D. Transactions event category

Incorrect

D. The Transactions event category monitors the status of transactions including the types of transactions being logged and when transactions are committed or rolled back.

MCM

70-228.03.06.003

A and D

Your company has implemented a new policy, which requires auditing SQL Server logons.

You must implement the new policy and accomplish the following:

• Track who has logged on to SQL Server.
• Track who has logged out of SQL Server.
• Do not track logon or logout of the sa user account.
• Track the time selected events begin.

You create a new trace in SQL Profiler, and you select the Security Audit event class and include the Audit Login event. You include data columns for EventClass, LoginName, StartTime, and SPID. Then you create a filter on TargetLoginName specifying Not Like sa.

Which of the following does your solution achieve? (Choose all that apply.)

A. You are tracking who has logged on to SQL Server.

Correct

A. By including the Audit Login event, you are tracking every time a user logs on to SQL Server.

B. You are tracking who has logged out of SQL Server.

Incorrect

B. The solution does not include tracking the Audit Logout event. This event would need to be included in order to view logouts.

C. You are not tracking logon or logout events for the sa user account.

Incorrect

C. The filter specified in this solution is the correct filter criteria, but it is being applied to the wrong data column. The appropriate data column is LoginName.

D. You are tracking the time selected events begin.

Correct

D. By including the data column for StartTime, you are tracking the time an event begins.

MCS

70-228.03.06.004

D

You are diagnosing a problem reported by a user logged on to SQL Server as JAQ. You would like to display the last SQL statement sent from JAQ’s machine to SQL Server. Which of the following would accomplish this task?

A. Display the Current Activity window in Enterprise Manager. Find the appropriate logon name, and view the Command column.

Incorrect

A. The Current Activity window’s command column shows you the current command being executed for a user. This column does not show you the last SQL statement sent from the client.

B. Run DBCC OPENTRAN ('JAQ').

Incorrect

B. DBCC OPENTRAN displays information about the oldest active transaction within a database.

C. Run DBCC INPUTBUFFER ('JAQ').

Incorrect

C. The DBCC INPUTBUFFER is the correct command to show the last SQL statement sent from a client, but the argument passed is incorrect. The valid argument for this command is the SPID.

D. Run EXEC sp_who 'JAQ' to determine the SPID of the user, and then run DBCC INPUTBUFFER (XYZ), where XYZ is the SPID.

Correct

D. The stored procedure sp_who provides the appropriate SPID, which is then used as the argument for the DBCC INPUTBUFFER command. The DBCC INPUTBUFFER command shows you the last SQL statement sent from the client machine.

MCS

70-228.03.06.005

B

Users report that performance seems slow when using SQL Server 2000 running on a Windows 2000 server. You suspect there is a link between the performance of SQL Server and the operating system. In order to monitor disk activity, you run System Monitor and add Physical Disk counters for %Disk Time and Avg Disk Queue Length. You also want to isolate the I/O generated by SQL Server. Which of the following SQL Server counters would you add to System Monitor to assist in this task?

A. SQL Server Databases counter: Transaction/sec

Incorrect

A. This counter shows the number of transactions started for the database per second.

B. SQL Server Buffer Manager counter: Page Reads/sec

Correct

B. This counter shows the number of physical database page reads that are issued per second. This in conjunction with the Page Writes/sec counter shows I/O generated by SQL Server.

C. SQL Server Locks counter: Number of Deadlocks/sec

Incorrect

C. This counter shows the number of lock requests per second that resulted in a deadlock.

D. SQL Server General Statistics counter: Logouts/sec

Incorrect

D. This counter shows the total number of logout operations started per second.