Query plan enhancement
Methods, systems, and apparatus, including computer programs encoded on a computer storage medium, for analyzing and enhancing query plans. In one aspect, a method includes receiving a query plan, automatically identifying, by one or more computers, one or more operations included within the query plan that may degrade the performance of a query, and providing a report that identifies the identified operations as performance degrading operations.
1. A computer-implemented method comprising:
receiving a particular query plan comprising a plurality of query operations, the query plan selected by a user for evaluation;
accessing, by one or more computers, one or more rules that identify query operations that degrade performance of a query plan or that render a query plan inoperable, wherein each of the one or more rules that identify query operations that degrade the performance of a query plan are associated with a single warning rating, and each of the one or more rules that identify query operations that render a query plan inoperable are associated with a single failing rating;
evaluating, by the one or more computers, each of the plurality of query operations against the one or more rules;
automatically identifying, by the one or more computers based on the evaluation of each of the plurality of query operations against the one or more rules, one or more query operations included in the particular query plan that violate one or more of the rules;
determining, for each of the one or more identified query operations that violate one or more of the rules, whether the rule violated by an identified query operation indicates that the identified query operation degrades performance of the particular query plan, or that the identified query operation renders the particular query plan inoperable;
assigning, for each of the one or more identified query operations that violate one or more of the rules, the rating associated with the rule violated by an identified query operation, wherein the assigned rating is (i) the single warning rating if the violated rule indicates that the identified query operation degrades performance of the particular query plan, or (ii) the single failure rating if the violated rule indicates that the identified query operation renders the particular query plan inoperable;
assigning an overall rating to the particular query plan based on the rating assigned to each of the one or more identified query operations, the overall rating being one of the single warning rating or the single failing rating;
generating a report that references: (i) the one or more identified query operations that violate one or more of the rules, (ii) the assigned rating for each of the identified one or more query operations that violate one or more of the rules, and (iii) the assigned overall rating for the particular query plan; and
providing the report for output to the user;
wherein the report further comprises:
a reason for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, wherein the reason includes the respective violated rule; and
a hyperlink to a tip for each rating, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation; and wherein the method further comprises:
receiving a modified particular query plan that the user has selected for evaluation, wherein one or more of the identified query operations are modified based on the rating for the respective query operation, the reason for the rating for the respective query operation, and the recommendation for improving the respective query operation.
2. The method of claim 1 , wherein the report further comprises:
a reason for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, wherein the reason includes the respective violated rule; and
a hyperlink to a tip for each rating, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation; and wherein the method further comprises:
receiving a modified particular query plan that the user has selected for evaluation, wherein one or more of the identified query operations are deleted based on the rating for the respective query operation, the reason for the rating for the respective query operation, and the recommendation for improving the respective query operation.
3. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying a query operation that includes a request to perform a table scan.
4. The method of claim 3 , further comprising suggesting parameters for a new index in response to automatically identifying a query operation that includes the request to perform a table scan.
5. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying a query operation that includes a request to create or use a temporary table.
6. The method of claim 5 , wherein automatically identifying a query operation that includes a request to create or use a temporary table comprises automatically identifying a “create table” command in context with a hash character.
7. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying a query operation that includes a request to perform an outer join operation.
8. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying a query operation that includes a request to perform an implicit conversion.
9. The method of claim 8 , wherein automatically identifying a query operation that includes a request to perform an implicit conversion operation comprises automatically identifying a “convert_implicit” command.
10. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying a query operation that includes more than a predetermined number of table join operations.
11. The method of claim 10 , wherein the predetermined number is five.
12. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate the one or more of the rules comprises automatically identifying a query operation that includes a request to return distinct query results.
13. The method of claim 12 , wherein automatically identifying a query operation that includes a request to return distinct query results comprises identifying a “select distinct” command.
14. The method of claim 1 , wherein automatically identifying one or more query operations included in the particular query plan that violate one or more of the rules comprises automatically identifying that a query returns more than a predetermined amount of data.
15. The method of claim 14 , wherein the predetermined amount comprises one thousand rows.
16. The method of claim 14 , wherein automatically identifying that a query returns more than a predetermined amount of data further comprises automatically determining that a value associated with a “row count” parameter exceeds the predetermined amount.
17. A system comprising:
one or more computers; and
a computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising:
receiving a particular query plan comprising a plurality of query operations, the query plan selected by a user for evaluation;
accessing, by the one or more computers, one or more rules that identify query operations that degrade performance of a query plan or that render a query plan inoperable, wherein each of the one or more rules that identify query operations that degrade the performance of a query plan are associated with a single warning rating, and each of the one or more rules that identify query operations that render a query plan inoperable are associated with a single failing rating;
evaluating, by the one or more computers, each of the plurality of query operations against the one or more rules;
automatically identifying, by the one or more computers based on the evaluation of each of the plurality of query operations against the one or more rules, one or more query operations included in the particular query plan that violate one or more of the rules;
determining, for each of the one or more identified query operations that violate one or more of the rules, whether the rule violated by an identified query operation indicates that the identified query operation degrades performance of the particular query plan, or that the identified query operation renders the particular query plan inoperable;
assigning, for each of the one or more identified query operations that violate one or more of the rules, the rating associated with the rule violated by an identified query operation, wherein the assigned rating is (i) the single warning rating if the violated rule indicates that the identified query operation degrades performance of the particular query plan, or (ii) the single failure rating if the violated rule indicates that the identified query operation renders the particular query plan inoperable;
assigning an overall rating to the particular query plan based on the rating assigned to each of the one or more identified query operations, the overall rating being one of the single warning rating or the single failing rating;
generating a report that references: (i) the one or more identified query operations that violate one or more of the rules, (ii) the assigned rating for each of the identified one or more query operations that violate one or more of the rules, and (iii) the assigned overall rating for the particular query plan; and
providing the report for output to the user;
wherein the report further comprises:
a reason for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, wherein the reason includes the respective violated rule; and
a hyperlink to a tip for each rating, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation; and wherein the method further comprises:
receiving a modified particular query plan that the user has selected for evaluation, wherein one or more of the identified query operations are modified based on the rating for the respective query operation, the reason for the rating for the respective query operation, and the recommendation for improving the respective query operation.
18. A non-transitory computer storage medium encoded with a computer program, the program comprising instructions that when executed by one or more computers cause the one or more computers to perform operations comprising:
receiving a particular query plan comprising a plurality of query operations, the query plan selected by a user for evaluation;
accessing, by the one or more computers, one or more rules that identify query operations that degrade performance of a query plan or that render a query plan inoperable, wherein each of the one or more rules that identify query operations that degrade the performance of a query plan are associated with a single warning rating, and each of the one or more rules that identify query operations that render a query plan inoperable are associated with a single failing rating;
evaluating, by the one or more computers, each of the plurality of query operations against the one or more rules;
automatically identifying, by the one or more computers based on the evaluation of each of the plurality of query operations against the one or more rules, one or more query operations included in the particular query plan that violate one or more of the rules;
determining, for each of the one or more identified query operations that violate one or more of the rules, whether the rule violated by an identified query operation indicates that the identified query operation degrades performance of the particular query plan, or that the identified query operation renders the particular query plan inoperable;
assigning, for each of the one or more identified query operations that violate one or more of the rules, the rating associated with the rule violated by an identified query operation, wherein the assigned rating is (i) the single warning rating if the violated rule indicates that the identified query operation degrades performance of the particular query plan, or (ii) the single failure rating if the violated rule indicates that the identified query operation renders the particular query plan inoperable;
assigning an overall rating to the particular query plan based on the rating assigned to each of the one or more identified query operations, the overall rating being one of the single warning rating or the single failing rating;
generating a report that references: (i) the one or more identified query operations that violate one or more of the rules, (ii) the assigned rating for each of the identified one or more query operations that violate one or more of the rules, and (iii) the assigned overall rating for the particular query plan; and
providing the report for output to the user;
wherein the report further comprises:
a reason for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, wherein the reason includes the respective violated rule; and
a hyperlink to a tip for each rating, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation; and wherein the method further comprises:
receiving a modified particular query plan that the user has selected for evaluation, wherein one or more of the identified query operations are modified based on the rating for the respective query operation, the reason for the rating for the respective query operation, and the recommendation for improving the respective query operation.
19. A computer-implemented method for evaluating a particular query plan, the method comprising:
receiving a particular query plan comprising a plurality of query operations, the query plan selected by a user for evaluation;
accessing, by one or more computers, one or more rules that identify query operations that degrade performance of a query plan or that render a query plan inoperable, wherein each of the one or more rules that identify query operations that degrade the performance of a query plan are associated with a single warning rating, and each of the one or more rules that identify query operations that render a query plan inoperable are associated with a single failing rating;
evaluating, by the one or more computers, the particular query plan, wherein the evaluating comprises:
analyzing encoded information included in the particular query plan in order to produce the plurality of query operations;
evaluating each of the plurality of query operations against the one or more rules;
identifying, based on the evaluation of each of the plurality of query operations against the one or more rules, one or more query operations included in the particular query plan that violate one or more of the rules;
determining, for each of the one or more identified query operations that violate one or more of the rules, whether the rule violated by an identified query operation indicates that the identified query operation degrades performance of the particular query plan, or that the identified query operation renders the particular query plan inoperable;
assigning, for each of the one or more identified query operations that violate one or more of the rules, the rating associated with the rule violated by an identified query operation, wherein the assigned rating is (i) the single warning rating if the violated rule indicates that the identified query operation degrades performance of the particular query plan, or (ii) the single failure rating if the violated rule indicates that the identified query operation renders the particular query plan inoperable; and
assigning an overall rating to the particular query plan based on the rating assigned to each of the one or more identified query operations, the overall rating being one of the single warning rating or the single failing rating;
generating, based on the evaluation, a report that references: (i) the one or more identified query operations that violate one or more of the rules, (ii) the assigned rating for each of the identified one or more query operations that violate one or more of the rules, and (iii) the assigned overall rating for the particular query plan; and
providing the report for output to the user;
wherein the report further comprises:
a reason for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, wherein the reason includes the respective violated rule; and
a hyperlink to a tip for each rating, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation; and wherein the method further comprises:
receiving a modified particular query plan that the user has selected for evaluation, wherein one or more of the identified query operations are modified based on the rating for the respective query operation, the reason for the rating for the respective query operation, and the recommendation for improving the respective query operation.
20. The method of claim 19 , wherein the particular query plan is an extensible markup language (XML) document comprising information encoded in XML.
21. The method of claim 19 , wherein a query operation that may degrades the performance of the particular query plan is one of an outer join, a table scan, or an implicit conversion.
22. The method of claim 19 , wherein a query operation that degrades the performance of the particular query plan comprises more than a predetermined number of table joins.
23. The method of claim 19 , wherein a query operation that the performance of the particular query plan comprises a query operation returning more than a predetermined number of rows of data.
24. The method of claim 19 , wherein a query operation that degrades the performance of the particular query plan comprises an operation that uses the DISTINCT keyword in a SELECT statement.
25. The method of claim 19 , wherein a query operation that degrades the performance of the particular query plan comprises a query operation that uses a temporary table.
26. The method of claim 19 , further comprising altering the particular query plan to remove one or more of the one or more identified query operations that violate one or more of the rules.
27. The method of claim 19 , wherein the report further comprises:
a hyperlink to a tip for the rating assigned to each of the one or more identified query operations that violate one or more of the rules, the tip providing further information regarding the rating, the reason for the rating, and a recommendation for improving the respective identified query operation.
28. The method of claim 27 , wherein the tip is included in an article associated with the hyperlink.