SQL Server Performance Blog where milliseconds matter

Hi Guys, Welcome to this new post! We have already spoken many times about sargability . For example here: Write high performing query: Is your query SARGABLE? SQL Server queries, write them Sargable: the ISNULL function   https://sqlserverperformace.blogspot.com/2019/11/sargable-queries-part-2-examples.html Sargability is the key factor to having a performant query but talking about dates is not always easy to get this property . Today we will see how to turn a non sargable query using dates into a sargable one . Obviously the difference in performance is really remarkable! Enjoy! Working with dates in a sargable way Sargability is a great property!     We can say that a query is sargable when its WHERE clause can use index in seek mode . I suggest you read my other posts where I tell you why often the queries are not sargable. For example when I use a function, but there are other cases.. And often when we handle dates we use functions. Who has never used the date...

Hi guys, As many of you know this blog was born with the main intent of talking about optimizations and performance . Today we see what is the fastest way to perform an operation that in reality often happens to perform. Let’s talk about "Insert if not exists" or insert new records in a table only if they are not already present. Ready to measure with your stopwatch in hand? Enjoy the reading!     The "Insert if not exists" operation Suppose you have a table which contains a list of all the products in our store whos name is  Products Suppose you have another table NewProducts , with the same structure, which also contains a list of products. What we want to do is to insert in the Products table all the products found in the NewProducts table that do not exist in the Products table . This is the structure of the two tables: CREATE TABLE PRODUCTS (ID INT IDENTITY (1,1), CODE VARCHAR (20) PRIMARY KEY CLUSTERED ([ID] ASC )) CREATE TABLE NEWPRODUCTS (ID INT IDENTITY (...

Hi Guys, November is a great month and today is a great day! Its a great month because it's the time for the  PASS Data Community Summit conference. This Hybrid Conference is held in Seattle from 15 to 18 November and hosts many Sessions also related to databases.   It is great day because today at the PASS Data Community Summit Microsoft announced the release to general availability (GA) of SQL Server 2022 ,    We followed step by step all the various CTP released and now it is the time for the general availability!   That'all for today, Luke Previous post: https://sqlserverperformace.blogspot.com/2022/11/cardinality-in-multicolumn-statistics.html

Hi guys, Welcome to this post!   We all knows how much statistics are of vital importance for the performances. If SQL Server estimates an incorrect cardinality it will also generate a non-optimal execution plan . For example, we might find a join type not suitable for the number of lines to manage. Used a nested loop join to read a table with millions of rows is like going from one side of the US to the other using a bicycle . I am sure It will take a long time! But the opposite can also happen... If we used a join operator suitable to handle many lines when instead we have few would be like going to find the neighbour by plane . We would use a lot of resources unnecessarily. Today we focus in the case of multicolumn statistics , let’s see how they are calculated and how this calculation has changed during the SQL Server versions and then at the change of compatibility level. I remember we have just talked about this topic here: SQL Server, A little about composite indices and ...

Hi Guys! Welcome to this second post of november.   Today we will talk about the tool Azure Data Studio . Download here   What’s new in this useful tool? Enjoy the reading!       Azure Data Studio now shows the execution plan! Azure Data Studio , here the latest version the 1.39.1, gain some very interesting possibilities. We can observe that now we can show the execution plan directly from the data studio and it is great!   Two buttons are added: Estimated plan and Enable Actual plan .   Pressing the Enable Actual Plan button and executing again the Query, the Execution plan will be shown:   Compared to SSMS there are also other additional functions For example, it is now possible to graphically show which action is more expensive according to various criteria within the execution plan: We can also compare two execution plan: To do this operation you must save an execution plan of the first Query by doing a right click on the select operator e t...