2

Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps without causing any issues? It takes more than an hour just to run the 1st command. Would it just be easier to drop the entire table and re-create it? Or is that not possible and safe with so much data? Can anyone think of any other ideas to speed this up? We are just trying to truncate the data, and then rebuild the table from scratch in our ETL process.

        DROP INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] WITH ( ONLINE = OFF )
        ALTER TABLE OBS_FACT DROP CONSTRAINT OBS_FACT_PK
        ALTER INDEX ALL ON OBS_FACT disable;

        -- add new data to OBS_FACT table via ETL process

        ALTER TABLE [dbo].[OBS_FACT] ADD CONSTRAINT [OBS_FACT_PK] PRIMARY KEY NONCLUSTERED 
        (
            [ENCOUNTER_NUM] ASC,
            [CONCEPT_CD] ASC,
            [PROVIDER_ID] ASC,
            [START_DATE] ASC,
            [MODIFIER_CD] ASC,
            [INSTANCE_NUM] ASC
        ) ON [PRIMARY]

        CREATE CLUSTERED INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] 
        (
            [CONCEPT_CD] ASC
        );  

        -- REBUILD indexes on OBSERVATION_FACT
        ALTER INDEX ALL ON OBS_FACT REBUILD

Often times, the drop index command causes this error in SQL Server Management Studio, if you try to re-launch SQL Server Management Studio in a different Window.

Lock request time out period exceeded (Microsoft SQL Server, Error: 1222)

  • Are you just re-creating the same clustered index, or are you actually changing things? Why ONLINE = OFF? Why not use CREATE ... WITH (DROP_EXISTING = ON, ONLINE = ON)? – Aaron Bertrand Apr 29 '15 at 15:15
  • Dropping the Clustered Index is a bad idea. If you're just trying to truncate the data and rebuild the table from scratch, then just TRUNCATE TABLE OBS_FACT; and re-insert the data in the ETL process. – Mark Sinkinson Apr 29 '15 at 15:16
  • Yes, after dropping that index, and adding data, we're creating the same clustered index again. Not sure why I have ONLINE = OFF. I'm moving this code from Pentaho Kettle to Informatica. So if we TRUNCATE TABLE OBS_FACT, what should the exact steps, and order be? – MacGyver Apr 29 '15 at 15:18
  • 1
    Why do you think you need to do any of those 6 commands? – Aaron Bertrand Apr 29 '15 at 15:29
  • 1
    @MacGyver you should read The Data Loading Performance Guide as it has many best practices when loading data into sql server. – Kin Shah Apr 29 '15 at 15:34
6

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful.

If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your Clustered Index order in the first place.

However, if you want to disable your indexes, then that would be as follows:

-- Disable indexes on OBSERVATION_FACT
-- If you're dropping, don't disable. If you're disabling, don't drop...
ALTER INDEX ALL ON OBS_FACT DISABLE;

-- Truncate your table    
TRUNCATE TABLE dbo.OBS_FACT;

-- ETL Process here....

-- REBUILD indexes on OBSERVATION_FACT
-- Or recreate them if you've dropped them in step 1
ALTER INDEX ALL ON OBS_FACT REBUILD WITH (ONLINE = ON);

Creating following by immediately rebuilding your indexes as per your script, is pointless, as creating builds the index. Why then immediately rebuild?

  • 2
    It might be worth making it clear that ALTER INDEX ALL ... DISABLE will also disable the clustered index. – Paul White Apr 29 '15 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.