SQL Server Performance

How do we find the frequecy of SP call?

Discussion in 'SQL Server 2005 General Developer Questions' started by DilliGrg, Dec 27, 2006.

  1. DilliGrg Member

    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
  2. merrillaldrich New Member

    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.
  3. bradmcgehee New Member

    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
  4. Haywood New Member

    Have your sp write a log entry to a table, then query it later for counts...

    Profiler would defenitly be my choice though.
  5. DilliGrg Member

    quote:Originally posted by bradmcgehee

    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

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

    Thanks,
    DilliGrg
  6. DilliGrg Member

    quote:Originally posted by Haywood

    Have your sp write a log entry to a table, then query it later for counts...

    Profiler would defenitly be my choice though.

    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)

  7. MohammedU New Member

    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 TraceID=@TraceID
    goto finish

    error:
    select ErrorCode=@rc

    finish:
    go


    Mohammed U.
  8. Madhivanan Moderator

  9. MohammedU New Member

  10. DilliGrg Member

    quote:Originally posted by MohammedU

    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 TraceID=@TraceID
    goto finish

    error:
    select ErrorCode=@rc

    finish:
    go


    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)

  11. DilliGrg Member

    quote:Originally posted by MohammedU


    quote:Originally posted by Madhivanan

    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.


    That's correct. It basically gives the output of sp_who2 info with additional command.



    Thanks,

    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  12. Haywood New Member

    quote:Originally posted by 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.


    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.
  13. DilliGrg Member

    quote:Originally posted by Haywood


    quote:Originally posted by 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.


    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)

  14. Haywood New Member

    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
  15. merrillaldrich New Member

    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.
  16. DilliGrg Member

    quote:

    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

    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)

  17. MohammedU New Member

    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.
  18. Haywood New Member

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

    I will go with trace not with log table...


    Mohammed U.
  20. DilliGrg Member


    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)

Share This Page