top io queries with plan compile stats

plan voodoo

		 SELECT
            TOP(100)
			collection_date = GETDATE(),
			database_name = DB_NAME(CAST(pa.dbid AS INTEGER)), 
			database_name_exec = DB_NAME(CAST(pa.dbid_execute AS INTEGER)),                         
			qs.total_worker_time, 
			qs.execution_count,
			qs.sql_handle, 
			qs.plan_handle,
			qs.creation_time,
			qs.last_execution_time,
			qs.last_worker_time, 
			qs.min_worker_time, 
			qs.max_worker_time, 			
			qs.total_physical_reads, 
			qs.last_physical_reads, 
			qs.min_physical_reads, 
			qs.max_physical_reads, 
			qs.total_logical_writes, 
			qs.last_logical_writes, 
			qs.min_logical_writes, 
			qs.max_logical_writes,
			qs.total_logical_reads, 
			qs.last_logical_reads, 
			qs.min_logical_reads, 
			qs.max_logical_reads, 
			qs.total_elapsed_time, 
			qs.last_elapsed_time, 
			qs.min_elapsed_time, 
			qs.max_elapsed_time,
			cp.refcounts, 
			cp.usecounts, 
			cp.size_in_bytes, 
			cp.objtype,
			--qs.total_rows,
			--qs.last_rows, 
			--qs.min_rows,
			--qs.max_rows,
			st.text,
            SUBSTRING(
                st.TEXT,
				(qs.statement_start_offset / 2) + 1,
				((CASE qs.statement_end_offset
									WHEN -1 THEN DATALENGTH(st.TEXT)
						ELSE qs.statement_end_offset
						END -
								qs.statement_start_offset) / 2) + 1)
					AS statement_text,
			qp.query_plan
			--,stmts.st.value('(@StatementOptmLevel)[1]','sysname') as StatementOptmLevel,
			--stmts.st.value('(@StatementOptmEarlyAbortReason)[1]','sysname') as StatementOptmEarlyAbortReason,
			--stmts.st.value('(QueryPlan/@CachedPlanSize)[1]','bigint') as CachedPlanSize,
			--stmts.st.value('(QueryPlan/@CompileTime)[1]','bigint') as CompileTime,
			--stmts.st.value('(QueryPlan/@CompileCPU)[1]','bigint') as CompileCPU,
			--stmts.st.value('(QueryPlan/@CompileMemory)[1]','bigint') as CompileMemory	
			into tempdb.dbo.query_plans_io		       
			FROM sys.dm_exec_query_stats AS qs
			JOIN sys.dm_exec_cached_plans cp 
				ON qs.plan_handle = cp.plan_handle
					CROSS APPLY	sys.dm_exec_sql_text(qs.sql_handle) AS st
					CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
					--CROSS APPLY query_plan.nodes('ShowPlanXML/BatchSequence/StmtSimple') stmts(st)
					CROSS APPLY (SELECT [dbid], [dbid_execute]
							FROM (
								SELECT attribute, value
									FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
								WHERE attribute IN ('dbid','dbid_execute')
							) AS pa
							PIVOT
							(
								MAX([value]) FOR attribute IN ([dbid], [dbid_execute])
							) AS pt) pa
			ORDER BY
					(qs.total_logical_reads + qs.total_logical_writes) DESC;  


	WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	SELECT 
		iop.*,
		stmts.st.value('(@StatementOptmLevel)[1]','sysname') as StatementOptmLevel,
		stmts.st.value('(@StatementOptmEarlyAbortReason)[1]','sysname') as StatementOptmEarlyAbortReason,
		stmts.st.value('(./QueryPlan/@CachedPlanSize)[1]','bigint') as CachedPlanSize,
		stmts.st.value('(./QueryPlan/@CompileTime)[1]','bigint') as CompileTime,
		stmts.st.value('(./QueryPlan/@CompileCPU)[1]','bigint') as CompileCPU,
		stmts.st.value('(./QueryPlan/@CompileMemory)[1]','bigint') as CompileMemory
	FROM tempdb.dbo.query_plans_io iop
	CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') stmts(st)
	WHERE query_plan is not null;

	DROP TABLE tempdb.dbo.query_plans_io ;