You are working for a small manufacturing company that has been operating well for some time. Lately, the end users have been reporting that when performing queries against information on customers, the system is growing increasingly slow. After examining the system, you determine that the table definition has recently been altered. You want the response time to improve and be similar to what it was before the change. How would you fix the problem?
A. Run DBCC DBREINDEX.
B. Drop and re-create the table's clustered index.
C. Drop and re-create all indexes.
D. Update the index statistics.
E. Stop and restart the server.
2.
You are working on a database implementation in a production environment. You would like to perform analysis on the server hosting the database. You need to get detailed information on the types of queries being performed and the locking effects of all operations. Which tool should you use?
A. Event Viewer
B. SQL Server Profiler
C. System Monitor
D. Activity Monitor
E. Database Engine Tuning Advisor
3.
Tom needs to achieve optimum performance within his application. He is examining different view technologies as a way to improve data access performance. He is currently managing a database that is replicated between five servers (for load balancing) and contains customer information for six sales divisions. The Customer Purchase table currently contains a consolidated list of 100,000,000 customer records. What could Tom try to implement to improve server performance?
A. Indexed views
B. Partitioned views
C. Complex views
D. Full outer views
4.
After a system crash, one of your production databases is experiencing problems. The problems center around a group of tables that have all been assigned to the same data file in a partitioned environment. None of the tables in other files seem to be affected. Which of the following will assist in troubleshooting the issue further?
A.DBCC CHECKFILEGROUP
B.DBCC CHECKCATALOG
C.DBCC CHECKDB
D.DBCC CHECKTABLE
5.
You are using System Monitor to diagnose performance issues on a server. You would like to see if there are any locking issues within one of your production databases. The two applications that use the database both utilize transactions to perform their updates. From which counter category would you select?
A.MSAS 2005:Locks
B.SQLServer:Locks
C.Distributed Transaction Coordinator
D.SQLAgent:JobSteps
6.
You are using the Database Engine Tuning Advisor to analyze a workload file. You would like to perform a test to determine the optimum settings for partitioning to achieve the best performance. Which option should you select?
A. No partitioning
B. Aligned partitioning
C. Full partitioning
D. Keep aligned partitioning
7.
You have used the SQL Server Profiler with the Tuning template against all the activity on a server over a period when the server activity was at its highest. The activity is saved to a table. You would like to analyze the session, but you are interested in only a single database. How would you extract and analyze the appropriate events?
A. Re-create the session and set a filter for the desired database when running SQL Server Profiler.
B. Use the Database Engine Tuning Advisor and select only the desired database on the General tab.
C. Open the session in SQL Server Profiler, set a filter for the desired database, and export the T-SQL events.
D. Open the session in SQL Server Profiler, set a filter for the desired database, and save the file as a new trace table.
