Find Most Expensive Stored PROCEDURE

Question

Find Most Expensive Stored PROCEDURE

yusufkahveci

SSC-Addicted

Points: 466
More actions
July 28, 2014 at 12:40 pm

#293873

Comments posted to this topic are about the item Find Most Expensive Stored PROCEDURE
SQL Server Consultant
yusufkahveci@sqlturkiye.com
www.sqlturkiye.com

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

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

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 306 More actions · Answer 4

Duplicate of:

http://www.sqlservercentral.com/scripts/Stored+Procedures/101475/

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

September 8, 2015 at 3:18 pm

#1825278

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

DennisPost SSCrazy Points: 2691 More actions · Answer 6

There's already a dmv for procedure stats.

sys.dm_exec_procedure_stats

SELECTDBName= 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

FROMsys.dm_exec_procedure_stats

WHEREdatabase_id < 32767

ORDERBY execution_count DESC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Plagiarized from here, which was published in 2011.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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