2016 introduced the dynamic view sys.dm_exec_function_stats
Was there a way, albeit more difficult I'm sure, to get the same info pre-2016?
We're setting up splunk dashboards and I already have a query for sprocs (sys.dm_exec_procedure_stats), but I need to differentiate between sproc and function execution stats on 2008 R2 and up.
May 1, 2018 at 10:14 am
Look at the views themselves and see if the underlying query can be run on an older SQL Server version. I'm not the expert on DMVs, so I don't know if there are earlier alternatives. You might want to check MSFT's documentation on the views and see what they say. Every now and then there's useful information on what to do for previous versions of SQL Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 10:38 am
To the best of my knowledge, there's not a way to see where/how system dmv's are derived. I'm hoping someone just knows what to do.
The MS info on the view includes nothing about previous versions.
May 1, 2018 at 11:20 am
robin.pryor - Tuesday, May 1, 2018 10:38 AMTo the best of my knowledge, there's not a way to see where/how system dmv's are derived. I'm hoping someone just knows what to do.
The MS info on the view includes nothing about previous versions.
It won't do you any good to see the definition of the DMVs since they often reference internal tables that we have no access to. You can see the references by checking he definition of the DMVs - SELECT OBJECT_DEFINITION(object_id('sys.dm_exec_procedure_stats'))
The question has come up before and all I've ever seen is that you'd need to pick a part the query stats to try to differentiate everything and it would not be a fun or easy thing to do.
Sue
robin.pryor - Tuesday, May 1, 2018 9:10 AM2016 introduced the dynamic view sys.dm_exec_function_stats
Was there a way, albeit more difficult I'm sure, to get the same info pre-2016?
We're setting up splunk dashboards and I already have a query for sprocs (sys.dm_exec_procedure_stats), but I need to differentiate between sproc and function execution stats on 2008 R2 and up.
Scalar function stats are available in the DMV: sys.dm_exec_query_stats, although you have to do an extra join from the sql_handle to find out what the object is for the query:SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time, qs.creation_time,
SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.modify_date,
qp.query_plan, qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qt.dbid = DB_ID()
AND o.type = 'FN'
ORDER BY total_seconds DESC;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply