August 27, 2014 at 10:27 am
Does anyone know of any know issue w/ sys.dm_exec_procedure_stats
I've got as process to store the results every 10 mins so i can track some proc stats and i'm seeing some stored procs that it's saying were recently called that i don't think have been.
Anyone else have this issue before. I'm using sql server 2012
August 27, 2014 at 10:48 am
I'm not aware of any issues with this DMV. What makes you think the procedures weren't executed?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2014 at 11:10 am
Are you checking the last_execution_time? Running a filtered trace will either confirm an issue or help identify how the procedure is being run.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 27, 2014 at 12:26 pm
yes. using last exe time. It should only run as a step in a process that run at 7am hour. I see that run in the result set however i also see other runs at 11 am and 130pm. Here's what i'm running. Perheps the way i'm getting the [object_name] has a flaw
SELECT DISTINCT 'XXXXX' instance,
Db_name(new.database_id) AS dbname,
Object_schema_name(new.object_id, new.database_id) AS [schema_name],
Object_name(new.object_id, new.database_id) AS [object_name],
new.[database_id],
new.[object_id],
new.[type],
new.[type_desc],
new.[sql_handle],
new.[plan_handle],
new.[cached_time],
new.[last_execution_time],
new.[execution_count],
new.[total_worker_time],
new.[last_worker_time],
new.[min_worker_time],
new.[max_worker_time],
new.[total_physical_reads],
new.[last_physical_reads],
new.[min_physical_reads],
new.[max_physical_reads],
new.[total_logical_writes],
new.[last_logical_writes],
new.[min_logical_writes],
new.[max_logical_writes],
new.[total_logical_reads],
new.[last_logical_reads],
new.[min_logical_reads],
new.[max_logical_reads],
new.[total_elapsed_time],
new.[last_elapsed_time],
new.[min_elapsed_time],
new.[max_elapsed_time],
Getdate() [createddate]
FROM sys.dm_exec_procedure_stats new
August 27, 2014 at 12:26 pm
going to run a trace for an hour or so
August 27, 2014 at 2:40 pm
You got the second parameter for the db, so it looks good to me. I think the trace will catch something.
Does the procedure run a long time? For example, do polling via waitfor over a long period of time?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
November 10, 2014 at 8:52 am
dropped the ball on this one for a bit however it has loomed it's ugly head again. I run the below and it isn't returning a proc call that took an hour to run this morning at 8am. Am i missing something here in the below statment? Oddly the below did return the proc call a few times last week.
select *
from sys.dm_exec_query_stats qs (NOLOCK)
cross APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
November 10, 2014 at 1:11 pm
so at this point for whatever reason it appears there are proc calls that are happening that arent getting returned dm_exec_procedure_stats nor dm_exec_query_stats. Either that or the object id that is being returned from these views is not valid and doesnt exist in sys objects. The below returns 688 records. How could there be an object getting returned from the below dynamic view that doesnt exist in it's associated databases sys objects table?
select distinct a.object_id
,count(*)
from sys.dm_exec_procedure_stats a
left join sys.objects b on a.object_id = b.object_id
where CONVERT(VARCHAR(10), a.last_execution_time, 101) = CONVERT(VARCHAR(10), Getdate(), 101)
and b.object_id is null
group by a.object_id
order by a.object_id
to be honest i really dont care why. I just need a way to store querry statistics and what stored proc the chunck of sql is from if it is indeed from a proc call. Unless i'm missing something i dont see that either dm_exec_query_stats nor dm_exec_procedure_stats provides that info for all acual proc calls for a given time period. Can you guys run the above sql and see if u too are returing records who do not exist in sys objects? nU should use a db w/ lots of objects and proc calls. I must be missing something here.
November 11, 2014 at 8:17 am
i see now that my logic is flawed when i assumed the these dynamic view hold what i want consistently. Apparently the only real way for me to get what i want is to run a trace. BOOOOOO!
November 11, 2014 at 8:37 am
I was about to reply when I saw that you found the blog that explains some of the limitations of the DMV's.
I will say that since you are on 2012 you should not use Profiler/SQL Trace, but should use Extended Events. Extended Events are more lightweight than a trace. The only reason to use SQL Trace is because there aren't the tools out there to aggregate Extended Event data like there is for Trace files. OF course, there are different targets in Extended Events that can do some aggregation for you.
As to why objects show up in the results with on corresponding object_id in the database, one way for this to happen is in system procedures that are in resourcedb (32767), which we don't permissions into.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply