SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

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

Hi Guys, Welcome to this blog! This time we will talk about the predicate and the residual predicate . Are you sure you know everything? ...otherwise you should invest a few minutes to read this post! We will use the Management Studio (SSMS) to take a look at the execution plan of which, as usual, we always try to learn something new.  So.. Enjoy the reading mate!   Predicate and the Residual Predicate This time we start by in this way! Suppose we have a table: a table with a field ID integer and with an identity.  The Id field is also the primary key. The same table has a datedoc field in datetime format and a field customer_name of type Varchar. CREATE TABLE ORDTES ( ID INT identity (1,1) PRIMARY KEY CLUSTERED , DATEDOC datetime , CUSTOMER_NAME varchar (80) )  We have used this structure many times in previous posts. Now suppose we need to search for a row of this table by datedoc and then by customer_name. If this sear...

Hi Guys,   For this last post of March we will talk about an operator that we have seen in the execution plan . Perhaps it is less known than the others but it is worth knowing. Today we will talk about the Bitmap operator .  It is used to filter data and to improve performance . As usual, I will try to be as clear as possible. Enjoy the reading!   The Bitmap Operator As mentioned earlier today we will talk about the Bitmap operator. This operator is born with the aim of improving performance when we are dealing with a lot of data and the execution plan works in parallel . Let's see with an example how to generate an execution plan that contains the Bitmap operator. For the example we generate a couple of heap tables: Create table TabA (id int identity (1,1), Progr int ) Create table TabB (id int identity (1,1), Progr int ) Both tables have the same structure: a field ID of type integer and a field Progr of type integer. We will join TabA and TabB thr...

Hi guys, Welcome back friends! Last  posts were dedicated to the spool operators, we have seen table spool, eager spool, lazy spool and window spool. Today is the moment of the Row count Spool. Do you want to know everything about this type of Spool operator? then you just have to read ..   The Row Count Spool What is the Row Count Spool ?  It is simply one of the four spool operators supported by SQL server. It counts the number of rows that receive in input and return these rows when needed . This type of spool therefore behaves with a Table Spool but it is optimized for cases where the only relevant information is the number of rows and the content of the rows is not. How does SQL Server implement such behavior? Through a simple but smart idea! The data, that are read only to obtain the cardinality, are not saved. Note that since the operator does not store its own input, no working table is created in the tempdb. This operator simply returns the same number of r...

Hi friends! Welcome back, today we will not continue to talk about the parser part of SQL Server as in the previous post, we will do it in the next post! Today we start a short new series of posts that talks about the Execution plan and how to calculate the cost of the operators inside it. Are you ready? Three, two, one ...go!   A bit of theory One of the less explored part of SQL Server is the logic contained inside the optimizer. We already talked about the optimizer in greater detail here It's goal is to find a good execution plan in order to resolve our Query. For example, is the optimizer that chooses which type of JOIN to use to resolve the T-SQL command. So you can follow me step by step, i have prepared this example for you Create table table1 (id int identity (1,1), codice varchar(40), descr varchar(80)) Create table table2 (id int identity (1,1), codice varchar(40), descr varchar(80)) Create table table3 (id int identity (1,1), codice varchar(40), de...