You are a database administrator for AIOTestKing.com. You are responsible for monitoring the performance of your SQL Server 2005 computers. You have a SQL Server Integration Services (SSIS) package that usually takes 10 minutes to run. Today, the package is taking more than one hour to run. You suspect memory is too low. You need to identify the problem. What should you do?

You are a database administrator for AIOTestKing.com. Your company is a large retail store chain with more than 500 outlets. Each retail store maintains its own SQL Server 2005 database that must replicate data to the corporate server each night. You need to monitor replication for each store. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

You are a database administrator for AIOTestKing.com. Users report that the performance of a SQL Server 2005 application seems slow. You verify that the SQL Server computers CPU utilization, disk utilization, and memory utilization are normal. You also verify that the network is operating normally. You need to examine more detailed performance information for the appropriate instance of SQL Server 2005. What should you do?

You work as DBA at AIOTestKing.com. You administer a SQL Server 2005 computer. The performance of the server has degraded to a great extent over time. You are troubleshooting the indexing environment. You rebuild the clustered indexes that are used the most frequently. You want to optimize the most frequently accessed nonclustered index. You access the sys.dm_db_index_usage_stats dynamic management view (DMV). The results are shown in the following table.

You need to identify which nonclustered index is being used the most frequently when the entire index is not read. Which object_id and index_id combination should you use?

You are a database administrator for AIOTestKing.com. The company uses a SQL Server 2005 database. A full backup of the database is made each night. Transaction log backups are made each morning and each afternoon. You notice that the database transaction log file grows very large throughout the day. You need to minimize the size of the transaction log file. You also need to minimize data loss in the event of a failure. What should you do?

You are a database administrator for AIOTestKing.com. You run SQL Server Profiler by using the SP:Recompile event class. You notice a large number of recompiles occurring.
You need to identify the possible cause of the large number of recompiles. What is the most likely cause of the problem?

You are the database administrator for AIOTestKing.com. The company uses a database that contains a table named Sales. The CREATE statement for the Sales table is shown in the exhibit.

The Sales table is very write-intensive and is commonly used in queries. Both of the indexes on the Sales table become fragmented within the first two hours of being rebuilt.
Both leaf and intermediate level pages split frequently. There is sufficient disk space available to fully optimize the indexes. You need to optimize the nonclustered index on the Sales_Amount column of the Sales table. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You are a database administrator for AIOTestKing.com. You administer a SQL Server 2005 database named Contracts. The Contracts database becomes corrupted. You perform a restore by using the following Transact-SQL script.
RESTORE DATABASE Contracts FROM contracts_bu_device WITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY After the restore, the SQL Server logs show errors in some of the page restores. You need to repair the database pages that are corrupt. You need to achieve this goal by using the method that facilitates the fastest possible repair. Which Transact-SQL statement should you use?

You are a database administrator for AIOTestKing.com. Users report that a SQL Server 2005 application is performing poorly. You use System Monitor and discover that disk activity on the volumes that contain the database data files is very high. You need to find out which SQL Server activity is causing the high disk activity. You want to accomplish this task by using SQL Server Profiler. What should you do?

You are a database administrator for AIOTestKing.com. You have separate SQL Server 2005 development and production environments. You use the Business Intelligence Development Studio to create a SQL Server Integration Services (SSIS) package in your development environment. Then, you use the SSIS package to import data into your development environment from one of your company’s trading partners. You need to deploy the SSIS package to your production environment. Your production environment uses different table names than your development environment. What should you do?