SQL 2008 - Procedure execution cost in CPU

  • Hi Team,

    I got a request from one developer.

    On SQL 2008 SP1, he needs to find out CPU usage in % of total CPU & Memory Consumed for one store procedure.

    I have tried below things.

    Activity Monitor = gives details in MS

    SQL Profiler = Same

    Perfmon = gives server level information.

    How can i give him above asked information.

  • The cpu% is rarely useful at that level.

    THe proc needs to go from a to b... to z.

    The only thing you can do to help here is to reduce the amount of work it takes to make that happen.

    The CPU % will depend on everything else that's going on on that server when the proc is running.

  • % of CPU over what? The course of the stored proc execution? An hour? A day?

    % of memory consumed - query workspace or query workspace + plan cache or query workspace + plan cache + data cache or query workspace + plan cache + data cache + various other caches and non-cache areas used?

    Neither of those requests make much sense. Why does the developer want a % of CPU rather than an actual count of ms of CPU time used

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Why does the developer want a % of CPU rather than an actual count of ms of CPU time used

    Developer may not ask for it but a Manager (absolutely non-technical Manager) can. They usually prefer everything in terms of Percentages.

    @chetan: Are you manipulating the scene? 😉

  • @dev ..

    Why should i manipulate the secen to make my life hell.... 😛

    and question has come from the DEV team / support team only......

    @Gila

    This Sp is taking 27 sec to execute.

  • chetanr.jain (12/5/2011)


    @Dev ..

    Why should i manipulate the secen to make my life hell.... 😛

    and question has come from the DEV team / support team only......

    @Gila

    This Sp is taking 27 sec to execute.

    Wow that's slow. If you can post the actual execution plan we can have a look at it to find the low hanging fruits (we usually can't do a full workup over forums since it takes too long).

  • chetanr.jain (12/5/2011)


    @Dev ..

    Why should i manipulate the secen to make my life hell.... 😛

    and question has come from the DEV team / support team only......

    ...

    Then this Developer possesses the qualities to become a Manager. Promote him.

    Jokes apart... As Gail has already explained there is no reason for asking such questions. They are not Logical / Practical.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply