QPee Tools 1.0
QPee Tools is set of tools to log SQL Server 2008 query plans, identify plan regressions and performance problems because of plan regressions. It utilizes the query_hash and query_plan_hash features introduced in SQL Server 2008 along with the execution dynamic management views and functions.
QPee is built around a logger stored procedure and job. The logged query plans allow for comparisons with what is in the procedure cache and\or currently executing. Two tools are provided to assist with this analysis. See usage.sql for more information on these tools.
usp_Now - This is like Activity Monitor on steroids. Not only does it show what is currently executing, the current query plan, the previous query plan(if any) and various stats but it goes beyond just reporting on cumulative totals for CPU and IO. Cumulative totals do not provide a lot of value if you need to see what is currently pegging the system.
usp_CheckCache - You can run this against what is currently in the procedure cache to see if any queries are using new plans. For example, you could use this after applying a service pack or hotfix to see if there were any negative optimizer changes.
If either of these tools show that you are currently using a suboptimal plan, you can use the logged xml plan in a plan guide.
usp_PlanLogger_SchemaChange - There are valid reasons for plans to change like adding an index. This is a utility that removes plans that reference changed objects from the query plan logging table.