In a test database, I am looking to:
- Drop clustered index (they are Primary Key Clustered Constraints on a row that is super useless for us.)
- Create new Clustered index
- re-create Primary Key constraint as nonclustered index
- rebuild all other non-clustered indexes.
My workflow is as above as well, with the addition of disabling all the non-clustered indexes prior to dropping the clustered.
Since dropping the clustered constraint index requires the table to save as a HEAP, the amount of time this process takes on our 45m row table is tremendous. The drop on the constraint has been going for 1:17:00 and seems to only be at about 31m (based on Logical Reads in Spotlight for the Session).
Is there a more efficient way to handle this workflow? Perhaps a way to drop the constraint index and rebuild as the new clustered index, rather than as a HEAP?
Thanks,
Wes
DDL Statements:
TABLE STRUCTURE
CREATE TABLE [dbo].[hist](
[prrowid] [varchar](36) NOT NULL,
[part] [varchar](30) NULL,
[date] [datetime] NULL,
[per_date] [datetime] NULL,
[type] [varchar](80) NULL,
[loc] [varchar](80) NULL,
[loc_begin] [decimal](28, 10) NULL,
[begin_qoh] [decimal](28, 10) NULL,
[qty_req] [decimal](28, 10) NULL,
[qty_chg] [decimal](28, 10) NULL,
[qty_short] [decimal](28, 10) NULL,
[um] [varchar](30) NULL,
[last_date] [datetime] NULL,
[nbr] [varchar](30) NULL,
[so_job] [varchar](80) NULL,
[ship_type] [varchar](30) NULL,
[addr] [varchar](80) NULL,
[rmks] [varchar](80) NULL,
[xdr_acct] [varchar](80) NULL,
[xcr_acct] [varchar](80) NULL,
[mtl_std] [decimal](28, 10) NULL,
[lbr_std] [decimal](28, 10) NULL,
[bdn_std] [decimal](28, 10) NULL,
[price] [decimal](28, 10) NULL,
[trnbr] [int] NULL,
[gl_amt] [decimal](28, 10) NULL,
[xdr_cc] [varchar](30) NULL,
[xcr_cc] [varchar](30) NULL,
[lot] [varchar](80) NULL,
[sub_std] [decimal](28, 10) NULL,
[gl_date] [datetime] NULL,
[qty_loc] [decimal](28, 10) NULL,
[userid] [varchar](80) NULL,
[serial] [varchar](50) NULL,
[effdate] [datetime] NULL,
[prod_line] [varchar](30) NULL,
[xslspsn1] [varchar](80) NULL,
[xslspsn2] [varchar](80) NULL,
[xcr_proj] [varchar](80) NULL,
[xdr_proj] [varchar](80) NULL,
[line] [int] NULL,
[user1] [varchar](80) NULL,
[user2] [varchar](80) NULL,
[curr] [varchar](30) NULL,
[ex_rate] [decimal](28, 10) NULL,
[rev] [varchar](30) NULL,
[time] [int] NULL,
[ovh_std] [decimal](28, 10) NULL,
[site] [varchar](80) NULL,
[status] [varchar](80) NULL,
[grade] [varchar](30) NULL,
[expire] [datetime] NULL,
[assay] [decimal](28, 10) NULL,
[xgl_ref] [varchar](30) NULL,
[_chr01] [varchar](80) NULL,
[_chr02] [varchar](80) NULL,
[_chr03] [varchar](80) NULL,
[_chr04] [varchar](80) NULL,
[_chr05] [varchar](80) NULL,
[_chr06] [varchar](80) NULL,
[_chr07] [varchar](80) NULL,
[_chr08] [varchar](80) NULL,
[_chr09] [varchar](80) NULL,
[_chr10] [varchar](80) NULL,
[_chr11] [varchar](80) NULL,
[_chr12] [varchar](80) NULL,
[_chr13] [varchar](80) NULL,
[_chr14] [varchar](80) NULL,
[_chr15] [varchar](80) NULL,
[_dte01] [datetime] NULL,
[_dte02] [datetime] NULL,
[_dte03] [datetime] NULL,
[_dte04] [datetime] NULL,
[_dte05] [datetime] NULL,
[_dec01] [decimal](28, 10) NULL,
[_dec02] [decimal](28, 10) NULL,
[_dec03] [decimal](28, 10) NULL,
[_dec04] [decimal](28, 10) NULL,
[_dec05] [decimal](28, 10) NULL,
[_log01] [bit] NULL,
[_log02] [bit] NULL,
[ref] [varchar](80) NULL,
[msg] [int] NULL,
[program] [varchar](30) NULL,
[ord_rev] [int] NULL,
[ref_site] [varchar](80) NULL,
[rsn_code] [varchar](80) NULL,
[vend_lot] [varchar](30) NULL,
[vend_date] [datetime] NULL,
[daycode] [varchar](80) NULL,
[for] [varchar](30) NULL,
[slspsn##1] [varchar](82) NULL,
[slspsn##2] [varchar](82) NULL,
[slspsn##3] [varchar](82) NULL,
[slspsn##4] [varchar](82) NULL,
[fsm_type] [varchar](80) NULL,
[upd_isb] [bit] NULL,
[auto_install] [bit] NULL,
[ca_int_type] [varchar](80) NULL,
[covered_amt] [decimal](28, 10) NULL,
[fcg_code] [varchar](80) NULL,
[batch] [varchar](30) NULL,
[fsc_code] [varchar](80) NULL,
[sa_nbr] [varchar](80) NULL,
[sv_code] [varchar](80) NULL,
[eng_area] [varchar](30) NULL,
[sys_prod] [varchar](30) NULL,
[svc_type] [varchar](30) NULL,
[ca_opn_date] [datetime] NULL,
[cprice] [decimal](28, 10) NULL,
[eng_code] [varchar](80) NULL,
[wod_op] [int] NULL,
[enduser] [varchar](80) NULL,
[ship_inv_mov] [varchar](80) NULL,
[ship_date] [datetime] NULL,
[ship_id] [varchar](30) NULL,
[ex_rate2] [decimal](28, 10) NULL,
[ex_ratetype] [varchar](80) NULL,
[exru_seq] [int] NULL,
[promise_date] [datetime] NULL,
[fldchg_cmtindx] [int] NULL,
[SrcPDB] [varchar](12) NULL,
CONSTRAINT [hist_PK] PRIMARY KEY CLUSTERED
(
[prrowid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CURRENT INDEXES
ALTER TABLE [dbo].[hist] ADD CONSTRAINT [hist_PK] PRIMARY KEY CLUSTERED ( [prrowid] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##addr_eff] ON [dbo].[hist] ( [addr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##batch] ON [dbo].[hist] ( [batch] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##date_trn] ON [dbo].[hist] ( [date], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##eff_trnbr] ON [dbo].[hist] ( [effdate], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##nbr_eff] ON [dbo].[hist] ( [nbr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_eff] ON [dbo].[hist] ( [part], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_trn] ON [dbo].[hist] ( [part], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##ref_filter] ON [dbo].[hist] ( [ref] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##serial] ON [dbo].[hist] ( [serial] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr] ON [dbo].[hist] ( [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##type] ON [dbo].[hist] ( [type], [effdate] ) WITH (FILLFACTOR=100);
DESIRED INDEXES
CREATE UNIQUE CLUSTERED INDEX [hist##date_trn_CX] ON [dbo].[hist] ( [date], [trnbr] ) WITH (FILLFACTOR=100);
ALTER TABLE [dbo].[hist] ADD CONSTRAINT [hist_PK] PRIMARY KEY NONCLUSTERED ( [prrowid] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##addr_eff] ON [dbo].[hist] ( [addr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##batch] ON [dbo].[hist] ( [batch] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##eff_trnbr] ON [dbo].[hist] ( [effdate], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_eff] ON [dbo].[hist] ( [part], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##part_trn] ON [dbo].[hist] ( [part], [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##ref_filter] ON [dbo].[hist] ( [ref] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##serial] ON [dbo].[hist] ( [serial] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr] ON [dbo].[hist] ( [trnbr] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##nbr_eff] ON [dbo].[hist] ( [trnbr], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##trnbr_char] ON [dbo].[hist] ( [trnbr_char] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##type] ON [dbo].[hist] ( [type], [effdate] ) WITH (FILLFACTOR=100);
CREATE INDEX [hist##vend_lot] ON [dbo].[hist] ( [vend_lot] ) WITH (FILLFACTOR=100);
— NOTE —
The below answer worked perfectly for me. I did have to have a volume added. I created a second Filegroup and a datafile on the new drive. Additionally, another log file as well.
Best Answer
Ideally you would do something like this:
DROP_EXISTING = ON
option set.That would skip the step of the table being converted to a heap. Unfortunately, step 1 doesn't appear to be possible in SQL Server.
In addition, BOL has this to say about changing a primary key with
DROP_EXISTING = ON
:As far as I can tell, the best that you can do is to avoid the heap conversion by creating a copy of the table and moving all data there. Removing or adding a clustered index creates an internal copy of the data anyway so it's not like it'll require more space. Here are a few hints to speed that up:
SELECT INTO
. That will copy the data to a heap which is the step you're trying to avoid. However, both theSELECT INTO
and the creation of the clustered index are eligible for parallelism in SQL Server 2014.INSERT INTO... SELECT
you'll need aTABLOCK
hint against the target table to get minimal logging.SORT_IN_TEMPDB = ON
option if tempdb is sized for it.As an aside, if you were curious to see step 2 in action (I was), here's some sample code which shows how the heap conversion step can be skipped: