Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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_stats
            )
            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
0
dbaSQL
Asked:
dbaSQL
  • 6
  • 5
1 Solution
 
Scott PletcherSenior DBACommented:
I see some potential issues here.

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).
0
 
dbaSQLAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dbaSQLAuthor Commented:
Sweet!  Thank you, Scott.
0
 
Scott PletcherSenior DBACommented:
DECLARE TableCursor CURSOR FOR
      SELECT s.name,t.name,
            (SELECT CASE WHEN EXISTS(
                   SELECT 1 FROM sys.index_columns ic
                         WHERE ic.object_id = t.object_id AND ic.index_id = 1 AND ic.key_ordinal = 1
                         AND COLUMNPROPERTY ( ic.object_id , COL_NAME(ic.object_id, ic.column_id) , 'IsIdentity' ) = 1)
                   THEN 100 ELSE 90 END) AS [fillfactor]
      FROM sys.tables t INNER JOIN sys.schemas s
        ON t.schema_id = s.schema_id        
      WHERE
            OBJECT_ID IN (
                  SELECT OBJECT_ID
                  FROM sys.dm_db_index_usage_stats
            )
0
 
Scott PletcherSenior DBACommented:
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)) + ');'
...
0
 
dbaSQLAuthor Commented:
Perfect!  Thank you, sir.
0
 
Scott PletcherSenior DBACommented:
You're welcome!

And, btw, view "sys.dm_db_index_usage_stats" contains entries for all dbs.  Thus, if you want to verify than an object is in it, you must include the db_id in the check:


WHERE
            EXISTS(
                  SELECT 1
                  FROM sys.dm_db_index_usage_stats ius
                  WHERE ius.database_id = DB_ID() AND ius.object_id = t.object_id                  
            )
0
 
dbaSQLAuthor Commented:
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_stats ius
                 WHERE ius.database_id = DB_ID()
                AND ius.object_id = t.object_id                  
       )

OPEN TableCursor
.......................................
..............
0
 
Scott PletcherSenior DBACommented:
Looks good.
0
 
dbaSQLAuthor Commented:
Thank you again, Scott.  Have a great weekend!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now