SQL Server Performance Blog where milliseconds matter

Hi Guys, Have a good week first of all! In this post, I want to introduce you to a toolkit called Stellar Toolkit for MS SQL . This toolkit contains 5 software that are useful in many critical situations, such as: To repair corrupted SQL Server database To extract database from corrupted backup To convert data from database to Access, SQLite, or SQL Anywhere To inspect the SQL Log file To reset the password of MS SQL Server   Enjoy the reading! Installation of the Toolkit Like other Stellar products, installation of this toolkit is simple. You just need to download the software from here . The size of the file is small. Once the .exe file is downloaded, just double-click it to open the setup wizard.   Then, click on the Next Button.   You must accept the license agreement and press the Next button.   Now, if needed, you can change the default location. Then, press the Next button.   You can select a location to create program shortcut or let it create in t...

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 Guys, In the last post we talked about the Lazy Spool operator , today to continue the discussion we will talk about another type of spool: the window spool . We will tell how it works and when the optimizer uses this type of spool. Enjoy the reading!   The window spool Let's say the window spool is both a logical and a physical operator. The optimizer uses this type of spool when dealing with a query that contains a Windows function . I am sure you already know what a windows function is, ...but in case there is someone just entered in the SQL world I gladly remember it .. A window function is simply a function that can operate on a set of rows and return a single aggregated value for each row. For example i can compute an aggregate value without group data. The highlighted part of the string is the window function: SELECT [NAME], [SALARY], MAX ([SALARY]) OVER ( PARTITION BY [NAME] ORDER BY [NAME]) AS MAX_SALARY FROM [EMPLOYEE] order by [NAME], [SALARY] This function return...

Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?  ...

Hi Guys, January is starting while February is coming and the days slowly begin to get longer! Today we will talk about interleaved execution , a feature introduced in SQL Server 2017 In many previous posts they had already talked about the news that the 2017 and 2019 versions of SQL Server had brought us. In the last article of this series we talked about adaptive join (in the photo under the blue box on the far left):  SQL Server and the Adaptive Join feature: "Adaptive Join" Vs. "wrong cardinality estimate" and "Uneven data distribution"   Today to complete the features called "Intelling Query Processing" we will therefore talk about Interleaved execution. Interleaved execution Interleaved execution is a feature that is part of the Adaptive QP family of features where the acronym QP stands for Query Processing. The term adaptive means that the execution plan is now able to adapt itself according to the data it receives as input . In the case...