A good trace to debug with an sp duration (to limit the results – which can be overwhelming – and check for long running queries):
Errors and Warnings:
AttentionBackground Job ErrorDatabase Suspect Data PageErrorLogEventLogExceptionExecution WarningsUser Error Message
Security Audit
Audit Login Failed
Stored Procedures
SP:CompletedSP:StartingSP:StmtCompleted
TSQL
SQL:StmtCompleted
Filter
Duration: Greater than 5000 (5 seconds – works only on a completed sp)
To filter columns: http://technet.microsoft.com/en-us/library/ms175520.aspx
