Solved

ALTER INDEX with a CASE for FILLFACTOR

Posted on 2014-02-07
11
411 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 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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
 
LVL 17

Author Comment

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

Accepted Solution

by:
ScottPletcher 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:ScottPletcher
ID: 39843323
Looks good.
0
 
LVL 17

Author Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now