As we continue our SQL Nexus journey, I would like to point out something interesting that I learned from reading the SQL Nexus chapter in the race car book (aka Professional SQL Server 2008 Internals and Troubleshooting).  The author, Christian Bolton, noted that the default Trace events that are collected when you run StartSQLDiagTrace2008R2.cmd from the performance scripts that we mentioned earlier in this series provide a good baseline for your monitoring activity.  However, he noted added the following events in order to enable additional functionality necessary to compare execution plans and drill down to the statement level:

• EventType “Performance”, Event name “Show Plan Statistics”
• EventType “Stored Procedures”,  Event name “SP:StmtCompleted”
• EventType “Stored Procedures”,  Event name “SP:StmtStarting”
• EventType “TSQL”,  Event name “SQL:StmtCompleted”
• EventType “TSQL”,  Event name “SQL:StmtStarting”

In order to enable these simply edit the XML file, SQLDiagPerfStats_Trace2008.xml, changing the above items from false to true.  After you collect these new events by running the CMD file, then import the collection into SQL Nexus and check out your new results.  Enjoy!