the below query runs in sql server 2008 not working in sql server 2012. why?
select top 20 total_worker_time / execution_count avg_cpu_time, execution_count, total_elapsed_time / execution_count avg_run_time, total_elapsed_time, (select substring(text, statement_start_offset / 2 + 1, statement_end_offset) sys.dm_exec_sql_text(sql_handle)) query_text sys.dm_exec_query_stats order avg_cpu_time desc
statement_end_offset means statement_end_offset number of characters executing batch or stored procedure @ executing statement ends.
issue : statement_end_offset returns -1 value @ gives error : "invalid length parameter passed left or substring function".
use below modified query work in sql server version :-
select top 20 total_worker_time/execution_count [avg cpu time], substring(st.text, (qs.statement_start_offset/2)+1, ((case qs.statement_end_offset when -1 datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) query_text sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) st order total_worker_time/execution_count desc; 