Ancillary Exam
1. One of your users tried to execute a query that is part of a custom application. When the user tried to run the query they got an error and they need your help. You were already looking into another problem, so you happen to have Activity Monitor open already, but you do not see any activity for the user that is requesting your assistance. Why not?
A. The user disconnected too quickly, so they are no longer listed
B. The application never actually connected to SQL Server.
C. You need to select the database that the user is trying to access before you will see their activity
D. You need to refresh Activity Monitor because it is not a real-time view
Answer: D
Activity Monitor is not a real-time view of current activity because that would place a tremendous burden on the SQL Server. You need to refresh Activity Monitor before new activity is displayed. Also, Activity Monitor monitors server activity, so you cannot select a specific database when using it.
Chapter: 1
2. Which of these system stored procedures would you use to return information regarding CPU usage?
A. sp_monitor
B. sp_activity
C. sp_cpu
D. sp_usage
Answer: A
The sp_monitor stored procedure returns, among other things, information on CPU usage. The other stored procedures listed do not exist.
Chapter: 1
3. The developers in your company have recently created several new user defined functions and stored procedures, some of which use CLR integration. They are concerned that one of the functions may not be executing when it is called. How can you tell if the CLR function is running?
A. Run the sys.dm_clr_tasks system stored procedure.
B. Open the sys.dm_clr_tasks dynamic management view.
C. Open the sys.clr_tasks system view.
D. Open the clr_tasks table from the master database.
Answer: B
The sys.dm_clr_tasks dynamic management view will give you a list of all running CLR tasks. None of the other options even exist.
Chapter: 1
4. You are the database administrator for a small company with a single SQL Server. This morning, your users started complaining of slow query response times against the Accounting database. They need to get some very important work done by the end of the day so you need to get SQL Server running at capacity again quickly. Where should you look to determine the cause of the slow down?
A. Verify that the system resources are not being depleted by looking in System Monitor.
B. Run the Database Tuning Advisor against the Accounting database to increase performance.
C. Verify that there are not too many users connected to the system by looking in the Current Activity Monitor.
D. Execute a trace in Profiler based on the Performance template.
Answer: A
System Monitor will tell you if your system resources are being depleted too quickly, which is usually the cause of a slowdown. You should look at the operating system first, SQL Server second.
Chapter: 1
5. Some of your users have been complaining of intermittent problems with a query. You have decided that the best way to track down the problem is execute a trace in Profiler for a day and execute the trace against your test system. Which template should you base your trace on?
A. TSQL_Playback.
B. Standard.
C. TSQL.
D. TSQL_Replay.
Answer: D
The TSQL_Replay template is specifically designed for capturing a trace that can be executed against another SQL Server. Also, the TSQL_Playback template does not exist.
Chapter: 1
6. Your database developers have created a new database that is not performing as well as expected. You suspect that the developers did not create all of the indexes necessary to ensure proper performance. Which dynamic management views could you use to determine if the database would benefit from new indexes (select all that apply)?
A. sys.dm_db_missing_index_group_stats
B. sys.dm_db_missing_index_groups
C. sys.dm_db_missing_index_details
D. sys.dm_db_missing_index_columns
Answer: A,B,C,D
All of the dynamic management views listed could help you determine if the database would benefit from having new indexes created.
Chapter: 2
7. In an effort to analyze system performance, you retrieved data from several of the dynamic management views that are designed you display information on missing indexes. You neglected to record the information before a scheduled reboot though. When the system reboots, you try to retrieve the data from the same DMVs, but the queries do not return any information. What happened?
A. Database indexes are automatically reorganized when SQL Server starts up, so the DMVs will show that there are no new indexes to create after a restart.
B. Database indexes are automatically reindexed when SQL Server starts up, so the DMVs will show that there are no new indexes to create after a restart.
C. DMVs are memory only structures, so they are empty when SQL Server first starts up.
D. The DMVs that deal with missing indexes store their reference data in TempDB, which is emptied when SQL Server is started up.
Answer: C
Dynamic management views are memory only structures, so they are empty when SQL Server first starts up. So you will need to export any data that you need to keep before restarting SQL Server.
Chapter: 2
8. You have a table that contains sales data which is updated frequently throughout the day. You want to ensure that there is enough free space in the leaf nodes of the index to insert new data throughout the day. How should you create the index to make sure that 10 percent of each leaf node is reserved for new data?
A. Create the index using the PAD_INDEX(10) function.
B. Create the index with a 90 percent fill factor.
C. Create the index with a 10 percent fill factor.
D. Do nothing; SQL Server leaves 10 percent of each index page open by default.
Answer: B
Specifying a fill factor of 90 percent tells SQL Server to fill the index up to 90 percent, leaving 10 percent open. Also, PAD_INDEX(10) is not a valid option, the only valid parameters for PAD_INDEX are on and off.
Chapter: 2
9. In an effort to analyze performance on a database named SalesDept you execute the following query to check for index fragmentation:
USE SalesDept;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats (db_id(),
object_id('Sales.SalesOrderDetail'),
1, null, 'LIMITED');
You receive this result:
INDEX_ID AVG_FRAGMENTATION_IN_PERCENT
1 31.06
Assuming that index 1 is named PK_IDX_Sales with a fill factor of 75 percent, what should you do to optimize this index?
A. ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD WITH (FILLFACTOR = 75, ONLINE = ON, STATISTICS_NORECOMPUTE = ON)
B. ALTER INDEX PK_IDX_Sales ON Sales.SalesOrderDetail REORGANIZE
C. ALTER INDEX PK_IDX_Sales ON Sales.SalesOrderDetail REBUILD WITH (FILLFACTOR = 75, ONLINE = ON, STATISTICS_NORECOMPUTE = ON)
D. ALTER INDEX ALL ON Sales.SalesOrderDetail REORGANIZE
Answer: C
Ten percent or less fragmentation requires no action because it is acceptable. On the other hand, 10 to 30 percent requires an index reorganization, and higher than 30 percent requires a rebuild. This is 31 percent fragmented, so it should be rebuilt; however, you do not want to rebuild all the indexes—just the one that is fragmented—so ALTER INDEX ALL is not required.
Chapter: 2
10. You are analyzing performance of this query:
USE SalesDept;
SELECT C.LastName, C.Address, P.ItemName, S.Total,
FROM Sales S
JOIN Customers C ON S.CID = C.CID
JOIN Products P ON S.PID = P.PID
You want to make sure that the tables are joined in the order that they appear in the query. What query hint can you use to ensure this?
A. FORCE JOIN ORDER
B. ORDER JOINS
C. FORCE ORDER
D. USE JOIN ORDER
Answer: C
The FORCE ORDER query hint forces SQL Server to join the tables in the query in the order they appear. None of the other options exist.
Chapter: 2
11. You have a very large database that is several hundred gigabytes in size. You perform a full backup of the database every Saturday, differentials throughout the week, and transaction log backups every 4 hours. On Tuesday at 10:00 AM, one of your users tried to perform a query and received an error message. Upon investigation, you found that one of the pages in the database is corrupt. What can you do to repair the corrupt page in the database as quickly as possible?
A. Restore the most recent differential backup using the PAGE parameter.
B. Restore the most recent transaction log backup using the PAGE parameter.
C. Restore the most recent full backup using the PAGE parameter.
D. You must complete the entire restore process, you cannot restore a single page.
Answer: A
You can restore the most recent differential backup using the PAGE parameter, you just need to find the page id of the corrupt page from the suspect_pages table in the MSDB database first.
Chapter: 3
12. You have a database that is approximately 350MB and you need to be able to recover the database to a specific point in time. Which recovery model should you use for this database?
A. Bulk-logged
B. Full
C. Simple
D. Transactional
Answer: B
The only recovery model that will allow you to back up enough of the log to do a point-in-time restore is the full recovery model. Also, there is no transactional recovery model.
Chapter: 3
13. You are the database administrator for a mid-size company with a 24-hour manufacturing plant. You need to make sure that your Manufacturing database is available at all times and that, if it crashes, you need to be able to bring it back to the most current state. This is a small database which does not change much during the day so you have decided to perform a full backup every night. What should you do to keep the transaction log from filling to capacity?
A. Set the recovery model to simple and perform a transaction log backup with the TRUNCATE_ONLY option every night immediately after the full backup.
B. Set the recovery model to bulk-logged and perform a transaction log backup with the TRUNCATE_ONLY option every night immediately after the full backup.
C. Set the recovery model to full and perform a transaction log backup with the TRUNCATE_ONLY option every night immediately after the full backup.
D. Just set the recovery model to simple, SQL Server will clear the log for you.
Answer: C
It is true that you could set the recovery model to simple and have SQL Server clear the log on every checkpoint, but then you would not be able to restore the database to the most current state in the event of a crash. The best way to keep the log clear and have complete recoverability is to perform a transaction log backup with the TRUNCATE_ONLY option immediately after the full backup.
Chapter: 3
14. You have a database that is used to store customer information and is set to use the full recovery model. You perform a full backup of the entire database once a week on Sunday. You perform a differential backup every night Monday through Saturday at 9:00 PM, and transaction log backups every hour starting at 6:00 AM until 6:00 PM. On Thursday at 8:00 AM, as soon as you get to work, you find that the database is has been down since 8:00 PM Wednesday night. What order should you restore each backup to bring the database back online? Choose all that apply and place them in order.
A. Restore the differential backup from Monday.
B. Restore the differential backup from Tuesday.
C. Restore the differential backup from Wednesday.
D. Restore each transaction log backup since the last differential backup.
E. Restore each transaction log backup since the last full backup.
F. Restore the full backup from last Sunday.
Answer: F, A, B, D
To get this database back online you need to restore the last full backup first, then you need to restore the last differential backup. Since the database went down at 8:00 PM and you run the backup at 9:00 PM then you cannot use the Wednesday backup because it didn’t happen. Finally you need to restore the transaction log backups in order since the last differential backup, not the last full backup.
Chapter: 3
15. You have a sales database which you use to store product and sales information. Your home office has requested that you send them a backup copy of this database so they can update their records. You need to send them this copy without affecting the backup strategy you are using. What should you do?
A. Make a full backup.
B. Make a copy-only backup.
C. Make a partial backup.
D. Make a differential backup.
Answer: B
A copy-only backup is perfect for this situation because it is specifically designed to make a backup without affecting the logs, so it will not affect your backup strategy in any way. A partial backup will only backup the Primary filegroup and all read/write filegroups in a database, so it is not a good choice. Full and differential backups both affect the logs and differential backups only back up changes since the last full backup.
Chapter: 3
16. You are the DBA for a medium size firm that has a single SQL Server at corporate headquarters. The server has been performing adequately for some time. Recently though, your company hired about 150 new people, each of whom required SQL Server access. Ever since these new people started your existing users have been complaining that the server is too slow. On investigation, you notice that the Pages/Sec counter is consistently at 6 and the Buffer Cache Hit Ratio count is sustained at 75. What should you do to increase performance?