CPU usage maxing out - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CPU usage maxing out

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-02-06 : 16:15:05

I'm trying to run a script against a database which seems to max out the CPU usage on the server.

When I run the same script against a copy of the database it runs fine.

Is there some sort of permission or database setting causing this to happen?

Here is the script:


SELECT  DD.ID, DD.transtype, DD.datum, DD.docdate, DD.vervdatfak, DD.project, DD.res_id INTO dbo.AgingDistDate_51A79C0011F33E46B1DCF306C7EE90A3
FROM (( SELECT bs.ID, gb.transtype, gb.datum, gb.docdate, gb.vervdatfak, gb.project, gb.res_id FROM  Banktransactions bs WITH(NOLOCK) INNER JOIN gbkmut gb WITH(NOLOCK) ON (bs.sysguid =  gb.banktransactionguid AND bs.sysguid IS NOT NULL AND gb.banktransactionguid IS NOT NULL)  AND (gb.reknr = bs.ledgeraccount AND bs.ledgeraccount IS NOT NULL  AND gb.reknr IS NOT NULL)  AND ISNULL(gb.debnr, '') = ISNULL(bs.debtornumber, '')  AND (gb.bkstnr = bs.entrynumber AND bs.entrynumber IS NOT NULL AND gb.bkstnr IS NOT NULL)
 WHERE bs.Type = 'S' AND (bs.CreditorNumber IS NULL  AND bs.DebtorNumber BETWEEN '   100' AND '802302' 
))
 UNION ALL
 (SELECT bw.ID, gb.transtype, gb.datum, gb.docdate, gb.vervdatfak, gb.project, gb.res_id FROM Banktransactions bw WITH(NOLOCK) INNER JOIN gbkmut gb WITH(NOLOCK) ON  ISNULL(gb.debnr, '') = ISNULL(bw.debtornumber, '')  AND ISNULL(gb.faktuurnr,'') = ISNULL(bw.invoicenumber,'')  AND (gb.bkstnr = bw.entrynumber AND gb.bkstnr IS NOT NULL AND bw.entrynumber IS NOT NULL)  AND (gb.reknr = bw.OffsetLedgerAccountNumber AND gb.reknr IS NOT NULL  AND bw.OffsetLedgerAccountNumber IS NOT NULL) AND gb.transtype <> 'V'  WHERE bw.Type = 'W' AND (bw.CreditorNumber IS NULL  AND bw.DebtorNumber BETWEEN '   100' AND '802302' 
))
UNION ALL
(SELECT bw.ID, gb.transtype, gb.datum, gb.docdate, gb.vervdatfak, gb.project, gb.res_id FROM Banktransactions bw WITH(NOLOCK) INNER JOIN Banktransactions ba ON ba.entrynumber = bw.entrynumber AND ba.offsetledgeraccountnumber = bw.offsetledgeraccountnumber AND ba.type = bw.type AND ba.invoicenumber = bw.invoicenumber AND ba.creditornumber <> bw.creditornumber INNER JOIN gbkmut gb WITH(NOLOCK) ON IsNull(gb.crdnr, '') = IsNull(ba.creditornumber, '') AND ISNULL(gb.faktuurnr,'') = ISNULL(ba.invoicenumber,'') AND (gb.bkstnr = ba.entrynumber AND gb.bkstnr IS NOT NULL AND ba.entrynumber IS NOT NULL) AND (gb.reknr = ba.OffsetLedgerAccountNumber AND gb.reknr IS NOT NULL AND ba.OffsetLedgerAccountNumber IS NOT NULL) AND gb.transtype <> 'V' WHERE bw.Type = 'W' AND (bw.CreditorNumber IS NULL  AND bw.DebtorNumber BETWEEN '   100' AND '802302' 
)
)) DD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-06 : 17:11:38

I would be suspicious of out-of-date statistics and/or a bad plan. Compare the execution plan, including stats info, in both environments.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-06 : 17:31:58

What Tara wrote.
Further more you might optimize your query to run thru the tables once, instead of three times - something like:

select case when ba.entrynumber is null then bw.ID else ba.ID end as ID
      ,gb.transtype
      ,gb.datum
      ,gb.docdate
      ,gb.vervdatfak
      ,gb.project
      ,gb.res_id
  into dbo.AgingDistDate_51A79C0011F33E46B1DCF306C7EE90A3
  from Banktransactions as bw
       left outer join Banktransactions as ba
                    on ba.entrynumber=bw.entrynumber
                   and ba.offsetledgeraccountnumber=bw.offsetledgeraccountnumber
                   and ba.type=bw.type
                   and ba.invoicenumber=bw.invoicenumber
                   and ba.creditornumber<>bw.creditornumber
       inner join gbkmut as gb
               on (bw.Type='S'
              and  bw.sysguid is not null
              and  bw.ledgeraccount is not null
              and  bw.entrynumber is not null
              and  gb.banktransactionguid=bw.sysguid
              and  gb.banktransactionguid is not null
              and  gb.reknr=bw.ledgeraccount
              and  gb.reknr is not null
              and  isnull(gb.debnr,'')=isnull(bw.debtornumber,'')
              and  gb.bkstnr=bw.entrynumber
              and  gb.bkstnr is not null
                  )
               or (bw.Type='W'
              and  bw.entrynumber is not null
              and  bw.OffsetLedgerAccountNumber is not null
              and  isnull(gb.debnr,'')=isnull(bw.debtornumber,'')
              and  isnull(gb.faktuurnr,'')=isnull(bw.invoicenumber,'')
              and  gb.bkstnr=bw.entrynumber
              and  gb.bkstnr is not null
              and  gb.reknr=bw.OffsetLedgerAccountNumber
              and  gb.reknr is not null
              and  gb.transtype<>'V'
                  )
               or (bw.Type='W'
              and  ba.entrynumber is not null
              and  ba.OffsetLedgerAccountNumber is not null
              and  isnull(gb.crdnr,'')=isnull(ba.creditornumber,'')
              and  isnull(gb.faktuurnr,'')=isnull(ba.invoicenumber,'')
              and  gb.bkstnr=ba.entrynumber
              and  gb.bkstnr is not null
              and  gb.reknr=ba.OffsetLedgerAccountNumber
              and  gb.reknr is not null
              and  gb.transtype<>'V'
                  )
 where bw.CreditorNumber is null
   and bw.DebtorNumber between '   100' and '802302'

Avoid using nolock.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-09 : 01:44:48

Check if Is Auto Create and Update Statistics is Enabled.
As suggested check execution plans and analyse for Hash Joins, Sorts, Filters.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources