none
Sort operator in Execution Plan RRS feed

  • Question

  • I have following simple query.

    SELECT STT.SWallet_ID
    FROM dbo.TRANSACTION STT 
    WHERE STT.Keyword='apmt'
    ORDER BY STT.SWallet_ID

    Because of order by clause, the execution plan shows sort operator. To remove the sort, I tried various indexes bout could not. According to BOL, sort should be removed by using index on column stt.SWallet_ID.

    Monday, October 21, 2019 8:34 AM

Answers

  • The index key order matters. Try specifying the column used in equality predicate first followed by the ORDER BY column. This will allow the first column to be used for the seek and the second column for ordering, hopefully avoiding the sort.

    CREATE NONCLUSTERED INDEX TRANSACTION_SWallet_ID ON [TRANSACTION](
    	 keyword
    	,source_wallet_id
    );


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 4:09 AM
    Monday, October 21, 2019 11:02 AM
    Moderator

  • following is the query:

    SELECT STK.Keyword_Description
    FROM dbo.SW_TBL_KEYWORD STK 
    WHERE STK.Keyword_Scope='a'
    ORDER BY STK.Keyword_Description 

    Given the revised DDL and query, have you tried this index?

    CREATE NONCLUSTERED INDEX SW_TBL_KEYWORD_Keyword_Scope ON SW_TBL_KEYWORD(
    	 Keyword_Scope
    	,Keyword_Description
    );


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 4:02 AM
    Monday, October 21, 2019 11:56 AM
    Moderator
  • I found the issue. The transaction table I posted above is partitioned. In other tables its fine. But in case of partitioned table the index must be created on primary. I created indexes without defining on primary but such indexes were not used. 

    Table partitioning is a significant detail that was missing in the posted DDL and explains the issue. With a nonunique partitioned index, the optimizer considers the partition number as if it were the first column of the index.

    Because the partitioning column is not specified in the WHERE clause, SQL Server must access each index partition individually and the order of the returned rows will not match the ORDER BY clause, necessitating a sort operator in the plan. To illustrate, if you were to add the partition number to the ORDER BY clause like the example below, the sort will be avoided. Of course, this is will not return rows in the actual order desired.

    SELECT STT.Source_Wallet_ID
    FROM dbo.SW_TBL_TRANSACTION STT
    WHERE STT.Keyword='apmt'
    ORDER BY $PARTITION.PF_SW_TBL_TRANSACTION(Transaction_ID), STT.Source_Wallet_ID;

    If indexes are created without defining filegroup where are they created?

    The default is the same ON clause as the underlying table. Since the table is partitioned, the nonclustered index is partitioned using the same partition scheme as the underlying table.  

    I don't know why this table is partitioned but be aware that the table and indexes are no longer aligned due to the non-partitioned index. Table partitioning affects the physical table and index structure and indexing strategy.

     


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 10:25 AM
    Tuesday, October 22, 2019 10:03 AM
    Moderator

All replies

  • Hi 

    Can you try creating a non-clustered index as below and test once

    CREATE NONCLUSTERED INDEX TRANSACTION_SWallet_ID ON [TRANSACTION](SWallet_ID)

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 8:58 AM
  • I tried that index but the sort still persists.

    There is an index on source_wallet_id and an index on keyword and source_wallet_id. The seek is on the later index.


    • Edited by Curendra Monday, October 21, 2019 9:09 AM
    Monday, October 21, 2019 9:04 AM
  • Hi

    Have you tried creating non-clustered index in a reverse order. i.e., (Source walleter & keyword) not Keyword & wallet

    Reference:-

    CREATE NONCLUSTERED INDEX TRANSACTION_SWallet_ID ON [TRANSACTION](source_wallet_id,keyword)

    Let me know how it goes.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 9:45 AM
  • The result is same as above.
    Monday, October 21, 2019 10:02 AM
  • The index key order matters. Try specifying the column used in equality predicate first followed by the ORDER BY column. This will allow the first column to be used for the seek and the second column for ordering, hopefully avoiding the sort.

    CREATE NONCLUSTERED INDEX TRANSACTION_SWallet_ID ON [TRANSACTION](
    	 keyword
    	,source_wallet_id
    );


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 4:09 AM
    Monday, October 21, 2019 11:02 AM
    Moderator
  • That didnt help either.



    • Edited by Curendra Monday, October 21, 2019 11:25 AM
    Monday, October 21, 2019 11:21 AM
  • Can you share the DDL (Includes Table Definition & Index creations)

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 11:27 AM
  • I would expect the same plan regardless of the number of rows if your actual query is the one below. Is that the case?

    SELECT STT.Source_Wallet_ID
    FROM dbo.[TRANSACTION] STT 
    WHERE STT.Keyword='apmt'
    ORDER BY STT.Source_Wallet_ID;

     

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, October 21, 2019 11:29 AM
    Moderator
  • I tried with different table with few,some 70, rows.

    following is the table:

    CREATE TABLE [dbo].[SW_TBL_KEYWORD]
    (
    [Keyword] [varchar] (5) NOT NULL,
    [Keyword_Description] [varchar] (100)  NOT NULL,
    [Keyword_Scope] [char] (1)  NOT NULL,
    [Created_By] [varchar] (100)  NOT NULL,
    [Created_Date] [datetime] NOT NULL ,
    [Modified_By] [varchar] (100)  NULL,
    [Modified_Date] [datetime] NULL,
    [Approved_By] [varchar] (100)  NULL,
    [Approved_Date] [datetime] NULL,
    [Is_Financial] [bit] NULL,
    [Chargeable] [char] (1)  NULL,
    [Kc_Id_Lookup] [char] (1)  NULL,
    [Commissionable] [char] (1)  NULL,
    [Kcm_Id_Lookup] [char] (1)  NULL,
    [MINIMUM_TRAN_AMOUNT] [money] NULL ,
    [INVOLVED_PARTY] [varchar] (10)  NULL,
    [ApplyTds] [bit] NULL,
    [Is_System_Keyword] [bit] NOT NULL 
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[SW_TBL_KEYWORD] ADD PRIMARY KEY CLUSTERED ([Keyword]) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [ncidx_ks_kd] ON [dbo].[SW_TBL_KEYWORD] ([Keyword_Scope]) INCLUDE ([Keyword_Description]) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [ncidx_kd] ON [dbo].[SW_TBL_KEYWORD] ([Keyword_Description]) ON [PRIMARY]
    GO

    following is the query:

    SELECT STK.Keyword_Description
    FROM dbo.SW_TBL_KEYWORD STK 
    WHERE STK.Keyword_Scope='a'
    ORDER BY STK.Keyword_Description 



    • Edited by Curendra Monday, October 21, 2019 11:46 AM
    Monday, October 21, 2019 11:34 AM

  • following is the query:

    SELECT STK.Keyword_Description
    FROM dbo.SW_TBL_KEYWORD STK 
    WHERE STK.Keyword_Scope='a'
    ORDER BY STK.Keyword_Description 

    Given the revised DDL and query, have you tried this index?

    CREATE NONCLUSTERED INDEX SW_TBL_KEYWORD_Keyword_Scope ON SW_TBL_KEYWORD(
    	 Keyword_Scope
    	,Keyword_Description
    );


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 4:02 AM
    Monday, October 21, 2019 11:56 AM
    Moderator
  • This worked in the above table but not in the following table.

    CREATE TABLE [dbo].[SW_TBL_TRANSACTION]
    (
    [Transaction_ID] [bigint] NOT NULL,
    [Keyword] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Source_Wallet_ID] [bigint] NOT NULL,
    [Dest_Wallet_ID] [bigint] NOT NULL,
    [Amount] [money] NOT NULL,
    [Souce_Balance_Before] [money] NOT NULL,
    [Source_Balance_After] [money] NOT NULL,
    [Dest_Balance_Before] [money] NOT NULL,
    [Dest_Balance_After] [money] NOT NULL,
    [Created_Date] [datetime] NOT NULL CONSTRAINT [DF__SW_TBL_TR__Creat__33D4B598] DEFAULT (getdate()),
    [Transaction_Fee] [money] NOT NULL,
    [Transaction_Comm] [money] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Reference_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Fee_Payer] [bigint] NULL,
    [Commission_Receiver] [bigint] NULL,
    [Txn_Date] [datetime] NOT NULL CONSTRAINT [Cnst_TxnDate] DEFAULT (getdate())

    GO
    ALTER TABLE [dbo].[SW_TBL_TRANSACTION] ADD CONSTRAINT [PK__SW_TBL_T__9A8D5625CF05426B] PRIMARY KEY CLUSTERED ([Transaction_ID]) 
    GO
    CREATE NONCLUSTERED INDEX [ncidx_kw_sw] ON [dbo].[SW_TBL_TRANSACTION] ([Keyword], [Source_Wallet_ID]) 
    GO

    following is the query:

    SELECT STT.Source_Wallet_ID
    FROM dbo.SW_TBL_TRANSACTION STT
    WHERE STT.Keyword='apmt'
    ORDER BY STT.Source_Wallet_ID 

    Monday, October 21, 2019 12:08 PM
  •  (Curendra) writes:

    following is the query:SELECT STT.Source_Wallet_ID
    FROM dbo.SW_TBL_TRANSACTION STT
    WHERE STT.Keyword='apmt'
    ORDER BY STT.Source_Wallet_ID 

    Can you upload the query plan somewhere, for instance to http://www.brentozar.com/pastehteplan
    so that we can look at it?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 21, 2019 9:53 PM
  • I found the issue. The transaction table I posted above is partitioned. In other tables its fine. But in case of partitioned table the index must be created on primary. I created indexes without defining on primary but such indexes were not used. 

    But in case of partitioned table we would create index on partition scheme but such indexes dont work.

    What is this problem? What am I missing?

    If indexes are created without defining filegroup where are they created?

    Tuesday, October 22, 2019 4:08 AM
  • And the partition key is not any of the other two columns? If you create the index on the partition scheme, the partitioning column is logically first in the index - since the index is split up in a number of partitions on that key. SQL Server can seek each partition on the keyword, but then it needs to sort to fulfil the ORDER BY condition. Just picture your self a number of boxes. In each box you can go and search on the keyword, and get a list of sorter wallet ids. But when you add the lists together, you need to merge them - that is sort them.

    If you create the index on a single filegroup, this does not happen, but in that case you will not be table to things like switch partitions with less than you drop the index when you perform such operations.

    Partitioning a table is nothing you should not do on a whim. Partitioning may be well-suited for a table where all queries include the one and same column. But tables that queried in all sorts of dimensions is probably best left un-partitioned.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 22, 2019 7:06 AM
  • No, the partition key is not any of the other two columns. transactionid is the partition key column.

    I tried using that column too in the index but didnt help.

    How to create index to fix this?

    Also, I could not remove distinct sort from execution plan. Is it the sort we cannot remove at all?

    Tuesday, October 22, 2019 7:46 AM
  • >>>

    CREATE NONCLUSTERED INDEX TRANSACTION_SWallet_ID ON [TRANSACTION](source_wallet_id,keyword)

    Isn't it helped to solve your initial issue. Just looking to know as it worked at my side.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, October 22, 2019 7:48 AM
  • Index in this order ON [TRANSACTION](source_wallet_id,keyword) removes sort but with scan. the opposite order gives seek.
    • Edited by Curendra Tuesday, October 22, 2019 8:18 AM
    Tuesday, October 22, 2019 7:56 AM

  • Also, I could not remove distinct sort from execution plan. Is it the sort we cannot remove at all?

    Now I can remove distinct sort operator by creating covering index by using order by column as the key.
    Tuesday, October 22, 2019 9:30 AM
  • I found the issue. The transaction table I posted above is partitioned. In other tables its fine. But in case of partitioned table the index must be created on primary. I created indexes without defining on primary but such indexes were not used. 

    Table partitioning is a significant detail that was missing in the posted DDL and explains the issue. With a nonunique partitioned index, the optimizer considers the partition number as if it were the first column of the index.

    Because the partitioning column is not specified in the WHERE clause, SQL Server must access each index partition individually and the order of the returned rows will not match the ORDER BY clause, necessitating a sort operator in the plan. To illustrate, if you were to add the partition number to the ORDER BY clause like the example below, the sort will be avoided. Of course, this is will not return rows in the actual order desired.

    SELECT STT.Source_Wallet_ID
    FROM dbo.SW_TBL_TRANSACTION STT
    WHERE STT.Keyword='apmt'
    ORDER BY $PARTITION.PF_SW_TBL_TRANSACTION(Transaction_ID), STT.Source_Wallet_ID;

    If indexes are created without defining filegroup where are they created?

    The default is the same ON clause as the underlying table. Since the table is partitioned, the nonclustered index is partitioned using the same partition scheme as the underlying table.  

    I don't know why this table is partitioned but be aware that the table and indexes are no longer aligned due to the non-partitioned index. Table partitioning affects the physical table and index structure and indexing strategy.

     


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Curendra Tuesday, October 22, 2019 10:25 AM
    Tuesday, October 22, 2019 10:03 AM
    Moderator
  • Above suggestions worked fine in queries with single table but in join query I could not get rid of sort.

    following is the query:

    declare @districtId int=11
    SELECT DISTINCT
    locationId = adl.districtCode,
    locationName = adl.districtName
    FROM api_districtList adl WITH(NOLOCK)
    left JOIN apiLocationMapping alm WITH(NOLOCK) ON adl.districtCode = alm.apiDistrictCode
    WHERE ISNULL(adl.isDeleted, 'N') = 'N' AND ISNULL(adl.isActive,'Y')='Y'
    AND alm.districtId = @districtId --(if this clause is removed, sort is also removed)
    ORDER BY adl.districtName

    and the index is:

    create nonclustered index ncidx_dcode_dname_isdel_isact on api_districtList(districtname,districtcode,isdeleted,isactive)



    • Edited by Curendra Thursday, October 31, 2019 11:25 AM
    Thursday, October 31, 2019 11:11 AM