I need way to get the query stats for queries that aren't procedures/functions/triggers.
sys.dm_exec_query_stats only appears to contain these, i need to gather similar stats that are in this dmv but for just normal queries submitted by ssms/EF/Web etc.
This is what i used and it returns no results. (not the most efficient but just a quick proof of concept)
SELECT txt.text last_execution_time, execution_count, total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt WHERE txt.text NOT LIKE '%CREATE PROC%' AND txt.text NOT LIKE '%CREATE FUNC%' AND txt.text NOT LIKE '%CREATE TRIGG%' AND txt.dbid = DB_ID('$(DB)')
I know there are several queries submitted regularly from client apps that don't use stored procs and take a long time to execute. I would have thought SQL keeps a record of these somewhere.