Review Questions

1. 

You are the database administrator for your company responsible for a SQL Server 2005 instance. You have noticed in Task Manager that the processor utilization is at 100 percent, so you need to investigate the cause. You decide to use System Monitor to determine whether the SQL Server instance is consuming the processor resources. What performance object counter should you monitor?

1. Processor : % Processor Time

2. Processor : % Privileged Time

3. Process(sqlservr) : % Processor Time

4. Process(sqlmangr) : % Processor Time

2. 

You are the database administrator for your company. You are responsible for designing a strategy to monitor a SQL Server 2005 instance. You need to implement a mechanism that will detect CREATE TABLE statements regardless of whether the DDL statement is committed. What mechanism should you implement?

1. Implement DDL triggers.

2. Implement DML AFTER triggers.

3. Implement DML INSTEAD OF triggers.

4. Implement event notifications.

3. 

You are the database administrator for your company. You need to implement a performance monitoring strategy for a SQL Server 2005–based solution. The business has requested that your monitoring strategy should record and store performance metrics. The monitoring strategy should monitor both hardware resource utilization and the execution of stored procedures and user-defined functions within the database. Additionally, the monitoring strategy should have a minimal impact on the performance of the database solution. What tools should you use? (Each correct answer represents part of the solution. Choose two.)

1. Performance Logs and Alerts

2. System Monitor

3. SQL Trace

4. Database Engine Tuning Advisor

5. Index Tuning Wizard

6. SQL Server Profiler

4. 

You are the database administrator for your company. You are designing a strategy to monitor your SQL Server 2005 database solution. Users of the database solution have started to experience a number of deadlocks that you need to further analyze. You need to implement a mechanism that will enable you to capture deadlock graphs. What SQL Server 2005 technology should you use? (Each correct answer represents a complete solution. Choose two.)

1. Implement DDL triggers.

2. Implement DML AFTER triggers.

3. Implement DML INSTEAD OF triggers.

4. Implement event notifications.

5. Implement logon triggers.

6. Implement WMI event alerts.

5. 

You are the database administrator for your company. You are designing a strategy to monitor your quad-processor SQL Server 2005 database solution and need to set the thresholds for poor performance of the memory subsystem. What thresholds should you set to indicate poor performance? (Each correct answer presents part of the solution. Choose two.)

1. Memory : Pages/sec > 5

2. Memory : Pages/sec > 50

3. SQLServer:Buffer Manager : Buffer Cache Hit Ratio < 90

4. SQLServer:Buffer Manager : Buffer Cache Hit Ratio > 90

5. SQLServer:Access Methods : Page Splits/sec > 5

6. SQLServer:Access Methods : Page Splits/sec > 50

6. 

You are the database administrator for your company. You have used System Monitor to set up a performance monitoring solution for a SQL Server 2005 instance that has been experiencing poor performance. After running System Monitor for a week, you have the following metrics:

Open table as spreadsheet

Performance Object Counter	Minimum	Maximum	Average
Processor(_Total) : % Processor Time	45	100	77
System : Processor Queue Length	2	5	3
Memory : Available Mbytes	10	100	12
Memory : Pages/sec	13	80	69
PhysicalDisk(*) : % Disk Time	0	40	20
PhysicalDisk(*) : Avg. Disk Queue Length	0	1	0.5
Network Interface(*) : Bytes Received/sec	4000	8000	4500
Network Interface(*) : Bytes Sent/sec	6000	12000	8000

What should you do to improve performance?

1. Upgrade the memory subsystem.

2. Upgrade the processor subsystem.

3. Upgrade the network I/O subsystem.

4. Upgrade the disk subsystem.

7. 

You are the database administrator for your company responsible for administering a SQL Server 2005 instance. You are designing a strategy to monitor your database solution and want to set up a SQL Server Profiler trace to capture queries that have expensive or potentially inefficient execution plans. What SQL Server Profile trace events should you capture? (Each correct answer represents part of the solution. Choose two.)

1. Deadlock Graph

2. Exception

3. Hash Warning

4. Lock:Escalation

5. Sort Warnings

6. SP:CacheHit

8. 

You are the database administrator for your company. You need to set up a strategy to monitor a SQL Server instance that notifies you whenever a transaction log for a database is more than 65 percent full. What mechanism should you use?

1. Implement a SQL Server Performance condition alert.

2. Implement a SQL Server event alert.

3. Implement a WMI event alert.

4. Implement an event notification.

9. 

You are the database administrator for your company responsible for maintaining a SQL Server 2005 solution. The SQL Server 2005 solution is rebooted every weekend as part of a maintenance plan. Users have been experiencing a lot of deadlocks lately. You need to implement a simple monitoring strategy on the SQL Server 2005 instance that will capture deadlock information for analysis. What should you do?

1. Use SQL Server event alerts.

2. Use SQL Server performance condition alerts.

3. Use WMI event alerts.

4. Use SQL Server Profiler.

10. 

You are the database administrator for your company. You want to set up a SQL trace to capture recompilations of T-SQL statements in both ad hoc batches and stored procedures. What event class should you use?

1. SP:Completed

2. SP:Recompile

3. SQL:StmtCompleted

4. SQL:StmtRecompile

11. 

You are the database administrator for your company. You need to set up a strategy to monitor a SQL Server instance that notifies you whenever a transaction log for a database is full. What mechanism should you use?

1. Implement a SQL Server performance condition alert.

2. Implement a SQL Server event alert.

3. Implement a DDL INSTEAD OF trigger.

4. Implement a DDL AFTER trigger.

12. 

You are the database administrator for your company. You support a mission-critical database solution running on a SQL Server 2005 instance. The database solution has 10,000 users connected to it at any given time. Certain critical stored procedures have been written that have a different timeout from SQL Server 2005’s default. You anticipate the database solution will have a lot of contention. Consequently, you want to capture deadlock information in an XML format and monitor the critical stored procedures that have timeouts because of contention. What SQL trace event classes should you monitor? (Each correct answer represents part of the solution. Choose two.)

1. Create a SQL trace that monitors the Lock:Escalation event class.

2. Create a SQL trace that monitors the Lock:Timeout event class.

3. Create a SQL trace that monitors the Lock: Timeout (timeout > 0) event class.

4. Create a SQL trace that monitors the Deadlock Graph event class.

5. Create a SQL trace that monitors the Lock:Deadlock Chain event class.

6. Create a SQL trace that monitors the Lock:Deadlock event class.

13. 

You are the database administrator for your company. You are responsible for designing a strategy to monitor a SQL Server 2005 instance. You need to implement a mechanism that will detect any new databases that are created on the SQL Server 2005 instance. What mechanism should you implement?

1. Implement DDL triggers.

2. Implement DML AFTER triggers.

3. Implement DML INSTEAD OF triggers.

4. Implement event notifications.

14. 

You are the database administrator for your company. You have used System Monitor to set up a performance monitoring solution for a SQL Server 2005 instance that has been experiencing poor performance. After running System Monitor for a week, you have the following metrics:

Open table as spreadsheet

Performance Object Counter	Minimum	Maximum	Average
Processor(_Total) : % Processor Time	66	100	69
System : Processor Queue Length	3	5	4
Memory : Available Mbytes	10	100	16
Memory : Pages/sec	0	13	4
PhysicalDisk(*) : % Disk Time	2	80	65
PhysicalDisk(*) : Avg. Disk Queue Length	0	13	4
Network Interface(*) : Bytes Received/sec	3000	6000	400
Network Interface(*) : Bytes Sent/sec	5000	10000	9000

What should you do to improve performance?

1. Upgrade the memory subsystem.

2. Upgrade the processor subsystem.

3. Upgrade the network I/O subsystem.

4. Upgrade the disk subsystem.

15. 

You are the database administrator for your company. You are responsible for designing a strategy to monitor a mission-critical SQL Server 2005 database solution where performance is important. The database solution has a number of complex triggers that create tables internally. You need to implement a mechanism that monitors these DDL operations. Any monitoring solution must not impact the trigger performance. What mechanism should you implement? (Each correct answer represents a complete solution. Choose two.)

1. Implement DDL triggers.

2. Implement DML AFTER triggers.

3. Implement DML INSTEAD OF triggers.

4. Implement event notifications.

5. Implement logon triggers.

6. Implement WMI event alerts.

16. 

You are the database administrator for your company. You are designing a strategy to monitor your uniprocessor SQL Server 2005 database solution and need to set the thresholds for poor performance of the processor subsystem. What thresholds should you set to indicate poor performance? (Each correct answer represents part of the solution. Choose three.)

1. Processor(_Total) : % Processor Time > 80

2. Processor(_Total) : % Processor Time > 20

3. SQLServer:General Statistics : Processes blocked > 10

4. SQLServer:General Statistics : Processes blocked > 2

5. System : Context Switches/sec > 100

6. System : Context Switches/sec > 1000

7. System : Processor Queue Length > 2

8. System : Processor Queue Length > 10

17. 

You are the database administrator for your company. You have decided to implement a SQL trace on a production SQL Server 2005 instance that is having some performance problems. You have defined the trace. What stored procedure do you use to add events to the SQL trace?

1. sp_trace_create

2. sp_trace_setevent

3. sp_trace_setfilter

4. sp_trace_generateevent

18. 

You are the database administrator for your company. You support a mission-critical database solution running on a SQL Server 2005 instance. The database solution is currently 800GB in size and grows at the rate of 100GB a quarter. Additional disk drive space is continually being added to the disk array. You need to ensure that the database solution does not fail because of a lack of disk drive space or the transaction log running out of space. You want to be alerted whenever there is less than 100MB of disk space available or whenever there is less than 10 percent space free in the transaction log. What performance condition alerts should you implement? (Each correct answer represents part of the solution. Choose two.)

1. Create a SQL Server performance condition alert based on the PhysicalDisk(*) : Avg. Disk Queue Length performance object counter.

2. Create a SQL Server performance condition alert based on the LogicalDisk(*) : % Free Space performance object counter.

3. Create a SQL Server performance condition alert based on the LogicalDisk(*) : Free Megabytes performance object counter.

4. Create a SQL Server performance condition alert based on the SQLServer:Databases : Percent Log Used performance object counter.

5. Create a SQL Server performance condition alert based on the SQLServer:Databases : Data File(s) Size (KB) performance object counter.

6. Create a SQL Server performance condition alert based on the SQLServer:Databases : Log Flush Waits/sec performance object counter.

19. 

You are the database administrator for your company responsible for developing a performance monitoring strategy for a database solution based on SQL Server 2005. You have been tasked with developing a proactive performance monitoring solution that will enable the DBA team to work out whether performance has degraded in the future and to capacity plan resources. What is the first thing you should do?

1. Create a baseline.

2. Create a benchmark.

3. Define SQL Server performance condition alerts.

4. Create a unit test plan for the stored procedures and views within the database.

20. 

You are the database administrator for your company. You are responsible for designing a strategy to monitor a SQL Server 2005 instance. Your monitoring solution must be able to detect whenever an extra data file is added to a database and whenever a database’s data file automatically grows. How can you accomplish these requirements? (Each correct answer represents part of the solution. Choose two.)

1. Implement DDL triggers.

2. Implement DML AFTER triggers.

3. Implement DML INSTEAD OF triggers.

4. Implement event notifications.

Answers

1.	C. Monitoring the Process(sqlservr) : % Processor Time performance object counter will indicate whether SQL Server 2005 or some other process is consuming your processor resources.
2.	D. Event notifications can detect the CREATE TABLE T-SQL statements and cannot be rolled back. DDL triggers are not appropriate because they are processed synchronously within the transaction and can be rolled back.
3.	A, C. The SQL Trace and Performance Logs and Alerts tools will allow you to monitor both the hardware resource and the database activity while impacting the performance of the database solution the least.
4.	D, F. Both event notifications and WMI event alerts allow you to capture the DEADLOCK_ GRAPH events for further analysis. DDL triggers enable you to trap only DDL statements. DML triggers enable you to trap only INSERT, DELETE, and UPDATE statements. Logon triggers enable you to trap only LOGON events.
5.	A, C. The Memory : Pages/sec > 5 threshold would indicate external memory pressure where the Windows operating system has resorted to excessive paging. The SQLServer:Buffer Manager : Buffer Cache Hit Ratio < 90 threshold would indicate some sort of internal memory pressure, because SQL Server 2005 is not finding the data pages requested from the buffer pool and is having to wait for disk reads to satisfy the requests.
6.	A. An average value of 12 for the Memory : Available Mbytes performance object counter would indicate that memory is the bottleneck for the SQL Server instance. The other sub-system’s performance object counters would seem to be in acceptable thresholds.
7.	C, E. The Hash Warning and Sort Warnings SQL Server Profiler event classes indicate expensive operations such as hash bailouts and sorts that do not fit into memory.
8.	A. A SQL Server performance condition alert will enable you to fire an alert when the SQLServer:Databases : Percent Log Used performance object counter is greater than 65 percent full.
9.	C. The easiest solution is to implement WMI event alerts to capture deadlock information. SQL Server event and performance condition alerts will not capture deadlock information. SQL Server Profiler cannot be run on the SQL Server 2005 instance because it will stop each time the SQL Server instance is rebooted.
10.	D. The SQL:StmtRecompile event class allows you to capture recompilations for statement-level recompilations caused by ad hoc batches and stored procedures.
11.	A. A SQL Server event alert will enable you to fire an alert when the 9002 error is generated, indicating a full transaction log.
12.	C, D. The Lock:Timeout (timeout > 0) event class indicates that a request for a lock on a resource has timed out for T-SQL statements whose timeout value is not 0. The Deadlock Graph event class generates an XML description of a deadlock.
13.	D. Event notifications can detect the CREATE DATABASE T-SQL statement. DDL triggers cannot fire against asynchronous, nontransacted T-SQL statements such as in the case of CREATE DATABASE.
14.	D. A value of 4 for the PhysicalDisk(*) : Avg. Disk Queue Length performance object counter would indicate a disk I/O bottleneck. This is further collaborated by the PhysicalDisk(*) : % Disk Time performance object counter. The rest of the performance object counter metrics would seem to be within normal operational norms.
15.	D, F. Both event notifications and WMI event alerts can detect the CREATE TABLE T-SQL statements. DDL triggers are not appropriate; they are processed synchronously within the transaction and thus can affect performance.
16.	A, F, H. The Processor(_Total) : % Processor Time > 80, System : Context Switches/sec > 1000, and System : Processor Queue Length > 10 thresholds would all indicate that the processor is the bottleneck on your SQL Server 2005 database solution.
17.	B. The sp_trace_setevent system stored procedure is used to add or remove an event or event column for existing traces.
18.	C, D. The LogicalDisk(*) : Free Megabytes performance object counter will enable you to be alerted whenever the 100MB threshold it met. The SQLServer:Databases : Percent Log Used performance object counter will enable you to be alerted whenever the transaction log is running out of space. The LogicalDisk(*) : % Free Space performance object counter is not appropriate because additional disk drive space is continually being added.
19.	A. Create a baseline. Creating a baseline will enable you to see any differences in the system’s performance and resource utilization in the future as the database solution gets used. Without a baseline, there is no way of determining whether performance has degraded over a period of time.
20.	A, D. DDL triggers will allow you to trap the ALTER DATABASE T-SQL statement. Event notifications can trap the DATA_FILE_AUTO_GROW event. DML triggers can trap only INSERT, UPDATE and DELETE T-SQL statements.