5

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: enter image description here

Alex Cheung
  • 61
  • 1
  • 1
  • 3
  • Possible duplicate of [How to see query history in SQL Server Management Studio](http://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio) – Mihai-Daniel Virna Mar 16 '16 at 08:19
  • The solution I found on your suggested post does not actually solve my problem. – Alex Cheung Mar 16 '16 at 08:22

2 Answers2

3

I executed below script repeatedly 

/***text***/

update test1 set name ='a'
where name ='test1'


update test1 set name='b'
where name='test2'

and then i executed below script to see stats..

select txt.text,st.execution_count,st.total_worker_time 
from sys.dm_exec_query_stats st
cross apply
sys.dm_exec_sql_text(st.sql_handle) txt

I could see output,even though there are only update statements..

enter image description here

Further,you can see from above snip,comments are not captured.I recommend checking for actual scripts instead of comments and further wrap,it up in a stored proc to see entire batch more clearly

I could see no issues with your query with exception of where clause..

enter image description here

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Thansk. The reason I search the comment instead of search the query is that I want to search a batch of scripts that have the same 'keyword' in the comment. I can see that dm_exec_cached_plans saves the scripts and the comment as well, while dm_exec_query_stats saves the scripts but without comments. I believe joining these two table is the solution. However, I can't find a 'common' key between them when I attempt to join them. Do you have any idea? – Alex Cheung Mar 17 '16 at 01:38
  • Thanks very much. Like I said, it can show the 'select' query (just like what's shown in your testing result). But it can't show the 'update' query. Please refer to my 'update2', – Alex Cheung Mar 17 '16 at 07:47
-1

Use the SQL Server profiler to see queries executed.

Also see How to see query history in SQL Server Management Studio.

Community
  • 1
  • 1
RobIII
  • 8,488
  • 2
  • 43
  • 93