Is there a way to read execution statistics for stored procedures that have "with recompile"

• Ian_McCann

  SSCertifiable

  Points: 5163

  More actions

  June 3, 2013 at 9:26 am

  #294129

  Is there a way to read execution statistics for stored procedures that have "with recompile"

  I've tried the following with no joy

  USE tempdb

  go

  CREATE PROCEDURE usp_testwithoutrecompile

  AS

  SELECT TOP 1 * FROM sys.objects

  go

  CREATE PROCEDURE usp_testwithrecompile

  WITH RECOMPILE

  AS

  SELECT TOP 1 * FROM sys.objects

  go

  EXEC usp_testwithoutrecompile;

  EXEC usp_testwithrecompile;

  SELECT OBJECT_NAME(object_id),* FROM sys.dm_exec_procedure_stats

  WHERE OBJECT_NAME(object_id) = 'usp_testwithoutrecompile';

  SELECT OBJECT_NAME(object_id),* FROM sys.dm_exec_procedure_stats

  WHERE OBJECT_NAME(object_id) = 'usp_testwithrecompile';

  DROP PROCEDURE usp_testwithoutrecompile;

  DROP PROCEDURE usp_testwithrecompile;

  or maybe you know a better way for me to work.

  I am using the execution stats [max_elapsed_time] and ([Total_elapsed_time]/[Execution_count]) to compare sp performance between our prod & QA servers.

  Now we have a number of procs that use with recompile and I can not judge the impact of the change.

  (I'm using SQL 2008 R2 Enterprise 64 bit)

  Many thanks in advance

  Ian

• Neeraj Dwivedi

  SSCertifiable

  Points: 6768

  More actions

  June 3, 2013 at 2:36 pm

  #1620712

  Use

  set statistics io on

  set statistics time on

  while running the SP. Also you can see the execution plan of both of the statements to see if there is any difference.

• Alexander Suprun

  SSCertifiable

  Points: 6233

  More actions

  June 3, 2013 at 3:20 pm

  #1620728

  You can use Profiler or Extended Events.

  ---

  Alex Suprun