Solved

ALTER INDEX with a CASE for FILLFACTOR

Posted on 2014-02-07
11
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 69

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 69

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 17

Author Comment

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

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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 69

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 69

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

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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

635 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