SQL Server Multiple Joins Are Taxing The CPU

Question

I have a stored procedure on SQL Server 2005. It is pulling from a Table function, and has two joins. When the query is run using a load test it kills the CPU 100% across all 16 cores! I have determined that removing one of the joins makes the query run fine, but both taxes the CPU.

 Select 
  SKey
 From 
  dbo.tfnGetLatest(@ID) a 
  left join [STAGING].dbo.RefSrvc b on 
   a.LID = b.ESIID
  left join [STAGING].dbo.RefSrvc c on 
   a.EID = c.ESIID 

Any help is appreciated, note the join is happening on the same table in a different database on the same server.

Answer 1

So what is the differnce in your execution plan? Is this even using an index?

Have you tried using a UNION all instead (I preseume what you are trying to get is the records which have one or the other id which your current query wouldn;t give, all ti woudl give is a complete list of all the skey values, don't need to join at all for that.)

Select  
    SKey 
From  
    dbo.tfnGetLatest(@ID) a  
    left join [STAGING].dbo.RefSrvc b on  
        a.LID = b.ESIID 
union all
Select  
    SKey 
From  
    dbo.tfnGetLatest(@ID) d  
    left join [STAGING].dbo.RefSrvc c on  
        d.EID = c.ESIID  

Still might not be efficient if it isn't using an index but it would return a better recordset probably.

Or consider puttingthe values returned from the tablevalued function into a temp table where it can be indexed and then doing the join.

Answer 2

This would be a lot clearer with a little sample output, but I'll jump in. What does this do for you?

SELECT SKey FROM dbo.tfnGetLatest(@ID)
WHERE LID IN
    (SELECT ESIID from [STAGING].dbo.RefSrvc)
AND EID IN
    (SELECT ESIID FROM [STAGING].dbo.RefSrvc)

Answer 3

What does dbo.tfnGetLatest(@ID) return and is it inline table valued or multi-statement?

If it's multi statement then

• it's a black box to the optimier
• cardinality is one
• there are no statistics for the results of the udf

Please see my answer here for why udfs can be bad