SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi guys, Welcome to this new post! This time we will talk about temporal features and temporal table (attention please... not temporary tables). We never talked about this topic in this blog but today is the right day to do it! Why is this an important topic? Well, how many times you had to  track changes in data contained into a table? I am sure, so many times! Probably you solved this problem of tracking changes to your data (and so to know what happened to your data in the past) using triggers. A trigger is fired every time the value of a field in a table change. This trigger will add a row into a log table. It is necessary to know that this operation is natively supported by many database systems and also our Microsoft SQL Server has been supporting it for several years. Enjoy the reading!   SQL:2011 and the Temporal Features. A Bit of Theory. We start talking about temporal tables by saying that they are supported by different database engines because they have been d...

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