Question 11

Answers A, B, and C are correct. DBCC CHECKTABLE can analyze and repair table issues. ALTER INDEX REORGANIZE can repair index fragmentation. UPDATE STATISTICS may provide for more appropriate query plans, particularly if it is used after mass alterations, additions, or deletions to data. CREATE STATISTICS provides the capabilities of creating additional statistics to be used by the Optimizer and does not apply in this instance. DBCC SHOWSTATISTICS displays distribution statistics for a table but does nothing to perform maintenance. DBCC UPDATEUSAGE reports and corrects pages and row count inaccuracies in the catalog views, providing for more accurate space usage readings.

For more information, see the following sources:

• Chapter 7, "Monitoring SQL Server Performance," and Chapter 8, "Troubleshooting and Optimizing SQL Server"

• SQL Server 2005 Books Online: "SQL Server Language Reference, Transact-SQL Reference, DBCC"

• SQL Server 2005 Books Online: "SQL Server Language Reference, Transact-SQL Reference, ALTER INDEX"

• SQL Server 2005 Books Online: "SQL Server Language Reference, Transact-SQL Reference, UPDATE STATISTICS"

• "Webcasts," http://msdn.microsoft.com/sql/community/webcasts/default.aspx

• Within MSDN Library, go to MSDN Home, SQL Server 2005 Webcasts: Many webcasts on fine tuning performance.