Query plans are the blueprints that relational database management systems (RDBMS) use to execute queries efficiently. However, query plans are not static and can change over time due to various factors. In this article, you will learn about the most common causes of query plan changes in RDBMS and how they can affect the performance and reliability of your database applications.
RDBMS rely on statistics to estimate the distribution and cardinality of data in tables and indexes. Statistics help the query optimizer choose the best plan for a given query based on the cost and benefit of different options. However, statistics can become outdated as data changes, leading to inaccurate estimates and suboptimal plans. Therefore, RDBMS periodically update statistics based on certain thresholds or events, such as data modifications, schema changes, or manual commands. Statistics updates can trigger query plan changes, which can be beneficial or detrimental depending on the nature and frequency of the data changes.
In my experience query plan changes occur due to a couple of reasons: 1. Change in host server specs/config or actual migration to a different server. 2. Change in statistics (Updated Vs Stale Stats). 3. Change/Upgrade of RDBMS Version. 4. Use of functionality like Partitioning. 5. Index rebuild/ reorg is also a slight factor.
Some of the reasons why query plan changes are: 1. Statistics change: Whenever there is change in distribution of data, optimizer can choose different plan. It could be either automatic or manual change. 2. Hardware changes: IF you have done any changes in the hardware configurations. 3. Parameter sniffing: While compiling a SP, the optimizer uses the parameter values to generate query plan. If the parameter values change, the optimizer may choose a different plan. 4. Memory pressure: When the SQL Server is running low on memory, it may remove query plans from the cache. This can cause recompilation & choose a different plan. 5. Schema changes: Changes to the schema of a table. 6. Data changes: Insert, update or delete
Yes, the query plan changes because of statistics changes. But in the biggest production databases, these are happening automatically and almost nobody doesn't really change the schema or do manual commands, and this also isn't a lucrative idea. The most important thing is SQL query clever design, as I said before in other contributions.
Parameter sniffing is a feature of RDBMS that allows the query optimizer to use the values of parameters passed to a query or a stored procedure as inputs for statistics estimation. This can result in more accurate and efficient plans for queries that use parameters. However, parameter sniffing can also cause query plan changes when different parameter values result in different optimal plans. For example, a query that uses a parameter to filter a large table may have different plans depending on whether the parameter value is selective or not. Parameter sniffing can lead to inconsistent and unpredictable query performance, especially for queries that have skewed or non-uniform data distribution.
To avoid parameter sniffing or even benefit from them, the entire database design and every SQL realized that must have the real situation thought before, as much can be known. Those parameters and only those that are already designated to drastically reduce the result in an SQL with multiple destinations can be let to decide plan changes, but the rest of them must be outer selected or left outer joined. In that way in where all important subselect inner joins must be let free to be decided which of them will be restricted first by the automatic plan changes.
Plan cache is a memory area where RDBMS store the query plans that have been compiled and executed. Plan cache helps reduce the overhead of query compilation and reuse the existing plans for subsequent executions. However, plan cache is not infinite and can be evicted or cleared by various factors, such as memory pressure, configuration changes, recompilation hints, or manual commands. Plan cache eviction can cause query plan changes when the evicted plans are recompiled with different statistics, parameters, or optimizer settings. Plan cache eviction can also affect the performance and stability of your database applications, as recompilation can consume more CPU and memory resources.
I don't think that the plan cache eviction is the real problem that this thing can change the result in a desired way. IMO, and in my entire experience, I still believe that the proper database and SQL design are more important than these situations explained here in which the SQL query builder seems more like a victim than an experienced builder. We can't change things according to what RDBMS are doing instead of let us to really think and proper build them, according to real situation we are design to be stored and querried.
Optimizer is the component of RDBMS that generates and evaluates the possible query plans based on the query syntax, statistics, parameters, and optimizer settings. Optimizer is constantly evolving and improving to produce better and faster query plans for various scenarios and workloads. However, optimizer changes can also cause query plan changes when the optimizer introduces new features, algorithms, or rules that affect the plan selection or execution. For example, an optimizer change may enable a new join type, a new index access method, or a new parallelism option for a query. Optimizer changes can have positive or negative impacts on the query performance and behavior, depending on the compatibility and suitability of the new features for your database environment.
I think the optimizer are made well done, even if it, itself, are optimized sometimes. In continuation I think and believe that a designer well experienced who know RDBMS very well, and also about the real situation designed, will know these all in that way in where to make right decisions on database and also on every query design. Because, otherwise, SQL design and unpredictable plan changes are just an reality not well known. I don't want to go to the other part of balance and say that will not happen ever unpredictable or undesired results. But the main problem is how to really build a good SQL.
Schema changes are the modifications that you make to the structure or definition of your database objects, such as tables, columns, indexes, constraints, or views. Schema changes can affect the query plans in several ways, depending on the type and scope of the changes. For example, adding or dropping a column can change the size and layout of a table or an index, which can affect the statistics and the access methods. Altering or creating an index can change the availability and cost of different index operations, such as scans, seeks, or lookups. Adding or removing a constraint can change the validity and cardinality of the data, which can affect the join and filter conditions. Schema changes can improve or degrade the query performance and functionality, depending on the design and implementation of the changes.
This is one of the reasons of having a robust planning phase. The business requirement document should be the basis of making technical recommendations. For instance, if you're working on an IoT project, it's obvious that you'll most likely need to use a non-relational database like Mongo, Couchbase or Cassandra. When unstructured/frequently changing data structure is at the core of a project, you shouldn't even use RDBMS in the first place.
to change this, we must already know how optimizer work and how SQL must be made to reduce as much as we can every undesired or unpredictable automatic plan changes. We can make a lot of tests and try different scenarios at first and every time when schema changes. Or opposite, to think from the beginning and every time when we make schema changes, in that way in which all to be fine all the time, without change anything. It's the same as in the design patterns programming way of thinking. To change this, we must already know how we had built previous SQLs, and you optimizer work and how SQL must be made to reduce as much as we can every undesired or unpredictable automatic plan changes.
Data changes are the operations that you perform to insert, update, delete, or load data into your database tables or indexes. Data changes can influence the query plans in various ways, depending on the volume and frequency of the changes. For example, inserting or deleting a large number of rows can change the density and distribution of the data, which can affect the statistics and the plan choices. Updating a large number of rows can change the values and order of the data, which can affect the index maintenance and the sort operations. Loading data from external sources can change the format and quality of the data, which can affect the data conversion and validation steps. Data changes can optimize or compromise the query performance and accuracy, depending on the nature and impact of the changes.
The data change that has the most impact on query plans is data volume. If your table starts growing a lot or updating the data a lot more - this could impact the query plan. I would say the most likely thing to happen is have a table start growing faster due to increased usage of a feature or even a potential error in code. (Say a log table starts logging data twice due to a bug). The best thing to do in this scenario may be to compare query plans against an older backup of the database vs the current database to help you understand what is going on.
Data changes are the operations that you perform to insert, update, delete, or load data into your database tables or indexes. Data changes can influence the query plans in various ways, depending on the volume and frequency of the changes. For example, inserting or deleting a large number of rows can change the density and distribution of the data, which can affect the statistics and the plan choices. Updating a large number of rows can change the values and order of the data, which can affect the index maintenance and the sort operations. Loading data from external sources can change the format and quality of the data, which can affect the data conversion and validation steps.