SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi Guys, Welcome back! I hope you enjoy this new post. Today i will show you others interesting facts about the SQL optimizer. Are you ready to enter in the detail of the partial aggregate ? Introduction The execution plan produced by a query depends a lot from data. This in because the optimizer is able to use both cardinality estimate and statistics in order to improve the execution plan . If you remember, is what we saw in the last posting when we was talking about the cost based optimization plan and the rules . Well, we continue today to talk about its operation logic!   The partial aggregate rules So, we find ourselves today with the same query of the last post: SELECT ANNDOC, NUMDOC, SUM (R.QTA1) AS SUM_QTA FROM ORDTES T JOIN ORDRIG R ON R.IDORDTES = T.ID WHERE ANNDOC = 2018 GROUP BY ANNDOC, NUMDOC ORDER BY ANNDOC, NUMDOC Our Query have the simple execution plan below: Now look what happens! I add a non clustered ...

Hi Guys, Welcome back! I hope you liked the last post of this series when we speak about trivial plan. Today we continue our topic looking at what happens next... Introduction In the last post we saw that some queries are admissible for a trivial plan while others are not. Queries that are not admissible for a trivial plan need to go through the cost based optimization process . Are you ready to enter inside the heart of the optimizer? Yes I suppose! The cost based optimization process If you remember, we have talked about this topic before. The goal of this step is to find quickly a good execution plan and not to find the best execution plan at all. During this step many important activities will be performed by the optimizer: Reading the logical tree, logical alternatives that produce the same results are explored . For each alternative: A Physical implementation is generated An estimated cost is assigned.Finally the cheapest physical solution is choosed. ...