Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Fragmented indexes
Message
 
 
To
17/03/2011 12:26:03
General information
Forum:
Microsoft SQL Server
Category:
Database management
Environment versions
SQL Server:
SQL Server 2005
Miscellaneous
Thread ID:
01503886
Message ID:
01503993
Views:
34
I don't see anything in this script that checks for the fragmentation level.
I suggest you to check this blog post
http://sqlfool.com/2009/03/automated-index-defrag-script/

Michelle Ufford is community recognized expert, so we can trust this script to being top notch.

BTW, I attended her session about indexes last night and I highly recommend it to everyone when it will be available to watch.
http://www.sqlpass.org/24hours/spring2011/Home/IndexInternalsforMereMortals.aspx

>>>In databases created by Sharepoint I am unable to effectively keep indexes from significant fragmentation. Index rebuilding is done every night, but there are still many with over 80% fragmentation. This is being done as part of the maintenance plan each night. Is there something I might do that would work better?
>>
>>What if the fill factor for your indexes and what kind of indexes they are? Also, how big are your tables?
>
>thank you Naomi, for your response. I am only helping with a database that is not on my project, so I am not as familiar with it as I would otherwise be. The rebuild script resets the fill factor to 80. The tables are not excessively large, not more that 200,000 rows I believe.
>
>The script is recommended by the SharePoint dept of MS. --
>
>
>
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE PROCEDURE [dbo].[sDefragmentIndices]
>AS
>    SET NOCOUNT ON
>    DECLARE @objectid int
>    DECLARE @indexid int
>    DECLARE @command varchar(8000)
>    DECLARE @baseCommand varchar(8000)
>    DECLARE @schemaname sysname
>    DECLARE @objectname sysname
>    DECLARE @indexname sysname
>    DECLARE @currentDdbId int
>    SELECT @currentDdbId = DB_ID()
>
>    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting'
>
>    -- Loop over each of the indices
>    DECLARE indexesToDefrag CURSOR FOR 
>    SELECT i.object_id, i.index_id, i.name
>    FROM sys.indexes AS i INNER JOIN sys.objects AS o
>    ON i.object_id = o.object_id
>    WHERE i.index_id > 0 AND
>		  o.type = 'U'
>    OPEN indexesToDefrag
>    FETCH NEXT FROM indexesToDefrag  -- Loop through the partitions.
>    INTO @objectid, 
>        @indexid,
>        @indexname
>    WHILE @@FETCH_STATUS = 0
>    BEGIN
>        -- Lookup the name of the index
>        SELECT @schemaname = s.name
>        FROM sys.objects AS o
>			JOIN sys.schemas AS s
>        ON s.schema_id = o.schema_id
>        WHERE o.object_id = @objectid
>
>        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + 
>			@indexname + ' is now being rebuilt.'
>
>        -- Fragmentation is bad enough that it will be more efficient to rebuild the index
>        SELECT @baseCommand = ' ALTER INDEX ' + @indexname + ' ON ' + 
>                @schemaname + '.' + object_name(@objectid) + 
>            ' REBUILD WITH (FILLFACTOR = 80, ONLINE = '
>
>        -- Use dynamic sql so this compiles in SQL 2000
>        SELECT @command = ' BEGIN TRY ' + @baseCommand + 'ON) ' + ' END TRY ' +
>            ' BEGIN CATCH ' + @baseCommand + 'OFF) ' + -- Indices with image-like columns can't be rebuild online, so go offline
>            ' END CATCH '
>
>        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding'
>        EXEC (@command)
>        PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done'
>
>        FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname
>    END
>    CLOSE indexesToDefrag
>    DEALLOCATE indexesToDefrag
>
>    RETURN 0
>
>
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform