Unexpected sort operation

GANGA_dba 2014-05-12 19:25:43

Hi,

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.

SQLkiwi 2014-05-12 20:59:10
Please edit your question to add more information rather than adding an answer (you may also choose to comment on an existing answer). I have done this for you on this occasion.

The point I am making in my answer is that the plan was produced on a pre-release version of SQL Server 2014. The disabled index ought not be included in the insert plan at all. I assume this was a bug in CTP 2 that was fixed before SQL Server 2014 was released to manufacturing (RTM). Upgrade/downgrade your development system to a non-preview version and re-run your tests. On a separate note, it seems odd to have different SQL Server versions in DEV, TEST, and PROD.

Dan Holmes 2014-05-12 19:45:13
the sort appears because of the index. The data is being sorted for a probable sequential write to the index. There is a spill to tempdb for the sort. That means even though your estimates for this operation are good, there wasn't enough memory to keep this action from writing to disk.

All that to say, i don't think you can do much about it except to build the index after the table is populated.

SQLkiwi 2014-05-12 19:59:36
Problem is, the index is disabled immediately before this statement, so it ought not be in the plan at all! The 2.6GB memory granted versus 329MB maximum used suggests other internal issues. That's CTP software for you.
SQLkiwi 2014-05-12 19:48:18
You are running SQL Server 2014 CTP2. Upgrade to RTM or RTM CU1.

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.