Post reply

Non clustered index not getting used for sorting on a large SQL 2005 table.

  • August 10, 2011 at 11:33 pm

    #255371

    Hi All.

    We've a SQL Server 2005 partitioned table which has over 2.5 Billion records. I've Two columns in the table which I'm interested. Col1 (INT), Col2 (Varchar(13)).

    A non clustered index (on the partition scheme) is created on this table as Col1 ASC, Col2 ASC. Table has a clustered index on Date_ID (Partition column), Col1. Server is a 16 proc box. with parallism switched on.

    When I issue the below query, and check the execution plan, it does not utilize the non clustered index for sorting. Instead it uses an explicit sorting operator which is very expensive and time/space consuming. Also it tries to fill up tempdb.

    "SELECT Col1, Col2 FROM MyTable ORDER BY Col1 ASC, Col2 ASC"

    I've used WITH (Index) to force the query to use the non clustered index as well as I've used FAST 1/FASTFIRSTROW options, Also I've switched off parallel execution using MAXDOP to see if that helps. Non Clustered index scan shows that ordered property is False whatever option I try. I believe if I could make it to become True, there won't be a need for the sorting operation (which is most expensive in the whole plan).

    Is there any way, I could make the query engine to use the non clustered index to return the records in its sort order (at least for me to compare the costs) without using an explicit sort operation (and without creating a custom execution plan and using USE PLAN) ?

    Notes:

    * The number of records in the table cannot be minimized because of the nature of the business.

    * Actually I'm looking for a solution for this non clustered index sorting as a part of a more complex CTE using aggregation. (not really to select all records in sorted order.)

    * Changing the clustered index wouldn't be an option (unfortunately)

    Thanks.

    Best Regards,

    Roshan N.A. Jayalath

  • August 11, 2011 at 2:59 am

    #1367117

    Table definition (create table statement), index definitions (create index) and execution plan please.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2011 at 8:34 am

    #1367970

    GilaMonster (8/11/2011)

    Table definition (create table statement), index definitions (create index) and execution plan please.

    Agreed - and OP, please be sure you set your SSMS scripting options to include everything related to partitioning, indexes, filegroups, which it does NOT do by default.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply