Using Transact-SQL to Manage Databases in Visual Studio .NET

Creation PDF417 in Visual Studio .NET Using Transact-SQL to Manage Databases

12

You use the DBCC CHECKDB statement to validate database integrity. The DBCC CHECKDB statement executes DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG statements, making individual execution of these statements unnecessary. Although the DBCC CHECKDB statement has options to repair issues found during its execution, it is recommended that DBAs attempt to restore the database from valid backup sets before attempting to use DBCC CHECKDB to repair integrity issues.

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each answer choice is right or wrong are located in the Answers section at the end of the book.

1. The DBCC CHECKDB statement issues which of the following statements A. DBCC CHECKCATALOG B. DBCC CHECKIDENT C. DBCC NEWALLOC D. DBCC TEXTALLOC 2. Which DBCC CHECKDB option is recommended for frequent checks against large databases A. NOINDEX B. REPAIR_FAST C. PHYSICAL_ONLY D. NO_INFOMSGS

Review the chapter summary. Review the list of key terms introduced in this chapter. Complete the case scenarios. These scenarios set up real-world situations involving the topics of this chapter and ask you to create a solution. Complete the suggested practices. Take a practice test.

Summary

To achieve maximum performance for your database queries, you should perform periodic index maintenance to keep index fragmentation in your databases to a minimum level. Index and column statistics are key to query performance. Because the query optimizer uses them to determine the execution plan of the query, you need to make sure that statistics are up to date. Another important database maintenance task is shrinking databases and database files that contain unneeded and unused space to a smaller size to recapture the unused disk space. You need to periodically check the integrity of your databases by using the DBCC CHECKDB command and handle errors found during its execution by restoring the database from a valid backup.

Do you know what these key terms mean You can check your answers by looking up the terms in the glossary at the end of the book.

external fragmentation histogram index fragmentation index rebuild index reorganization internal fragmentation page split statistics string summary

In the following case scenarios, you apply what you ve learned about how to maintain SQL Server 2005 by using Transact SQL. You can find answers to these questions in the Answers section at the end of this book.

You are a DBA for a local book publisher. As part of your job, you must design a method to manage index fragmentation levels. You have decided to create a SQL Server Agent job that checks index fragmentation levels and issues the appropriate ALTER INDEX statement to defragment fragmented indexes. To achieve this goal, you need to answer the following questions: 1. What mechanism will your job use to check index fragmentation levels 2. What threshold will the job use to determine whether the indexes have external fragmentation 3. What threshold will the job use to determine whether your indexes have internal fragmentation

You are a DBA for a large phone company. You need to design a database integrity check job in SQL Server Agent. During a planning meeting, your manager asks you the following questions about your integrity job: 1. In large databases, what options will you use with the DBCC CHECKDB statement to minimize any performance impact of running the statement 2. What other options will you use when you run the DBCC CHECKDB statement against all databases 3. How can you make sure that if the integrity-check job finds an issue, you can correct the problem and ensure the database s integrity