High CPU utilization and query is slow

Question

High CPU utilization and query is slow

Minaz Amin

SSChampion

Points: 11052
More actions
May 15, 2008 at 7:51 am

#380529

Please help me ..I dont know why the CPU is hike. My understanding When first time( 40 sec) we run the SP it loads all the data in the cache after execution. Second / 3rd time with same parameter if I run the same Sp it takes lots of time approx 2 minutes. Why should it?
Query and explain is attached.:)
I know lots of sorting is going on :
My Perform says :
Pages/Sec it shows 0.00
Avg. Disk Queue Length: 30 %
% Processor Time 98-99 % why ?
Checked sysprocess and enable dealock graph no locking info !!
I am applying SP2 now on the server ..hope this resolve. Please send me suggestions.
Thanks inadvance !!!
"More Green More Oxygen !! Plant a tree today"

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Have you looked at the execution plan for the query? It may not be using indexes appropriately, doing scans, etc. If you have a particular query that's killing performance, check the execution plan to understand what's going wrong.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You have lots of table scans. They ain't gonna be fast

@tmp_strategyid - A table variable? How many rows?

[MARKET_RISK_DB_US].[publish].[DIM_STRATEGY] - 3 table scans. How many rows?

[MARKET_RISK_DB_US].[publish].[DEAL_DTL] - 2 index seeks with RID lookups. No clustered index?

[MARKET_RISK_DB_US].[publish].[DEAL_DTL] - table scan. How many rows?

I count 5 sorts in total and a number of hash matches. Those are memory-intensive operations and often are slow.

Can you post the table schema, the index definitions and the query?

If you're on SQL 2005, please save the exec plan as a .sqlplan file (from SSMS gui), zip it and attach. It's easier to read and has more info than the text plan you posted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Minaz Amin (5/15/2008)
...
I am applying SP2 now on the server ..hope this resolve. Please send me suggestions.
...

Be sure to also apply Cumulative update 5 !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Sorry, I must have missed the execution plan earlier.

These are going to prevent the code from using indexes:

isnull([@lv_processComparisonDate],[@lv_processCOBdate])<=isnull([MARKET_RISK_DB_US].[publish].[DIM_STRATEGY].[EFCT_END_DT] as [selStrategy].[EFCT_END_DT],'9999-12-31 00:00:00.000'

Also, based on the fact that there are so many table scans, do you have clustered indexes on these tables? I would expect to see index scans at least.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

I'd recommend you post the code... I've not seen Order By in an execution plan with so many columns ever before.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (5/15/2008)
I'd recommend you post the code... I've not seen Order By in an execution plan with so many columns ever before.

..which would mean indexing isn't covering that ORDER BY, since AFAIK indexes are limited to 16 columns in the main part , with a bunch more possibly in the INCLUDE (but the include does squat for you on ORDER BY).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Minaz Amin SSChampion Points: 11052 More actions · Answer 7

Thank you all for your support.

Thanks Grant for pointing out issues with the code.

Jeff Please find the Qery text for your ref. Iam waiting for your and Gila's comment for it.

Once I will get all the feedback I will start tuning. :). I will try to send the table / index defn.

Still I am not able to explain to my Manager that:

Once we execute the query and resturn the result set if we execute it for second/ 3rd time with same parameter it takes more time than first time. Wouldn't be the fetch will be same or faster than that?

Thanks again !!

"More Green More Oxygen !! Plant a tree today"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Minaz Amin (5/16/2008)
Jeff Please find the Qery text for your ref. Iam waiting for your and Gila's comment for it.

Where?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minaz Amin SSChampion Points: 11052 More actions · Answer 9

Dear Gila,

Please find the query.

"More Green More Oxygen !! Plant a tree today"

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

I suspect you're also getting recompiles during execution. These will kill performance. You might want to use the KEEPFIXED PLAN hint internally on the queries, but not on the proc itself. Test this to see if it makes a difference.

How much data comes in through the CSV? If it's not much, you're just as well off to join against the that UDF instead of loading it into a table variable.

You've got that data type as varchar(255). Is that the data type of the columns you're joining against? if not you're getting an implicit convert on the data type and this will prevent the index from being used.

To really tune this, you need to post the schema and the indexes because the data types in all these comparisons and how you've built your clustered indexes and other indexes really matters.

These functions are also going to prevent the use of indexes:

AND COALESCE(pos1.DEAL_NUM,'x') = COALESCE(pos2.DEAL_NUM,'x')

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Minaz Amin SSChampion Points: 11052 More actions · Answer 11

Hi All,

Iam still working on the High CPU utilization..As per the suggestions We have created cluster indexex and some non cluster indexex which has increases the performamance of the query. Thanks a lot for it.

Today morning I took the trace which indicates lots of sort warnings followed by Data file Autogrowth for the Temp Database. These are the reason for high CPU utlilization..right

please correct me If Iam wrong.

How do I tune this.There are so many coloumns in the Group by Clause and the query have 3 group by clause with same or less number of columns. The environment is Datawarehousing so they are not ready with PM keys ..

Group By Clause :

GROUP BY

pos.strategy_id,selStrategy.STRATEGY_KEY,selStrategy.STRATEGY_NM,pos.DEAL_NUM,pos.ORDER_NUM,pos.ITEM_NUM,pos.END_AVERAGE_DT,

pos.CALENDAR_CD,pos.CURVE_KEY,pos.CURVE_NM,pos.MARKET_PRICE,pos.CONTRACT_PRICE,pos.STRIKE_PRICE,pos.DEAL_PREMIUM,pos.BUY_SELL_NM,

pos.PUT_CALL_NM,pos.DELTA,pos.GAMMA,pos.THETA,pos.VEGA,pos.LOCATION_CD,pos.GROUP_CD,pos.DIVISION_CD,pos.DEPT_CD,pos.COMMODITY_KEY,pos.COMMODITY_NM,

pos.POSITION_TYPE_NM,pos.DEAL_PRICE_LOCATION,pos.SOURCE_SYSTEM_ID,pos.MATURITY_DT,pos.NO_OF_DAYS_PRICED,pos.NO_OF_PRICING_DAYS,pos.PRICE_SOURCE,

pos.STATUS,DEAL_DT,pos.DEAL_UNIT,pos.PAYMENT_DUE_DT,pos.POSITION_BEGIN_DT,pos.LAST_TRADE_DT_OF_RISKPERIOD,pos.CONVERSION_FACTOR_TO_BBL,pos.INSTRUMENT_KEY,

pos.INSTRUMENT_NM,pos.TRADER_KEY,pos.TRADER_INITIAL,pos.CP_ENTITY_SRC_ID,pos.COB_DT,pos.CLOSED_PL,pos.MTM_AMOUNT

Thanks for your Help...Awating your reply which will help me a lots !!!

"More Green More Oxygen !! Plant a tree today"

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 12

I'd bust up the query and store interim answers in a Temp Table instead of doing such a large group by... especially since the GROUP BY is to support 1 lousy column.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Minaz Amin SSChampion Points: 11052 More actions · Answer 13

Ouch. that hurt!!

This is the query they are not ready to change. What best solution can I give them to tune it? As I see lots of Data file and log Auto growth, Object created etc information for the Temp DB,

I was thinking to do the following:

Checking for enough space HDD

Give sufficient disk space to Temp Db ( 1 GB)

Move it to different drive ( from C to D)

Sufficient increment say by 100 MB

"More Green More Oxygen !! Plant a tree today"

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 14

Heh... wasn't meant to be personal, at all.

I'd make the startup size of TempDB whatever size it grew to after a month. 1 GB may not be enough... shoot, my desktop server has 3 GB temp DB and the production server (terabyte size) starts up with 12 GB for TempDB. It's just as important as having enough memory in the server.

Long term though... that query has to change.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)