Find Most Expensive Stored PROCEDURE

  • yusufkahveci

    SSC-Addicted

    Points: 466

    Comments posted to this topic are about the item Find Most Expensive Stored PROCEDURE

    SQL Server Consultant
    yusufkahveci@sqlturkiye.com
    www.sqlturkiye.com

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    The title is suggesting the most expensive, but you're actually reporting the most used SPs, which is not by default the most expensive. I suggest to rename the title

    Wilfred
    The best things in life are the simple things

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Wilfred van Dijk (8/14/2014)


    The title is suggesting the most expensive, but you're actually reporting the most used SPs, which is not by default the most expensive. I suggest to rename the title

    I agree.

  • jjeffryes

    SSC Journeyman

    Points: 78

    I agree the word "expensive" is ambiguous by itself. You can report expense in terms of CPU usage, I/O, etc. This query doesn't report expense in those terms. Still, I like collecting DMV queries, and I simply saved this one with a name describing its true use, finding most frequently executed procs.

  • jghohengarten

    Old Hand

    Points: 300

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks, but I think I've seen this before.

  • DennisPost

    SSCrazy

    Points: 2691

    There's already a dmv for procedure stats.

    sys.dm_exec_procedure_stats

    SELECT DBName = DB_NAME(database_id)

    , SPName = OBJECT_NAME([object_id], database_id)

    , ExecCnt = execution_count

    , TotalTime = total_worker_time

    , TotalReads= total_physical_reads

    --.... Heaps of columns

    FROM sys.dm_exec_procedure_stats

    WHERE database_id < 32767

    ORDER BY execution_count DESC

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Plagiarized from here, which was published in 2011.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 8 posts - 1 through 8 (of 8 total)

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