Find the most executed stored procedure(s)

Question

Post reply

Find the most executed stored procedure(s)

Mehernosh

SSC Eights!

Points: 826
More actions
August 30, 2013 at 1:11 am

#282569

Comments posted to this topic are about the item Find the most executed stored procedure(s)
Mehernosh.

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

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

asdawkins 16457 Valued Member Points: 56 More actions · Answer 1

Thanks for this. I will be using it a lot.

How do I add a column that shows the "last execution date"?

samp.silvercreek Ten Centuries Points: 1190 More actions · Answer 2

I'm unsure how useful this script is for an 'active' database.

Running against our development server, with few if any active connections at the time, produced some interesting information, but when it was run against a production server that was being actively used by multiple users, the results varied greatly from execution to execution.

I assume this is because it is pulling the information from the cache?

Mehernosh SSC Eights! Points: 826 More actions · Answer 3

Hi asdawkins 16457

Thank you for your kind words, appreciate it.

Thanks for this. I will be using it a lot.
How do I add a column that shows the "last execution date"?

I was checking the Last Execution Date for your query and it you would be better of using the DMV - SYS.DM_EXEC_PROCEDURE_STATS which has a Last execution Time Column which can be used.

An example - SYS.DM_EXEC_PROCEDURE_STATS

SELECT SO.NAME, EPS.LAST_EXECUTION_TIME , EPS.EXECUTION_COUNT

FROM SYS.DM_EXEC_PROCEDURE_STATS EPS

INNER JOIN SYS.OBJECTS SO ON EPS.OBJECT_ID = SO.OBJECT_ID

WHERE EPS.DATABASE_ID = DB_ID() -- FILTER BY CURRENT DATABASE

ORDER BY EPS.EXECUTION_COUNT DESC

Please check http://msdn.microsoft.com/en-us/library/cc280701.aspx for more details.

E&OE - Other variations of this script might be existing too.

Hope this helps.

Mehernosh.

Mehernosh SSC Eights! Points: 826 More actions · Answer 4

Hi samp 35029

Thanks for the reply.

I'm unsure how useful this script is for an 'active' database.
Running against our development server, with few if any active connections at the time, produced some interesting information, but when it was run against a production server that was being actively used by multiple users, the results varied greatly from execution to execution.
I assume this is because it is pulling the information from the cache?

That's correct it is for Cached data.

sys.dm_exec_cached_plans returns a row for each query plan that is cached by SQL Server for faster query execution. Useful for finding cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Kindly check the following weblinks for more examples and where this can be useful.

http://technet.microsoft.com/en-us/library/ms187404.aspx

http://www.sql-server-performance.com/2008/Monitor-Stored-Procedure-Performance/">

http://technet.microsoft.com/en-us/library/ms187404.aspx

http://www.sql-server-performance.com/2008/Monitor-Stored-Procedure-Performance/

The query Plans are flushed out if SQL Server Service gets started manulally or automatically(In cluster mode)/runs update stats command / reindexing / using DBCC CACHE Clean Commands.

Hope his helps.

Mehernosh.