Random jump in CPU for specific stored procedure....
-
I have a stored procedure that jumps from 1000 CPU in profiler to 5000 CPU and stays there. If I recompile the proc, it goes back down to 1000 for a few days. Any ideas on this one?
Cal
-
Sorry, left out details...
SQL 2000 Enterprise SP3a
Database is currently a subscription of a published database on another server. Stored procedures are NOT being replicated.
Calvin
-
What if you add WITH RECOMPILE at the end of the procedure? I would be interested to know if you still get the same result with this option.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
I will give it a try. I suspected that maybe the only option left.
Cal
-
There might be something else going on, but can't tell unless you post the sproc
--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)
Intro to Tally Tables and Functions -
Please also post execution parameters that cause 1000cpu and others that make it jump to 5000. It could well be parameter sniffing and one or more situations cause it to get a 'bad' query plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Post proc as something may be in the proc that forces a recompile and subsequently introduces the elevated CPU util.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply