CPU bound ?

Last Post 10 Sep 2007 11:13 AM by gilou28. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gilou28
New Member
New Member

--
10 Sep 2007 10:44 AM
I have one user session that has the following behaviour:

Does about 1000 pages Logical I/O for about 3 seconds, then only consume CPU for about 2 minutes. Then does it all over again. This process is exporting data from the DB, 200 objects at a time (SharePoint content deployment).

There is no "wait time" nor "blocked by".

At the instance level, I/O is inexistant during that 2 minutes, no wait, nothing... Except the fact that one of my CPU is topped (got four) on a thread owned by SQL Server. At the session counter level, CPU counter adds about 1000 per seconds, so my conclusion is that this process takes one whole CPU for imself...

Now my question: Is it possible to determine what type of CPU usage this process does ? It's not for sorting since I do not see any I/O at the TEMPDB level. NIC is sleeping, client processor is 1%,also sleeping.

Thanks.

gilou28
New Member
New Member

--
10 Sep 2007 11:11 AM
RM:

Yep, did that!

That is what I did and I can see that only one proc is being called. The duration of that call match the CPU consumption (filter to durations > 5 seconds).

From there, I'd like to know whty is it taking so much CPU. What does the CPU does exactly? That is the question I need to respond and I only know for sure which priocedure is being called. Since I cannot re-run it (it is using a temporary table created in another procedure and populated by zillions of procs...), I'm having a hard time pinpointing the cause.

One question: that proc is doing one big SELECT, then calls 5 other procs (no looping). In the trace that I've done, nowhere do I see those other procedures, so I conclude that it's the select that takes that 120+ seconds, is that the correct assumption, since I only see the main procedure as being longer than 5 seconds ?


Thanks
gilou28
New Member
New Member

--
10 Sep 2007 11:13 AM
RUSSELLB:

If it was doing table scan for that long (more than 2 minutes) I whould see I/O during that whole period, no ? In my case, the I/O is concentrated in 2-3 seconds, then no I/O, just CPU.

Thanks.
gilou28
New Member
New Member

--
11 Sep 2007 04:18 AM
Thanks all for these replies. All point to one select statement that seems to be very CPU intensive. That select is Microsoft's code (SharePoint 2007, full deployment path, SP: proc_DeplGetListItemData) and I cannot modify it (or else...).

I have found that this problem has been reported by other to MS, I expect a design change, since the approach is far from being performant.

The volume is not that bad, full backup of that DB takes about 7 minutes and the application export: 5 hours :-(

MS should release a fix.... someday.

Thanks again!


You are not authorized to post a reply.

Acceptable Use Policy