SQL Profiler-Duplicate events for same stored procedure | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
SQL Profiler-Duplicate events for same stored procedureHello, 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.
Wired. Can you add startdate, ClientProcessID & Textdata and check? Do the values for startdate, SpID, clientProcessID etc. same for both events?
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
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 .