Beyond Statistics Histogram Part 4 – Significant Performance Regression in SQL Server 2014

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 […]

Beyond Statistics Histogram – Part 3 (TF 2389 feat. TF 4139)

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 […]

A Funny Bug with New CE or Introduction to Out-of-Histogram Estimations

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 […]

New Cardinality Estimator Part 8 – Bug No. 2

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 […]

New Cardinality Estimator Part 7 – Single Table, Multiple Predicates and Trace Flags

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 […]

Speaking at SQLSaturday 275 in Copenhagen

Last night I received an email that I have been chosen to speak at SQLSaturday #275 in Copenhagen on the 29th of March. I will present my session “SQL Server 2014 – New Cardinality Estimator” where I will be going over reasons for new cardinality estimator in SQL Server 2014 and will compare execution plans […]

New Cardinality Estimator Part 6 – Simple JOIN And Foreign Key

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 […]

New Cardinality Estimator Part 1

SQL Server 2014’s main favorites are definitely In-Memory OLTP engine and cluster column stored indexes and if you find proper use cases for them you can significantly improve performance and usage of your OLTP system. However, for both of them you can decide when, how, and in which degree you will implement them. In the series […]