Home > SQL Challenge > CLUSTERED INDEX on a Big Table on SQL Server

CLUSTERED INDEX on a Big Table on SQL Server

This table grows into a big table with 40+ m records (it seems to me that inserting log data daily into this staging table is not really necessary…but it was designed this way). Let’s create a clustered index on load_dt in this table.

CREATE CLUSTERED INDEX [IX_Loss_Mit_Actn_Log_Archive_Load_date] ON [dbo].[tbl_Loss_Mit_Actn_Log_Archive]
(
      [LOAD_DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

However, this can slow down the insert. So we modified the SSIS package to drop the index on the fly when inserting, and creating the index otherwise (again not sure if this will really improve the performance; I still think that removing the old data to a different table for archiving purpose is a cleaner solution).

 image

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: