Full Screen
Recommendations in the Dashboard
The SQLGrease Recommendation Engine monitors top query workload throughout the day and performs analysis to determine if there are tuning opportunities. Different types of tuning opportunities are analyzed. One tuning opportunity that is analyzed is unsargable predicates. An unsargable predicate prevents the potential use of indexes. This describes how to interpret the information provided by the recommendation engine for unsargable predicates.
Dashboard Recommendations Recommendations can be reached from the dashboard. The dashboard appears when you first login. This will contain recommendations for the different instances in your environment. In the case you use instance groups, this will contain the instances that are part of the selected group.
1 Recommendations Last Day This shows the number of recommendations that occurred in the last day.
2 Muted Recommendations Last Day Recommendations can be muted. This shows the number of recommendations that occurred in the last day but were muted. Clicking this will take you to the history view for recommendations.
Full Screen
History View - Recommendations
The History View allows you to see the impact the queries with recommendations are having relative to your total workload.
1 Non Recommendation Query Workload This series in the chart contains all other workload unrelated to queries with recommendations.
2 Recommendation Query Workload There is an individual series for every query with recommendations. Clicking on a series for a given query will take you to the details of the recommendation(s) associated with it.
Full Screen
Query Detail View - Recommendations
The Query Detail View contains detailed information for the selected query. The recommendations tab contains a list of recommendations for the selected query. This section contains information for an unsargable predicate.
1 Mute this recommendation Recommendations can be muted if it is determined they are not applicable. Clicking this button will cause the unsargable predicate recommendation to be muted for the selected query. Muted recommendations can be unmuted.
2 Plan Hash The execution plan containing the recommendation. Multiple plans might have an unsargable predicate recommendation for a given query. Multiple plans might be shown here as indicated by the plan hash.
3 Operator/Node This is the logical operator and node in the execution plan where the unsargable predicate occurs. Clicking on this link will open the plan and hilite this node in the plan. Many times in a large complex query it is difficult to find where the unsargable predicate occurs much less that one exists. This helps simplify the process.
4 Estimate Rows/Estimated Rows Read This contains the number of rows estimated to be returned from the operator relative to the number of rows read. Having a very high ratio of estimated rows read to estimate rows indicates the operator is not very efficient since it requires reading so many rows in order to produce a few rows.
5 FunctionUnsargable predicates are typically caused by applying a function to a column in a table. This shows the name of the function applied that is making the predicate unsargable. Clicking on this will take you to a SQLGrease blog post with details on how to improve performance of the given function when used in an unsargable predicate.
6 Columns These are the column(s) that are unsargable. There can be one or more columns associated with a given function.
Full Screen
Plan View - Unsargable Predicate
When clicking on the Operator/Node, a new tab will be launched with the plan in it. This view will contain the plan and have the Operator/Node hilited in it.