SQL Nexus, Adding Additional Collection Events

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!

 

About these ads

About Ed Watson

Florida Gator and Certified SQL Server Enthusiast, Star Wars fanatic and is there anything else...oh yeah PS3! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on May 29, 2012, in Monitoring and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,357 other followers

%d bloggers like this: