--DBCC TRACEON (2865)
--RECONFIGURE

Use tempdb
drop function sql_text
Go


-- Examining dm_exec_query_stats
select * from sys.dm_exec_query_stats


-- Create function to extract SQL Text from handle and offsets
create function sql_text (@handle [varbinary](64), @start [int], @end [int])
returns varchar(max)
as
begin
        declare @result varchar(max)
		declare @batch nvarchar(max)
		set @batch = (select [text] from sys.dm_exec_sql_text(@handle))
        if (@end = -1)
                set @end = len(@batch) * 2
        set @result =  substring(@batch, @start/2, (@end - @start)/2)
        return @result
end

select 	* 
from 	sys.dm_exec_query_stats qs cross apply sys.dm_exec_query_plan(qs.plan_handle);

-- Refactor dm_exec_query_stats
--
select	dbo.sql_text(sql_handle, statement_start_offset, statement_end_offset) [sql_text],
		query_plan,
		datediff(minute, creation_time, getdate()) [min_since_compilation],
		datediff(minute, last_execution_time, getdate()) [min_since_execution],
		stat.*
from 	sys.dm_exec_query_stats stat cross apply sys.dm_exec_query_plan(stat.plan_handle)
-- order by execution_count desc  			                    -- Which queries execute most?
-- order by plan_generation_num desc                            -- Which queries recompile the most?
-- order by (total_physical_reads + total_physical_writes) desc -- Which queries are responsible for causing the most I/O?

-- Perf by object...
select 	qs.execution_count, 
		st.dbid, 
		st.objectid, 
		st.text 
from 	sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(sql_handle) st 
order by execution_count desc

select 	plan_generation_num, 
		st.dbid, 
		st.objectid, 
		st.text 
from 	sys.dm_exec_query_stats qs 
	cross apply sys.dm_exec_sql_text(sql_handle) st 
where 	st.objectid is not null 
order by plan_generation_num desc


select * from sys.databases

select * from sys.objects where object_id = 1415676091
