You are the database administrator of your company. Users report an instance of SQL Server 2008 named SQL1 is performing poorly.
You suspect that deadlocks are causing the server to perform slowly. You want to identifythe cause of the deadlocks.
To achieve this, you want to collect information about the deadlocks and save theinformation to a file. What should you do to achieve this objective?
A.
Create a SQL Server Profiler trace.
B.
Create a trace flag.
C.
Create a Policy-based Management policy.
D.
Create a System Monitor counter.
Explanation:
You should create a SQL Server Profiler trace. SQL Server Profiler is a tool that provides a graphical userinterface for monitoring an instance of the SQL Server Database Engine or Analysis Service services. SQL ServerProfiler allows you to capture and save data about an event to a table or a file for analysis. To trace deadlockevents, you should add the
Deadlock graph event class to a trace. To do this, you should create a new trace byusing SQL Server Profiler. You should perform the following steps to create a new trace:
1. Open SQL Server Profiler, select the New Trace option on the File menu, and then connect to an instanceof SQL Server.
2. In the Trace Properties dialog box, type a name for the trace in the Trace name field.
? If you want to base your trace on a template, select the template from the
Use the template drop-down list. Otherwise, select the Blank option.
? If you want to capture the trace to a file, select the Save to file check box.
Specify a value in the Setmaximum file size field to specify the maximum file size for the trace file. You can also optionallyselect the Enable file rollover option and the
Server processes trace data option.
? If you want to capture the trace to a table, select the Save to table check box. To specify thenumber of maximum rows in the table, specify a value in the Set maximum rows field.
? To stop the trace at a specified time, specify a stop date and time in the Enable trace stop time field.
3. Click the Events Selection tab.
4. Expand the Locks event category in the Events data column, and select the Deadlock graph check box.If the Locks event category is not available, select the Show all events check box to display it. The Events Extraction Settings tab will be added to the Trace Properties dialog box.
5. Select the Save Deadlock XML Events Separately option on the Events Extraction Settings tab.
6. Specify a file name for the trace file in the Save As dialog box.
7. Select the All Deadlock XML batches in a single file option to save all deadlock graph events in a singleExtensible Markup Language (XML) file, or select the Each Deadlock XML batch in a distinct file optionto create a new XML file for each deadlock graph.You cannot create a new trace flag in SQL Server 2008. Trace flags can be used to diagnose performance issuesor debug stored procedures. Trace flags allow you to set particular characteristics of a server on a temporarybasis or to turn off specific behavior. SQL Server contains some existing trace flags that can be used for thispurpose. SQL Server 2008 does not allow you to create new trace flags. You should not create a Policy-based Management policy because it cannot be used to collect information aboutdeadlock events and save the information to a file. Policy-Based Management policies manage entities, such asdatabases or other SQL Server objects, on an instance of SQL Server 2008. You should not create a System Monitor counter. System Monitor is primarily used for monitoring resource usageassociated with server processes. A System Monitor counter does not allow you to capture information aboutdeadlock events and save the information to a file.Objective:
Monitoring and Troubleshooting SQL ServerSub-Objective:
Identify concurrency problems.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance > Performance Monitoring and Tuning How-to Topics > Server Performance and Activity Monitoring How-to Topics> How to: Save Deadlock Graphs (SQL Server Profiler) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance > Monitoringand Tuning for Performance > Tools for Performance Monitoring and Tuning