Identify queries causing logfile growth or consuming CPU,Memory

 During troubleshooting performance related issues such as what is caused for log file growth , currently running , causing CPU spikes etc the below query is very handful.

SELECT TOP 50 qs.execution_count
	,exec_count_per_sec = 

qs.execution_count / 

NULLIF(DATEDIFF(second, qs.creation_time, qs.last_execution_time), 0)
	,qs.creation_time
	,qs.last_execution_time
	,qs.total_worker_time AS Total_CPU
	,total_CPU_inSeconds = --Converted from microseconds
	qs.total_worker_time / 1000000
	,total_elapsed_time_inSeconds = --Converted from microseconds
	qs.total_elapsed_time / 1000000
	,qs.total_logical_reads
	,qs.total_logical_writes
	,qs.total_elapsed_time
	,st.TEXT
	,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
--WHERE st.text like 'test%'
ORDER BY qs.total_logical_writes DESC