I have recently visited our client and we found out that He has a very high number of stored procedures that have recompiled. Certainly, we have observed some perfomance decreasing. Fortunately , the client has alredy upgraded to SQL Server 2005(SP2) and apart from running SQL Server Profile to indentify those stored procedures we came out with the following query.
It gives you the top 10 stored procedures that have been recompiled.
select top 10
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
order by plan_generation_num desc
Note that the plan_generation_num indicates the number of times the stored procedure has recompiled. Very cool feature.