Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ALTER INDEX with a CASE for FILLFACTOR

Posted on 2014-02-07
11
Medium Priority
?
425 Views
Last Modified: 2014-02-07
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
Comment
Question by:dbaSQL
  • 6
  • 5
11 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39842955
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 39843001
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39843079
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Author Comment

by:dbaSQL
ID: 39843097
Sweet!  Thank you, Scott.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39843104
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39843112
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 39843184
Perfect!  Thank you, sir.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39843236
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 39843266
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39843323
Looks good.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39843352
Thank you again, Scott.  Have a great weekend!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question