D. Because the table structure has recently been altered, there is a good possibility that this change has caused the indexing information to become unstable or that statistics affecting the index have not been updated. If you restart the service, SQL Server should then update the statistical information accordingly, but this may affect use of the server, so it may not be a possibility. After the restart, you might want to ensure that all statistics are intact. You should also consider index fragmentation as a possible source of the problem. For more information, see the section "Using the Database Console Command (DBCC)."
2.
B. To perform analysis of this type, you use SQL Server Profiler to gather detailed serverwide information. Activity Monitor and System Monitor do not provide any details of commands being used. Event Viewer only reports on locking if it causes errors and is therefore not suitable in this situation. The Database Engine Tuning Advisor is for database structure analysis, not procedure execution.
3.
B. Partitioned views, and specifically distributed partitioned views, may improve the data access for Tom's database. Because a large number of rows could be separated by sales division per server (with one server maintaining information for two divisions), Tom's database tables may be ideal candidates for distributed partitioned views. For more information, see the section "Data Partitioning Across Servers."
4.
A. The CHECKCATALOG option checks for consistencies in system table storage and does not help much in a production database. CHECKDB checks the allocation and structural integrity of all the objects in the database, not just the tables affected. CHECKTABLE could be used, but only to check one table as a time. CHECKFILEGROUP checks the allocation and structural integrity of tables in a file or filegroup. For more information, see the section "Using the Database Console Command (DBCC)."
5.
B.MSAS 2005:Locks and any MSAS counters deal only with analysis services and would not be appropriate for production databases. Distributed Transaction Coordinator counters would only provide information for two-phase commit-style processing across multiple databases. SQLAgent counters have nothing to do with database usage. SQLServer counters pertain to the database engine and the objects controlled by the engine. For more information, see the section "Locks, Blocks, and Deadlocks."
6.
C. No Partitioning would not perform the desired analysis. Aligned Partitioning would limit partition analysis to what is currently set up. To analyze all partitioning options to achieve the best performance from partitioning, you should select Full Partitioning. For more information, see the section "Data Partitioning Across Servers."
7.
B. The Database Engine Tuning Advisor can perform the analysis from one or more databases within a single workload file without the need for any other alterations. For more information see the section "Using the DTA."
