the query to compare results is:

select *,a.execution_count-isnull(case when a.execution_count>=b.execution_count then b.execution_count else 0 end,0) delta

from sp_stats_after_20m a full join sp_stats b on a.objName=b.objName and a.plan_handle=b.plan_handle

where a.objName is null or b.objName is null

and there are results with SP1 and execution_count = 2431, while in the trace is only 1 run

actually the sp name was something like p_blah_blahblah (not SP1)

the trace definition is:

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 15

declare @filecount int=6

declare @traceFile nvarchar(500)= --'C:\Data\MSSQL10_50.MSSQLSERVER\MSSQL\trace\SPExecLog'

N'X:\MSSQL10_50.MSSQLSERVER\MSSQL\Trace\SPExecLog'

exec @rc = sp_trace_create @TraceID output, 2, @traceFile,

@maxfilesize, NULL ,@filecount

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 43, 15, @on

exec sp_trace_setevent @TraceID, 43, 34, @on

exec sp_trace_setevent @TraceID, 43, 3, @on

exec sp_trace_setevent @TraceID, 43, 12, @on

exec sp_trace_setevent @TraceID, 43, 22, @on

exec sp_trace_setevent @TraceID, 43, 62, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 34, 0, 6, N'atisp%'

exec sp_trace_setfilter @TraceID, 34, 1, 6, N'p[_]%'

exec sp_trace_setfilter @TraceID, 34, 0, 1, NULL

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

so the filter is only for SP names ... no other filters

All that have place only when the server is under high pressure. when the proc cache is full, and server's engine decides to eliminate some rarely used SP stats from the cache

Right now, for example, I can't reproduce the issue, only on rush hours. When there are ~4K queries per second ... Now there are ~1K. So the issue is not reproduced