The famous Yankee, Yogi Berra once said, “If you don't know where you are going, you'll end up someplace else.” This applies to lots of different things and since this is a blog about SQL, you likely know where I am going with this - Execution Plans. Simply put, Execution Plans, sometimes referred to as Query Plans, are maps of the query's data retrieval methods.
In my spare time, I am an avid hiker. When my buddies and I pick a place to explore, we read about the routes different hikers used. We consider what is mentioned around how long it takes, how difficult the hike is as it relates to the terrain, and the available surrounding resources available (is there a source for fresh water?)....and if the trail has a high number of bear sightings, of course.... A DBA should do the same and consider the variables involved with query access paths. Can shorter execution times be accomplished? Is the query using full table scans or indexes? Is the query draining resources?
Enter Quest Foglight Performance Investigator's Plan Analysis. The PI Plan Analysis provides critical insight into your query's path. Let's have a look. Here is an example screenshot of the PI Plan Analysis interface.
To access Plan Analysis, navigate to the SQL PI drilldown, highlight a SQL statement, and you'll see the 'Analyze Plan' button.
Once you click on the 'Analyze Plan' button, you'll see the Plan Analysis interface.
In the top left, notice that Plan Analysis notes an "Actual" Plan versus an Estimated Plan.
Note: This Foglight text may be a bit misleading and it will be updated in a future release.
The definitions are as follows:
- Estimated = User Generated
- Actual -= Cache Collected
Via the tabs, the Plan Analysis provides a breakdown of the operations, a percentage-based operator cost, and a breakdown of both CPU and I/O cost. Additionally, it lists the objects involved along the way. This information allows for easier identification of where bottleneck or 'expensive' areas may be along the path.
In the top right corner, there are options to Compare Plans, generate an Estimated Plan, or open the plan in SSMS (SQLServer Management Studio).
In the words of The A Team's Hannibal.... "I love it when a plan comes together!"
Visit the Foglight for Databases page for a free trial.
