• Run SQL Server Profiler

• Create new trace
  • [1] File -> [2] New Trace

• Connect to MS SQL Server
  • [1] Type your server name
  • [2] Type your credentials
  • [3] Click “Connect”

• Configure options on the “General” tab
  • [1] Choose template -> Tuning
  • [2] Check “Save to File” (not recommended to save trace on system hard drive or hard drive with transaction log or hard drive with database)
  • [3] “Set maximum file size (MB):” = 200
  • [4] Check “Enable file rollover” (checked by default)

• Configure options on the “Events Selection” tab
  • [1] Uncheck “SP:StmtCompleted”

Add “Deadlock graph”

• [1] Check “Show all events”
• [2] Choose “Locks”
• [3] Choose “Deadlock graph”
• [4] Uncheck “Show all events”

Check “Show all columns”

Make sure following columns are checked:

Events Locks -> Deadlock graph:

• LoginName (checked by default)
• SPID (checked by default)
• StartTime
• TextData (checked by default)
• TransactionID

Events Stored Procedures -> RPC:Completed:

• ApplicationName
• CPU
• DatabaseID (checked by default)
• DatabaseName (checked by default)
• Duration (checked by default)
• EndTime
• Error
• LoginName (checked by default)
• Reads
• RowCounts
• SPID (checked by default)
• StartTime
• TextData (checked by default)
• TransactionID
• ObjectName

Event TSQL -> SQL:BatchCompleted:

• ApplicationName
• CPU
• DatabaseID (checked by default)
• DatabaseName (checked by default)
• Duration (checked by default)
• EndTime
• Error
• LoginName (checked by default)
• Reads
• RowCounts
• SPID (checked by default)
• StartTime
• TextData (checked by default)
• TransactionID

• Run trace
  • [1] Click “Run”

• Finish tracing

To finish trace click [1]“Stop” and [2]“File” -> [3] “Close”

From my SQL Server DBA handbook.