You are the database administrator of your company. You are creating a new trace on an instance of SQL Server2008 named SQL1.
You want to capture events when a compiled plan is cached for the first time, recompiled, orevicted from the plan cache.
You navigate to the Events Selection tab in the Trace Properties dialog box. Which event class should you monitor?

A.
Plan Guide Successful

B.
Performance Statistics

C.
Showplan All

D.
Plan Guide Unsuccessful

Explanation:

You should monitor the Performance Statistics event class. You can create a new trace in SQL Server Profilerto capture events as they occur on an instance of SQL Server 2008. The Events Selection tab in the TraceProperties dialog box contains a complete list of event classes and their event categories that you can configureto capture information about specific events. The Performance Statistics event class under the Performance event category allows you to capture events that occur when a compiled plan is cached for the first time,recompiled, or evicted from the plan cache. You can also configure Resource Governor to detect queries that have exceeded the CPU threshold value. To dothis, you should configure the CPU threshold exceeded event class under the Errors and Warnings eventcategory. You should not monitor the Plan Guide Successful , Showplan All , or Plan Guide Unsuccessful event classes because these event classes cannot be used to capture events when a compiled plan is cached for the first time,recompiled, or evicted from the plan cache. The
Plan Guide Successful event class captures events when SQLServer successfully produces an execution plan for a query or batch that contained a plan guide. The ShowplanAll
event class displays the query plan along with complete compile-time details of the SQL statement that isbeing executed. The Plan Guide Unsuccessful event class captures events when SQL Server is unsuccessful inproducing an execution plan for a query or batch that is contained a plan guide.

Objective:
Optimizing SQL Server Performance

Sub-Objective:
Use Performance Studio.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Performance Event Category > Performance Statistics EventClass MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference