Predicting index fragmentation caused by database statements
Systems and techniques for determining a total predicted index fragmentation associated with executing a structured query language statement are described. An execution plan associated with the structured query language statement may be determined. A plurality of tables estimated to be affected by execution of the structured query language statement may be determined. For individual tables, an estimated number of rows that the structured query language statement is to affect may be determined. A predictive model, such as a support vector machine, may be used to determine, for individual tables whose indexes are estimated to be affected, a predicted change in an average index fragmentation percentage associated with individual indexes on the affected tables. The sum of the predicted change in the average index fragmentation percentage of affected indexes may be determined to determine the total predicted index fragmentation associated with executing the structured query language statement.
1. A computer-implemented method, comprising:
determining an execution plan associated with a structured query language (SQL) statement;
identifying, based on the execution plan, a first table estimated to be affected by the SQL statement;
determining a first index definition of a first index on the first table, wherein the first index includes a lookup table of the first table;
applying a first weighted value to the first index based on determining a stability of the first index, where the stability of the first index is based on whether the first index is subject to less fragmentation than another index on the first table;
determining, based on a predictive model, a first predicted change in an average index fragmentation percentage associated with the first index;
identifying a second table predicted to be affected by the SQL statement;
determining a second index definition of a second index on the second table;
applying a second weighted value to the second index based on determining the stability of the second index, where the stability of the second index is based on whether the second index is subject to less fragmentation than another index on the second table;
determining, based on the predictive model, a second predicted change in the average index fragmentation percentage associated with the second index;
determining a total predicted fragmentation based at least in part on the first weighted value, the second weight value, the first predicted change in the average index fragmentation percentage associated with the first index and the second predicted change in the average index fragmentation percentage associated with the second index; and
performing, based on the total predicted fragmentation, at least one remedial action to reduce fragmentation, the at least one remedial action being (a) creating a remedial SQL statement that creates less index fragmentation compared to other SQL statements that achieve similar results, (b) scheduling performance of a remedial SQL statement prior to an index rebuild or defragmentation that is predicted to less fragmentation, (c) identifying a remedial SQL statement for which an index may be dropped prior to executing the SQL statement and the index recreated after execution, (d) creating an index for a table that incurs less fragmentation than an alternative type of index, or (e) a combination thereof.
2. The computer-implemented method of claim 1 , wherein the predictive model is based at least in part on a support vector machine.
3. The computer-implemented method of claim 1 , wherein determining, based on the predictive model, the first predicted change in the average index fragmentation percentage associated with the first index comprises determining at least one of:
a type of a SQL operation identified in the execution plan;
a data-type of each column included in the first index;
a fill factor of the first index;
an index type of the first index, the index type including one of a clustered type or a non-clustered type;
a page-count of the first index; or
a current average index fragmentation percentage associated with the first index.
4. The computer-implemented method of claim 1 , further comprising:
determining, based on the execution plan, a first set of operations to be performed on the first table;
for individual operations in the first set of operations, determining a first estimated number of rows affected in the first table, based on the execution plan;
determining, based on the execution plan, a second set of operations to be performed on the second table; and
for each operation in the second set of operations, determining a second estimated number of rows affected in the second table, based on the execution plan.
5. The computer-implemented method of claim 4 , further comprising:
determining, based on the predictive model, the total predicted fragmentation based at least in part on:
the first predicted change in the average index fragmentation percentage associated with the first index;
the first estimated number of rows affected in the first table;
the second predicted change in the average index fragmentation percentage associated with the second index; and
the second estimated number of rows affected in the second table.
6. The computer-implemented method of claim 1 , further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold; and
scheduling execution of the SQL statement prior to defragmenting at least one of the first index or the second index.
7. The computer-implemented method of claim 1 , further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold; and
scheduling execution of the SQL statement prior to rebuilding at least one of the first index or the second index.
8. One or more non-transitory computer-readable media storing instructions that are executable by one or more processors to perform operations comprising:
determining an execution plan associated with a structured query language (SQL) statement;
identifying, based on the execution plan, a first table estimated to be affected by the SQL statement;
determining a first index definition of a first index on the first table, wherein the first index includes a lookup table of the first table;
applying a first weighted value to the first index based on determining a stability of the first index, where the stability of the first index is based on whether the first index is subject to less fragmentation than another index on the first table;
determining, based on a predictive model, a first predicted change in an average index fragmentation percentage associated with the first index;
identifying a second table predicted to be affected by the SQL statement;
determining a second index definition of a second index on the second table;
applying a second weighted value to the second index based on determining the stability of the second index, where the stability of the second index is based on whether the second index is subject to less fragmentation than another index on the second table;
determining, based on the predictive model, a second predicted change in the average index fragmentation percentage associated with the second index;
determining a total predicted fragmentation based at least in part on the first weighted value, the second weight value, the first predicted change in the average index fragmentation percentage associated with the first index and the second predicted change in the average index fragmentation percentage associated with the second index; and
performing, based on the total predicted fragmentation, at least one remedial action to reduce fragmentation, the at least one remedial action being (a) creating a remedial SQL statement that creates less index fragmentation compared to other SQL statements that achieve similar results, (b) scheduling performance of a remedial SQL statement prior to an index rebuild or defragmentation that is predicted to less fragmentation, (c) identifying a remedial SQL statement for which an index may be dropped prior to executing the SQL statement and the index recreated after execution, (d) creating an index for a table that incurs less fragmentation than an alternative type of index, or (e) a combination thereof.
9. The one or more non-transitory computer-readable media of claim 8 , the operations further comprising:
modifying the SQL statement to create a modified statement;
determining a modified execution plan associated with the modified statement;
determining a modified total predicted fragmentation associated with the modified statement; determining that the modified total predicted fragmentation associated with the modified statement is less than the total predicted fragmentation associated with the SQL statement; and
executing the modified statement instead of the SQL statement.
10. The one or more non-transitory computer-readable media of claim 8 , the operations further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold;
dropping at least the first index;
executing the SQL statement; and
recreating at least the first index.
11. The one or more non-transitory computer-readable media of claim 8 , the operations further comprising:
determining a third index definition of a third index on the first table;
determining, based on the predictive model, a third predicted change in the average index fragmentation percentage associated with the third index;
determining that the third predicted change in the average index fragmentation percentage associated with the first index is less than the first predicted change in the average index fragmentation percentage associated with the first index; and
selecting the third index for use with the first table.
12. The one or more non-transitory computer-readable media of claim 11 , the operations further comprising:
preventing the first index from being used with the first table.
13. The one or more non-transitory computer-readable media of claim 8 , wherein the first index on the first table comprises at least one of:
a current average index fragmentation percentage associated with the first index;
an index type indicating whether the first index is a clustered index or a non-clustered index; or
a page count associated with the first index.
14. A server, comprising:
one or more processors; and
one or more non-transitory computer-readable media storing instructions that are executable by the one or more processors to perform operations comprising:
determining an execution plan associated with a structured query language (SQL) statement;
identifying, based on the execution plan, a first table estimated to be affected by the SQL statement;
determining a first index definition of a first index on the first table, wherein the first index includes a lookup table of the first table;
applying a first weighted value to the first index based on determining a stability of the first index, where the stability of the first index is based on whether the first index is subject to less fragmentation than another index on the first table;
determining, based on a predictive model, a first predicted change in an average index fragmentation percentage associated with the first index;
identifying a second table predicted to be affected by the SQL statement;
determining a second index definition of a second index on the second table;
applying a second weighted value to the second index based on determining the stability of the second index, where the stability of the second index is based on whether the second index is subject to less fragmentation than another index on the second table;
determining, based on the predictive model, a second predicted change in the average index fragmentation percentage associated with the second index;
determining a total predicted fragmentation based at least in part on the first weighted value, the second weight value, the first predicted change in the average index fragmentation percentage associated with the first index and the second predicted change in the average index fragmentation percentage associated with the second index; and
performing, based on the total predicted fragmentation, at least one remedial action to reduce fragmentation, the at least one remedial action being (a) creating a remedial SQL statement that creates less index fragmentation compared to other SQL statements that achieve similar results, (b) scheduling performance of a remedial SQL statement prior to an index rebuild or defragmentation that is predicted to less fragmentation, (c) identifying a remedial SQL statement for which an index may be dropped prior to executing the SQL statement and the index recreated after execution, (d) creating an index for a table that incurs less fragmentation than an alternative type of index, or (e) a combination thereof.
15. The server of claim 14 , the operations further comprising:
determining, based on the execution plan, a first set of operations to be performed on the first table;
for individual operations in the first set of operations, determining a first estimated number of rows affected in the first table, based on the execution plan;
determining, based on the execution plan, a second set of operations to be performed on the second table; and
for individual operations in the second set of operations, determining a second estimated number of rows affected in the second table, based on the execution plan.
16. The server of claim 15 , the operations further comprising:
determining, based on the predictive model, the total predicted fragmentation based at least in part on:
the first predicted change in the average index fragmentation percentage associated with the first index;
the first estimated number of rows affected in the first table;
the second predicted change in the average index fragmentation percentage associated with the second index; and
the second estimated number of rows affected in the second table.
17. The server of claim 14 , the operations further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold; and
scheduling execution of the SQL statement prior to defragmenting at least one of the first index or the second index.
18. The server of claim 14 , the operations further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold; and
scheduling execution of the SQL statement prior to rebuilding at least one of the first index or the second index.
19. The server of claim 14 , the operations further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold;
dropping at least the first index;
executing the SQL statement; and
recreating at least the first index.
20. The server of claim 14 , the operations further comprising:
determining that the total predicted fragmentation satisfies a predetermined threshold;
dropping at least the first index;
executing the SQL statement; and
recreating at least the first index after executing the SQL statement.