Microsoft-PRO Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 Set 4

You administer a SQL Server 2008 Enterprise Edition instance that hosts a large database. The database uses the simple-recovery model. The database contains the following three additional filegroups: FileGroupA that is read/write FileGroupB that is read-only FileGroupC that is read-only FilegroupB contains less critical data than FilegroupC. You discover that the PRIMARY filegroup and FileGroupB have failed. You need to recover the database in the minimum possible time. You also need to ensure that data is available as soon as possible. What should you do?

You administer a SQL Server 2008 instance that hosts a large database. The following backup strategy is used for the database: A full database backup is performed at 02:00 hr every Monday. A differential backup is performed at 23:00 hr every day. A transaction log backup is performed on an hourly basis. A power failure on Thursday causes the SQL Server 2008 server to restart at 09:15 hr. Fifteen minutes after the server restarts, the users report that they are unable to execute certain queries that access customer data. You discover that the customer data is unmodified after the power failure. When you execute the DBCC CHECKDB command on the database, you receive the following error message: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed. See other errors for d etails. Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF- > bstat)) failed. Values are 16928422 and -8. CHECKDB found 0 allocatio n errors and 2 consistency errors in table 'tbl_Customer' (object ID 2814307832). When you execute the sp_help 'tbl_customer' stored procedure you receive the following result set: index_name index_description PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY You need to ensure that the data is available as quickly as possible with minimal effect on users. What should you do?

You administer a SQL Server 2008 infrastructure. Your company requires capacity planning information. You need to design a long-term infrastructure monitoring strategy. Which two tasks should you include in your design? (Each correct answer presents part of the solution. Choose two.)

You administer a SQL Server 2008 instance. Customers report server performance degradation because of a newly implemented process. You use Dynamic Management Views to verify that there are no long running queries. You need to correlate the operating system performance data with the actual query execution trace by using minimum administrative effort. What should you do?

You administer a SQL Server 2008 instance. The instance is using a 32-bit version on a Windows Server 2008 64-bit server. The awe enabled option is enabled. The instance will experience a predictable increase in query activity. You plan to ascertain the appropriate time when the migration of the databases to a 64-bit SQL Server 2008 server on the same hardware is beneficial. You need to identify a data collector type that provides the appropriate information. Which collector should you use?

You administer a SQL Server 2008 infrastructure. You discover that an instance experiences performance degradation for the following reasons: Excessive CPU usage Server processes paging Deadlocks You need to design a monitoring solution that can provide data, including detailed deadlock information, to monitor and troubleshoot performance issues. You want to achieve this goal by using the minimum amount of administrative effort. What tool should you use?

You administer a SQL Server 2008 instance. You plan to design a monitoring solution for the instance to monitor object usage statistics. The solution must identify a list of first 10 objects for each of the following components: Most frequently executed stored procedures and functions Long running Transact-SQL statements You need to implement the monitoring solution to minimize performance effect by using the least amount of administrative effort. What should you do?

You administer a SQL Server 2008 infrastructure. A financial application uses several instances across multiple servers. The majority of queries require distributed transactions across all servers. The application administrator reports that the application experiences excessive deadlocks. You need to design a data collector type that provides the appropriate information for remote servers. What collector should you use?

You administer a SQL Server 2008 infrastructure. The instance contains a database required for the day-to-day business of your company. Users experience slow response time when they execute reports. You plan to design a performance monitoring strategy that captures and stores the following data: Executed Transact-SQL statements and query activity. Blocking and deadlock information. Counters for disk, CPU, and memory. You need to implement the monitoring process by using the minimum amount of administrative effort. What should you do?

You administer a SQL Server 2008 infrastructure. You plan to design a maintenance strategy for a mission-critical database that includes a large table named Orders. The design plan includes index maintenance operations. You must design the strategy after considering the following facts: The Orders table in the database is constantly accessed. New rows are frequently added to the Orders table. The average fragmentation for the clustered index of the Orders table is less than 2 percent. The Orders table includes a column of the xml data type. You need to implement the strategy so that the performance of the queries on the table is optimized. What should you do?