SQL Server Performance Blog where milliseconds matter

Hi Guys, Welcome back! World is becoming increasingly global and so, for this reason, we are increasingly dealing with SQL Server installations located in countries that are very different from ours . So, we have to talk absolutely about collation ! ERROR 468: Cannot resolve the collation conflict between “AAAA” and "BBBB" in the equal to operation Yes, I am sure! this error will have happened to you at least once:  Message 468, level 16, state 9, row 19 Cannot resolve the collation conflict between “AAAA” and "BBBB" in the equal to operation. What does this error tell us? The message say that there was a problem during the two column comparison (equal to). But what kind of problem? A problem with the comparison rules! The comparison rules are defined within the collations. To better understand let's take an example Let's create a table with the standard collate of our database and fill it with so...

Hi guys, welcome back! Today we change the subject I present you a problem happened to me last week! So, if you are a DBA then i suggest to bookmark this post! Introduction Driiiiin...   On the other end of the phone a customer has a big problem.  For a few days it has been increasingly difficult to work!  Its management is always slower with the passage of time .. A few minute and we are already connected hunting for slowdowns ! What is a virtual log file (VLF)? When slowdowns involve all areas of your application, I personally take a look also to the transaction log. You should know that even if the physical transaction log (a file with the .ldf extension) is a single file it is divided logically in many parts that are called Virtual Log file aka VLF . Actually having too many virtual log files could rapresent a problem for performances. So how to determine how many VLF have your databases? You can count number of VLF through the qu...

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. ...

Welcome back Guys! Today, after going into the details of the cardinality estimate in a few posts ago, we will continue with the next step taken by the optimizer. So here the fourth part of the series Inside the SQL Server Query Optimizer . We will talk about Trivial plan . As usual I will try to explain myself in the simplest way possible. But tell me what doesn't seem clear to you  If you lost the previous parts you can click here: Inside the SQL Server Query Optimizer - part 1 Introduction and the input tree Inside the SQL Server Query Optimizer - part 2 All about the Simplification Inside the SQL Server Query Optimizer - part 3 Cardinality estimation   Enjoy the reading! What is a Trivial plan? A trivial plan is a mode to avoid the cost of the full “cost based optimization” . SQL Server Optimizer use a trivial plan when detect a Query with a simple structure For example: SELECT ID FROM ORDTES WHERE ID = 1 You can see if a trivial plan is ...

Hi Guys, Today for you a basic post! We do a comparison between a Query with Sub-Queries and with the Apply operator! So, what are differences between using sub-query and using cross/Outer Apply? Well, if you don't know you should absolutely read this post! Introduction For our comparison we choose these queries:     A query with two sub-queries (A subquery is simply a query with another query inside it's select) Select ( Select MAX (OrdRig.Qta1) as conta from OrdRig where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null), ( Select MAX (OrdRig.Qta2) as conta from OrdRig where Ordrig.idordtes = o.Id and OrdRig.IdComrig is not null) From eco..ordtes o   A query that use a Cross Apply operator select t . Qta1 , t . Qta2 from eco .. ordtes o CROSS APPLY   ( Select MAX ( OrdRig . Qta1 ) as Qta1 , MAX ( OrdRig . Qta2 ) as Qta2    from OrdRig    where Ordrig . ido...