SQL Server Performance Forum – Threads Archive

How do we find the frequecy of SP call?

Hi All,
Is there any other way to find out the frequency of each stored procedure call from application without using SQL Profiler? I am trying to find out the long running and frequently called SP in production server but I do not want to run profiler in production database because it will cause performance issues. Any solution would be really appreciated. Thanks,
DilliGrg

---

I don’t imagine a properly structured profiler trace — collecting only that data, specifically — would have that much impact. Traces that capture huge amounts of varied data obviously could.

---

There are some third party programs, such as the ones from SQL Power Tools, that will do what you want without negatively affecting performance. —————————–
Brad M. McGehee, SQL Server MVP

---

Have your sp write a log entry to a table, then query it later for counts… Profiler would defenitly be my choice though.

---

Brad,
Thanks for the response. Is there any free third party programs which does this? Thanks,
DilliGrg

---

Haywood,
Thanks for the response. What kind of insert SP do I need to write to capture log entry to a table? I am not very clear what to capture from SP and how can it be used to identify what application is calling what SP? If anyone can give me some hints, it will be great. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

DilliGrg, You can run the trace using extended procs…profiler definitely a peformance hit but trace is not that much specially when you are writing the trace file locally…
Even if you use third party tools most of them uses sql trace some of them read from memory… Here is the script… — Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 — Please replace the text InsertFileNameHere, with an appropriate
— filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension
— will be appended to the filename automatically. If you are writing from
— remote server to local drive, please use UNC path and make sure server has
— write access to your network share exec @rc = sp_trace_create @TraceID output, 2, N’InsertFileNameHere’, @maxfilesize, NULL
if (@rc != 0) goto error — Client side File and Table cannot be scripted — Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
— Set the Filters
declare @intfilter int
declare @bigintfilter bigint — Set the trace status to start
exec sp_trace_setstatus @TraceID, 1 — display trace id for future references
select [email protected]
goto finish error:
select [email protected] finish:
go
Mohammed U.

---

Will this help?
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html Madhivanan Failing to plan is Planning to fail

---

With this method I think we may miss some of the procedure executions… Mohammed U.

---

Thanks Mohammed for your code. I will try to implement this in development server first and see if i get the desired result sets. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

That’s correct. It basically gives the output of sp_who2 info with additional command. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Create a log table that will hold the spname and execution time. In your sp that you want to track, at the end of the sp, add an insert statement to the log table. Later on you can query the log table for times & counts.

---

Haywood,
Thanks for the response. I am not sure if I understood your point correctly but I will try this as well. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

It’s pretty simple.. Create a log table… CREATE TABLE Admin.dbo.SP_Log
(SPName sysname, ExecTime datetime)
Then in your procedure… CREATE PROCEDURE usp_Foo
AS
SELECT ‘Bar’
FROM dbo.TblFooBar; INSERT INTO Admin.dbo.SP_Log
VALUES (‘usp_Foo’, getdate());
GO Then you can query it later on for usage counts… SELECT SPName
, count(*)
FROM Admin.dbo.SP_Log
WHERE SPName = ‘usp_Foo’
GROUP BY SP_Name

---

Anyone have a guess whether Haywood’s method of actually inserting rows in a table would have more or less performance impact than a trace with specific filters? I guess more, but only based on a gut feeling — the inserts would be logged, and saved on the same disks the production server is using, etc… I wonder.

---

Haywood,
That’s what I was understanding from your point which is perfect for few specific(if I know what SP I want to see the frequency) SPs. BUT, There are 100+ of SPs and I don’t think I can modify those and include insert statement to each SP to be inserted into log table. Unless I am missing some points here. I am pretty sure there has to be some kind of methods which dynamically capture the SPs that are calling from application and put them into log table and count the frequency from there. Thanks again for your response. Until then I will look into all the solutions that are posted here. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

It may not be much performance inpact because it insert into a table single row but you have to modify each and every procedure in your db or all dbs… If you want to use Haywood method make sure table don’t have any PK OR index on it..
Mohammed U.

---

A properly created trace would be much less impact, and significantly less work if you have 100+ procedures to monitor.

---

I will go with trace not with log table…
Mohammed U.

---

That seems to be the only viable solution for this. Thanks all for your time. Happy New Year 2007! Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

]]>