Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2008-11-20 : 17:52:28
|
| Hey everyone.We have a sproc that is very cpu intensive. When it runs we see our CPU get pegged out. Here is a picture from perfmon showing what happens wheen this sproc is run:http://www.postimage.org/image.php?v=aV1jkVg0So, we are doing normal maintenance on the sproc in order to fix it's CPU issues. That's not the problem. Since we have a multi-CPU server (eight dual-cores), while it was running I actually went to view the CPU utilization graph from the server's Task Manager instead of looking at perfmon so I could see what each CPU was doing. I was surprised to see this:http://www.postimage.org/image.php?v=Pq1vRjG9We have parallelism turned off (server maxdop set to 1). So how can one sproc take over all CPUs? That seems wrong to me.SQL Server 2005 Enterprise Edition (sp2)Windows Server 2003 Datacenter64bit, 8 Dual Core 3.4 GHz CPUs, 128 GB RamThanks for any insight you have.Jerry==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-20 : 19:53:30
|
| Have you checked the query plan?Maybe it's ignoring the maxdop.Maybe it's something else that's using the cpu - maybe internal processes are being spawned that are doing it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-20 : 20:00:57
|
| High CPU usage occurs due high recompilation of SP and there is no proper index on it as nr said.And you should not turn parallelism off for whole server,You should use option hints for that.Set the profiler with Sp events and track it down.Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114445 |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2008-11-21 : 10:56:11
|
quote:
Originally posted by nr
Have you checked the query plan?Maybe it's ignoring the maxdop.Maybe it's something else that's using the cpu - maybe internal processes are being spawned that are doing it.
I wondered if it was ignoring the maxdop also. The plan currently in the cache will not display due a limitation in SQL Server with the number of nested levels allowed in the xml data type. I am going to try and run it in our dev environment to capture the plan, but it is destructive and requires everything to be set up perfectly.I guess its possible other internal processes are being spawned, I'll dig deeper.Thanks.Jerry==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2008-11-21 : 11:05:36
|
quote:
Originally posted by sodeep
High CPU usage occurs due high recompilation of SP and there is no proper index on it as nr said.
Yes, but its not recompiling. And there are proper indexes for the most part on the tables involved. Like I said, we're working on the sproc itself, I'm confident I can reduce its cpu dependency. The query stats DMV is pretty clear which statements are causing the issue. I was just surprised to see the execution take over all 16 CPUs.quote:
And you should not turn parallelism off for whole server,You should use option hints for that.
Well, maybe in most cases, but not for us. We didn't turn it off without significant data and analysis against our our workload that showed pretty convincingly we were better off with maxdop at 1.Thanks, guys.Jerry==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-21 : 12:47:33
|
| Can you explain what exactly you see in Execution plan ? Can you post snapshot of it so we can analyze? |
|
|
|
|
|
|
|
|
Why all CPUs consumed when no parallelism