SQL Server Performance Blog where milliseconds matter

Hi Guys, Wow! Today is the day!  It’s the time for the first public contact with the new SQL Server 2022. I am happy to announce infact that the first public preview (CTP 2.0) is now available for download  here We already talked some time ago about the CTP 1.4 https://sqlserverperformace.blogspot.com/2022/04/speed-news-sql-server-2022-is-coming.html SQL SERVER 2022 is here! I must admit that I am waiting this moment since sometime. But we at are ready! Ready to try every new features and talk about every news. So stay tuned ... as from the next post we will start talking about the performance related features! Stay tuned!   If you are genuinely and truly interested in one of the products that appear in advertising then click the adv from my blog to support my blog!      Previous post: SQL Server, benchmark's time! "Singleton_lookup Vs. Range_scan" and "Index Vs. Unique Index"   A

Hi Guys, I know I know! It's Friday night, are you waiting for dinner, sipping a good glass of sparkling white wine and you don't know what to do? Don't worry, I'll take care of it.  Here is a post that will take you just 5 minutes!   Some time ago we talk about Singleton_lookup and Range_scan . You can read here:  SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan   and   SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan. Part 2 ...some other infos.   We were asked, which of the two operations is the faster? The interesting fact is that this question allows us to answer the question as well: We have two equal indices except that one is unique Which of the two allows us to perform better?   It's benchmark time men!  ...It's benchmark time! ha ha ha! Enjoy! "Singleton_lookup Vs. Range_scan" & "Index Vs. Unique Index"   To compare the speed of the two different access modes we use a com...

Hi Guys, Today a short midweek post!   We will talk about the composite index and statistics . Nothing particularly profound, but any information we can learn, I'm sure will be useful in the future. Enjoy!   Composite index What is a composite index?  Well, a composite index is simply an index that defined on more than one column . For example. Upon the following table ORDTES CREATE TABLE ORDTES (Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED , DOC_YEAR int , DOC_NUMBER int , CUSTOMER_NAME varchar (80) );   We can define an index on both the columns DOC_YEAR, DOC_NUMBER: CREATE INDEX IDX_ORDTES_DOC_YEAR_DOC_NUMBER ON ORDTES (DOC_YEAR, DOC_NUMBER)     This means that if I search for a specific pair of DOC_YEAR, DOC_NUMBER values I should arrive directly with a seek of the searched record. Is this true? Yes, but let's see more in detail by taking a look at the statistics ... we will find out interesting things .. Statistics Statistics are critic...

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, Welcome back!   If you missed the first part of this post well click here ! We have seen the physical structure of an index and have talked about Singleton lookup and Range Scan .   Today, i will show you other details to complete the discussion.   It will be a short post but the next time then we will talk about residual predicates and it will be a very interesting topics! Finally, thanks to a suggestion of ​​my friend Alessandro Alpi there will also an other post in which we will talk about composite indexes and how, in this case, the index search takes place. Enjoy the reading!   Other infos about Singleton lookup and Range Scan In the previous post, we talked about Singleton lookup and Range Scan . Now we know how record are phisically searched inside an index.   We have seen that it is not easy to know which method we are using. In this case DMVs comes to our aid!   Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) a...

Hi Guys, Welcome back for another important post here on my blog! Today we are going to deepen the functioning of a seek and see in more detail how it works . I hope many of you are reading this post. My goal is in fact to explain in the clearest and most interesting way how SQL Server works. If something is not clear to you, just ask, it will help me to improve. Last news before start, in case you are genuinely and truly interested in one of the products that appear in advertising ... click the adv from my blog to support my site! Crew, let's go !!!    Recap about index and the way them works Well, first of all we must do a recap about indexes and about the way them works .   I blogged many times about index for example, i suggest to read here: SQL Server, Difference between a seek and a scan operation and the Latch coupling An index, clustered or not, has the following structure: Every search start from the Root Page, then continue to the Intermediate pages and finall...