SQLSACT (7/15/2013)
dwilliscp (7/15/2013)
Good Article, but how do we tell what query is creating the spill over? I have been trying to figure out why a section of my job keeps taking over 2 hours to run... using your sniffing it does give both hash and sort warnings, but the plan has several of these objects.David Potter
Look for queries/procedures containing sort/hash operations.
If you have the execution plan, isolate the sort/hash operation and tune the query.
An index will remove the need for a sort operation.
If you are seeing hash operations, look at the columns being joined in the Hash join, look for missing indexes.
Thanks for the help.. I do have both the Stored Proc and the XML Plan.. I have run the sections of the Stored Proc, so I know what part is creating the problem... and it is the query that pulls everything gained from the other servers together. It has several sort operations and a three table join. I will take a look at the join again.. Profiler did not show any mising indexes, but maybe I can see one that it missed. I will also take a second look at the joins.. the data is comming from several servers... and the views SQL server has about job's and their execution. (I realy wish MS would have stored the data better)
David