Running SQL Server Profiler with Project Server

Quite often when you are performing customizations or when you want to troubleshoot your Project Server deployment it's useful to go "deep" and see what's happening under the SQL hood.

The best way to achieve that is to run a SQL Server Profiler trace on the SQL Server instance you want to analyze. Since Project Server 2007's architecture is based on a queuing system you will find a lot of "noise" if you run a standard trace.

I have attached at the end of this post a template I have created that eliminates a lot of the extra SQL queries due to the queuing system.

Procedure

  • Download trace template and copy it when you'll be running the SQL Profiler from
  • Double click on it:

 125 2008-02-28, 21_55_37

  • SQL Server Profiler is launched and you should get this message:

126 2008-02-28, 21_57_29

  • Click OK and click New Trace then connect to the DB server
  • Under Use Template select Project Server 2007 SQL Trace Template, and run trace

127 2008-02-28, 21_59_30

  • Sample trace should look like this:

128 2008-02-28, 22_05_57

Please note that this trace only looks at databases with the suffix Published and ignores commands with prefix MSP_TimesheetQ.
Happy tracing!

Project Server 2007 SQL Trace Template.tdf