Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increase in CPU per Execution when a SP is called in volume Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 4:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 6, 2014 6:07 AM
Points: 291, Visits: 481
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!
Post #1562583
Posted Thursday, April 17, 2014 4:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:03 PM
Points: 4,608, Visits: 11,002
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
Post #1562588
Posted Thursday, April 17, 2014 5:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 6, 2014 6:07 AM
Points: 291, Visits: 481
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?

Post #1562610
Posted Thursday, April 17, 2014 5:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:03 PM
Points: 4,608, Visits: 11,002
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
Post #1562615
Posted Thursday, April 17, 2014 6:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 6, 2014 6:07 AM
Points: 291, Visits: 481
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...
Post #1562625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse