Hi Sharky,

I do not totally agree with you!

A - Did you even look at Glenn Barry's scripts?

-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost

-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure

-- Top Cached SPs By Total Logical Writes (SQL 2008). Logical writes relate to both memory and disk I/O pressure

All of these are based upon the 'sys.dm_exec_procedure_stats' DMV. While this will not tell you all the spids that are running at the time of the CPU spike, it will tell you the problem queries that are sucking up CPU, Memory & Disk. By improving these queries, the overall performance should increase.

B - A tool like SQL Sentry Performance monitor and event manager will find the needle in the hay stack at $1500-1995 dollars per server.

http://www.sqlsentry.com/solutions-sql-server.asp

C - A caution about extended events and SQL Server traces. Both do occur an overhead. A nice article by Jonathan Kehayias comparing the two.

http://www.sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

D - Even if you have a SQL trace file, you still have to weed through all the connections (spids) and queries running at that time.

I suggest trying out Clear Trace to weed through the hay. http://www.sqlmag.com/article/performance/cleartrace

In short, I do not think a trace will show the smoking gun a very busy system without a bunch of analysis.

John