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
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!