I want to find the execution time and execution count of the script ran in my sql server.
I have added, for example, /* searchThisString */ on every executed script. And then I use the following code to find them out.
SELECT dest.text, deqs.last_execution_time, deqs.execution_count
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.last_execution_time > '<time>'
AND dest.text LIKE '%<searchThisString>%';
However, there is one case that this code failed. If a script only consist of 'update' queries, the above code fails to find it out:
For example,
example_script.sql
/*searchThisString*/
update <table> set colA = 'A' where colB = 'B'
Then the above code will fail to find this executed script out.
Update: I have just found that sys.dm_exec_cached_plans can actually find all the executed scripts. However, it does not have the last_execution_time and execution_count information of the executed scripts.
I have tried to join it with sys.dm_exec_query_stats using the following code (but it does not work either). I think because sys.dm_exec_query_stats does not have this executed query, so it return 'null' for the last_execution_time and 'null' for execution_count when I join them up.
SELECT dest.text, deqs.last_execution_time, deqs.execution_count
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
LEFT JOIN sys.dm_exec_query_stats As deqs
ON decp.plan_handle = deqs.plan_handle
WHERE deqs.last_execution_time > '<time>'
AND dest.text LIKE '%<searchThisString>%';
Update2:
Here is the result:
