Create a CRM profiler template and trace for CRM issues
You might have experienced this situation: You tried to investigate a CRM problem in your environment and in order to do this you took a SQL Profiler trace.
But choosing the standard tracing template didn’t capture all the relevant information and the trace was useless.
So, what do we need to capture a good, relevant SQL Profiler trace ?
Before you capture a SQL Profiler trace, you might want to generate a SQL Profiler template that contains the necessary tracing information.
We differentiate here between two specific issue you might have within your CRM environment:
a) CRM errors (e.g. SQL errors) and CRM performance issue (on SQL Server side)
b) CRM Deadlocking and Blocking issues (on SQL Server side)
After deciding which problem category your issue relates to (issue a or issue b?), you need to generate a SQL profiler template like this:
1) Start SQL Profiler
2) Click on File è Templates è New Template
3) In general tab: select the correct SQL version and a name for the template
4) Most import is the event selection tab.
4a) For problem category (a) , you should activate these events:
- Tick all events beneath “Errors and Warnings”
- Tick all events beneath “TSQL”
- Tick these events beneath “Stored Procedures”:
RPC:* (all RPC events)
SP.Starting
SP.Completed
Stmt:Starting
Stmt:Completed
4b) For problem category (b) , you take all the events the category (a) contains and this additional ones:
- Tick these events beneath “Locks”:
Deadlock Graph
Lock:Deadlock
Lock:Deadlock Chain
Lock:Escalation
Lock:Timeout
Lock:Timeout (timeout >0)
Now you are ready to save your SQL profiler template.
You now want to capture a SQL profiler trace using this template during problem reproduction.
This is what you need to do:
1. Start SQL Profiler
2. Connect to the correct SQL Server instance (the instance that contains your <ORG>_MSCRM database)
3. Click on File and choose “New Trace” in order to create a new trace
3.1. Choose the template that you created before (see above) – beneath drop-down “use the template”
3.2. Tick the option to “save the trace to file”
3.3. Raise the default file size from 5 MB to 100 MB
4. Start the trace and stop it immediately by clicking the red square
5. Do an IISReset (only if possible – means if you can still reproduce the issue after IISreset – some performance issues are gone after IISreset)
6. Prepare your problem reproduction in CRM so that you are only “one click” away from reproducing (in order to keep the trace small)
7. Start the SQL Profiler trace (click the green arrow) – you don’t need to save the trace from the first start (4)
8. Reproduce the issue now.
9. Stop the SQL Profile trace immediately after reproduction è It should not become too large
10. Save the trace once again under a new file second file name (different from 3.2) for security reason – then you won’t loose any information
Now you are prepared to analyze your SQL profiler trace yourself or to send it to our CRM support team in order to investigate at Microsoft.
Also attached 2 example templates in .rar for SQL Server 2008 R2 when having performance issues and blocking/ deadlocking occurring.
Best Regards
Dynamics CRM Team