Question 9

Answers D and E are correct. Both performing a query against sys.dm_db_index_physical_stats and checking the fragmentation page of the index from within SQL Server Management Studio allow you to see fragmentation as well as many other physical characteristics. While querying sys.dm_fts_indexpopulation returns information about the full-text index populations currently in progress, sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans.

For more information, see the following sources:

• Chapter 8, "Troubleshooting and Optimizing SQL Server"

• SQL Server 2005 Books Online: "SQL Server Language Reference, Transact-SQL Reference, System Views (Transact-SQL), Dynamic Management Views and Functions, Index Related Dynamic Management Views and Functions, sys.dm_db_index_physical_stats"

• "Fill Factor," http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_9ak5.asp