SQL Server Performance Blog where milliseconds matter

Hi Guys, The Last SQL Server cumulative update 16 (CU16) is out! It was officially released the 04/18/2022.   As usual I want to emphasize the importance of updating the product : Maybe not the same day the CU is made available but still update!  Let's see what it contains! Enjoy the reading. Cumulative Update 16 for SQL Server 2019 For each cumulative update, Microsoft releases a list of the changes and corrections it makes, we can find it here Although divided by areas and by platform, its reading is unfortunately not always very clear. I refer to reports like this:  "Bug reference 14522124 - Dropping temp tables in some rare cases causes an unresolved deadlock and dump" It is not clear when the error occurs and how to reproduce it.  Certainly, however, it is better that a problem of this type is solved. Now let's see what we find interesting within this CU   Extended Event (XEvent): A new one, named query_store_plan_forcing_user_change is added to track when the

Hi Guys, I just got back from the cinema where I watched The Lost City with Sandra Bullock and Channing Tatum.  Yess, a cool film! But now it is the time to turn on the laptop and, as promised in the last post, click on the publish button for a special post!   A day at work with me! Remember , when a day starts before 8:00 with a business phone call it's usually never a good sign! And if an important customer is blocked and you start the day without coffee, this is all ... confirmed! Ok, let's be more precise! When the call from your customer "X" arrives, whose procedure for calculating the stocks of his material is no longer successful! Enjoy the reading! Deadlock & warehouse stock It's urgent! Let's connect with the customer to see what's happening! What to say ...often an error message can tell us a lot: Well, yes (houston!) we have a deadlock problem !   And now, how to proceed? We activate a couple of trace flags: DBCC TRACEON (1204,-1) GO

Hi Guys, Today just a short post in which we will see how to prevent users from executing the same stored procedure at the same time . I think it's a very useful thing to know and in some cases it can save us the day. Furthermore, this could be useful for building a more robust procedure.   Enjoy the reading!   Sp_getapplock & Sp_releaseapplock First of all, you should know that SQL Server offers two procedure which have been developed for this purpose. These two stored procedure are sp_getapplock and sp_releaseapplock. The logic behind these functions is as follows:  We have a resource that we want to be executed by only one process at a time. With stored procedure Sp_GetApplock we ​​can set a lock on a resource so that a second process finds this resource already in use. The stored procedure sp_releaselock instead removes the lock from the resource. Before giving an example to understand how to use these functions let's see the parameters: The sp_getapplock puts the lo

Hi Guys,  I hope you had a happy Easter! Today, as we often do, we talk about slowdowns .   To be honest, one of the questions I get asked most often is this: "I have a Query that works fine for me and is very fast but when I try it from on customer’s server it is very slow! ...Why?" To answer this question, let's think first of all of everything that changes from your environment to that of the customer:  everything !   The data Are you querying the same data ?  SQL Server Optimizer creates the execution plan based on the "data it finds to read" (and store the execution plan inside the cache plan for performance reason.) So suppose you have taken a copy of the customer data via a backup and therefore have the same data. Are you already okay? No, absolutely! What you need is to have the same statistics and the same index fragmentation . You will therefore understand the first reason why the tests made on your PC are not very significant. Tests must be done

Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo

Hi Guys, SQL Server 2022 is coming! … and as always happens, each new version is always eagerly awaited ! Suffice it to say that since its announcement at Ignite in November 2021 , the early adoption program (EAP) has received over 2,500 requests to join the private preview. A big number!   During the SQLBits 2022 event that took place in March 2022 Bob Ward announced that the SQL Server Public Preview is coming by the end of the first half of the calendar year 2002 .   Application to join the EAP are now closed. Demos were made during the event using the CTP 1.3 version. While the version used in the EAP was CTP 1.0. I have already talked about the new features introduced by SQL Server 2022 here: Ladies and gentlemen ...drumroll... SQL Server 2022 is among us! New features and improvements!     That's all for today and stay tuned! Luca Support me  Previous post: SQL Server, Non-updating updates ...why avoid them clearly explained!

Hi Guys, Today we will talk about Non-updating updates . I recommend that you read because these are important aspects when it comes to performance. I will not go into the details of what happens (maybe in the future) rather I have developed a very clear example. What Non-updating updates are?   Well, Enjoy the reading!     Non-updating updates  A non updating update is an update the not change any value.    Suppose we perform an update on field A which contains the value 1 to put the value 1 ... this is it a non updating update. UPDATE TABLE SET MYFIELD = 1 (when the value of the field myfield is already equal to 1) UPDATE TABLE SET MYFIELD = MYFIELD WHERE ...   But who is it that does such a thing?   ...unfortunately many! Many always perform a single update on all fields in a table, even if these have not changed. To make you understand the weight of this operation, I have developed a simple example that demonstrates the difference in performance.   The Example   For t