Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

high cpu Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 1:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 805, Visits: 1,401
Jeff, Thank you so much for the help. I will correct the code
Post #1540586
Posted Wednesday, February 12, 2014 5:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 805, Visits: 1,401
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
Post #1540674
Posted Wednesday, February 12, 2014 6:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 15,729, Visits: 28,132
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1540680
Posted Wednesday, February 12, 2014 7:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1540727
Posted Wednesday, February 12, 2014 7:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:54 PM
Points: 37,075, Visits: 31,634
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1540734
Posted Wednesday, February 12, 2014 8:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 805, Visits: 1,401
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
Post #1540780
Posted Wednesday, February 12, 2014 8:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1540787
Posted Wednesday, February 12, 2014 8:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:14 AM
Points: 805, Visits: 1,401
using sql profiler
Post #1540791
Posted Wednesday, February 12, 2014 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:54 PM
Points: 37,075, Visits: 31,634
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1540797
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse