Unexpected sort operation
I am doing a simple insert into a staging table selecting from the 125 Million rows record table,i was not expecting the sort operation but it cost me more(64%) in the execution plan and hence the SP is running for more than an Hour.
I am disabling the Index before I insert millions of records to staging table ones I insert I rebuild index.
TRUNCATE TABLE DML.ExceptMissingOLSMachine ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine DISABLE INSERT INTO ExceptMissingOLSMachine SELECT PartnerEntitlementID FROM DML.ExceptionsTrackingTable WHERE ExceptionType <> N'No OLS Machine Data' AND PartnerEntitlementID IS NOT NULL AND ActivationModel = 'Subscription' ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine REBUILD
I have index on staging table:
CREATE NONCLUSTERED INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON DML.ExceptMissingOLSMachine(PartnerEntitlementID)
and also the table DML.ExceptionsTrackingTable has a covering Index ExceptionType ,PartnerEntitlementID ,ActivationModel with 125 million records.
The database has been restored on DEV machine with 2014 version but the query actually runs in SQL server 2008 R2 once we move to test environment.
I have an non clustred Index on staging table,I am sorry my serve is not responding otherwise i could have attached the Index detaisl as well.
All that to say, i don't think you can do much about it except to build the index after the table is populated.
Mixing DDL (disabling the index) with DML (performing the insert) can be problematic within a transaction in some situations, but I believe what you are seeing is a bug that did not make it into RTM (disabling the index ought to cause any cached INSERT plan to recompile).
A similar test certainly works correctly for me on SQL Server 2014 RTM CU1 (build 12.0.2342). I am reluctant to look into this further until you are using a supported version.