January 15, 2013 at 11:04 am
Hi All,
I have recently written a script to rebuild, reorg indxes depending on thye fragmention%. I am ataching the script with this post. I am sole DBA for the whole proj. I have no one to go and take advice about this script. Can any please look at the script and point out any problems with. I would like a expert DBA to validate it before I schedule it in PROD. Please advice any changes that I need to make. Any advice would be greatly appriciated.
Thank you in advance.
ALTER PROC [dbo].[sp_RebuildIndexV2](@dbname VARCHAR(100))
AS
SET NOCOUNT ON
DECLARE @STMT varchar(2000)
--DECLARE @dbname VARCHAR(100) = 'DIASTG'
DECLARE @IndexName VARCHAR(200)
DECLARE @ObjectName VARCHAR(200)
DECLARE @cnt INT = 0
--DECLARE @Tname VARCHAR(100)
DECLARE @ListOfTablesNotRebult VARCHAR(MAX) = ''
DECLARE @FragmentPercent FLOAT
SET @STMT = 'SELECT DISTINCT ''['+@dbname+'].['' + OBJECT_SCHEMA_NAME(ddips.[object_id],
DB_ID('''+@dbname+''')) + ''].[''
+ OBJECT_NAME(ddips.[object_id], DB_ID('''+@dbname+'''))+'']'' AS TableName ,
OBJECT_NAME(ddips.[object_id], DB_ID('''+@dbname+''')) AS Tname,
i.[name] AS [index_name], ddips.[avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(DB_ID('''+@dbname+'''), NULL,
NULL, NULL, ''detailed'') ddips
INNER JOIN '+@dbname+'.sys.[indexes] i ON ddips.[object_id] = i.[object_id]
AND ddips.[index_id] = i.[index_id]
WHERE ddips.[avg_fragmentation_in_percent] > 15 AND ddips.index_type_desc <> ''HEAP''
AND OBJECTPROPERTY(I.object_id, ''isMSShipped'') = 0
--AND ddips.[page_count] > 500'
--select @STMT
CREATE TABLE #IndexListTemp(TableName VARCHAR(500), index_name VARCHAR(100), FragmentPercent FLOAT)
INSERT INTO #IndexListTemp
exec (@STMT)
DECLARE @indexList CURSOR
SET @IndexList = CURSOR FOR SELECT DISTINCT * FROM #IndexListTemp
OPEN @indexList
FETCH NEXT
FROM @indexList INTO @ObjectName, @IndexName, @FragmentPercent
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt+1
--PRINT @Tname
BEGIN TRY
IF @FragmentPercent >= 30
BEGIN
PRINT 'REBUILDING INDEX '+@IndexName+' ON '+@ObjectName
EXEC('ALTER INDEX '+@IndexName+' ON '+@ObjectName+' REBUILD WITH (ONLINE = ON)')
END
ELSE IF @FragmentPercent >= 15
BEGIN
PRINT 'REORGANIZING INDEX '+@IndexName+' ON '+@ObjectName
EXEC('ALTER INDEX '+@IndexName+' ON '+@ObjectName+' REORGANIZE')
END
END TRY
BEGIN CATCH
PRINT 'IN CATCH :'+ERROR_MESSAGE()
SET @ListOfTablesNotRebult = @ObjectName+', '+@ListOfTablesNotRebuilt
End CATCH
FETCH NEXT
FROM @indexList INTO @ObjectName, @IndexName, @FragmentPercent
END
PRINT 'TOTAL INDXES REBUILT/REORG ARE '+CAST(@cnt AS VARCHAR)
PRINT 'The Tables Not Built '+@ListOfTablesNotRebult
CLOSE @indexList
DEALLOCATE @indexList
DROP TABLE #indexListTemp
January 15, 2013 at 11:27 am
What you have will work, however as most members of this forum will tell you, it's best to use what Ola Hallengren has already perfected: http://ola.hallengren.com It's proven, accepted, and it just works.
If you are invested in your code and want to keep using it, I'd recommend you add in a few checks to determine 1) whether or not you can rebuild the index online (indexes with specific xTypes cannot be built online, not can SQL Standard editions) 2) potentially check that you have the available space necessary to rebuild large indexes and 3) Consider putting in a MAXDOP =X . Of course in your code, these will be caught by the CATCH block...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 15, 2013 at 11:38 am
While there is Ola's script, there is also a widely accepted script by Michelle Ufford
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
This one is also widely accepted and works.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 15, 2013 at 11:46 am
Yeah the reason I put TRY/CATCH was so that I could catch those indexes that conatin LOB columns and cannot be done online and rebuild them manually during downtime.
I think the isuue with this script that I am concerned of is that it gives frgmention at each index level. So if an index with 3 levels being fragmented will it produce 3 rows and since the cursor will take in row by row will there be any dupllicate rebuild operation happen? Am I right in assuming that would happen. In my test env I did see any duplicates. But it my differ in any other env or some other time when fragmentaion increases. Please advice.
I hope I was clear with the issue I explained.
Thanx for the advice on the Ola Hallengren script. I will suerly look into it. The only prob is I would like to go with those scripts that I completly understand so that it will easy for trouble shooting.
January 15, 2013 at 12:09 pm
dedicatedtosql (1/15/2013)
Thanx for the advice on the Ola Hallengren script. I will suerly look into it. The only prob is I would like to go with those scripts that I completly understand so that it will easy for trouble shooting.
Check those scripts and evaluate them. Figure out what Michelle or Ola did in their scripts and understand those. They are tried and true scripts that work.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply