Stored Proc Activity-Usage

Question

Post reply

Stored Proc Activity-Usage

sql_prodigy

SSCrazy

Points: 2254
More actions
April 29, 2009 at 1:36 am

#133596

hi everyone, i was assigned the task of unearthing the usage of stored procedures in our database. Is there anyway i can monitor the usage of all stored procedures to see which ones are being used the most often and which ones aren't?

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

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

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 1

SQL_DBA_3

SSCarpal Tunnel

Points: 4772

April 29, 2009 at 2:24 am

#985687

SQL Profiler is the one you can use.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

You can use sys.dm_exec_cached_plans to know the procs executed the most.

Check out this query i got from the web.

SELECT CASE when dbid = 32767

then 'Resource'

else DB_NAME(dbid) end [DB_NAME],

--OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],

OBJECT_NAME(objectid)AS [OBJECT_NAME],

SUM(usecounts) AS [Use_Count],

dbid,

objectid

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)

WHERE objtype = 'Proc'

AND UPPER(

-- remove white space first

REPLACE(

REPLACE(text,' ',' '),

' ',' '),

' ', ' '),

' ',' '),

' ',' ')

)

LIKE '%CREATE PROC%' and dbid = 7

GROUP BY dbid, objectid

Order by sum(usecounts) desc;

"Keep Trying"

Atif Hall of Fame Points: 3578 More actions · Answer 3

If you are not calling stored procedure with schema prefix, then it is possible that it will not be picked by plan cache every time. So to use SQL profiler is more better option.

DBDigger Microsoft Data Platform Consultancy.

sql_prodigy SSCrazy Points: 2254 More actions · Answer 4

found a few articles pertaining to monitoring stored procs using Dynamic management views (DMV) and Dynamic management functions (DMF), basically utilizes the cache to return statistics. Problem is i cant use these functions...

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

You need to use a server-side trace. You can build this manually using the T-SQL commands or you can use the SQL Profiler tool to build it for you. This article has an introduction. Don't use the Profiler GUI directly against production databases[/url], especially if they're already experiencing a stressful load.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sql_prodigy SSCrazy Points: 2254 More actions · Answer 6

http://www.sql-server-performance.com/articles/per/Monitor_Stored_Procedure_Performance_p2.aspx

for ur viewing plueasure:-)

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 7

Server-side Profiler trace will be the best option.

check this for help...

http://sqlserverpedia.com/wiki/Running_Traces_with_TSQL

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]