Continuing on the theme of using what you have at hand here is a query that can give you some insight to possible performance issues by using what should be available, the default trace.  In a perfect world when you are asked to help tune a misbehaving SQL Server all of the performance problems are easily reproducible, and/or all of the necessary information from a myriad of DMVs has been captured as the problem presented itself.  Sometimes that’s not the case so you have to pull together what you can from whats available and see if you can make sense of it all.  While the query below won’t tell you specifically what is wrong it may help point you in the correct direction.  The Get_TraceMemoryWarnings.sql query does just that, total up the current Hash and Sort memory warnings captured in the default trace files.  This also gives you something to look at while you are waiting SQL Trace and DMV capture to provide additional information.

SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by: sfibich
*ScriptName: Get_TraceMemoryWarnings.sql
*Version: 1.0.2013.1.2
*SQL Versions: 2008R2
*Purpose: Report on the number of hash and sort warnings found in the default trace, by host, application
*            login, and database.  Displaying the first occurrence and last occurrence.
*
*********************************************************************************************************/
go
declare @filename nvarchar(256)
select
@filename=convert(nvarchar(256),tg.value)
from
fn_trace_getinfo(default) as [tg]
where
tg.traceid =1
and
tg.property = 2
 
select
tc.name as [Category_Name]
,te.name as [Event_Name]
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName
,COUNT(*) as [Row_Counts]
,MIN(startTime) as [Min_Start_Time]
,MAX(startTime) as [Max_Start_Time]
from
fn_trace_gettable(@filename,NULL) as [tg]
inner join
sys.trace_events te
on
tg.EventClass =te.trace_event_id
inner join
sys.trace_categories tc
on
tc.category_id =te.category_id
where
te.Name in (
'Sort Warnings'
,'Hash Warning'
)
group by
tc.name
,te.name
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName
order by
tc.name
,te.name
,tg.HostName
,tg.ApplicationName
,tg.LoginName
,tg.DatabaseName

A sort or hash warning is record each time a query sort operations do not fit into memory.  It is my understanding the “not fitting into memory” has to do with a compilation time error in calculating the amount of memory need to execute the sort in memory.  That understanding coupled with my experience leads me to investigate database statistics when I see a large number of Sort/Hash Warning Events in the default trace.  These events may point to either stale statistic or the need to have more complex multi-column and/or filtered statistics.  Checking out the database statistics is normally my next step.  It could however point to bad or improper cache plan re-use, or possible time outs during query plan selection, and that would lead me to looking at whatever is currently in the sys.dm_exec_query_stats dmv.  Normally gathering additional information is key in fixing the issue.  At the end of the day this script is just one more tool on the workbench.

Additional related links:
Inside Hash and Sort Warnings (Great descriptions of Hash and Sort Warnings)
Hash Warning Event Class
Sort Warning Event Class

This code is available at the SQLScripts Project