January 6, 2015 at 1:20 pm
I ran the following query
select o.name, last_execution_time from sys.dm_exec_procedure_stats ps
inner join sys.objects o
on ps.object_id = o.object_id
where o.type = 'P'
and o.name = 'Proc1'
The results
-----------
proc1 2015-01-06 13:54:25.707
But I need to know what/who is running this.
I set a profiler with the following filters and cannot find any traces that is executing this sp.
I used the Tuning template
Selected the RPC completed in Event Selection section
Edited the Textdata as like %proc1%
Edited the database as like %db1
But the profiler does not show the proc as executing. But the above scripts returns the last execution time in every 5 minutes proving that the proc is getting executed in every 5 minutes.
January 6, 2015 at 1:26 pm
Leave out the database name. I've found that to be an issue and here is the reason....
You need to find out who/what is running proc1 that exists in DB1. However, the person running it is logged into Master. So, the database in Profiler is actually Master. Likewise, if it is a job calling it, the database will be MSDB, and if the it is being called from DB2, it won't show up in a trace only looking at DB1.
Example:
USE DB2;
EXEC DB1.dbo.proc1;
A trace watching DB1 will not capture that as it is running from DB2.
-SQLBill
January 6, 2015 at 2:19 pm
Thank you for the reply but still it's not showing up in the trace.
January 6, 2015 at 2:25 pm
I renamed the proc to proc2 and it's still showing up as executed few minutes ago though no jobs have failed and still it does not show up in the trace. Very strange.
January 6, 2015 at 3:19 pm
Keep in mind that a proc exists in that view as long as it is still in the procedure cache. As long as the proc is in the cache, the stats remain, even if the proc is not currently running. When the proc is removed from cache, it's deleted from that view.
Also, you should always run SELECTs from sys.dm_exec_procedure_stats twice, ignoring the first run. According to MS, the first run can be inaccurate if there is a workload running on the server at the time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 6, 2015 at 3:48 pm
If you want to use the dynamic management views to look for currently executing queries or procedures, instead of going off of sys.dm_exec_procedure_stats (or sys.dm_exec_query_stats), I'd suggest using sys.dm_exec_requests. That's going to show the currently executing queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2015 at 7:55 am
I missed something in your original post...you are tracing for RPC. Change that to SP:Starting and SP:Completed. Those are for stored procedures.
-SQLBill
January 7, 2015 at 8:48 am
This is what happened and very strange to me
select o.name, last_execution_time from sys.dm_exec_procedure_stats ps
inner join sys.objects o
on ps.object_id = o.object_id
where o.type = 'P'
and o.name = 'Proc1'
I was running the above statement on db1 so the o.name in the result was the sp from db1 but the
last_execution_time was from my admin database (dbadmin) where the object had id = 1991678143 and this id matched with the object_id of proc1 in db1.
Thank you Bill, you showed me the starting point. Once I modified the profiler to show only SPs running, the objectId gave me a hint that sent me on a chase. I was on it for last two days, thanks a lot . I appreciate it. Glad that I nailed it
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply