ALTER INDEX with a CASE for FILLFACTOR
I'd like to use a CASE in this REBUILD. If is_identity = 1, I will use FILLFACTOR = 0. Otherwise, I am going with 90. I am having a hard time CASING that rebuild statement, and was hoping for a quick pointer from the Experts.
USE DBNAME;
DECLARE
@table VARCHAR(155),
@schema VARCHAR(25),
@sql NVARCHAR(200),
@stmt VARCHAR(2000)
DECLARE TableCursor CURSOR FOR
SELECT s.name,ao.name
FROM sys.all_objects ao INNER JOIN sys.schemas s
ON ao.schema_id = s.schema_id
WHERE
OBJECT_ID IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stat
)
AND [type] = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON '+@schema +'.' + @table + ' REBUILD WITH (FILLFACTOR = 90);'
-- EXEC sp_executesql @stmt = @SQL
PRINT @SQL
FETCH NEXT FROM TableCursor
INTO @schema,@table
END
CLOSE TableCursor
DEALLOCATE TableCursor
USE DBNAME;
DECLARE
@table VARCHAR(155),
@schema VARCHAR(25),
@sql NVARCHAR(200),
@stmt VARCHAR(2000)
DECLARE TableCursor CURSOR FOR
SELECT s.name,ao.name
FROM sys.all_objects ao INNER JOIN sys.schemas s
ON ao.schema_id = s.schema_id
WHERE
OBJECT_ID IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stat
)
AND [type] = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON '+@schema +'.' + @table + ' REBUILD WITH (FILLFACTOR = 90);'
-- EXEC sp_executesql @stmt = @SQL
PRINT @SQL
FETCH NEXT FROM TableCursor
INTO @schema,@table
END
CLOSE TableCursor
DEALLOCATE TableCursor
ASKER
Good point on the 100, Scott. And I agree.
As for the ALL indexes, I am in a bit of a one-time situation. I've got a database already confirmed to be very, very fragmented. I have stats per table, per index. I am performing a one time fix this weekend, just to clean things up. Afterward, the regular index maint will run using ola hallengren's method, which of course, is based on the detected fragmentation.
I've already run the effort, actually, on a backup. It completed successfully, but I realized after the fact that my FILLFACTOR really should be case based, where the table doesn't have an IDENTITY.
Even so, the blanket approach probably isn't optimal. I hear you. And I don't want to spend my whole Sunday on this. Let's say I CASE the fillfactor and rebuild the indexes with 20% or more fragmentation.
What do you think?
As for the ALL indexes, I am in a bit of a one-time situation. I've got a database already confirmed to be very, very fragmented. I have stats per table, per index. I am performing a one time fix this weekend, just to clean things up. Afterward, the regular index maint will run using ola hallengren's method, which of course, is based on the detected fragmentation.
I've already run the effort, actually, on a backup. It completed successfully, but I realized after the fact that my FILLFACTOR really should be case based, where the table doesn't have an IDENTITY.
Even so, the blanket approach probably isn't optimal. I hear you. And I don't want to spend my whole Sunday on this. Let's say I CASE the fillfactor and rebuild the indexes with 20% or more fragmentation.
What do you think?
Sounds good. You could even build them all if you're in a one-off situation.
I would alter the cursor to include the desired fragmentation based on whether or not the table is clustered on identity. I'll post that code as soon as I can.
I would alter the cursor to include the desired fragmentation based on whether or not the table is clustered on identity. I'll post that code as soon as I can.
ASKER
Sweet! Thank you, Scott.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then of course adjust the FETCH and WHILE loop to use that value:
...
FETCH NEXT FROM TableCursor INTO @schema, @table, @fillfactor
...
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON '+@schema +'.' + @table + ' REBUILD WITH (FILLFACTOR = ' + CAST(@fillfactor AS varchar(3)) + ');'
...
...
FETCH NEXT FROM TableCursor INTO @schema, @table, @fillfactor
...
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON '+@schema +'.' + @table + ' REBUILD WITH (FILLFACTOR = ' + CAST(@fillfactor AS varchar(3)) + ');'
...
ASKER
Perfect! Thank you, sir.
You're welcome!
And, btw, view "sys.dm_db_index_usage_sta
WHERE
EXISTS(
SELECT 1
FROM sys.dm_db_index_usage_stat
WHERE ius.database_id = DB_ID() AND ius.object_id = t.object_id
)
And, btw, view "sys.dm_db_index_usage_sta
WHERE
EXISTS(
SELECT 1
FROM sys.dm_db_index_usage_stat
WHERE ius.database_id = DB_ID() AND ius.object_id = t.object_id
)
ASKER
what do you think?
.......
................
THEN 100 ELSE 90 END) AS [fillfactor]
FROM sys.tables t INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE EXISTS(
SELECT 1
FROM sys.dm_db_index_usage_stat
WHERE ius.database_id = DB_ID()
AND ius.object_id = t.object_id
)
OPEN TableCursor
..........................
..............
.......
................
THEN 100 ELSE 90 END) AS [fillfactor]
FROM sys.tables t INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE EXISTS(
SELECT 1
FROM sys.dm_db_index_usage_stat
WHERE ius.database_id = DB_ID()
AND ius.object_id = t.object_id
)
OPEN TableCursor
..........................
..............
Looks good.
ASKER
Thank you again, Scott. Have a great weekend!
1) FILLFACTOR of 0 is the same as 100, but less obvious. Why not just use 100 instead?
2) You're rebuilding ALL indexes in one statement, but the increased fillfactor should really only apply to the clustered index if it's based on an identity column, right? That's easy enough to do in isolation, but it won't help with all the issues.
3) You're rebuilding ALL indexes w/o bothering to check for fragmentation. No need to rebuild an index unless it's at the very least 10% fragmented (and most authorities recommend at least 20% for a full rebuild).