April 17, 2014 at 4:24 am
Hi Guys,
I have an interesting problem that I don't know the answer to! That's why i'm posting 🙂
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!
April 17, 2014 at 4:35 am
It might have to do with the actual parameters passed in. Have you checked that?
-- Gianluca Sartori
April 17, 2014 at 5:22 am
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?
April 17, 2014 at 5:33 am
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
April 17, 2014 at 6:25 am
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...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply