Q:

workfiles created/sec spiking out of control

in one of my databases I've got the workfiles created/sec performance counter spiking out of control averaging between 10K and 20K.
does anyone have an idea on how to find out which query is causing this problem?

A:

The SQL Server docs sadly aren't that great in terms of completely describing what's going on here when you see high numbers of workfiles created/sec. 
But, they do provide some insight:
http://msdn.microsoft.com/en-us/library/ms177426.aspx
First, they point out that workfiles (as opposed to work tables) are created to store HASH joins and hash aggregates - where hashing is usually going on because of operations that are attempting to join/combine/aggregate LARGE numbers of rows/results on BOTH sides of the equation (compared, say, to nested loop or other joins/operations). See this for more info on different JOIN types (and remember that not all hashing = joins):
http://msdn.microsoft.com/en-us/library/ms191426%28v=SQL.100%29.aspx
So. Translation: the reason you're seeing high numbers of workfiles is because your workload is 'mashing' large numbers of results from one table (or set of JOINs) against another table (or set of JOINs) over and over again. 
In a multi-tenant system, it's common to see high-ish numbers of workfiles created/sec (i.e. higher than the typical range of < 20 that most tuning gurus recommend for a SINGLE database). But the number you're reporting is obviously QUITE high. 
That said:
- If you're not experiencing OTHER problems (users threatening to kill you, slow page loads, etc), then (If you have gobs of RAM) this MIGHT not be a huge issue. It may, instead, just be a kind of 'lurking' problem that the server is handling JUST fine, but which will stunt your ability to scale. 
- The only REAL way to fix or correct this issue would be to look at your code AND operations. If you're combining large numbers of rows in single/gigantic slice+dice queries that try to do GOBs of JOINs + Aggregation against LARGE amounts of data, then breaking those single queries out into multiple, smaller 'sub queries' and 'pre-filtering' can/will decrease the number of workfiles being created AND will have an appreciable impact on overall performance and throughput (i.e., you should see that rewrites of your queries can/will be NOTICEABLY faster in many cases). 
I've written about the notion of 'pre-filtering' here: 
http://devproconnections.com/database-development/generating-high-performance-sql-server-query-results

