Chapter 12. Monitoring and Tuning SQL Server Databases

O BJECTIVES

This chapter covers the following Microsoft-specified objectives for aspects of troubleshooting, monitoring, and auditing SQL Server for the 70-229 Database Design SQL Server 2000 exam:

Troubleshoot programming objects. Objects include stored procedures, transactions, triggers, user -defined functions, and views.

• One of the primary jobs of any developer is that of troubleshooting and debugging. Troubleshooting is also a part of almost every IT job description. The appropriate use of all SQL Server tools is an important aspect in being able to troubleshoot effectively.

Optimize programming objects used in SQL Server 2000 including the interaction with the operating system.

• The SQL Server and operating system work together to provide a productive database management environment. There are many SQL Server hooks into the operating system and OS resources available to observe the database server as it operates. Other tools allow for quick diagnosis of problems that may be affecting the server.

Optimize programming objects. Objects include stored procedures, transactions, triggers, user-defined functions, and views.

• Any database implementation will need periodic adjustments or fine tuning. To achieve better performance, more throughput, faster response times, and periodic maintenance are necessary.

Monitor and troubleshoot database activity by using SQL Profiler .

• This is an extremely involved topic and one that has implications throughout multiple exam objectives and questions. This is also the area of premier importance when you are on the job.

Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags .

• To really get inside the server and see all the interactions will require the use of the Profiler. With this tool all activity within the database environment can be viewed , reported , and replayed.

Analyze the query execution plan. Considerations include query processor operations and steps.

• Use of the Query Analyzer can help you obtain more efficient processes and procedures. It is an important element in the fine tuning of T-SQL processes.

Define object-level security, including column-level permissions, by using GRANT , REVOKE , and DENY .

• Who can create and use objects, and in what manner they can use them, are defined by SQL Server permissions. Permissions can be given on the objects or on the underlying data in the tables. Knowledge of statement and data permissions is important in the development of most business procedures.

O UTLINE

Tools Available for Monitoring and Tuning

Stored Procedures Used to Diagnose and Optimize

Database Console Command (DBCC)

Alternative Mechanisms

Optimizing the OS Configuration

Using Performance/System Monitor

Counter Values

The Event Viewer

The Windows Application Log

Query Governor Cost Limit

Optimizing SQL Server Configuration

Current Server Activity

Stored Procedures

Monitor Activity with the Profiler

Defining a Profiler Trace

Profiler Traces to Diagnose Locking

Using Profiler Results

Trace Playback and Diagnosis

Playback Requirements

Performing the Replay

Templates and Wizards for Specific Monitoring

SQL Server Optimizer

Query Analyzer

Proactive/Automated Optimization

Securing Objects

C2 Security

Statement and Object Permissions

Security Audits

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

• Practice with each tool using the Step by Step procedures, exercises, and experiments of your own. Try a variety of different options and variants with each tool.

• Note where each tool plays an important role and in what instances one tool is chosen over another.

• Experiment with settings in a controlled environment; the production environment is not the place to try playing trial and error. The information in this chapter will potentially be the hardest to master; because of that, extra time should be taken in observing the details of each operation before you take the exam.