Review Questions

1.	You are concerned about poor performance of a table in your database that users are running range queries on. You have queried sys.dm_db_index_physical_stats and discovered that the avg_fragmentation_in_percent value for the table’s index is 75. You have also run DBCC SHOW_STATISTICS and noticed that they have not been updated for a week. What should you do to improve performance? Update the statistics. Defrag the index. Drop and create the statistics using a full scan. Rebuild the index.
2.	You are responsible for managing 100 SSRS instances in your organization. You want to make a configuration change on all the SSRS instances. What is the best tool to achieve this? rs.exe rsconfig.exe SQL Server Configuration Manager Reporting Services Configuration Manager
3.	What tool should you recommend for a junior DBA to use to create a simple backup strategy for a database? Database Engine Tuning Advisor SQL Server Configuration Manager Database Maintenance Plan Wizard Database Maintenance design surface
4.	You organization has more than 200 SQL Server instances installed. Management wants to purchase a product that will allow the DBAs to monitor vital statistics about the server and databases and generate alerts that can be responded to. What product do you recommend? SQL Server Health and History Tool (SQLH2) Microsoft Operations Management (MOM) Windows Server Update Services (WSUS) Systems Management Server (SMS)
5.	You are responsible for managing a large SSRS reporting solution. Management has indicated that it expects that there will be a lot more report snapshots being generated by users. What system database should you check the size of and potentially increase? master reportserver reportservertempdb tempdb
6.	You have a VLDB running on SQL Server 2005 Standard Edition. Ten critical tables in the VLDB are in excess of 10,000,000 rows. You have noticed that whenever SQL Server updates statistics automatically on any of these 10 tables during business hours, performance degrades unacceptably. Query performance, as always, is important. You do not want automatic updating of statistics on these tables but want to schedule the appropriate command to run after hours daily. What command should you schedule? UPDATE STATISTICS ... WITH 1000 ROWS UPDATE STATISTICS ... WITH 1000 ROWS, NORECOMPUTE UPDATE STATISTICS ... WITH FULLSCAN, NORECOMPUTE UPDATE STATISTICS ... WITH FULLSCAN
7.	What SSRS utility is used to extract, restore, create, and delete the encryption keys used by SSRS? rs.exe bcp.exe rskeymgmt.exe sac.exe
8.	You want to empty one of the data files that make up your database. What command should you take advantage of? The DBCC SHRINKDB command The DBCC SHRINKFILE command The DBCC DBREINDEX command The ALTER DATABASE command
9.	What is the minimum number of target servers required for you to be able to create a master server? 0 1 10 100
10.	You are responsible for managing a large SSRS reporting solution. Management has indicated that users are complaining about the performance of subscriptions that they are supposed to be receiving every business day morning at 9 A.M. What performance object should you monitor to help you identify any potential problems? LogicalDisk Memory MSRS 2005 Web Service MSRS 2005 Windows Service
11.	You have a 100TB SalesDB database implemented on SQL Server 2005 Enterprise Edition running with 64GB of RAM and a quad-processor server. You have noticed that there are a number of database consistency errors appearing in the SQL Server error log. What command should you run first? DBCC CHECKDB ('SalesDB', REPAIR_ALLOW_DATA_LOSS) DBCC CHECKDB ('SalesDB', REPAIR_REBUILD) DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY DBCC CHECKDB ('SalesDB') WITH DATA_PURITY
12.	Your company has a SQL Server Reporting Services solution that you are responsible for managing. Users have been complaining about various errors in the Report Manager. What log file do you examine? ReportServerService_<timestamp>.log ReportServerService_main_<timestamp>.log ReportServerWebApp_<timestamp>.log ReportServer_<timestamp>.log
13.	You are designing a maintenance strategy for your database on SQL Server 2005. The OLTP database is currently 100GB in size and growing by 25 percent quarterly. You want to ensure that your database will be recoverable in the case of a disk failure, will be reliable, and will have optimal performance. You have decided to schedule these tasks nightly. What tasks should you schedule? (Choose all that apply.) Run DBCC CHECKDB on the database. Rebuild all the indexes in the database. Update statistics on all indexes in the database. Shrink the database. Back up the database.
14.	You want to manage your Reporting Services solution by adding multiple report servers in a scale-out deployment. What should you do? Use the Reporting Services Configuration Manager, and configure all your report servers to use the same report server virtual directory. Use the Reporting Services Configuration Manager, and configure all your report servers to use the same Report Manager virtual directory. Use the Reporting Services Configuration Manager, and configure each report server to the shared report server database. Use the SQL Server Surface Area Configuration tool to enable OLE Automation.
15.	You are creating a database maintenance plan and need to schedule the following tasks: a database shrink, a database consistency check, the rebuilding of all your indexes, and a full database backup. You want to be notified as soon as possible if there is a database consistency error. You also want to ensure that the backup will have a corruption-free, optimal database. What order should you perform the tasks in? (Choose the answer with the steps in the correct sequence.) DBCC SHRINKDATABASE DBCC CHECKDB DBCC DBREINDEX BACKUP DATABASE 2, 1, 3, 4 3, 2, 1, 4 4, 3, 1, 2 3, 1, 4, 2
16.	You have a 1TB SalesDB database running on SQL Server 2003 Enterprise Edition. You have noticed that the nightly DBCC CHECKDB ('SalesDB') job is starting to take too long, and you want to reduce the amount of time that the check will take. What command should you schedule to run nightly instead? DBCC CHECKDB ('SalesDB', REPAIR_ALLOW_DATA_LOSS) DBCC CHECKDB ('SalesDB', REPAIR_REBUILD) DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY DBCC CHECKDB ('SalesDB') WITH DATA_PURITY
17.	What DBCC should you run to check only the metadata of the database? DBCC CHECKALLOC DBCC CHECKCATALOG DBCC CHECKTABLE DBCC CHECKDB
18.	Your organization has 50 SQL Server 2005 instances that you want to run common maintenance tasks on. All these tasks will run only via T-SQL scripts. What strategy should you use? Use SQL Server Health and History Tool (SQLH2). Implement a multiserver administration solution. Use the Windows Server Update Services (WSUS). Use Systems Management Server (SMS) to push out the jobs as T-SQL scripts.
19.	You need plan to schedule a database consistency check, schedule a database shrink, change some passwords for a number of logins, and truncate a table. What two commands should you not run simultaneously? ALTER LOGIN DBCC SHRINKDATABASE TRUNCATE TABLE DBCC CHECKDB
20.	Users are complaining about poor query performance against the [Customers] table. You suspect that the table might be heavily fragmented because it is frequently modified throughout the day. Your junior DBA is convinced that the problems lie elsewhere. You execute the DBCC SHOWCONTIG command against the table with the following results: DBCC SHOWCONTIG scanning 'Customers' table... Table: 'Customers' (83416733); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 2264448 - Extents Scanned..............................: 285485 - Extent Switches..............................: 287092 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 99.96% [287092:287094] - Logical Scan Fragmentation ..................: 0.06% - Extent Scan Fragmentation ...................: 3.69% - Avg. Bytes Free per Page.....................: 486.8 - Avg. Page Density (full).....................: 93.96% DBCC execution completed. If DBCC printed error messages, contact your system administrator. What should you do next? Execute the DBCC INDEXDEFRAG statement against the table. Execute the DBCC DBREINDEX statement against the table. Execute the DBCC PHYSICAL_ONLY statement against the table. Tell your junior DBA they are right.

Answers

1.	D. Microsoft recommends that you rebuild the index when avg_fragmentation_in_percent is greater than 30. Rebuilding the index will also update the statistics. Rebuilding the statistics by itself will not solve the problem because the table is heavily fragmented.
2.	A. The script host utility (rs.exe) runs custom Visual Basic scripts against multiple report server instances.
3.	C. The Database Maintenance Plan Wizard is designed to create simple database maintenance plans quickly and easily. They can then be customized further through the Database Maintenance design surface if required.
4.	B. MOM allows you to implement a proactive monitoring and alerting solution against your SQL Server instances.
5.	B. The reportserver system database stores configuration/security data, subscription/schedule definitions, and report snapshots.
6.	C. The UPDATE STATISTICS ... WITH FULL SCAN command will update statistics using a full scan of the table and not automatically recomputed statistics during business hours. Option D will update statistics during business hours. Options A and B have a very poor sampling that will impact query performance.
7.	C. The rskeymgmt utility (rskeymgmt.exe) is used to extract, restore, create and delete the symmetric key used to protect sensitive report server data against unauthorized access.
8.	B. Only the DBCC SHRINKFILE command allows you to empty the contents of a specific database data file.
9.	B. To create a master server, you must at least have one target server.
10.	D. The MSRS 2005 Windows Service performance object includes a collection of counters used to track scheduled operations.
11.	B. The DBCC CHECKDB ('SalesDB', REPAIR_REBUILD) command performs repairs on the database without any risk of data loss. The REPAIR_ALLOW_DATA_LOSS option will potentially allow error loss and should be used as a last resort. The PHYSICAL_ONLY and DATA_PURITY options will not repair errors.
12.	C. The ReportServerWebApp_<timestamp>.log is the trace log for Report Manager.
13.	A, B, E. You should schedule a DBCC CHECKDB, reindex operations, and a backup for the nightly tasks. There is no need to update statistics because they will be automatically updated during the reindexing operation. There is no need to perform a shrink of the database nightly because it will not improve performance, reliability, or recoverability. It can additionally fragment the database if it is performed after the reindex operations and the database only seems to be growing.
14.	C. The Reporting Services Configuration Manager also allows you to configure a scale-out deployment of your reporting solution by connecting each report server to the shared report server database.
15.	A. The DBCC CHECKDB task should be run first as you want to be notified as soon as possible if there is a database consistency error. The DBCC REINDEX task should be run after the DBCC SHRINKDATABASE task because otherwise the database shrink will fragment the indexes. The BACKUP DATABASE task should be run last because you want the database backup to be error free and have the rebuilt indexes.
16.	C. The DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY command will not take as long to run as the DBCC CHECKDB command by itself because it does a physical check only of the database, not a logical one. The REPAIR_ALLOW_DATA_LOSS and REPAIR_REBUILD options are used for fixing errors. The DATA_PURITY option is used only to check the domain of the column.
17.	B. The DBCC CHECKCATALOG command checks the system tables (database catalog) of your database.
18.	B. The multiserver administration solution enables the DBA to control multiple SQL Server instances through a centralized SQL Server.
19.	B, D. DBCC SHRINKDATABASE and DBCC CHECKDB are both resource intensive (particularly I/O). It would not be advisable to run them simultaneously.
20.	D. The table has neither a high level of internal fragmentation (93.96 percent) nor external fragmentation (99.96 percent). The problem lies elsewhere.