SQL disable Index then Bulk Load Problem.

[Nisusmage]

I'm aware that to speed up a bulk load process in SQL we need to disable the indexes and then load and the rebuild them.

So, in the SQL Bulk loader stage, I'm trying to use the Before-SQL and After-SQL to execute these statements.
Before: ALTER INDEX ALL ON TableName DISABLE
After: ALTER INDEX ALL ON TableName DISABLE

The strangest error returns. "Internal table not avialable or does not exist".

I've tried manually disabling the index and then running the load and I get the same problem until I either REBUILD the indexes or DROP them.

Strangely enough, I once tried to do this while the table was loading and it sped up the load drastically. Once done I ran the REBUILD. Interesting.

Please can anyone she some light on this problem. I want to dynamically DISABLE the indexes and then REBUILD them when finished with the load.

After this process I want to try to do this just just partitions and partitioned indexes that are only afftected. Store a lookup with the different partitions, DISABLE just the partition then just REBUILD the partitioned INDEX. that'll work if I can get this part working.

[chulett]

In your shoes, I would be taking a problem like this to my DBA since it has nothing to do with DataStage. You may not be able to simply disable them but rather need to drop and then rebuild them.

[Nisusmage]

I'm currently filling the DBA's role. I don't think doing a drop and recreate index is a viable index. This requires me to constantly maintain stored procedures to keep up to date with any new indexes I or others may create and/or drop because it's not used.

Currently our environment is very dynamic that way, because we are constantly speeding up new queries and dimensioning scripts.

A Disable all and Rebuild all is really the only option I have at this stage, until we can establish slow changing index requirements.

I just need to find a way to do it dynamically.

[chulett]

I'm currently filling the DBA's role. I don't think doing a drop and recreate index is a viable option. This requires me to constantly maintain stored procedures to keep up to date with any new indexes I or others may create and/or drop because it's not used.

Not really. Properly created processes can dynamically capture the index information from system tables before dropping them, then use that captured dynamic sql to rebuild them.

Of course, you could do the same thing for disable and rebuild all dynamically. I could help accomplishing that in Oracle, but SQL Server... not so much.

[Nisusmage]

Here we go peoples.

turns out when you disable your clustered index the SQL bulk loader, or any loader, fails with some error. So you can only really disable your non clustered indexes. FYI: order your data you loading the same as the clustered index for speed.

So... check this little proc out. I've tested it and speeds the loads remarkably.

Code: Select all

ALTER PROCEDURE [dbo].[spa_IndexControl] @tName varchar(1000), @Enable bit = 0
AS
BEGIN

	DECLARE @iFunction varchar(1000)
	SET @iFunction = case when @Enable = 0 then 'DISABLE' else 'REBUILD' end

	DECLARE NonCLIndexes CURSOR READ_ONLY STATIC FORWARD_ONLY
	FOR		SELECT	i.name
			FROM		sys.indexes i
			INNER JOIN	sys.tables t	ON i.object_id	= t.object_id
			WHERE	i.type	= 2
			AND		t.name	= @tName

	DECLARE @iname varchar(1000)
	OPEN NonCLIndexes

	FETCH NEXT FROM NonCLIndexes INTO @iname
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN
			EXEC('ALTER INDEX ' + @iName + ' ON ' + @tName + ' ' + @iFunction)		
		END
		FETCH NEXT FROM NonCLIndexes INTO @iname
	END

	CLOSE NonCLIndexes
	DEALLOCATE NonCLIndexes

END

[Nisusmage]

BTW this DISABLE feature is anly available in SQL 2K5 and SQL 2K8.