Chapter Summary

Troubleshooting, performance tuning, and resource optimization require in-depth knowledge of how SQL Server operates, as well as knowledge of the applications to which the database server is being applied. We have seen numerous tools and options that can be used to assist in this process.

KEY TERMS

• Query Analyzer

• SQL Profiler

• System Monitor

• Current Activity window

• T-SQL

• error logs

• Performance Monitor

• blocked process

• Windows application log

• jobs

• alerts

• operators

• sp_who

• sp_lock

• sp_spaceused

• sp_monitor

• meta data

• statistics

• scalar function

• sp_configure

• reconfigure

• SET

• built-in function

• DBCC

• fragmentation

• trace

• filter

• trace flags

• SNMP

• TCP/IP

• row lock

• page lock

• extent lock

• database lock

• table lock

• xp_sqlmaint

• sp_dboption

• sp_help

• execution plan

• query optimizer

• statement permissions

• object permissions

The first problem you will face on the exam will be which tool to use, given a set of circumstances. Second, you will need to present a course of action for monitoring and troubleshooting. Third, you will need to read and diagnose the output and then select an appropriate solution. A person who implements databases needs to be comfortable in all these areas, and you will find a few of each type of question on the exam.

Throughout this book, a great deal of information has been provided that could potentially apply to this vast topic. Everything from appropriate database design, to the correct use of technologies, to the proper definition and use of SQL Server objects all will play a role. Expect exam questions that cover a variety of these topics wound into a single scenario.

Now that you have completed the entire book and have an understanding of all the concepts covered, the exam should be a cake-walk for you. If you have completed the Case Studies, Exercises, and Step by Steps of the chapter, you will have obtained valuable practical experience with the product. In the appendixes of this book are a couple of other elements that you can use for final preparation. Quick facts about the exam, more sample exam questions, and terminology definitions can all be found in the pages that follow.