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.

Are you able to post a query execution plan? `SET SHOWPLAN_TEXT ON` — Andomar, Apr 12 '10 at 22:23

score 2 · Accepted Answer · answered Apr 12 '10 at 22:19

2

This may shed some light on the problem. Can you separate the first join out into a CTE?

Something like this:

with FirstJoin(SKey,EID) as (
   select a.Skey,a.EID from 
       dbo.tfnGetLatest(@ID) a 
       left join [STAGING].dbo.RefSrvc b on a.LID = b.ESIID
)

select Skey from FirstJoin fj left join [STAGING].dbo.RefSrvc c on fj.EID = c.ESIID

Also, since both of your joins are left joins how do they narrow the result set? Isn't that query equivalent to select sKey from dbo.tfnGetLatest(@ID)?

answered Apr 12 '10 at 22:19

Zack Angelo

425
3
8

It is only getting one record from the TF and from the joins. – Dustin Laine Apr 12 '10 at 22:52
If it's only a single record and dbo.RefSrvc is indexed on the rows in question, perhaps you should try two subselects instead of left joins. – Zack Angelo Apr 13 '10 at 00:11

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

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

edited May 23 '17 at 12:19

Community

1
1

answered Apr 13 '10 at 04:54

gbn

394,550
75
549
647

HLGEM · Answer 3 · 2010-04-12T22:09:17.033

0

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.

edited Apr 12 '10 at 22:09

answered Apr 12 '10 at 22:03

HLGEM

88,902
11
105
171

That is the wierd thing, this only happens when load testing or in production. The query itself runs in under a second. When load testing I cannot even get 20 users on there before it hits 100% on a ll 4 XEON quad cores. With one join removed I have ramped it up to over 2000 users just fine. To answer your question the execution plan changes to show the one less join, but it overall remains the same. It is using a clustered index seek on the tables in questions and a table scan on the table function. – Dustin Laine Apr 12 '10 at 22:06
It is only getting one record from the TF and from the joins. – Dustin Laine Apr 12 '10 at 22:37
A union would bring in two records when I need only one, the multiple joins are getting data from two different ID's not duplicate. – Dustin Laine Apr 12 '10 at 23:09

score 0 · Answer 4 · answered Apr 13 '10 at 04:47

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)

SQL Server Multiple Joins Are Taxing The CPU

4 Answers4