In the last few months I’ve been creating a few snippets of code to extract as much useful information from the plan cache as possible. Why to do this?
We at Microsoft GBS PFE deliver a type of service called a SQL Performance Tuning and Optimization Clinic , which is directed at finding and resolving issues with your server configuration, database design and/or T-SQL coding from a performance standpoint, right there in your workplace. Given this, it makes sense to quickly explore what’s going on with your cached plans, for one.
Fixing performance issues by having a proper database design, indexing and well written code is not only better but also much less expensive that upgrading your servers hardware, as a way to minimize performance issues.
Having said that, let’s see how to retrieve valuable information that is sitting right there in your plan cache . If you ever looked at a graphical execution plan , it is nothing more than a well-formed XML file. So it makes sense to use xqueries to explore the richness of information that is stored there.
These are the scripts detailed further below:
Download scripts here: xqueries_plancache_part1
Part 2 of this series is available here .
Will allow you to get a sense if the engine is outputting any information on what may be perceived as inadequacy between the current database design and possible benefits of creating new or changing current indexes for your relevant workload.
It may be important to review this information against the current indexes, verify its validity against the importance of the workload it refers to, and always test before making any changes.
Last but not least, in an OLTP environment, never create redundant indexes.
Its output will resemble this:
This one is especially useful in SQL Server 2012 and above, where we have many more and quite useful warnings about the plan execution. Bob Beauchemin wrote a post about those
Still, you can use from SQL Server 2005 to 2008R2 to find warnings regarding ColumnsWithNoStatistics and NoJoinPredicate .
In SQL Server 2012 and above, this can also get warnings such as UnmatchedIndexes (where a filtered index could not be used due to parameterization) and convert issues ( PlanAffectingConvert ) that affect either Cardinality Estimate or the ability to choose a Seek Plan .
Also note that we cannot leverage this type of cache exploration queries to know where SpillToTempDb warnings occur, as they are only found when we output an actual execution plan, and not in cached execution plans.
And the output will resemble the following, with the statement, and the operation where the warning occurs:
Implicit conversions are “evil”. Now that I got that out, let me tell you why it is good to look for these, and code in such a way that we can get rid of them.
An implicit conversion will have an overhead in your code execution because it will cause CPU cycles to be wasted, and may also limit the query optimizer to make the most appropriate choices when coming up with the execution plan. This is mostly because the optimizer will not be able to do correct cardinality estimations, and with that, it will leverage scans where seeks would be more suitable (this is a generalization). Just look at the following example that will illustrate what I’m saying:
This will generate the following plan, with a
warning inside, because the
is actually of the NVARCHAR(15) data type, not an integer.
As I stated above, if you are not running on SQL Server 2012 or above, you get no such warning, and that is why searching the plan cache for implicit conversions can be an important exercise.
So this is the code:
That will get an output like the following, where you have the statement, and the “offending” conversion expression:
This one will allow you to find where we are doing index scans. Why is this important? As you might know, scans are not always a bad thing, namely if you are not being narrow enough in your search arguments (if any), where a scan may be cheaper than a few hundred or thousand seeks. You can read more on a post I did some time ago, regarding
a case of seeks and scans
The following code is most useful by allowing you to identify where scans are happening on tables with a high cardinality, and even look directly at the predicate for any tuning you might do on it.
And the output will resemble this:
Searching for lookups, namely on large tables, may be a good way to search for opportunities to fine tune performance from the index standpoint.
If a lookup is being done for a small subset of columns of a table, it may be a chance to review the existing non-clustered indexes, namely the one that is being used in conjunction with the lookup, and possibly add included columns to avoid these lookups.
The following code allows you to search for lookups and give you some information to quickly identify these potential issues:
And the output will resemble this:
I still have a few more xqueries to share, but I’ll leave those for another time. Hope you find these useful.
Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.