CodePlex Project: Qpee Tools 1.0

Posted by Jason on Sunday, August 24, 2008 to SQL Server 2008, Heroes Happen Here
986 Views | 0 Comments |

This is going to be my submission for the SQL Heroes contest. Have you entered? I don't really care about winning but it would nice if it kick started a community generating useful SQL tools. I may get in a few point revisions in before September especially if I get some good feedback.

I was totally going to create a set of SSRS reports that combine the historic data from the data collectors with real time OS info from WMI. However, the GUI\Manageability team did such a great job that my UI skills pale in comparison. I changed directions at the last minute after RTM.

Please check it out and let me know what you think. I have been staring at this project for the past week+ so my perspective may be off. Let me know if something doesn't make sense. Here is the description:

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.

Later on, I am going to ask for contest votes but only if you think it is the best submission. Download here.

 

P.S. Whooh.... Now that my vacation is over and this is submitted, I have whole backlog of posts to write. As a teaser, one of these will be an discussion eFight with my BrentO. He is sooooooooooo pwnt and he doesn't even realize it yet. hehe. Stay tuned.