Find Top Recompiles

-- To investigate top recompiles , use this Stored Procedure

/*
exec sp_lib_TopRecompiles  10 -- Top 10 Queries having high recompilation
*/


CREATE PROCEDURE dbo.sp_lib_TopRecompiles @CountID INT = 25

AS
BEGIN

SET NOCOUNT ON

SELECT TOP (@CountID), sql_text.TEXT, sql_handle,
plan_generation_num, execution_count, dbid,objectid
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC
END

GO