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. Optimize programming objects used in SQL Server 2000 including the interaction with the operating system. 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 . Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags . Analyze the query execution plan. Considerations include query processor operations and steps. 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. |