for Application Developers
In this article, I will demonstrate a small change in the Cardinality Estimator component under the latest compatibility level – 150 (SQL Server 2019). Let’s first create and populate a sample table: DROP TABLE IF EXISTS dbo.T; CREATE TABLE dbo.T( id INT IDENTITY(1,1) NOT NULL, c1 INT NOT NULL, c2 INT NULL, c3 CHAR(5) DEFAULT […]
In the previous articles we’ve covered differences in estimations between the old and new CE related to queries that use predicates with literals which values are beyond the statistics histogram. We saw some issues with key ascending columns in SQL Server 2012 which can be solved with the trace flags 2389, 2390 and 4139. And […]
In the previous article we’ve shown how can we use TF 2390 to handle queries with literals beyond statistics histogram in SQL Server 2012. We saw that SQL Server creates some statistics on the fly for all columns used in filter expressions. If we would enable trace flag on the instance level that means that […]
In the previous article of this series we introduced the problem with queries using literals which are beyond statistics histogram, but exist in the table. We saw that this can be a serious problem in large tables in SQL Server 2012. We saw also that SQL Server 2014 handles it better. In this article we’ll […]
In this article I will start to examine estimations and execution plans for queries that compare values in columns with literal values which are beyond the statistics histogram for that column. The problem is also known as key ascending column statistics problem. As in previous posts we remain here conservative regarding sample tables. We’ll create […]
Since the release of SQL Server 2014 you can find several articles and blog posts about the changes in the new redesigned cardinality estimator. An excellent and detailed guide about the changes in the new CE you can find in the white paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator. Almost […]
In this article I will describe another query pattern where new CE in SQL Server 2014 brings significant regression. And, similar to the case in this post I would interpret the behavior of new cardinality estimator in this case as a bug, too. Let’s recreate our usual sample table and populate it this time with […]
This week I have got a question regarding my post New Cardinality Estimator Part 4 – Single Table and Multiple Predicates. The question was about possibilities to affect the estimation with multiple predicates done by new cardinality estimator. To demonstrates these possibilities we will use again the AdventureWorks2012 database and the SalesOrderHeader table. SQL Server […]
In this article we will check if PK-FK relationship is respected by new cardinality optimizer in case of a simple JOIN with tables in the mentioned relation. The old CE did not meet expectations, we hope that new CE will handle it better. We will use again the AdventureWorks2012 database and the JOIN between Production.TransactionHistory […]
In this article I will describe a case which is not properly handled by the new cardinality estimator, introduced in SQL Server 2014 CTP2. The discrepancy between estimated and actual values is significant, generated execution plans are sub-optimal and execution details are 5 – 50x worst compared to the old CE plans,. Since performance regression is significant […]
SQL Server 
Recent Comments