SQL Server Performance Blog where milliseconds matter

 Hi Guys, In the last posts we spoken of all the teory begin the new features of SQL 2019. Today it's time to get to work! I propose you an exercise on which you can also try your hand at home . Given a Query it will be necessary to write it in order to make it as fast as possible . All the techniques we have said up to now are valid. Given a table containing movements what we want to obtain is the number of the various classifiers. If you think about it it is a very common request. I will propose some solutions but I expect your solutions! Ready? Let the "Group by" Race begin As a data structure we have a table called Moviments: CREATE TABLE [dbo].[Movements]( [Id] [int] IDENTITY (1,1) NOT NULL, [Qty] [float] NULL, [Price] [float] NULL, [Classifiers] [varchar](10) NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX CI_MOVEMENTS_ID ON Movements(ID) A clustered index (CI) is defined on the column Id . For this example we fill our table with 50,000,000 records using the c

Hy Guys, Are you ready for another post? Today we will discuss about another new feature introduced by the last SQL Server 2019 that is called Memory Grant FeedBack for Row Mode . Enjoy the reading mate!     A recap! Just to recap what we are talking about in these last posts and where we have arrived . In the past posts we have seen what are the new features introduced by SQL 2019. We have addressed all but one. We have talked about: Table Variables deferred compilation Batch Mode on Rowstore Approx_Count_Distinct function (we also talked about the OPTIMIZE FOR SEQUENTIAL KEY clause) So, what subject do we still have to talk about? well.. Memory Grant FeedBack for Row Mode Many times we have focused on performance. We want to get a Query that is quick to execute and that reads as few pages of memory as possible. This certainly remains true! However, there is another aspect to focus on: the use of memory . Obviously memory is a limited resource and the more the queries require memory

Hi Guys, As you know in our blog, the final goal is always performance. So today is the time to talk about another best practice. Are you Ready? yes? go! p.s. By the way, if you want to know when my posts come out follow me also on linkedin here   SET NOCOUNT ON What is the option NOCOUNT?    The NOCOUNT is an option, its value can be ON or OFF and determines whether  the DONE_IN_PROC messages are sent to the client of not. The DONE_IN_PROC message indicates the completion status of an SQL statement within a stored procedure (and therefore also a trigger). Inside a SP a message is sent for ech executed statement. By default, the NOCOUNT option is set to OFF but you can set it to ON with the command   SET NOCOUNT ON In this case, SQL Server will not send the DONE_IN_PROC messages . DONE_IN_PROC messages are sent to the client via the TDS protocol. Tabular (o token) data Stream (or TDS) is the protocol implemented by drivers that allows an application to communicate directl

Hi Guys, Today, after the last tips  SQL Server, Concatenates text from multiple rows into a single string! I want to show you another tips! I suggest you bookmark this page. This way, when you need it, a copy and paste will suffice! How many times did it take you to have a table with a fixed number of rows at your fingertips? Here it is!   Generate a number of rows Suppose you want to generate 10 rows. Just write (or copy and paste) this command: ; WITH progr(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM progr WHERE x < 10 ) SELECT x FROM progr OPTION (MAXRECURSION 0); And here is the result: What we have done? simple: we used a CTE in order to trigger recursion. That's all for today! Stay tuned mate! Luca Luca Biondi @ SQLServerPerformance blog! Next post: Previous post: SQL Server 2019 and the Approx_Count_Distinct function

Hi Guys, Today we continue with the speech of the new features of SQL 2019, if last time we talked about Batch mode on Rowstore , today we talk about another new feature: the function Approx_Count_Distinct . Enjoy the reading, mate! The Approx_Count_Distinct function  Let's suppose for simplicity we take the same "movements" table used in the last post and already filled with 3 million rows. CREATE TABLE Moviments (ID INT IDENTITY(1,1), YEAR FLOAT , QTY FLOAT , PRICE FLOAT, CLASSIFICATORS VARCHAR(10)) Now suppose you want to know the number of items purchased for each classificators. Simple, you will say! just write this command: SELECT COUNT( DISTINCT CLASSIFICATORS) FROM MOVIMENTS True! However, it will not surprise you to know that this operation, which apparently seems to be so simple, instead requires many resources (and to read through all the rows of our table):   Table 'Worktable

Hello everyone! Welcome back guys, Are you ready to continue the roundup of the new features introduced by SQL Server 2019? Yes, because if you have read the previous articles by now you will know that there are many.   As usual, we will focus on the novelties that allow us to improve performance and therefore also the novelty we will see allow us to achieve greater performance! Let's talk about Batch Mode on RowStore . But first we need to take a step back to see what a Batch is! What is a batch? Simply stated, a batch is an internal 64 KB wide storage structure that contains a group of rows ranging from 64 to 900 depending on the number of columns of which they are composed. Each column used by the query is stored in a continuous column vector of fixed-size elements, where the qualifying row vector indicates which rows are still logically part of the batch :   How it works?    When batch mode is used for row store data, the rows of data are scanned and loaded into a batch.