Is there a way to query sys.dm_exec_text_query_plan so that it only returns plans that performed at least one seek on index IX_SalesOrderDetail_ProductID? For example under <OptimizerStatsUsage> I can see all my indexes listed however the query itself doesn't even touch that index.
I got another related question with regards to single use plans / plan aging hope it's ok to post it in the same topic.
So, I can see in sys.dm_exec_cached_plans that I got over 90,000 single use plans in the "Prepared" category (out of 100,000) and 30,000 single use plans in the "Ad-hoc" group (out of 31,000). Let's say I move some prepared queries to stored procedures accountable for all those single use plans - when will those plan get cleared from the cache (since there should only be 1 for the SP) or is this something I need to clean up manually?
What is the drawback of having so many single use plans (other than the cache being large taking lots of memory)? What performance improvement can I expect if I decrease the number of single use plans?
Will the ALTER INDEX (or DROP/CREATE) statement automatically recompile any plans that used a given index? If so what happens with the old plans? Would they get wiped immediately?
Allzu viel ist ungesund...