SQL Server Performance

SQL Profiler-Duplicate events for same stored procedure

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by anonymous2009, Sep 17, 2011.

  1. anonymous2009 New Member

    Hello,

    I ran sql profiler with Template as "TSQL_Duration" for a stored procedure(A) which inturn calls couple of other stored procedures(B,C,D) from within it.
    I see Two RPC:Completed event for the same stored procedure(D) in sql profiler.

    Why does this happen?
    First RPC:Completed event for stored procedure (D) takes 47 ms and
    the second RPC:COMPLETED event for the same stored procedure (D) takes 40 ms.

    So should I add those up and take that as the total duration for that stored procedure D (or) do an average of it?

    Thanks.
  2. preethi Member

    Wired. Can you add startdate, ClientProcessID & Textdata and check? Do the values for startdate, SpID, clientProcessID etc. same for both events?
  3. Shehap MVP, MCTS, MCITP SQL Server

    For this regard of capturing Expensive query and estimate its time ,
    They could be audited automatically through variety of options:

    1- Using SQL Server 2008 Activity Monitor where you could find cached expensive queries.

    But not sufficient for a comprehensive auditing since recycling of expensive queries is there and you mightn’t be able to track old ones.

    2- Use SQL Profiler >>> Performance Event >> Performance Statistics >> select all columns

    But this is also not sufficient from 2 perspectives:

    Profilers almost post more overload on production DB servers + Master DB + yield to a performance degradation which might range from 50 % -100% .

    Not easily tracked particularly for huge OLTP transactions

    3- DMVs which is the best practice and to utilize it easily + operationally to audit long history of expensive queries , you could follow the below scripts by the same order :

    USE [msdb]

    CREATETABLE [dbo].[Exp_table](

    [EXP_Query] [nvarchar](max)NULL,

    [Time] [datetime] NULL,

    [Elapsed_Time] [int] NULL

    )ON [PRIMARY]

    USE [msdb]

    DELETEFROM EXP_TABLE WHEREDATEDIFF(D,TIME,GETDATE())>n

    --n = no of days needed to keep audting results

    USE [msdb]

    insertinto msdb.dbo.Exp_Table SELECT

    (SELECTtextFROMsys.dm_exec_sql_text(qs.sql_handle))AS query_text,qs.last_execution_time, qs.last_elapsed_time/1000000

    FROMsys.dm_exec_query_statsAS qs

    WHERE last_elapsed_time >3000000

    and qs.last_execution_time notin(selecttimefrom [msdb].[dbo].[Exp_Table])

    orderby last_elapsed_time desc

    And then you could schedule the last 2 scripts in one scheduled job to run by any polling interval like 5 minutes.

    Advantage of this solution:

    Precise auditing of all possible expensive queries every millisecond.

    Long retention period of auditing which might be for months or years.

    The least consume of resources since no CPU overload or Storage capacity needed at all for it.

    More accessible b first class objects ( SQL commands ) to be filtered easily by an time interval needed
  4. satya Moderator

    Do follow what Shehap suggested as the PROFILER can be a culprit to get down the server resources, as you are using latest version fo SQL its good to take advtange of available tools that depending upon the third party tools or techniques :).

Share This Page