Change PK cols and Non-CL Index should DROP NCL First?

PDinCA

I could be wrong, but I thought it was more efficient when changing the PK and dropping a non-clustered Index on the same table to drop the non-clustered index first, then drop the PK.

In my case, I swapped the PK sequence of the 3rd and 4th columns along with dropping an obsolete index.

SC drops the PK then the non-clustered index, so perhaps it's doing unnecessary IO. With the table in question comprising over 110MM rows, this is a potential excessive-wait situation in the making...

(Sadly, I inherited the poorly chosen table and column names; they're pervasive, so I'm stuck with them for now, so please no observations on them...!)

Like I said, I could be wrong...

Here's the generated script:

/*
Run this script on:

        xcQA.xcp_rpt    -  This database will be modified

to synchronize it with:

        xcdev2012.xcp_rpt

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 4/3/2014 3:20:41 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [xcp_rpt]
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [dbo].[t_meter_agg]'
GO
ALTER TABLE [dbo].[t_meter_agg] DROP CONSTRAINT [PK_t_meter_agg]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [ix_ma_EndTime] from [dbo].[t_meter_agg]'
GO
DROP INDEX [ix_ma_EndTime] ON [dbo].[t_meter_agg]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_t_meter_agg] on [dbo].[t_meter_agg]'
GO
ALTER TABLE [dbo].[t_meter_agg] ADD CONSTRAINT [PK_t_meter_agg] PRIMARY KEY CLUSTERED  ([agg_site_id], [agg_tag_id], [agg_interval_code], [agg_start_time], [agg_input_id]) WITH (FILLFACTOR=100, DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Brian Donahue

I can suggest this change - how did you come to the conclusion that it would be better to change the order of the index drop? I tried recreating a facsimile of your table and indexes and analyzed the execution plan both ways it did not seem to be very different.

PDinCA

Probably because when a PK is dropped all other indexes featuring the PK IDs have their "secret columns" removed as the table is now a heap.

Like I said. "I could be wrong" but that was how I understood the behavior to work.

Brian Donahue

I will give you credit where credit is due - switching the indexes around makes the query run consistently 5 seconds faster on my server. It looks like there are a lot more latches going on.

If you are using Source Control, it may be a good idea to put a migrations script there to change the order. I can log a bug on this side but it doesn't seem to be a critical issue.

PDinCA

Thanks Brian.

For completeness, the table in question contains about 110MM rows, which takes considerably longer to alter with the NCI in place.

Haven't yet got the hang of migration scripts in SSC - too much else going on to spend time on them, to be honest, so I keep my scripts in sequence in the file system and run them manually. Thankfully with only one target production server per application, it's not arduous.

Best regards,

Stephen