Indexes on Large Data Extract

[tim999]

Dear all,

I have a question regarding indexes on a data extract database we have - MSSQL2000.

Each night a scheduled job:

1. Deletes the indexes
2. Truncates the table data (empties the tables of all data)
3. Imports the latest data from a UNIX database
4. Recreates the indexes.

Q1. Is this the best way to deal with the indexes? I believe the deleting and recreating is causing a problem on the scheduled job but im not sure of the best way to set this up. Is there a command to rebuild them? Or will it simply do it itself?

The data imported is large amount, several million transactions in 1 table alone so the need for indexes is paramount.

Q2. Overall is this a good method to perform this extract?

Many thanks for any help.

Tim

[ashwingawande]

Hi,

I think u can rebuilt the index instead of deleting and recreating it which of course affects adversly. The command to rebuild an index is : DBCC DBREINDEX. Also u need to check for Index fragmentation. As u have mentioned that ur volume of data is very high so continuously u need to keep an eye on index fragmentation and u need to DEFRAGMENT that index after every interval. To know about fragmentation details use this command :DBCC SHOW CONTIG and for defragmentation just rebuild the index. Also depending upon the frequency of inserts and updates in table u need to specify FILL FACTOR.

Hope this will help u.

Ashwin

Dear all,

I have a question regarding indexes on a data extract database we have - MSSQL2000.

Each night a scheduled job:

1. Deletes the indexes
2. Truncates the table data (empties the tables of all data)
3. Imports the latest data from a UNIX database
4. Recreates the indexes.

Q1. Is this the best way to deal with the indexes? I believe the deleting and recreating is causing a problem on the scheduled job but im not sure of the best way to set this up. Is there a command to rebuild them? Or will it simply do it itself?

The data imported is large amount, several million transactions in 1 table alone so the need for indexes is paramount.

Q2. Overall is this a good method to perform this extract?

Many thanks for any help.

Tim

[Jim Doherty]

Hi,

I think u can rebuilt the index instead of deleting and recreating it which of course affects adversly. The command to rebuild an index is : DBCC DBREINDEX. Also u need to check for Index fragmentation. As u have mentioned that ur volume of data is very high so continuously u need to keep an eye on index fragmentation and u need to DEFRAGMENT that index after every interval. To know about fragmentation details use this command :DBCC SHOW CONTIG and for defragmentation just rebuild the index. Also depending upon the frequency of inserts and updates in table u need to specify FILL FACTOR.

Hope this will help u.

Ashwin

In addition check your database recovery model if it is 'Full Recovery' then for merely importing a huge dataset you might want to consider changing it to Bulk Logged for the period of time it is importing (to reduce process and thereby restrict the growth your transaction log

(database node in EM right click properties...re covery model)


Heres a stored procedure you might want to check out to set as a job to reindex all tables that currently have indexes. You can pass into this the fillfactor if you need to that is as a parameter

 
CREATE PROCEDURE dbo.usp_RebuildAllIndexes (
@dbname sysname = null,
@fillfactor tinyint = null
)
AS
DECLARE @execstr nvarchar(255)
SET NOCOUNT ON
IF @dbname IS NULL SELECT @dbname = DB_NAME()
IF @fillfactor IS NULL
	SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')"'
ELSE
	SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'','''',' + str(@fillfactor) + ')"'
EXEC(@execstr)
GO

Regards

Jim

[tim999]

Many thanks for your replies guys.

I need to do some reading into the fill factor aspect but the rebuild command sounds much neater than a script that deletes and then recreates the indexes.

In addition check your database recovery model if it is 'Full Recovery' then for merely importing a huge dataset you might want to consider changing it to Bulk Logged for the period of time it is importing (to reduce process and thereby restrict the growth your transaction log

(database node in EM right click properties...re covery model)


Heres a stored procedure you might want to check out to set as a job to reindex all tables that currently have indexes. You can pass into this the fillfactor if you need to that is as a parameter

 
CREATE PROCEDURE dbo.usp_RebuildAllIndexes (
@dbname sysname = null,
@fillfactor tinyint = null
)
AS
DECLARE @execstr nvarchar(255)
SET NOCOUNT ON
IF @dbname IS NULL SELECT @dbname = DB_NAME()
IF @fillfactor IS NULL
	SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')"'
ELSE
	SELECT @execstr = 'EXEC ' + @dbname + '..sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'','''',' + str(@fillfactor) + ')"'
EXEC(@execstr)
GO

Regards

Jim