On MS SQLServer 2008 R2 SP3, the DMV sys.dm_exec_procedure_stats is reporting incorrect statistics for me. It is reporting statistics of a regular INSERT statement against a stored procedure.I have reproduction to proved this which I will cut-n-paste towards the end of this conversation . In the reproduction, I am executing an insert statement on a table t1 with trigger tri_t1 ‘n’ times and executing a stored procedure, proc1, 1 time. When I look at the output of the dm_exec_procedure_stats, it shows that the stored procedure has been executed (n+1) times and reports all the statistics against proc1. If I get rid of the trigger, dm_exec_procedure_stats correctly shows that proc1 got executed just 1 time. i.e the table needs to have a trigger for this issue to manifest.

I cannot reproduce the issue on MS SQL2012 or higher. dm_exec_procedure_stats is reporting the statistics correctly on these versions.

Has anyone experienced this issue before ? I “think” this is a bug but would like the communities opinion before jumping to any conclusion. And if you have experienced this, do you have any workarounds ?

Here is the reproduction.

set nocount on go use master; go if  db_id('db1') is not null begin    drop database db1 end go CREATE DATABASE db1 go use db1 go create table t1 (t1_ID int identity, c1 int CONSTRAINT [pk_t1_ID] PRIMARY KEY CLUSTERED (t1_ID)) go /* **The table needs to have a trigger for the sys.dm_exec_procedure_stats to malfunction */ create trigger tri_t1 on t1 for insert  as begin    set nocount on end go if  object_id('t1_proc') is not null begin    drop proc t1_proc end go                        CREATE PROCEDURE [dbo].proc1 AS BEGIN  set nocount on  END  go   DBCC FREEPROCCACHE; go  --Call insert 4 times followed by a single call to the procedure  insert t1 values(1); insert t1 values(2); insert t1 values(3); insert t1 values(4); exec   proc1 go  --notice that the output of sys.dm_exec_procedure_stats shows that the proc was executed 5 times  select @@version  SELECT  cast(OBJECT_NAME(object_id, database_id) as varchar(10))                as  'proc name',           cast(db_name(database_id)as varchar(10))                as  'database name',         execution_count,         * FROM sys.dm_exec_procedure_stats AS d     go 

And here is the result of the above query. Notice the execution count of 5 in the column execution_count instead of 1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

proc name database name execution_count database_id object_id type type_desc sql_handle plan_handle cached_time last_execution_time execution_count total_worker_time last_worker_time min_worker_time max_worker_time total_physical_reads last_physical_reads min_physical_reads max_physical_reads total_logical_writes last_logical_writes min_logical_writes max_logical_writes total_logical_reads last_logical_reads min_logical_reads max_logical_reads total_elapsed_time last_elapsed_time min_elapsed_time max_elapsed_time

proc1 db1 5 19 5575058 P SQL_STORED_PROCEDURE 0x0300130092115500E20D120140A800000100000000000000 0x050013009211550040E1C985000000000000000000000000 2017-12-05 16:37:48.070 2017-12-05 16:37:48.070 5 10 2 1 2 0 0 0 0 0 0 0 0 0 0 0 0 13 3 2 3