August 13, 2012 at 9:28 am
Hey all,
I have a stored proc that is supposed to either disable (for ETL) indexes on a table or rebuild them afterwards (0 or 1 in the param). Here is the proc:
Create PROC [dbo].[SP_Maint_Index] @table VARCHAR(200), @function BIT
As
BEGIN TRY
DECLARE @IndexName Varchar(100)
DECLARE @IndexType Varchar(30)
DECLARE @Is_Primary_key BIT
DECLARE @TSQL NVARCHAR(1000);
DECLARE cur CURSOR LOCAL
FOR
SELECT i.name AS IndexName ,
CASE i.[type]
WHEN 0 THEN 'Heap'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'Non Clustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
END AS IndexType ,
i.is_primary_key
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.[type] = 'U' --USER TABLE
AND i.[type] IN (1,2) --Clustered or Non Clustered
AND s.name + '.' + o.name = @Table
AND i.[type] = CASE WHEN @Function = 0 THEN 2 ELSE i.[type] END
AND i.is_unique = CASE WHEN @Function = 0 THEN 0 ELSE i.is_unique END
AND i.is_unique_constraint = CASE WHEN @Function = 0 THEN 0 ELSE i.is_unique_constraint END
AND
(LEFT(i.name,3) <> 'DP_' OR @Function = 1)
ORDER BY i.[type] asc
OPEN cur
FETCH NEXT FROM cur INTO @IndexName, @IndexType, @Is_Primary_key
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL = CASE WHEN @Function = 0 THEN
'ALTER INDEX '+@IndexName+' ON '+@table+' DISABLE'
ELSE
'ALTER INDEX '+@IndexName+' ON '+@table+' REBUILD WITH ( FILLFACTOR = 100, PAD_INDEX = ON)'
END
SELECT @TSQL
EXEC sp_executesql @Tsql
FETCH NEXT FROM cur INTO @IndexName, @IndexType, @Is_Primary_key
END
CLOSE cur
DEALLOCATE cur
END CATCH
Before the ETL i run a 0 on a table then afterwards i run a 1. The second run gives me two outputs that should have been exec`ed.
Here is one:
ALTER INDEX IND_AD ON odr.tbl_ExpandedPermissions REBUILD WITH ( FILLFACTOR = 100, PAD_INDEX = ON)
Now as soon as this ETL has loaded and the above proc run (which gives me the above SQL, and runs it) if i look at the system tables there is still fragmentation. However in query analyser if i run the above SQL directly the frag goes away.
Why is my proc not working? Is it something to do with in the same batch\session or similar?
Many thanks
Dan
August 13, 2012 at 9:31 am
Actually - i have an idea.
Am i correct in saying that Exec sp_executesql doesnt wait? If so i recon its still rebuilding the other index which is the PK. Could that cause the other index to need to be rebuilt? I assume so?
Dan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply