• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • Last Modified:

Help with rebuilding indexes dynamically

I have made the move from being a SQL db architect to a db administrator and am fairly new to this game.  The DBA admin that just left had begun the process of changing the nightly automated process he had in place to rebuild indexes.  From what I have been told, the dba before the recently gone dba had been using the dbcc command to do this.  The last dba was recently looking at the new abilities in SQL 2005 for performing dynamic rebuilding of the indexes that is supposed to be much better, which makes use of the Alter Index statement. He was able to get to a certain point with a stored procedure that decided what to do based on index fragmentation.  My guess is this stored procedure would be called by an SSIS package so that it could be automated.  If an experienced dba could take a look at the syntax in his stored procedure he had written so far and tell me one, if he was on the right path, and 2 (even better), how to complete this SP to provide an effective and efficient way of performing accurate index tuning I would be much obliged.  Here is the code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[proc_IndexHouseKeeping]
(
      @pfAverageFragPercent float = 10.0,
      @pfRebuildBoundary float = 30.0,
      @piShowList int = 0,
      @piDebug int = 1
)
As
      
SET NOCOUNT ON;

-- Declarations
Declare
      @iDebug int,
      @iObjectID int,
      @iIndexID int,
      @iCount int,
      @iRowCount int,
      @iPartitionCount bigint,
      @sSchemaName sysname,
      @sObjectName sysname,
      @sIndexName sysname,
      @iPartitionNum bigint,
      @iPartitions bigint,
      @fFrag float,
      @fRebuildBoundary float,
      @sSQL varchar(8000);

If (@pfAverageFragPercent = 0) Begin
      Print
      '
            proc_IndexHouseKeeping provides a method for index housekeeping.
            
            Indexes are selected based on their fragmentation percent based on
            the Average Fragmentation Percent specified.

            Two index housekeeping functions occur based on the Rebuild Boundary specified
            in conjunction and based on the Average Fragmentation Percent of the index.  If
            the Average Fragmentation Percent is equal to or greater than the Rebuild
            Boundary specified the index will be REBUILT.  If, on the other hand, the Average
            Fragmentation Percent is less than the Rebuild Boundary specified the index will be
            REORGANIZED.

            Parameters:

            @pfAverageFragPercent (default 10.0) - Average Fragmentation Percent.
            @pfRebuildBoundary (default 30.0) - Rebuild Boundary.
            @piShowList (default 0) - 0 will execute REBUILD or REORGANIZE.
                                                - 1 will present a list of objects and their indexes that meet the
                                                    specified criteria.  REBUILD and REORGANIZE will not execute.
            @piDebug (default 1)      - 0 will execute REBUILD or REORGANIZE.
                                                - 1 will present a list of the ALTER INDEX command.  REBUILD and
                                                    REORGANIZE will not execute.

            Sample:

            Exec proc_IndexHouseKeeping - presents a list of the ALTER INDEX command.
            Exec proc_IndexHouseKeeping @piDebug = 0 - Executes REBUILD or REORGANIZE.
            Exec proc_IndexHouseKeeping @piShowList = 1 - Presents a list of objects and their indexes
                                                                                but REBUILD or REORGANIZE is not executed.
      ';
      Return;
End

-- Defaults
Set @iDebug = 1;
Set @fRebuildBoundary = 30.0;

-- Work table
If Exists (Select
                  [name]
                  From sys.objects
                  Where type = 'u'
                        And [name] = 'TB_TempWork') Begin
      Truncate Table TB_TempWork;
End
Else Begin
      Create Table TB_TempWork
      (
            ObjectId int,
            IndexID int,
            PartitionNumber int,
            AvgFrag float,
            RowSeq bigint IDENTITY(1,1)
      )
End

Insert Into TB_TempWork
Select
    object_id As objectid,
    index_id As indexid,
    partition_number As partitionnum,
    avg_fragmentation_in_percent As frag
From sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
Where avg_fragmentation_in_percent > @pfAverageFragPercent AND index_id > 0;

Set @iRowCount = @@ROWCOUNT;

If (@piShowList = 1) Begin
      Select
            Object_Name(ObjectID) As 'ObjectName',
            IndexID,
            PartitionNumber,
            AvgFrag,
            RowSeq
      From TB_TempWork;
      Return;
End

Set @iCount = 1;

While (@iCount <= @iRowCount) Begin
      Select
            @iObjectID = ObjectID,
            @iIndexID = IndexID,
            @iPartitionNum = PartitionNumber,
            @fFrag = AvgFrag
      From TB_TempWork
      Where RowSeq = @iCount;

      Select
            @sObjectName = so.name,
            @sSchemaName = s.name
    From sys.objects As so
            JOIN sys.schemas As s
                  ON s.schema_id = so.schema_id
    Where so.object_id = @iObjectID;

    Select
            @sIndexName = name
    From sys.indexes
    Where object_id = @iObjectID
            And index_id = @iIndexID;

    Select
            @iPartitionCount = count (*)
    From sys.partitions
    Where object_id = @iObjectID
            And index_id = @iIndexID;

      -- Reorganize
--      If (@fFrag < @pfRebuildBoundary) Begin
            Select @sSQL = 'ALTER INDEX ' + @sIndexName + ' ON ' + @sSchemaName + '.' + @sObjectName + ' REORGANIZE';
            If (@iPartitionCount > 1) Select @sSQL = @sSQL + ' PARTITION=' + CONVERT (CHAR, @iPartitionNum);
            If (@piDebug = 0) Begin
                  Exec (@sSQL);
                  Print @sObjectName + ' ' + @sIndexName + ' Reorganized';
                  Set @sSQL = 'Update Statistics ' + @sSchemaName + '.' + @sObjectName + ' ' + @sIndexName;
                  Print @sObjectName + ' ' + @sIndexName + ' Statistics Updated';
                  Exec (@sSQL);
            End
--  End

      -- Rebuild
--      If (@fFrag >= @pfRebuildBoundary) Begin
--            Select @sSQL = 'ALTER INDEX ' + @sIndexName +' ON ' + @sSchemaName + '.' + @sObjectName + ' REBUILD';
--            If (@iPartitionCount > 1) Select @sSQL = @sSQL + ' PARTITION=' + CONVERT (CHAR, @iPartitionNum);
--            If (@piDebug = 0) Exec (@sSQL);
--  End

      If (@piDebug = 1) Select @sSQL;
      Set @iCount = @iCount + 1;

End
0
jacobymatt
Asked:
jacobymatt
  • 10
  • 9
1 Solution
 
itdrmsCommented:
I'm not a fan of the new maintenance plans -- but for Index Rebuilding they are great.
Management
Maintenance Plans
right click
new plan
view toolbox
Drag a "Rebuild Index" task to your blank pane
right click the task
edit
and follow the prompts

Then set up schedule
0
 
jacobymattAuthor Commented:
So in your opinion letting SQL do most of the work for you by taking your suggested approach is better than what the code I pasted above would do?  I should scrap the more humanized logic that he wrote for the built in Rebuild Index maintenance plan?  What scares me is that I know he knew about the maintenance plan tool in sql 2005 but he opted to write this stored procedure instead.  Any clue why he might have chosen that path?
0
 
itdrmsCommented:
possibly for the same reasons I don't use it for other tasks.  Maintenance plans can't be scripted out, so you can't easily recreate, keep in version control, or edit without using the #$*% GUI.  They are also sometimes buggy.
I had a method to maintain indexes in SQL 2000 based on how much they were fragmented, but since i have Enterprise version for SQL 2005, there's less impact from doing all indexes because they can be done on-line.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jacobymattAuthor Commented:
What do I do if I get this message when I click on "New Maintenance Plan"

'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (ObjectExplorer)
0
 
itdrmsCommented:
Is your SQL Agent started and set to automatic?  SQL2005, Configuration tools, Configuration Manager.
0
 
jacobymattAuthor Commented:
got that error fixed by executing

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

which enabled the agent's extended stored procedures, required for creating maintenance plans.


Okay, last thing and I'll let this go.  So it sounds like even though you used the script you had for SQL 2000 for more of a dynamic maintaining of indexes based on fragmentation, as a SQL 2005 DBA you would opt for using the built in Rebuild Index method over trying to write your own script?  To be honest, I forgot that with Enterprise Edition the task can be performed online.  But here's the bonus, they just want something that can be automated to run around 2 or 3 in the morning, not continuously throughout the day.  At that time the task could practically utilize all the resources it needed to do whatever SQL felt would  be improve efficiency to the indexes.  

So I guess this is the million dollar question (or 250 pts, whatever), do you see anything in the script pasted above that could potentially do a better job than SQL would with the maintenance plan as far as choosing the right things to when addressing each index?  If there isn't anything better, and SQL 2005 really will make the best choices, even 90% of the time, than it seems like a no-brainer to go with the maintenance plan.  I just need to be sure that's the case.  

Thanks!!!
0
 
jacobymattAuthor Commented:
Also, would you not make use of the Reorganize Index task as well?  Or do you think the Rebuild is sufficient?  Why would you not use both?
0
 
itdrmsCommented:
Reorganize isn't necessary if you are rebuilding.
Reorganize Index Task uses ALTER INDEX REORGANIZE
akin to DBCC INDEXDEFRAG
Rebuild Index Task uses ALTER INDEX REBUILD
akin to DBCC DBREINDEX or DROP/CREATE

If you're going to reindex, you're going to start over without fragmentation.

I don't have the luxury of any down time on my servers, so I reorganize nightly, and reindex weekly.

If I didn't have any users between 2 and 3, I would just reindex (maybe)

I honestly do not know how intelligent the maintenance plans are.  I'm holding on to the dream that MS did something right with the new maintenance plans.  But if you run the plan, in the history of the plan you can see the T-SQL that was run.  (supposedly you can do this in the maintenance plan itself -- but haven't had any luck there). (right click the plan, history, expand the steps, at bottom you'll see a hyperlink to view T-SQL)
0
 
jacobymattAuthor Commented:
Yea I get an error when I tried to run the rebuild index maintenance plan saying it could not perform that task due to the fact that the database was still online.  I'm using Standard Edition, both on the test server and production.  So now that you know I have standard edition, is there something I can add to the maintenance plan that takes the db offline first, then runs the rebuild index step, then once completed brings the db back online? I think if I"m doing this around 2 or 3 in the morning then taking it offline might be acceptable depending on how long it takes to complete the rebuilding index operation.  
0
 
itdrmsCommented:
The database doesn't go offline -- the index does and the underlying table is not available.  
According to http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1286607&SiteID=1
you should see ONLINE=OFF in the generated SQL.
0
 
itdrmsCommented:
Found a pretty good summary copied as is from http://blogs.sqlservercentral.com/blogs/steve_jones/archive/2007/06/05/1769.aspx

INDEX REBUILD
  - does all or one index and deals with existing RI and can work online
  - Adheres to fill factors and padding, updates stats, and can uses parallelism.
  - Best chance of getting contiguous index.
 However, there are cons.
  - Locks the entire table for the entire operation if it's offline.
  - One large transaction, so log space can be an issue
  - Can take awhile and uses lots of resouces. Requires 1.2x size of the index.

REORGANIZE
  - minimal locks, fills pages up to the fill factor
  - can be stopped and restarted
  - log can be backed up while this occurs
  - doesn't require extra free space.
HOWEVER, the cons
 - Can log several times the size of the index over time because pages can move more than once.
 - Doesn't update statistics and is single threaded. Rebuilds the leaf level only and works only on one file at a time.

Which is better? It depends. Use the one that works best in each situation. You may have servers that require REORGANIZE, but others that can use REBUILD. Need to consider the size of tables, maintenance window, size of tables, hardware, online requirements, etc. You don't necessarily need to do every index on every table every night. Might have different schedules for different databases. Check under SHOWCONTIG (SS2K) or under the DMV (SS2K5) is an example for reindexing based on thresholds.
0
 
jacobymattAuthor Commented:
thanks for the info above.  Since I will be running this around 2 in the AM I can get away with the tables being accessible for a short period.  But something troubles me, I have been executing the rebuild index task against our test/staging database which is identical to production and when I execute the rebuild it takes less than a second to complete and this is on a database that is almost 11 gigs in size.  doesn't that sound wrong?  I have been trying to find the actual generated sql that executed with the rebuild index task after the fact but I don't see it in the history of the maintenance plan.  I want to make sure it is doing something because I don't see how it could be accomplishing anything under a second with 120 tables and almost 11 gigs.  In the task properties I have the db selected that I want to rebuild and I have all 120 tables selected.  

What could I be missing?
0
 
jacobymattAuthor Commented:
Any more help on this?  I would like to start the automation for this on our production server tonight!
0
 
jacobymattAuthor Commented:
Need an answer on this last part and I will go ahead and award points.....please help on the last question.
0
 
itdrmsCommented:
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31846/
Ran across this script today -- don't have any personal experience with it -- but it compares to yours well and is rated 4 stars and has no flamers as far as I can tell.  Yours has the added flexibility of running variable thresholds.  referenced one has the added feature of retaining data for future analysis (if you are always maintaining an index, it may need to be redefined).  You can review your results only until the next time it is run.

I'm concerned that you can't see the history. The maintenance plan has history and so does the job scheduled by the subplan.  did you check the job, too?  Did the details of the job step show anything?
It definitely should take some time.  did it run once, then since it is test the indexes have not gotten defragged again and therefore doesn't have anything worthwhile to do each subsequent time?  When I look at my maintenance plan history, expand the details, and click on the step, the "view t-sql" link at the bottom highlights and I can see the executed statements.  Did you expand the steps?
0
 
jacobymattAuthor Commented:
First,
I ran this on the production server last night at 2:30 and it took just over 16 minutes, so I think you are right about it just being different on the test server.

Next,
I can see view the history on the job, but when I check the details after the job completes all it gives me is:
Date            10/10/2007 2:36:14 AM
Log            Job History (RebuildIndexesHTQ.Subplan_1)

Step ID            1
Server            HTQDB01
Job Name            RebuildIndexesHTQ.Subplan_1
Step Name            Subplan_1
Duration            00:16:35
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: HTQDB01\SYSTEM. ... 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:36:14 AM  Progress: 2007-10-10 02:36:16.03     Source: {1DFA8CD1-EF9F-4836-89D6-E5E4EC2F78BD}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2007-10-10 02:37:39.12     Source: Rebuild Index Task      Executing query "USE [htq]  ".: 0% complete  End Progress  Progress: 2007-10-10 02:37:39.15     Source: Rebuild Index Task      Executing query "ALTER INDEX [PK_accessType] ON [dbo].[accessType] ".: 0% complete  End Progress  Progress: 2007-10-10 02:37:39.15     Source: Rebuild Index Task      Executing query "USE [htq]  ".: 0% complete  End Progress  Progress: 2007-10-10 02:38:02.32     Source: Rebuild Index Task      Executing query "ALTER INDEX [adminID] ON [dbo].[adminTracking] REB".: 0% complete  End Progress  Progress: 2007-10-10 02:38:02.32  ...  The package executed successf...  The step succeeded.

The only way I can see the T-SQL statement is to edit the rebuild task inside the maintenance plan and that's where the button is to view what it thinks will be produced, but it even says at the bottom that it may not be the same as what actually gets executed due to any configuration options, etc....  I was thinking I could see what T-SQL statements actually were executed after the fact.  Do you have that option or do you have only what I described?  Just so you can see it when I do click that button it produces:
USE [htq]
GO
ALTER INDEX [PK_accessType] ON [dbo].[accessType] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [adminID] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [dateTracked] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [http_referer] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [isPowerAdmin] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [pageAction] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [PK_adminTracking] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [remoteIP] ON [dbo].[adminTracking] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [PK_adTypes] ON [dbo].[adTypes] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [IX_AffID] ON [dbo].[aff2prod] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [htq]
GO
ALTER INDEX [PK_aff2prod] ON [dbo].[aff2prod] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO

....and many more statements just like that, apparently for every index in this database

Lastly,
the question that I think I asked earlier but only got part of the answer to:  The database stays online when the index rebuild task is running, correct?  I'm pretty sure all it does is take each index offline, one at a time, while it rebuilds them, but the db, tables, sp's, etc., those are all just as accessible, correct?  Which brings up the more important sub-part to that question: If someone is querying a table that uses the index that is being rebuilt, what is the worst thing that could happen?

Okay, answer me that please and I'll let this one go and award the points.  Thanks!!!
0
 
itdrmsCommented:
When I look at my **maintenance plan** history, expand the details, and click on the step, the "view t-sql" link at the bottom highlights and I can see the executed statements.  I don't know if that is an edition issue.

The differences between Standard and Enterprise:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Yes, I agree, the T-SQL from within the maintenance plan is not what you wanted -- it is estimated execution, not actual.  (and I have the experience of it often not returning anything)

But moot point anyway -- since your job did take time as expected on prod, and you can see most of the actual statements run from the job history.

BOL ALTER INDEX for ONLINE=OFF:
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. ***This prevents all user access to the underlying table for the duration of the operation.***

So the worst is that the user could time out, or just wait longer at that exact point in time -- but the index maintenance is a necessity.
0
 
jacobymattAuthor Commented:
You've been so much help.  thanks a bunch!

I'm going to go ahead and give the points but do you mind telling me the easiest way to prove that the sql server's performance has improved since I started utilizing the rebuild index task and I also moved the database files to a different drive than the operating system.  I'm sure the performance has to be better but so far the traces I have been running in SQL Profiler have not shown anything useful.  Is Profiler what you would use and if so what would you look at if you needed the proof to boast that I do?  
0
 
itdrmsCommented:
I think you would have had to have benchmarks before to prove improvement after.
I would have run perfmon for some key statistics before and after for the same duration, during the same peak time.  I then bring perfmon results into Excel for min/max/average (depending on unit of measure)
You should have seen better disk utilization, by not having to share with the OS
"\PhysicalDisk()\% Disk Time"
"\PhysicalDisk()\Avg. Disk Queue Length"
"\PhysicalDisk()\Disk Reads/sec"
"\PhysicalDisk()\Disk Writes/sec"

And I think you should have seen better CPU utilization by getting the indexes defragged
"\Processor(_Total)\% Processor Time"

And keeping indexes up to date should result in less page splits
"\SQLServer:Access Methods\Page Splits/sec"

And if you ever had a problem with locks or deadlocks, better indexing resulting in faster execution should reduce those
"\SQLServer:Locks(_Total)\Lock Wait Time (ms)"
"\SQLServer:Locks(_Total)\Number of Deadlocks/sec"
"\SQLServer:Memory Manager\Lock Blocks
"\SQLServer:Locks(_Total)\Lock Timeouts/sec"
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now