Skip to main content
Database Administrators

Return to Question

added [performance-tuning] to 516 questions - Shog9 (Id=1924)
Link
Community Bot
Community Bot
  • 1
deleted 6 characters in body; edited tags
Source Link
Joe Obbish
Joe Obbish
  • 33.3k
  • 4
  • 77
  • 156

I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

  1. Set Recovery Model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.

    Set recovery model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.

  2. Add table hit TABLOCK to release any locking during the executions.

    Load data with the TABLOCK hint to release any locking during the executions.

  3. Set max dop to 30 and change it back to normal.

    Set MAXDOP to 30 and change it back to normal.

Example of the statemetsstatements:

/*Insert Example*/ INSERT INTO [dbo].[Order]( [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] SELECT [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] FROM [Order_old] WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Delete Example*/ DELETE Ord FROM [Order] Ord WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Update Example*/ UPDATE Order_Type SET [TYPE]=NULL WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.

I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

  1. Set Recovery Model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.

  2. Add table hit TABLOCK to release any locking during the executions.

  3. Set max dop to 30 and change it back to normal.

Example of the statemets:

/*Insert Example*/ INSERT INTO [dbo].[Order]( [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] SELECT [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] FROM [Order_old] WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Delete Example*/ DELETE Ord FROM [Order] Ord WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Update Example*/ UPDATE Order_Type SET [TYPE]=NULL WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.

I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

  1. Set recovery model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.
  2. Load data with the TABLOCK hint to release any locking during the executions.
  3. Set MAXDOP to 30 and change it back to normal.

Example of the statements:

/*Insert Example*/ INSERT INTO [dbo].[Order]( [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] SELECT [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] FROM [Order_old] WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Delete Example*/ DELETE Ord FROM [Order] Ord WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Update Example*/ UPDATE Order_Type SET [TYPE]=NULL WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.

Source Link
user141153
user141153

Copying large amounts of data to another target tables

I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

  1. Set Recovery Model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.

  2. Add table hit TABLOCK to release any locking during the executions.

  3. Set max dop to 30 and change it back to normal.

Example of the statemets:

/*Insert Example*/ INSERT INTO [dbo].[Order]( [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] SELECT [CREATED] ,[LAST_MODIFIED] ,[number] ,[LAST_MODIFIED] ,[Owner_FK] ,[Owner2_FK] ,[Owner3_FK] FROM [Order_old] WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Delete Example*/ DELETE Ord FROM [Order] Ord WHERE [Owner_FK] IS NOT NULL OR [Owner2_FK] IS NOT NULL OR [Owner3_FK] IS NOT NULL /*Update Example*/ UPDATE Order_Type SET [TYPE]=NULL WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.