SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


high cpu


high cpu

Author
Message
pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1870
Jeff, Thank you so much for the help. I will correct the code
pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1870
jeff,

I have re written the query by joining the de norm table in from clause, but the no of
reads and cpu are high with the new query. Old query is showing low cpu and less no of reads. I also executed DTA for any new indexes but it showed no recommendations

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99595 Visits: 33014
pmadhavapeddi22 (2/12/2014)
jeff,

I have re written the query by joining the de norm table in from clause, but the no of
reads and cpu are high with the new query. Old query is showing low cpu and less no of reads. I also executed DTA for any new indexes but it showed no recommendations

Thanks


You can't trust the DTA. In fact, it misses lots and lots of tuning opportunities. Instead, read the execution plan of the query to understand the choices made by the optimizer in order to see where you might be able to influence those choices by restructuring your tables, your indexes, or, most of the time, your code.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67565 Visits: 18570
pmadhavapeddi22 (2/12/2014)
jeff,

I have re written the query by joining the de norm table in from clause, but the no of
reads and cpu are high with the new query. Old query is showing low cpu and less no of reads. I also executed DTA for any new indexes but it showed no recommendations

Thanks


You were 100% cpu previously, so what does high mean now?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218400 Visits: 41996
pmadhavapeddi22 (2/12/2014)
jeff,

I have re written the query by joining the de norm table in from clause, but the no of
reads and cpu are high with the new query. Old query is showing low cpu and less no of reads. I also executed DTA for any new indexes but it showed no recommendations

Thanks


I have to admit that you have just confused the heck out of me. I thought you said you were at 100% before. Please post the same query you made changes to and let's have a look. You might have done the new join incorrectly.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1870
Jeff,

I am sorry if I have confused you.

I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one

If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue

Please find the below query which I have modified




Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )



FYI,

The no of rows per table

de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249



Thanks
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67565 Visits: 18570
pmadhavapeddi22 (2/12/2014)
Jeff,

I am sorry if I have confused you.

I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one

If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue

Please find the below query which I have modified




Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )



FYI,

The no of rows per table

de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249



Thanks


How are you measuring and comparing CPU use between the new and the old?

Have you evaluated the execution plans?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

pmadhavapeddi22
pmadhavapeddi22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 1870
using sql profiler
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218400 Visits: 41996
pmadhavapeddi22 (2/12/2014)
Jeff,

I am sorry if I have confused you.

I still stand on the same point that there are set of update statements in a stored procedure.Due to these updates, CPU is touching almost 100%.
But when I have executed one single update query and compared the old and new query ,
new query is taking high cpu than the old one

If I could tune one update statement then I can apply the same to rest of the update statements and reduce cpu issue

Please find the below query which I have modified




Update dnbatr set fax=x.fax
from de_norm_buscard_attr dnbatr
inner join view_a x on x.contractid=dnbatr.contract_id
inner join table_a pcm WITH (NOLOCK) on pcm.contract_id=dnbatr.contract_id
inner join table_b pra on pcm.pcm_party_role_id=pra.party_role_id
INNER JOIN table_c y WITH (NOLOCK) ON pra.party_phn_id=y.party_phn_id
where (y.lst_updt_dtm >='02/12/2012' OR pra.lst_updt_dtm >='02/12/2012' )



FYI,

The no of rows per table

de norm table -- 297352
view_a -- 296781
table_a -- 297347
table_b -- 450238
table_b -- 276249



Thanks

That IS the correct form for the UPDATE that you're trying to do. The next step, as Grant and others have suggested, would be to examine the execution plan especially since there's a view involved. Folks on this site can help a lot there if given enough information. Please see the second link under "Helpful Links" in my signature line below for how to do that.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search