MCSE Readiness Review -- Exam 70-028: Administering Microsoft® SQL Server™ 7.0

Author Jill Spealman Pages 448 Disk 1 Companion CD(s) Level All Levels Published 09/01/1999 ISBN 9780735606722 ISBN-10 0-7356-0672-2 Price(USD) $29.99 To see this book's discounted price, select a reseller below.	More Information About the Book Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Objective Domain 5: Monitoring and Optimization

• Tested Skills and Suggested Practices
• Objective 5.2: Tune and optimize SQL Server.
• Key Terms
• Further Reading

The Monitoring and Optimization domain examines how you can monitor system performance and optimize system resources for SQL Server. You can use SQL Server Profiler and Windows NT Performance Monitor to monitor system performance and determine whether performance problems exist. If they do, you can resolve them by tuning and optimizing SQL Server or by limiting resources used by queries.

Tested Skills and Suggested Practices

• Monitoring SQL Server performance. Be able to use SQL Server Profiler and the counters SQL Server adds to Windows NT Performance Monitor to monitor SQL Server components.
• Practice 1: Learn the purpose of SQL Server Profiler. Use SQL Server Profiler to monitor performance by setting up traces to collect information. Learn how the Index Tuning Wizard uses a workload file captured by SQL Server Profiler to analyze trace results.
• Practice 2: Learn the purpose of the counters SQL Server adds to Performance Monitor. Use Performance Monitor to monitor performance. Learn the purpose of SQL Server objects, object instances, and counters. View the results of the counters on the Performance Monitor chart.
• Tuning and optimizing SQL Server. Be able to tune and optimize SQL Server memory and CPU usage.
• Practice 1: Familiarize yourself with the counters used in Windows NT Performance Monitor to indicate SQL Server memory and CPU usage.
• Practice 2: Familiarize yourself with the configuration options used to tune SQL Server memory and CPU usage.
• Limiting resources used by queries. Be able to use the query cost value to control resources used by queries.
• Practice 1: Set the query cost value for the local server. Set the query cost value for the current session.

Objective 5.2: Tune and optimize SQL Server.

You must periodically monitor and analyze the computer running SQL Server to confirm that memory usage is within normal ranges. Analyze the following Performance Monitor counters to determine memory usage:

• Memory: Available Bytes: Number of bytes of memory currently available for use by processes. Low values may indicate an overall shortage of memory or that an application is not releasing memory.
• Memory: Pages/sec: Number of pages that were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults. A high rate may indicate excessive paging.
• Memory: Page Faults/sec: Number of page faults caused by hard page faults when pages must be retrieved from disk or by writes to disk to free space in the working set. May indicate if disk activity is caused by paging.
• Process: Page Faults/sec: Number of page faults for the SQL Server process instance. May indicate if SQL Server rather than another process is causing excessive paging.
• Process: Working Set: Memory used by a process. May indicate that SQL Server is configured for too much memory if consistently below memory configuration amount. May indicate that a change is necessary in working set size.
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio: Percentage of pages found in the buffer cache without having to read from disk. A rate of 90 percent or higher is desirable and indicates that more than 90 percent of all requests for data were satisfied from the data cache.
• SQL Server: Buffer Manager: Free Buffers: Number of free buffers available. Low values may indicate that more memory is required.
• SQL Server: Memory Manager: Total Server Memory (KB): Total amount of dynamic memory that the server is currently using. High values (compared with the amount of physical memory) may indicate that more memory is required.

You can also manage and optimize memory resources through configuration options using SQL Server Enterprise Manager or the sp_configure system stored procedure. Although it is recommended that configuration options be left at their default values, the following options can be configured to optimize server performance in response to SQL Server memory usage issues:

• Min Server Memory: Specifies the minimum amount of memory (in MB) SQL Server can use.
• Max Server Memory: Specifies the maximum amount of memory (in MB) SQL Server can use.
• Max Worker Threads: Specifies the total number of threads available to SQL Server. Generally, this configuration value should be set to the number of concurrent connections, but it cannot exceed 1,024.
• Index Create Memory: Specifies the maximum amount of memory (in KB) SQL Server can use when creating indexes.
• Min Memory Per Query: Specifies the minimum amount of memory (in KB) SQL Server can use for the execution of a query.

You should periodically monitor and analyze the computer running SQL Server to determine if CPU utilization rates are within normal ranges. Analyze the following Performance Monitor counters to determine CPU usage:

• Processor: % Processor Time: Amount of time the CPU spends processing a non-idle thread. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors.
• System: % Total Processor Time: Average amount of time that all CPUs in the system spend processing non-idle threads. A consistent state of 80 to 90 percent may indicate the need for CPU upgrades or the addition of more processors.
• Processor: % Privileged Time: Percentage of time the CPU spends executing Windows NT kernel commands. If consistently high in conjunction with the Physical Disk counters, SQL Server could be I/O-bound. Consider upgrading the disk subsystem.
• Processor: % User Time: Percentage of time the CPU spends executing user processes such as SQL Server. If consistently high, may indicate the need for a CPU upgrade or the addition of more processors.
• System: Processor Queue Length: Number of threads waiting for processor time. If more than a few program processes are contending for most of the processor's time, may indicate the need for a CPU upgrade or the addition of more processors.

You can also manage and optimize CPU resources through configuration options using SQL Server Enterprise Manager or the sp_configure system stored procedure. Although it is recommended that configuration options be left at their default values, the following options can be configured to optimize server performance in response to SQL Server CPU usage issues:

• Affinity Mask: Specifies processors on which SQL Server threads can run.
• Cost Threshold for Parallelism: Specifies the cost threshold for creating parallel queries.
• Max Async IO: Specifies the maximum number of asynchronous I/O requests that the server can issue against a file. Generally, leave the Max Async IO server configuration option at the default value of 32. For very sophisticated I/O subsystems with many disks and controllers, the value can be increased to 64 or possibly higher.
• Max Degree of Parallelism: Specifies the maximum number of threads used to execute parallel queries.
• Max Worker Threads: Specifies the total number of threads available to SQL Server processes.
• Priority Boost: Allows SQL Server to run at a higher priority than other processes running under Windows NT.

Questions related to this objective are designed to determine if you have an awareness of these issues. To successfully answer the questions for this objective, you need a firm understanding of several key terms. For definitions of these terms, refer to the Glossary in this book.

Key Terms

• Cache
• Configuration options
• Input/output (I/O)
• Optimize
• Page faults
• Paging file
• Performance Monitor
• Thread
• Tuning
• Working set

70-028.05.02.001

You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. You find that the Available Bytes counter value is very low. Which condition could be indicated by this value?

1. The Max Server Memory option is set too low.
2. There is a surplus of memory for SQL Server.
3. The system is experiencing an overall memory shortage.
4. SQL Server is consuming too much of the system's available memory.

70-028.05.02.001

You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. You find that the Available Bytes counter value is very low. Which condition could be indicated by this value?

Correct Answer: C

1. Incorrect: The low setting of the Max Server Memory option does not cause the Available Bytes counter to be low.
2. Incorrect: The Available Bytes counter indicates the number of bytes of memory currently available for use by system processes. A low number of available bytes does not indicate a surplus of memory for SQL Server.
3. Correct: The Available Bytes counter indicates the number of bytes of memory currently available for use by system processes. A low number of available bytes may indicate an overall shortage of memory or that an application is not releasing memory.
4. Incorrect: A low number of available bytes does not necessarily indicate that SQL Server is consuming too much of the system's available memory. To monitor the amount of memory being used by SQL Server, examine the following performance counters: Process: Working Set, SQL Server: Buffer Manager: Buffer Cache Hit Ratio, SQL Server: Buffer Manager: Free Buffers, SQL Server: Memory Manager: Total Server Memory (KB).

70-028.05.02.002

You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. What should you do if the Buffer Cache Hit Ratio value is 60 percent?

1. Reduce the size of the working set.
2. Decrease the Max Server Memory option.
3. Add more memory until the value is 90 percent or higher.
4. Determine which applications are not releasing memory.
5. Monitor the Available Bytes counter, and increase memory if its value is too high.

70-028.05.02.002

You are using Performance Monitor to monitor the memory usage of the computer running SQL Server. What should you do if the Buffer Cache Hit Ratio value is 60 percent?

Correct Answer: C

1. Incorrect: Reducing the size of the working set reduces the physical memory space available for SQL Server. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it cannot be set by reducing the size of the working set.
2. Incorrect: Decreasing the Max Server Memory option decreases the maximum amount of memory (in MB) in the buffer pool that SQL Server uses (not the amount of memory used by the entire process). The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it cannot be set by decreasing the Max Server Memory option.
3. Correct: The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk. A value of 90 percent or higher is desirable. If the value is 60 percent, you should add more memory until the value is consistently greater than 90 percent.
4. Incorrect: Determining which applications are not releasing memory does not affect the value of the buffer cache hit ratio, which is the percentage of pages found in the buffer cache without having to read from disk.
5. Incorrect: The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. High values for the Available Bytes counter indicate that there is a memory surplus and you do not need to increase memory. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk and it is not set by evaluating the number of available bytes.

70-028.05.02.003

SQL Server is running on a multiprocessor system. You need to determine if the CPU usage rates are within an acceptable range. Which Performance Monitor counter should you use to determine the average amount of time all of the processors spend processing non-idle threads?

1. Processor: % User Time
2. Processor: % Privileged Time
3. System: % Total Processor Time
4. System: Processor Queue Length
5. Processor: % Processor Time

70-028.05.02.003

SQL Server is running on a multiprocessor system. You need to determine if the CPU usage rates are within an acceptable range. Which Performance Monitor counter should you use to determine the average amount of time all of the processors spend processing non-idle threads?

Correct Answer: C

1. Incorrect: The Processor: % User Time counter indicates the percentage of time the processor spends executing user processes such as SQL Server.
2. Incorrect: The Processor: % Privileged Time counter indicates the percentage of time the processor spends executing Windows NT kernel commands such as processing SQL Server I/O requests.
3. Correct: The System: % Total Processor Time counter combines the average processor usage of all processors into a single counter.
4. Incorrect: The System: Processor Queue Length counter indicates the number of threads waiting for processor time.
5. Incorrect: The Processor: % Processor Time counter indicates the processor usage of one processor.

70-028.05.02.004

You are using Performance Monitor to monitor the CPU usage for the computer running SQL Server.

The required result is to determine the percentage of time the processor spends processing SQL Server I/O requests.

The first optional result is to determine the percentage of time the processor spends executing user processes.

The second optional result is to determine the number of threads waiting for processor time.

The proposed solution is to use Performance Monitor to activate the SQL Server: Buffer Manager: Buffer Cache Hit Ratio, the Processor: % User Time, and the System: Processor Queue Length counters.

What does the proposed solution provide?

1. The required result and all optional results.
2. The required result and one optional result.
3. The required result but none of the optional results.
4. The proposed solution does not provide the required result.

70-028.05.02.004

You are using Performance Monitor to monitor the CPU usage for the computer running SQL Server.

The required result is to determine the percentage of time the processor spends processing SQL Server I/O requests.

The first optional result is to determine the percentage of time the processor spends executing user processes.

The second optional result is to determine the number of threads waiting for processor time.

The proposed solution is to use Performance Monitor to activate the SQL Server: Buffer Manager: Buffer Cache Hit Ratio, the Processor: % User Time, and the System: Processor Queue Length counters.

What does the proposed solution provide?

Correct Answer: D

1. Incorrect: See the explanation for answer D.
2. Incorrect: See the explanation for answer D.
3. Incorrect: See the explanation for answer D.
4. Correct: The Buffer Cache Hit Ratio, the % User Time, and the Processor Queue Length counters cannot determine the percentage of time the processor spends processing SQL Server I/O requests. To determine the percentage of time the processor spends processing SQL Server I/O requests, use the Processor: % Privileged Time counter. To determine the percentage of time the processor spends executing user processes, use the Processor: % User Time counter. To determine the number of threads waiting for processor time, use the System: Processor Queue Length counter.

70-028.05.02.005

Why should you monitor the Performance Monitor Memory: Page Faults/sec counter?

1. To ensure that disk activity is not caused by paging
2. To determine if SQL Server is consuming too much memory
3. To determine the average number of pages read from memory each second
4. To ensure that pages are not being stolen from SQL Server by the Virtual Memory Manager

70-028.05.02.005

Why should you monitor the Performance Monitor Memory: Page Faults/sec counter?

Correct Answer: A

1. Correct: The Performance Monitor Memory: Pages/sec counter indicates the number of page faults caused by hard page faults when pages must be retrieved from disk or by writes to disk to free space in the working set. Since a high rate for the Pages/sec counter may indicate excessive paging, you should monitor the Memory: Page Faults/sec counter to make sure that paging does not cause the disk activity.
2. Incorrect: To monitor the amount of memory being used by SQL Server, you should examine the following performance counters: Process: Working Set, SQL Server: Buffer Manager: Buffer Cache Hit Ratio, SQL Server: Buffer Manager: Free Buffers, SQL Server: Memory Manager: Total Server Memory (KB).
3. Incorrect: The Performance Monitor Memory: Page Reads/sec is used to determine the average number of pages read from memory each second.
4. Incorrect: It is normal for Windows NT Virtual Memory Manager (VMM) to steal pages from SQL Server and other processes as it trims the working set sizes of those processes, causing page faults. A low level of page faults is normal, even if the computer has plenty of available memory.

Further Reading

Use Microsoft SQL Server Books Online (free download available at http://support.microsoft.com/download/support/mslfiles/sqlbol.exe) to search for "monitoring memory usage," "optimizing server performance using memory configuration options," "monitoring CPU use," and "server memory options" for details on the Windows NT Performance Monitor counters used for SQL Server performance tuning.

Download the White Paper "Microsoft SQL Server 7.0 Performance Tuning Guide" available under "support" at /sql for information about SQL Server 7.0 index selection, disk I/O subsystem tuning, and performance tuning tools.

The Microsoft Windows NT Workstation 4.0 Resource Kit, Chapter 10, contains detailed information about using Windows NT Performance Monitor counters.

SQL Server System Administration. New Riders, Indianapolis, IN, 1999. ISBN 1-56205-955-6. Chapter 11 contains a detailed discussion of performance tuning and optimization.

Using Microsoft SQL Server 7.0. Que, Indianapolis, IN, 1999. ISBN 0-7897-1628-3. Chapter 18 contains a detailed discussion of SQL Server optimization and tuning fundamentals.