One area where a lot of development work went into SQL Server 2014 is the area of query optimization where a crucial component got re-written in order to improve query access and predictability.
To get a better idea about the significance of the change and the way how we deal with it in the SAP space, let’s first check what the component of cardinality estimation is responsible for.
When a query is executed for the first time, it is getting ‘compiled’. In opposite to a compilation of executables where we talk about binary compilation, the compilation of a SQL Statement is limited to figuring out the most efficient access to the data requested. This means, for example, the query engine needs to decide which index to pick in order to have the most efficient access to the data rows or, in case of table joins, the order of the join and the join methods need to be decided. At the end of the compilation there is a descriptive query plan which will be cached. During the execution phase the query plan is read from cache and interpreted. Since compilation of a query plan represents significant resource consumption, one usually is keen to cache these query plans.
At a very high level, the Query Optimizer uses the following input (in order) during the compilation phase:
There might be a few more inputs, but these are the main ones. The first step which often is decisive for the performance of the query is to estimate how many rows the query might deliver back based on the inputs above. It is not only estimated how many rows the query would deliver. In order to decide for the correct join method in e.g. a table join, one also needs to estimate how many rows would be delivered by each execution step and query branch accessing each of the tables involved in the join. This is done in the so called Cardinality Estimation (referred to as CE from now), which this article is about. The estimated number of rows will be the base for the next step of query optimization assuming that the estimated number of rows is correct. This means the choice of picking a certain index or the choice of a join order or join type is majorly impacted by the first step of estimating the cardinality of a query.
If that estimation is plain wrong, one can’t expect that the subsequent steps of query optimization will be able to derive an efficient plan, unless the query itself leans to a particular index like querying exactly one row by specifying the Primary Key or hardly any indexes are available to be used.
In order to estimate the number of rows returned, CE requires the statistics over indexes and columns of tables as a reliable source of information to figure out how often a certain value of a parameter value, submitted with the query, is present. Hence if there are no statistics or plain wrong or stale statistics, the best CE logic will hardly be able to predict the right values of returning rows for a query.
The role CE is playing in the process to generate a good query plan is important. In the last 15 years since introduction of SQL Server 7.0, most of the issues in Query Optimization around SAP workload which required SQL Server code fixes circled around issues in Cardinality Estimation.
At a very high level, the CE process in SQL Server 2012 was the same through all prior releases back to SQL Server 7.0 (the release before SQL 2000). In recent years we tried to put a lot of fixes or QFEs in regards to query optimization (including cardinality estimation) under trace flag in order not to cause a general regression over all those releases due to changed behavior by the fix.
With the release of SQL Server 2014, there were several good reasons to overhaul what was basically the CE as introduced 15 years ago. Our goals in SQL Server Development certainly were to avoid issues we so far experienced over the past 15 years and which were not fixable without a major redesign. However to state it pretty clearly as well, it was NOT a goal to avoid any regressions compared to the existing CE. The new SQL Server 2014 CE is NOT integrated following the principals of QFEs. This means our expectation is that the new SQL Server 2014 CE will create better plans for many queries, especially complex queries, but will also result in worse plans for some queries than the old CE resulted in. To define what we mean by better or worse plans, better plans have lower query latency and/or less pages read and worse plans have higher query latency and/or more pages read.
Knowing that the CE has changed and purposefully was not overcautiously re-architected to avoid regressions, you need to prepare a little more carefully for a SQL Server 2014 upgrade if you will utilize the new Cardinality Estimator. Based on our experience so far, you need to know first:
As delivered, SQL Server 2014 decides if the new Cardinality Estimator will be utilized for a specific database based simply on the compatibility level of that database within the SQL Server 2014 instance. In case the compatibility level of a specific database is set to ‘SQL Server 2014 (120)’ as shown below, the new CE is going to be used.
If the Compatibility level of a specific database is set to a lower value, like ‘SQL Server 2012 (110)’, the old CE is going to be used for the specific database.
As with other releases of SQL Server, upgrading your SQL Server instances in place or attaching existing databases from earlier releases of SQL Server will not result in a change of the compatibility level of a database. Hence by purely upgrading a SQL Server instance or attaching a database from SQL Server 2012 or earlier releases will not activate the new CE. One would need to change the compatibility level of such a database manually to ‘SQL Server 2014 (120)’ to activate the new CE. However toggling between the compatibility levels of 110 and 120 in case of SQL Server 2014 does have some other impact beyond the change of CE algorithms. E.g. the parallel insert functionality of SELECT INTO would be disabled by staying on the old compatibility levels as well.
Another alternative to enable/disable the different CE algorithms is the usage of a trace flag. Even with the compatibility level of a database set to ‘SQL Server 2014 (120)’, the trace flag would enforce the usage of the old CE. In opposite to the compatibility level which is applied to a specific database, the trace flag, if used as startup trace flag, would affect the usage of the CE SQL Server instance wide. This behavior might not be desired when many different databases are run in one instance. However, for SAP NetWeaver databases we still require only one SAP database per instance in productive deployments and only SAP NetWeaver databases if one consolidates multiple databases under one SQL Server instance. Hence the way we approach the change to the new CE would be the same and we will, for SAP, use the trace flag 9481 to enforce the old CE algorithms.
In this section we refer quite a bit to index or column statistics and the specific data that is captured in those. For folks less familiar with how index or column statistics are structured and what is contained in those, it would be advisable to read the Microsoft TechNet article, “ Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 ” before continuing.
One of the most significant changes in the new CE is the way how the CE calculates the selectivity of a combination of filters submitted with a query. In the old CE, the assumption was that the selectivity of the filters was independent, assuming any combination of values stored in the different columns the filters are applied to, would have the same probability of occurring. However very often reality is that combinations of values of different columns are not as independent. Especially with SAP workloads and schemas we have a lot of cases like this.
Let’s see at what the differences really are in the calculation of selectivity.
Let’s assume the following indexes against a table like this:
The query issued against the table looks like:
SQL Server’s query optimizer using the old CE would choose the first non-clustered index (FILCA~1) to access the table. This means there would be five filters which could be applied since all the five columns of the ~1 index are specified in the query.
In the single column statistics the densities of each single column look like:
Treating the selectivity/density of each filter/column independently, the question of the overall density would be:
0.125 * 0.3333 * 0.5 * 0.5 * 1 = 0.0104
A value which is confirmed when we look at the combined densities displayed in the statistics of index FILCA~1:
Assuming an even distribution over the values of the columns, we can assume density values as those displayed above. Be aware that CE using parameter sniffing would use the particular density value for a submitted value if the data distribution is uneven. But for simplicity reasons we assume an even distribution. Having a very small number of rows in our table of only 27776 rows, the estimated number of rows basically was calculated out of:
# of rows / (1/combined density) which would be: 27776/(1/0.01041667) = 289.33
This means CE would expect to get 289 rows back using this index FILCA~1
Looking at the query it becomes clear that using the non-clustered index, not all columns could be filtered that were specified in the where clause. After reading the 289 entries of the non-clustered index, the real data rows would be read and the additional column filters applied then.
Now to the change with the new CE, where we assume that the combination of the different column values are not as independent. The assumption is that certain combinations of values are not coming up at all, whereas others are showing up more frequently. An example of such dependencies between columns are found in all business processes covered with SAP applications and therefore in the underlying data in database tables. For example, materials or goods that are produced or stored in certain locations and warehouses only would have such a data distribution. Or, employees associated with a certain profit center or organization would also have such a data distribution. This means the reality is that the values in different columns are sometimes related to each other. In order to reflect that in the calculation of the possible selectivity/density, we apply a new formula with a so called ‘exponential back-off’. The logic would work like:
If we would calculate for 5 th or even 6 th filter value with d5^(1/16) or d6^(1/32), the values would be close to 1 and hence have little impact on the result anyway. Therefore the restriction to the 4 most selective values does not change the result significantly.
In our case this would be:
0.125 * 0.3333^(1/2) * 0.5^(1/4) * 0.5^(1/8) = 0.0556468
Applying the same formula of # of rows / (1/combined density), we would look at:
27776/(1/0.0556468 ) = 1539 rows estimated to get out of the non-clustered index.
Which of the estimates is more accurate certainly depends on the actual data distribution at the end.
Note: Be aware that the new calculation formula to calculate combined density of filters is not reflected in the index and column statistics. The section which shows ‘all density’ in the statistics is calculated and stored the same way it always has been. No changes there due to the new CE.
The old CE had some problems in the case of ascending or descending key values. These are scenarios where data is getting added to the table and the keys are going into one direction, like a date or incrementing order number or something like that. As the CE, in the attempt to compile a query, is trying to estimate the number of rows to be returned, it looks into the column statistics to find indications about the value submitted for this column filter. If the histogram data does show the value directly or gives indications that the value could exist within the value range the statistics cover, we usually are fine and can come back with a reasonable estimate. The problem more or less starts where the value we are looking for is out of the upper or lower bounds of the statistics. The old CE is usually assuming then that there is no value existing. As a result we will calculate with one potential row that could return. Not a real problem if we perform a select that qualifies all the columns that define a primary key or only gets a small number rows back. This could, however, develop into a larger issue if that estimation of just one row decides that this table access is the inner table of a nested loop, but there happen to be 50K rows with that very value. The fact that a new sequential value falls outside the statistics range could happen because the statistics simply didn’t get updated recently. In order to mitigate the risk encountering such a situation, we introduced trace flag 2371 a while back that should force more frequent update of the statistics. We described this scenario in our blog and in SAP Note 1725220.
The new CE should handle these cases differently now.
Let’s show it with an example. Let’s show it with the BUKRS column which contains the company code in a lot of the financial tables of SAP ERP. Let’s assume we have one of those tables where we have 100 different company codes between 1000 and 1200 in 1 million rows. Now let’s go through a few different scenarios:
Now let’s assume we add another company code and give it a value beyond 1200 with around 10K rows
What if we would create a new company code within the range of an index? The question is whether the new CE would behave differently if we would create a new company code within the range of existing company codes. The behavior here is not too much different between the old and the new CE. Assuming that the statistics did catch all the company codes, and assuming that there are less than 200 company codes, the statistics would indicate that no values exist between the different values documented in the histogram in the statistics. Hence the new as well as the old CE would return an estimate of 1 row only. This same estimation would happen even if there were quite a few changes to the table after update statistics was performed. This means the specific problem class of:
Is not getting solved by the new CE either, because the logic applied for out of range values is not applied for values which are in-range of the statistics.
Whereas if there are that many values within a column that the 200 buckets are not able to keep all values in the histogram, the old and the new CE estimate a number of rows for a non-existing value which is within the range of the column statistics. The value estimated will be calculated mainly based on values stored as RANGE_ROWS and DISTINCT_RANGE_ROWS in the column statistics.
The small amount of examples and scenarios described here should give an idea of the most significant changes the new CE introduces. Of course, these are hypothetical explanations and dependent on the structure and dependencies between values of different columns, it might or might not be close to the truth. But especially with accesses through non-clustered indexes it can mean that with the new CE one moves a bit earlier to use the clustered index instead of the non-clustered index. But since we today have data mostly present in memory, the difference might not play out to be too significant.
On the other side, due to the way the new CE treats ascending key scenarios, table joins might benefit with better plans. Therefore more complex table joins could perform better, or have less query latency, using the new CE. This is usually caused due to a different plan being generated that provides faster execution.
How can we detect whether a plan got generated with the new or old CE?
The best way checking is to look at the XML showplan text (not the graphic representation of it). In the top section you can see a section like this:
When CardinalityEstimationModelVersion is set to 70 the old CE is in use. When it is set to 120 then the new CE is in use.
After a lot of tests with the new CE, we decided to enforce the old CE but also set the compatibility level to SQL Server 2014 by setting trace flag 9481 for the SQL Server 2014 instance. There are multiple ways the flag will be set:
Nevertheless this does not mean that the new CE with SAP NetWeaver applications is not supported. Actually, we highly encourage you as a customer to test the new CE algorithms and run with the new CE if that works with you. To deactivate the old CE, it is as simple as deleting the trace flag 9481 as startup parameter and restart of the SQL Server instance. From a support side, we (SAP and Microsoft) will assume that if this trace flag is missing as startup parameter it was a conscious decision by you as a customer in order to use the new CE.
The reason for us to set the trace flag initially is to provide a smooth upgrade experience for our customers. Since the new CE could result in different performance patterns of the SAP application than with prior SQL Server releases, customers must carefully and thoroughly test business critical processes with the new CE before implementing it in their production system. Many SAP customers don’t have the time, resources or the interests in careful and thorough testing for the new CE during an upgrade process. Since, as mentioned earlier, it is expected that there can be changes to query execution which can go both ways, we felt that for the mass of upgrade projects, heavy testing for different query behavior should not be a mandatory step, but should be a step that can be done combined or separate from the upgrade testing and time line. Therefore, we enable the old CE behavior in all cases and once a customer has completed their testing of the new CE they can simply remove the SQL Server trace flag to switch to the new CE.
In the end, you the customer will need to decide after your thorough testing if the new CE should be enabled in your system. If the queries that show regressions are in non-critical processes and you find better performance of critical processes, then the new CE should be enabled. However, if you find regressions in critical processes that would affect your business, it is better to stay with the old CE. And either option is fully supported by SAP and Microsoft.
A summary of this content can also be found in SAP Note 1961467 “SQL Server 2014 and later – New CE in Query Optimization”.
Thanks to Yi Fang from our Query Execution team for her support writing this article.
That was it for Part2. Part3 will look into a new functionality called low priority wait.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.