CPU IS Running mostly above 95%

Question

CPU IS Running mostly above 95%

Arman Khan

SSC Eights!

Points: 864
More actions
August 21, 2008 at 11:40 am

#191460

Hello All,
I have Sql Server 2000 (8.00.2187SP4Standard Edition) which is running above 95% at all times. Pleae tell what are the common steps i should take and what are the things i should check 1st.
Thanks..
Ar's

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

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

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

Run profiler during busy times, say for a hour. Monitor the T-SQL batch completed and the RPC:Completed.

Once the trace has finished, look through and find 5 or so queries with the highest CPU usage. Take a look at those queries and see if you can optimise them. That may require rewriting the queries or adding/changing indexes. It may require both.

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

Arman Khan SSC Eights! Points: 864 More actions · Answer 2

Thanks Gila, Thats what i did earlier and found few queries which were taking most of the CPU. There is a need of indexes on them.

I just post this Question is that there is any thing else i can take a look and make sure before i go and suggest indexes to the Client.

Thanks..Arm's

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

You can try them out on a test server and make sure that they are useful. Nothing else comes to mind.

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

Arman Khan SSC Eights! Points: 864 More actions · Answer 4

I have two below tables which are the main cause and i check the Execution Plan and mostly we are getting table & indexd scans. Please advise me that where i should replace, drop and put index.

Can you please tell me that which coulmn(s) (Data Type) are Good for what sort of Indexes.

Thanks Again ... Arm's

Table1 =

Index Name Type Data Type Lenght

IX__Authornonclustered located on PRIMARY float 8

IX__STATUSnonclustered located on PRIMARY float 8

IX_PK_3 nonclustered, unique, primary key int 4

TaBle2 =

Index Name Type Colmun Names

PK_T nonclustered, unique, primary key located on PRIMARY AID, MID, ProductNo

Data Type Lenght

AID int4

MID int4

ProductNo int4

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

Arman (8/21/2008)
Please advise me that where i should replace, drop and put index.

I have absolutely no idea.

Indexes should be defined based on the queries that affect the tables. Without seeing the common queries that run against those tables there is absolutely no way of identifying what should and should not be indexed.

If you want advice on indexes, please post the table definitions, the index defintions, the most common queries and, if possible, the execution plans of those indexes (saved as .sqlplan files, zipped and attached to your post)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Arman (8/21/2008)
Thanks Gila, Thats what i did earlier and found few queries which were taking most of the CPU. There is a need of indexes on them.

I am curious how you determined this. Especially how you determined that the queries needed indexes without being able to figure out what indexes they needed.

Being CPU bound on a DB server is a uncommon problem and throwing indexes at it is not usually the way to fix it. Sometimes, yes, but not usually.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

I agree... indexes won't fix the problems associated with accidental cross joins, triangular joins, or the RBAR associated with Cursors and/or While loops especially those in a UDF. Some views, especially views of views that have aggregation can be absolute CPU killers with or without any indexes on the underlying tables.

If you've identified the worst performing queries, consider rewritting them to use real set-based programming techniques. Yes, I'm assuming there's RBAR in the code (it's typical) whether it's obvious like a While loop or not so obvious like some of the mistakes folks make with concatenation functions and things like running totals.

There are other not so obvious things that can be wrong like an update that recompiles after a single row is updated. Those types of problems are very hard to determine if they exist but a spot check of the code will usually confirm if they are present or not. For example, any UPDATE that's joined to another table must also have the target table of the UPDATE in the FROM clause or sometimes (very rare but a killer when it happens) a 20 second update on a million rows can peg 4 CPU's to the wall for hours (voice of experience there).

Another form of hidden RBAR is the use of correlated sub-querys and the "new" 2k5 function that does a correlated sub-query internally... CROSS APPLY. The use of indexes won't help much there, either.

And, finally... you may be correct... it may be as simple as a couple of well formed indexes being needed... typically anything in the Join and Where clauses needs to be in "key" positions in the indexes and, for a little extra speed, you can add the columns that appear in the Select list in the Include section of the index. But, keep in mind that it doesn't take much to destroy the ability to use an index... just one lousy formula on a column in a Where clause and "POOF!", the best you can get is an index scan.

As Gail suggested, if you'd like a bit more detailed help, please provide the CREATE TABLE statements, the existing CREATE INDEX statements, the offending code, and the execution plan.

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 8

[Quote]

... accidental cross joins, triangular joins, or the RBAR associated with Cursors and/or While loops especially those in a UDF. ..

[/quote]

Looks the start of another article - I certain enjoy everything you have written so far.

You can add stored procedure parameter sniffing to the list. For example (simplified): OrganizationHierarchy is a view containing a CTE that does a recursive join.

create procedure OrganizationActivity_by_ParentOrg as

(@OrganizationId_Parent int

,@ActivityDate datetime )

as

select OrganizationHierarchy.OrganizationId_Parent

, OrganizationHierarchy.OrganizationId

, SUM( OrganizationActivity.ActivityAmt )

FROM OrganizationHierarchy

LEFT OUTER JOIN OrganizationActivity

on OrganizationHierarchy.OrganizationId = OrganizationHierarchy.OrganizationId

WHERE OrganizationHierarchy.OrganizationId_Parent = @OrganizationId_Parent

AND ActivityDate between @ActivityDate and @ActivityDate +1

GROUP BY OrganizationHierarchy.OrganizationId_Parent

, OrganizationHierarchy.OrganizationId

Looks simple enough, but problem occurs when the Senior VP runs the query at 6 AM for the entire organization and the query plan has a table scan on the OrganizationActivity table. Then, when the remainder of the staff runs the same sp for just there own activities, you get hundreds of personnel doing table scans instead of index scans.

To add insult to injury, this is a world wide application and the Senior VP is in Europe ! I was paged 4 times this week at 2AM CST (8AM GMT) because of slowness.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Oh Yeah! I forgot about that, Carl! Parameter sniffing can indeed cause these kinds of problems.

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

By the way... could you post a copy of the code that fixed your parameter sniffing problem? Thanks...

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

"could you post a copy of the code that fixed your parameter sniffing problem?"

This application is running under SQL Server 2000 and the analysis consisted of running a trace to determine what stored procedures were being run early in the morning with nontypical parameters. The solution was to schedule a job that executed sp_recompile on each of those procedures before most of the staff arrived.

Since the application was a package, we could not change the stored procedure code nor change the sp to recompile on each execution.

SQL = Scarcely Qualifies as a Language

e.j.h.lohnstein Grasshopper Points: 11 More actions · Answer 12

Are you sure it is SQL server that uses the CPU not some other progam?

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 13

Out of date statistics can also lend to increased CPU usage.

When was the last time you rebuilt your indexes or updated statistics on these tables?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

I would look for queries with very high IO as well, and check the plans for spools. Also UDF usage can lead to wicked bad looping queries. Likewise explicit cursors.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service