SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi guys! After the simplification phase discussed in the last article, today we will speak about another fundamental step called Cardinality Estimation . During this step the optimizer try to predict the number of rows returned by each node of the execution plan. As you can imagine this is a step of primary importance because a good prediction generate an accurate execution plan with a lower processing cost to execute. The statistics In order to estimate the number of rows returned by a query the Cardinality Estimator use so called statistics . By default for each column and each index created, SQL Server create it's relative statistic. But what is a statistic? A statististic for query optimization is a binary large object called BLOBs that contain statistical information about the distribution of values for one o more columns of a table. We can observe statistics through the T-SQL command: DBCC SHOW_STATISTICS ( <TABLE> , <FIELD_NAME> ...

Hi guys, I am back! After the introduction done in the previous article , today we talk about the simplication step of the Query Optimizer. In my opinion, knowing what types of optimization SQL Server might apply is very important in order to write a Query that run Quickly! So, are you ready for the second part of this exciting journey to discover the Query Optimizer? Ready? Go! The Simplification Let now start speaking about the Simplification phase. Simplification is the first step of the optimization pipeline. It's an important step because during this phase the optimizer try to modify the logical tree in order to remove redundancies. Optimizer try also to change the order of the logical operation in order to facilitate later step . We can split the simplification phase into various sub phases that are: Constant Folding Domain simplification Predicate push-down Join simplification Contradiction detection The constant folding During this phase SQL s...

Welcome back Guys! We are in 2020 and I would wish you a happy new year! I hope it will be a year of professional and personal growth for you. I hope it is a year of health, dedicated to affections and family! Today we start with the first article of this year and the second year of this blog that's growing fast! This first article of this year is about the Query Optimizer. I will present to you a series of articles in which we will go into detail on how the Query optimizer works. Enjoy the reading! Introduction  We had already mentioned the subject in this article wich i really invite you to read here: SQL Server, the Parametrization and the Parameter Sniffing So let's review what we have already said: 1) The goal of the Query Optimizer is to find a good way to return data (aka find a good execution plan) that will be executed by the Query Executor. Remember : not the best plan. This is because in a non-trivial query there can be hundreds or thousand...