Add these 5 Must-Have DMV Queries to your DBA Toolbelt!
In order to get the best results from these queries, you should run DBCC FREEPROCCACHE on your server a few minutes before you run them. Otherwise, the Age in Cache values will not be the same, which will skew the results of these queries. Of course, you should be aware for the effect of running DBCC FREEPROCCACHE on a production server before you do it.
Query 1 tells you which stored procedures are being called the most often, which is good to know for baseline and troubleshooting purposes. Don