October 26, 2010 at 4:26 am
Hi All,
Can any body please provide me a script which rebuilds the all indexes on all user databases whose fragmentation is more than 10%.
Your Help much appriciable.
thanks in advance.
Thank You.
Regards,
Raghavender Chavva
October 26, 2010 at 4:31 am
have a look at Ola's scripts they are all really good and there is one for index maintainence (IndexOptimize)
October 26, 2010 at 4:34 am
you may want to look @
You can configure the IndexOptimize script to match your needs...
October 26, 2010 at 5:13 am
one more thing...this should be online rebuild.
Thank You.
Regards,
Raghavender Chavva
October 26, 2010 at 6:09 am
above solution is not working.
below is the error:
Msg 208, Level 16, State 1, Procedure IndexOptimize, Line 187
Invalid object name 'dbo.DatabaseSelect'.
Thank You.
Regards,
Raghavender Chavva
October 26, 2010 at 6:42 am
You will need the DatabaseSelect function and maybe the CommandExecute.
October 26, 2010 at 7:34 am
how to create that object ?
Thank You.
Regards,
Raghavender Chavva
October 26, 2010 at 7:53 am
Raghavender (10/26/2010)
how to create that object ?
You did not look very hard.
October 26, 2010 at 8:11 am
They are additional functions on the website I posted earlier.
October 26, 2010 at 4:49 pm
Here is the Script make sure the code is working on test server
It Rebuilds or Reorgs based on the level of fragmentation.
--Description : This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold
-- You can define the threshold for reorganize as well as for rebuild and script will work accordingly
-- INPUTS : @fillfactor - While rebuilding index what would be FILLFACTOR for new index
-- @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level, it will be considered for reorganize
-- @@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level, it will be considered for rebuild
-- NOTES : PRINT statements are all queued up and don't show up until the entire script is printed. However, there is an alternative to PRINTing messages.
-- You can raise an error that isn't really an error (code of 0) and you'll get the same effect--message will be printed immediately.
DECLARE @cmd NVARCHAR(1000)
DECLARE @Table VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFragmentationInPercent DECIMAL
DECLARE @fillfactor INT
DECLARE @FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10)
DECLARE @FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10)
DECLARE @Message VARCHAR(1000)
SET NOCOUNT ON
--You can specify your customized value for reorganize and rebuild indexes, the default values
--of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10
--and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt
SET @fillfactor = 90
SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' -- Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '30.0' -- Percent
BEGIN TRY
-- ensure the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL
DROP TABLE #FramentedTableList;
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieving indexes with high fragmentation from ' + DB_NAME() + ' database.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieved indexes with high fragmentation from ' + DB_NAME() + ' database.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
WHILE EXISTS ( SELECT 1 FROM #FramentedTableList WHERE IsProcessed = 0 )
BEGIN
SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,
@SchemaName = SchemaName, @IndexName = IndexName
FROM #FramentedTableList
WHERE IsProcessed = 0
--Reorganizing the index
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganizing Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganize Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END
--Rebuilding the index
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuilding Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuild Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END
UPDATE #FramentedTableList
SET IsProcessed = 1
WHERE TableName = @Table
AND IndexName = @IndexName
END
DROP TABLE #FramentedTableList
END TRY
BEGIN CATCH
PRINT 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
PRINT 'ERROR CODE : ' + CONVERT(VARCHAR, ERROR_NUMBER())
PRINT 'ERROR MESSAGE : ' + ERROR_MESSAGE()
END CATCH
November 3, 2010 at 1:32 am
If you're interested in using my solution I recommend that you download the script MaintenanceSolution.sql. The script contains all the objects that you need and also a set of jobs.
http://ola.hallengren.com/scripts/MaintenanceSolution.sql
Ola Hallengren
November 3, 2010 at 4:55 am
Raghavender (10/26/2010)
fragmentation is more than 10%.
Are you sure ? i think it should be around 70-80%.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 14, 2010 at 5:30 am
Bhuvnesh (11/3/2010)
Raghavender (10/26/2010)
fragmentation is more than 10%.Are you sure ? i think it should be around 70-80%.
Yes....
Thank You.
Regards,
Raghavender Chavva
November 14, 2010 at 7:04 am
Ola's scripts are indeed great, Michelle Ufford over at SQLFool[/url] also has a great index maintenance script. Check them both out and go with whichever you prefer!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2016 at 11:26 am
Looks like the function is no longer used in Ola's updated scripts now available on the site. Here is the DDL for that function. You should run it in the same DB where you created all of the other objects.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DatabaseSelect] (@DatabaseList nvarchar(max))
RETURNS @Database TABLE (DatabaseName nvarchar(max) NOT NULL)
AS
BEGIN
----------------------------------------------------------------------------------------------------
--// Source: http://ola.hallengren.com //--
----------------------------------------------------------------------------------------------------
DECLARE @DatabaseItem nvarchar(max)
DECLARE @Position int
DECLARE @CurrentID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentDatabaseStatus bit
DECLARE @Database01 TABLE (DatabaseName nvarchar(max))
DECLARE @Database02 TABLE (ID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(max),
DatabaseStatus bit,
Completed bit)
DECLARE @Database03 TABLE (DatabaseName nvarchar(max),
DatabaseStatus bit)
DECLARE @Sysdatabases TABLE (DatabaseName nvarchar(max))
----------------------------------------------------------------------------------------------------
--// Split input string into elements //--
----------------------------------------------------------------------------------------------------
WHILE CHARINDEX(', ',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,', ',',')
WHILE CHARINDEX(' ,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,' ,',',')
WHILE CHARINDEX(',,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,',,',',')
IF RIGHT(@DatabaseList,1) = ',' SET @DatabaseList = LEFT(@DatabaseList,LEN(@DatabaseList) - 1)
IF LEFT(@DatabaseList,1) = ',' SET @DatabaseList = RIGHT(@DatabaseList,LEN(@DatabaseList) - 1)
SET @DatabaseList = LTRIM(RTRIM(@DatabaseList))
WHILE LEN(@DatabaseList) > 0
BEGIN
SET @Position = CHARINDEX(',', @DatabaseList)
IF @Position = 0
BEGIN
SET @DatabaseItem = @DatabaseList
SET @DatabaseList = ''
END
ELSE
BEGIN
SET @DatabaseItem = LEFT(@DatabaseList, @Position - 1)
SET @DatabaseList = RIGHT(@DatabaseList, LEN(@DatabaseList) - @Position)
END
IF @DatabaseItem <> '-' INSERT INTO @Database01 (DatabaseName) VALUES(@DatabaseItem)
END
----------------------------------------------------------------------------------------------------
--// Handle database exclusions //--
----------------------------------------------------------------------------------------------------
INSERT INTO @Database02 (DatabaseName, DatabaseStatus, Completed)
SELECT DISTINCT DatabaseName = CASE WHEN DatabaseName LIKE '-%' THEN RIGHT(DatabaseName,LEN(DatabaseName) - 1) ELSE DatabaseName END,
DatabaseStatus = CASE WHEN DatabaseName LIKE '-%' THEN 0 ELSE 1 END,
0 AS Completed
FROM @Database01
----------------------------------------------------------------------------------------------------
--// Resolve elements //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @Database02 WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentID = ID,
@CurrentDatabaseName = DatabaseName,
@CurrentDatabaseStatus = DatabaseStatus
FROM @Database02
WHERE Completed = 0
ORDER BY ID ASC
IF @CurrentDatabaseName = 'SYSTEM_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] IN('master','model','msdb','tempdb')
END
ELSE IF @CurrentDatabaseName = 'USER_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] NOT IN('master','model','msdb','tempdb')
END
ELSE IF @CurrentDatabaseName = 'ALL_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
END
ELSE IF CHARINDEX('%',@CurrentDatabaseName) > 0
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] LIKE REPLACE(PARSENAME(@CurrentDatabaseName,1),'_','[_]')
END
ELSE
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] = PARSENAME(@CurrentDatabaseName,1)
END
UPDATE @Database02
SET Completed = 1
WHERE ID = @CurrentID
SET @CurrentID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentDatabaseStatus = NULL
END
----------------------------------------------------------------------------------------------------
--// Handle tempdb and database snapshots //--
----------------------------------------------------------------------------------------------------
INSERT INTO @Sysdatabases (DatabaseName)
SELECT [name]
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
----------------------------------------------------------------------------------------------------
--// Return results //--
----------------------------------------------------------------------------------------------------
INSERT INTO @Database (DatabaseName)
SELECT DatabaseName
FROM @Sysdatabases
INTERSECT
SELECT DatabaseName
FROM @Database03
WHERE DatabaseStatus = 1
EXCEPT
SELECT DatabaseName
FROM @Database03
WHERE DatabaseStatus = 0
RETURN
----------------------------------------------------------------------------------------------------
END
GO
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply