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


Increase in CPU per Execution when a SP is called in volume


Increase in CPU per Execution when a SP is called in volume

Author
Message
Rin Sitah
Rin Sitah
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 548
Hi Guys,

I have an interesting problem that I don't know the answer to! That's why i'm posting Smile
Anyway, we have an SP on our SQL Server that is typically called around 4 times per second. The CPU Usage per execution is around 50,000. However, we had a particularly busy time and the SP was getting called around 8 times per sec, but the CPU per execution went up to 600,000!

At the same time, another SP on the same DB and Server which normally get called 20 times per sec and uses 6000 CPUs per execution went up to around 450 times per second but the CPU per execution only rose to around 6500...

Any idea what may have caused the first SPs CPU usage to go through the roof?
Is there more info I need to provide for a better diagnosis?

If the 2 Sprocs hit a common table would that cause the CPU rise?

Any help would be appreciated. And do let me know if you need more info.

Cheers!
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9822 Visits: 13350
It might have to do with the actual parameters passed in. Have you checked that?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Rin Sitah
Rin Sitah
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 548
He there.
Yeah- that is a possibility. It could be that the params being called weren't the usual ones.
What happened was a bot crawled our site and was hitting a lot of pages that aren't usually hit, with strange options etc, so probably passing a bunch of not frequently called params.

Am I right in guessing that because they were unusual params, that the query optimiser didnt use indexes efficiently, causing an increase in the CPU used for that proc.
If so, how would we mitigate against this happening in the future?
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9822 Visits: 13350
Rin Sitah (4/17/2014)
If so, how would we mitigate against this happening in the future?


Hard to tell. Stored procedures are compiled against the first runtime parameter passed, which are cached along with the plan (that's called parameter sniffing). When unusual params are passed in, the cached plan could be non optimal. How to prevent it? You can disable it altogether with a TF or use some hints such as OPTIMIZE FOR UNKNOWN. In your case I wuoldn't worry much. I wuold focus on the users originated params.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Rin Sitah
Rin Sitah
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 548
After further investigation I have found that the CPU Time also counts CPU Waiting time.
The 2nd SP that increased frequency massively, but not CPU usage was probably adding contention to the CPU for the 1st SP, increasing the CPU waiting time.
We had a large number of waits of type SOS_SCHEDULER_YIELD (240 per sec) during that period which also suggests this.

http://www.confio.com/logicalread/sql-server-sos-scheduler-yield-wait-type/#.U0_AjoVxhiY
"Plans that process large data amounts (large scans) can often require many cycles of CPU to ‘process’ all the data. This could lead to higher SOS_SCHEDULER_YIELD waits as it has repeating cycles of running and yielding, reducing the amount of data to be processed (for example, turn those ‘Scans’ into ‘Seeks’), and can often reduce the # CPU cycles required and thus amount of SOS_SCHEDULER_YIELD waits."

Also I found that the 1st SP needed a covering NC index as it was performing an index scan on a 1M row table. That table was also the main table in the 2nd SP, but im not sure that would have added to CPU contention. Both SPs are simple selects.

I'm not sure how the 1st SP that was missing the index managed to fly under the radar for so long...
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