### Setup

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

### Test query Display 9% of the rows in primary key order:

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

dbfiddle demo ### Results ### Execution plan --- ## Question Why does SQL Server sort the column when the clustered index provides exactly that order? --- ### More rows If I increase the number of rows in the table, I get an Eager Spool instead of a Sort and the index is scanned in order:

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern. In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a SORT but I have an index that should cover it. The use case for this UPDATE is for existing rows where we've added additional columns to the ingest since the row was first added to the HISTORY table. This SORT is resulting in the Procs where the updates are on our larger/wider tables to be painfully slow. How do I adjust the index or query to remove the **SORT** in **query 3**? Here's the **updated** execution plan as requested by J.D. Here's the DDL. DROP TABLE IF EXISTS dbo.STAGE; GO CREATE TABLE dbo.STAGE ( Id varchar(18) NULL, CreatedDate varchar(4000) NULL, LastModifiedDate varchar(4000) NULL, LastReferencedDate varchar(4000) NULL, [Name] varchar(4000) NULL, OwnerId varchar(4000) NULL, SystemTimestamp datetime2(7) NULL ) GO DROP TABLE IF EXISTS dbo.HISTORY; GO CREATE TABLE dbo.HISTORY ( HistoryRecordId int IDENTITY(1,1) NOT NULL, [Hash] binary(64) NOT NULL, [IsActive] BIT NOT NULL , ActiveFromDateTime datetime2(7) NOT NULL, ActiveToDateTime datetime2(7) NOT NULL, Id varchar(18) NOT NULL, CreatedDate datetime2(7) NULL, LastModifiedDate datetime2(7) NULL, LastReferencedDate datetime2(7) NULL, [Name] varchar(80) NULL, OwnerId varchar(18) NULL, SystemTimestamp datetime2(7) NULL ) GO CREATE UNIQUE CLUSTERED INDEX [CL__HISTORY] ON dbo.HISTORY ( Id , [ActiveToDateTime] ASC, [IsActive] ASC ) GO CREATE NONCLUSTERED INDEX [IX__HISTORY_IsActive] ON dbo.HISTORY ( [Id] ASC ) INCLUDE([IsActive],[ActiveToDateTime]) GO DROP TABLE IF EXISTS #updates; GO WITH src AS ( SELECT CONVERT(VARCHAR(18), t.[Id]) AS [Id] , CONVERT(DATETIME2, t.[CreatedDate]) AS [CreatedDate] , CONVERT(DATETIME2, t.[LastModifiedDate]) AS [LastModifiedDate] , CONVERT(DATETIME2, t.[LastReferencedDate]) AS [LastReferencedDate] , CONVERT(VARCHAR(80), t.[Name]) AS [Name] , CONVERT(VARCHAR(18), t.[OwnerId]) AS [OwnerId] , CONVERT(DATETIME2, t.SystemTimestamp) AS SystemTimestamp , dgst.[Hash] , CONVERT(DATETIME2, SystemTimestamp) AS [ActiveFromDateTime] , RN = ROW_NUMBER() OVER ( PARTITION BY t.[Id] ORDER BY CONVERT(DATETIME2, SystemTimestamp) DESC ) FROM dbo.STAGE t OUTER APPLY ( SELECT CAST(HASHBYTES('SHA2_256', COALESCE(CAST([CreatedDate] AS NVARCHAR(4000)), N'') + N'||' + COALESCE(CAST([LastModifiedDate] AS NVARCHAR(4000)), N'') + N'||' + COALESCE(CAST([LastReferencedDate] AS NVARCHAR(4000)), N'') + N'||' + COALESCE(CAST([Name] AS NVARCHAR(4000)), N'') + N'||' + COALESCE(CAST([OwnerId] AS NVARCHAR(4000)), N'') + N'||' + COALESCE(CAST(SystemTimestamp AS NVARCHAR(4000)), N'') ) AS BINARY(64)) AS [Hash] ) dgst ), tgt AS ( SELECT * FROM dbo.HISTORY t WHERE t.[ActiveToDateTime] > GETUTCDATE() AND 1 = 1 ) SELECT tgt.HistoryRecordId , src.* INTO #updates FROM src LEFT JOIN tgt ON tgt.[Id] = src.[Id] WHERE src.RN = 1; GO --Create index on temp table (#updates) CREATE NONCLUSTERED INDEX NCCI_#updates__Kimble_HISTORY_ForecastStatus ON #updates ( [Id] , ActiveFromDateTime, [Hash] ); GO UPDATE tgt SET tgt.[Hash] = src.[Hash] , tgt.IsActive = 1 , tgt.[CreatedDate] = src.[CreatedDate] , tgt.[LastModifiedDate] = src.[LastModifiedDate] , tgt.[LastReferencedDate] = src.[LastReferencedDate] , tgt.[Name] = src.[Name] , tgt.[OwnerId] = src.[OwnerId] , tgt.SystemTimestamp = src.SystemTimestamp FROM dbo.HISTORY tgt INNER JOIN #updates src ON tgt.[Id] = src.[Id] AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] AND tgt.[Hash] src.[Hash] ; GO

What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join? I have now see a couple of examples where it obviously makes a mistake and hinting the query will make it a lot faster. The examples I have seen are from SQL Server 2016.

One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and based on the user search criteria, it passes parameters to the query. I looked into the execution plan and I see sort operations. One of the sort operation is SORT(DISTINCT). I checked the query and I found some order by in the query. Removing order by didn't help, and I dont have a DISTINCT in the query. On checking the properties of the SORT(DISTINCT) it is not showing the field on which it is doing sort. I have attached the screenshot of that. After doing some research I learned that OR in where conditions can generate SORT operations as the MERGE required some kind of sort, and I do have OR in where conditions which looks like: where (column1 = @param1) OR (column1 IS NULL) OR (@param1 IS NULL) Someone suggested me in an earlier post that, this are "Kitchen sink" queries and it will cause performance issues. Is there anyone who had to deal with similar situation? Any solutions?

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything. Anyone got any ideas how to get this SORT percentage down or remove it all together? Here is the query I'm running. SELECT Time_ID, Site_Type_ID, Abandoned_ID, WorkType_ID, SUM (staging.dbo.measure.ring_time) AS Ring_Time, SUM (staging.dbo.measure.hold_time) AS Hold_Time, SUM (staging.dbo.measure.talk_time) AS Talk_Time, SUM (staging.dbo.measure.acw_time) AS ACW_Time, COUNT(*) CallCount FROM measure INNER JOIN DataMartEnd.dbo.Time_Dim ON measure.StartTimeDate BETWEEN Time_Dim.Time_Start AND Time_Dim.Time_End INNER JOIN datamartend.dbo.Site_Type_Dim ON measure.DBID = Site_Type_Dim.Site_Type_Code INNER JOIN datamartend.dbo.Abandoned_Call_Dim ON measure.Abandoned = Abandoned_Call_Dim.abandoned_value INNER JOIN DataMartEnd.dbo.Work_Type_Dim ON measure.DBID = work_type_dim.MIG_Site_ID AND Work_Type_Dim.Work_Type_Code = measure.Queue AND measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND Work_Type_Dim.DimEffectiveEndDtm GROUP BY Abandoned_ID, WorkType_ID, Site_Type_ID, time_id Thanks for the help.

I am struggling to minimise the cost of sort operation on a query plan with the warning Operator used tempdb to spill data during execution with spill level 2 I have found several posts related to spill data during execution with spill level 1 , but not level 2. Level 1 seems to be caused bu outdated statistics , what about level 2? I could not find anything related to level 2. I found this article very interesting related to Sort warnings: Never Ignore a Sort Warning in SQL Server **My Sql Server?** > Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun > 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise > Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) **My Hardware?** running the query below for find the harware: -- Hardware information from SQL Server 2012 SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_kb/1024 AS [Physical Memory (MB)], affinity_type_desc, virtual_machine_type_desc, sqlserver_start_time FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE); **currently allocated memory** SELECT (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory; when I run my query with one year scope I don't get any warning whatsoever, as per the picture below: But when I run it only for 1 day scope I get this warning on the sort operator: this is the query: DECLARE @FromDate SMALLDATETIME = '19-OCT-2016 11:00' DECLARE @ToDate SMALLDATETIME = '20-OCT-2016 12:00' SELECT DISTINCT a.strAccountCode , a.strAddressLine6 , a.strPostalCode , CASE WHEN a.strCountryCode IN ('91','92') THEN 'GB-Int' ELSE a.strCountryCode END AS [strCountryCode] FROM Bocss2.dbo.tblBAccountParticipant AS ap INNER JOIN Bocss2.dbo.tblBAccountParticipantAddress AS apa ON ap.lngParticipantID = apa.lngParticipantID AND apa.sintAddressTypeID = 2 INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah ON ap.lngParticipantID = ah.lngParticipantID INNER JOIN Bocss2.dbo.tblBAddress AS a ON apa.lngAddressID = a.lngAddressID AND a.blnIsCurrent = 1 INNER JOIN Bocss2.dbo.tblBOrder AS o ON ap.lngParticipantID = o.lngAccountParticipantID AND o.sdtmOrdCreated >= @FromDate AND o.sdtmOrdCreated < @ToDate OPTION(RECOMPILE) the query plan is here the query plan using pastetheplan Questions: 1) in the query plan I see this: StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" why 70? I am using sql server 2014 2) how do I get rid of that sort operator (if at all possible)? 3) I have seen page life expectation pretty low, apart adding more memory to this server, is there any other thing I can have a look at to see if I can prevent this warning? cheers **Update after the answer from Shanky and Paul White** I have checked my statistics according to the script below, and they seem all correct and updated. these are all indexes and tables used in this query. DBCC SHOW_STATISTICS ('dbo.tblBAddress','IDXF_tblBAddress_lngAddressID__INC') GO DBCC SHOW_STATISTICS ('dbo.tblBOrder','IX_tblBOrder_sdtmOrdCreated_INCL') GO DBCC SHOW_STATISTICS ('dbo.tblBAccountHolder','PK_tblAccountHolder') GO DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipant','PK_tblBAccountParticipants') GO DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipantAddress','IDXF_tblBAccountParticipantAddress_lngParticipantID') GO this is what I have got returned: This is a partial results, but I have re-visited them all. For statistics update I currently have Ola Hallengren the Index Optimise Job - scheduled to run once a week - Sundays EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES,-%Archive', @Indexes = 'ALL_INDEXES' , @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @PageCountLevel=1000, @StatisticsSample =100 ,@UpdateStatistics = 'Index', @OnlyModifiedStatistics = 'Y', @TimeLimit=10800, @LogToTable = 'Y' Although the stats seemed to be updated After I run the following script, I got no more warning on the sort operator. UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder] WITH FULLSCAN --1 hour 04 min 14 sec UPDATE STATISTICS [Bocss2].[dbo].tblBAddress WITH FULLSCAN -- 45 min 29 sec UPDATE STATISTICS [Bocss2].[dbo].tblBAccountHolder WITH FULLSCAN -- 26 SEC UPDATE STATISTICS [Bocss2].[dbo].tblBAccountParticipant WITH FULLSCAN -- 4 min UPDATE STATISTICS [Bocss2].[dbo].tblBAccountParticipantAddress WITH FULLSCAN -- 7 min 3 sec

I'm using Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 3 2019 13:27:00 Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs). Query: offer progress has a history of statuses for each offer and I want to get the first row for each offer/status. So, for offer #1 I want the first time it was set to status 1, status 2, etc. SELECT OfferProgressId, OfferId, DateCreated, OfferStatusDate, OfferStatusId, Name FROM ( SELECT op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId, u.Name, ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus FROM OfferProgress op LEFT JOIN [User] u ON u.UserId = op.UserId WHERE op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17) AND op.DateCreated >= '8/1/2017' ) t WHERE t.FirstStatus=1 Execution plan: https://www.brentozar.com/pastetheplan/?id=rkhP--LcN My goal is to get rid of the sort operator that has a warning. My indexes: IX_OfferProgress_DateCreated, IX_OfferProgress_OfferId_OfferStatusId_DateCreated, IX_OfferProgress_OfferStatusId_DateCreated_OfferId_UserId_with_include

I have two tables with identically named, typed, and indexed key columns. One of the them has a *unique* clustered index, the other one has a *non-unique*. **The test setup** Setup script, including some realistic statistics: DROP TABLE IF EXISTS #left; DROP TABLE IF EXISTS #right; CREATE TABLE #left ( a char(4) NOT NULL, b char(2) NOT NULL, c varchar(13) NOT NULL, d bit NOT NULL, e char(4) NOT NULL, f char(25) NULL, g char(25) NOT NULL, h char(25) NULL --- and a few other columns ); CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h) UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000; CREATE TABLE #right ( a char(4) NOT NULL, b char(2) NOT NULL, c varchar(13) NOT NULL, d bit NOT NULL, e char(4) NOT NULL, f char(25) NULL, g char(25) NOT NULL, h char(25) NULL --- and a few other columns ); CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h) UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000; **The repro** When I join these two tables on their clustering keys, I expect a one-to-many MERGE join, like so: SELECT * FROM #left AS l LEFT JOIN #right AS r ON l.a=r.a AND l.b=r.b AND l.c=r.c AND l.d=r.d AND l.e=r.e AND l.f=r.f AND l.g=r.g AND l.h=r.h WHERE l.a='2018'; This is the query plan I want: (Never mind the warnings, they have to do with the fake statistics.) However, if I change the order of the columns around in the join, like so: SELECT * FROM #left AS l LEFT JOIN #right AS r ON l.c=r.c AND -- used to be third l.a=r.a AND -- used to be first l.b=r.b AND -- used to be second l.d=r.d AND l.e=r.e AND l.f=r.f AND l.g=r.g AND l.h=r.h WHERE l.a='2018'; ... this happens: The Sort operator seems to order the streams according to the declared order of the join, i.e. c, a, b, d, e, f, g, h, which adds a blocking operation to my query plan. **Things I've looked at** * I've tried changing the columns to NOT NULL, same results. * The original table was created with ANSI_PADDING OFF, but creating it with ANSI_PADDING ON does not affect this plan. * I tried an INNER JOIN instead of LEFT JOIN, no change. * I discovered it on a 2014 SP2 Enterprise, created a repro on a 2017 Developer (current CU). * Removing the WHERE clause on the leading index column does generate the good plan, but it kind of affects the results.. :) **Finally, we get to the question** * Is this intentional? * Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes.

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve. The query is generated from the .Net application using LINQ to SQL. I can see large sort operation in the plan and i have indexes for SORT columns.Also i see OR in where conditions. Can anyone please direct me where the issue is?I am still learning on how to debug from the execution plan. Execution Plan Even after hard-coding the linq parameters it takes 23 seconds to run. Since it is generating from .net application,i have less control over the query and i tried adding different indexes(some covering indexes to avoid key lookups) etc.,but of no help till now. Appreciate any kind of help in providing more insight into the plan on problem areas.

while working on the query below in order to answer this question: How to query chart data in a database agnostic way? Having the following tables: CREATE TABLE [dbo].[#foo] ( [creation] DATETIME NOT NULL, [value] MONEY NULL, [DT] AS (CONVERT([date],[CREATION])) PERSISTED) -- add a clustered index on the dt column CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT) GO and this other table for joining: create table #bar (dt date primary key clustered) go the loading of data into these tables can be found here . But when running the following query: WITH RADHE AS ( SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT), THE_DATE=B.dt, THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END , THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0) FROM #BAR B LEFT OUTER JOIN #FOO F ON B.dt = F.dt ) --get rid of the duplicates and present the result SELECT THE_DATE, THE_NUMBER_OF_RECORDS_ON_THIS_DAY, THE_TOTAL_VALUE_FOR_THE_DAY FROM RADHE WHERE THE_ROW = 1 I get something like this picture below, which is exactly what I was looking for. But the execution plan generated has several Sort and Nested Loops Operations, as you can see on the picture below. The full query plan can be found here. this is a very simple operation, a left outer join between 2 tables, the indexes are already ordered, and therefore I was wondering if I could simplify the query plan. alternatively, I could change the query code. why exactly do we need nested loops 2 times and sort 2 times in the query plan?

I have been tuning some queries that had a warning on the sort operator, which is generally an expensive operator : Operator used tempdb to spill data during execution with spill level 2 The way to improve the performance and get rid of the warning on the Sort Operator,was to update the statistics. UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder] WITH FULLSCAN --1 hour 04 min 14 sec But as you can see it took over an hour to update the stats. Now I have a similar query with the same problem. when the @toDate is today, or yesterday I get the same warning: Operator used tempdb to spill data during execution with spill level 2 The query is below, the plan is here ., Picture below for a quick look. DECLARE @FromDate DATETIME = getdate()-1 DECLARE @ToDate DATETIME = getdate()-0 SELECT DISTINCT ap.strAccountCode ,em.strEmail ,em.dtmLastUpdated FROM Bocss2.dbo.tblBAccountParticipant AS ap LEFT JOIN Bocss2.dbo.tblBAccountParticipantEmail AS em ON ap.lngParticipantID = em.lngParticipantID INNER JOIN Bocss2.dbo.tblBAccountHolder AS ah ON ap.lngParticipantID = ah.lngParticipantID INNER JOIN Bocss2.dbo.tblBOrder AS o ON ap.lngParticipantID = o.lngAccountParticipantID WHERE o.sdtmOrdCreated >= @FromDate AND o.sdtmOrdCreated = @FromDate AND o.sdtmOrdCreated < @ToDate OPTION (RECOMPILE) Updating the stats of the order table solves this problem, however, it takes over an hour to run. UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder] WITH FULLSCAN --1 hour 04 min 14 sec I have good reasons to update the stats manually. I also found this link interesting: Why does SQL Server refuse to update these statistics with anything but fullscan? ***Question:*** Instead of updating the stats with full scan, is there a way I can just update the stats for the last day? that would be something I could run on a daily basis. this table in particular get 50k new records every day on average.