Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Fragmented indexes
Message
De
17/03/2011 13:20:52
 
 
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Maintenance bases de données
Versions des environnements
SQL Server:
SQL Server 2005
Divers
Thread ID:
01503886
Message ID:
01503997
Vues:
28
Yes, that is the rebuilding script that also resets the fill factor to 80. Thank you for the links. The script that checks does it by querying sys.database :
-- =============================================
-- Author: Rollin Burr
-- Create date: 8 Jun 09
-- Description: Captures Index Fragmentation 20%> on all dbs
-- =============================================
CREATE PROCEDURE [dbo].[sGetIndxFrag]
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO IndexFragmentation (
		DBName,
		objectID,
		indexTypeDesc,
		FragPerc,
		EvalDateTime)
	SELECT
	(SELECT [name] 
		FROM sys.databases 
		WHERE sys.databases.database_id =
			sys.dm_db_index_physical_stats.database_id) AS DBName, 
		CAST([object_id] AS BIGINT), 
		index_type_desc, 
		avg_fragmentation_in_percent AS FragPerc, 
		GETDATE()
	FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) 
	WHERE object_id IS NOT NULL 
		AND index_type_desc <> 'HEAP'
		AND database_id <> 1 
		and database_id <> 4 
		and avg_fragmentation_in_percent > 20 
	ORDER BY FragPerc DESC

	-- Update table with ave frag perc for this day
	UPDATE IndexFragmentation
		SET AvgFragByDay =
			(SELECT TOP (1) AVG(FragPerc) AS AvgFragPerc
			FROM dbo.IndexFragmentation
			WHERE (YEAR(GETDATE()) = YEAR(EvalDateTime)) AND 
				  (DATEPART(dy, GETDATE()) = DATEPART(dy, EvalDateTime))
			)
		WHERE (YEAR(GETDATE()) = YEAR(EvalDateTime)) AND 
			  (DATEPART(dy, GETDATE()) = DATEPART(dy, EvalDateTime))

END
>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
>>
>>
Thank You

Rollin Burr

Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform