December 15, 2011 at 9:26 am
Hi,
I've been tasked with investigating an application's performance issues (Autonomy Worksite to be precise).
One thing I have noticed is that we have not been running any maintenance schedules (!) for the databases...
I have run a basic script to bring back the fragmentation information on the indexes accross the databases...it was bad reading...many of the indexes were 100% fragmented.
So, I went to task on a rebuild script - I didnt have to do much as there is so much out there already on this.
I wanted a simple process for rebuilding the badly fragmented indexes (over 30%) on any given DB. What I came up with was this sproc...
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'sp_FragmentedIndexRebuild'
)
DROP PROCEDURE dbo.sp_FragmentedIndexRebuild
GO
CREATE PROCEDURE dbo.sp_FragmentedIndexRebuild @dbname varchar(400)
AS
/*
FragmentedIndexRebuild SPROC - pass in a database name and all of the indexes on all tables which are above 5% fragmented
will be rebuilt.
*/
-- remove temporary table if it already exists
IF EXISTS(
SELECT * FROM tempdb.dbo.sysobjects o
WHERE o.xtype in ('U')
AND o.id = object_id(N'tempdb..#IndexRebuilds')
)
DROP TABLE #IndexRebuilds
-- populate a temporary table with details of the indexes which need to be rebuilt on the database (where fragmentation is greater than 5%)
SELECT @dbname+'.'+OBJECT_SCHEMA_NAME(IPS.OBJECT_ID) + '.' + OBJECT_NAME(IPS.OBJECT_ID) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count
INTO #IndexRebuilds
FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), NULL, NULL, NULL , 'DETAILED') 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 avg_fragmentation_in_percent > 30 AND SI.name IS NOT NULL
ORDER BY 1,5
SELECT * FROM #IndexRebuilds
-- declare the variables to store the SQL, table and index names
DECLARE @SQL VARCHAR(MAX)
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
-- declare the cursor which will iterate through the temp table
DECLARE csr_index CURSOR FOR
SELECT DISTINCT TableName
FROM #IndexRebuilds
ORDER BY TableName
-- open the ursor and fetch first row
OPEN csr_index
-- bring back the first row from the cursor and populate the table and index variables
FETCH NEXT FROM csr_index INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT 'Rebuilding Indexes on Table '+@tablename+' ...'
-- populate the SQL variable and execute
SET @SQL = 'ALTER INDEX ALL ON ' +@tablename+ ' REBUILD;'
PRINT @SQL
EXEC (@SQL)
PRINT 'Indexes on Table '+@tablename+' have been rebuilt successfully.'
--SET @SQL = N'UPDATE STATISTICS '+ @tablename + ' WITH SAMPLE 100 PERCENT;'
--PRINT @SQL
--EXEC (@SQL)
END TRY
BEGIN CATCH
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = '###Index Rebuild Failure### : ' + ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
FETCH NEXT FROM csr_index INTO @tablename
END
-- close and de-al cursor
CLOSE csr_index
DEALLOCATE csr_index
-- drop temp table
DROP TABLE #IndexRebuilds
GO
The weird thing is...when I run this and then check the statistics again, the indexes are STILL fragmented. Am I missing something crucial here or simply not understanding the functions involved?
Many Thanks,
D
December 15, 2011 at 9:29 am
Best script around for that task => http://sqlfool.com/2011/06/index-defrag-script-v4-1
Smaller indexes can't be defragged. 1000 pages is the "common" treshold where it start to matter.
Anything under 8 pages just won't defrag.
December 15, 2011 at 9:34 am
Thanks for the prompt reply - I've checked out the page counts on each and they seem really low (12 or less) so I'm guessing that could be it.
Yes! I love that script, I came accross it in my investigations too and thought it was great - just wanted to keep things simple while I tested and because I was confused about the results....
So basically, I think my sproc works on the high-page indexes but for the rest it is just having no effect. Would you advise skipping the indexes with low page counts (i.e. putting a clause in to say WHERE page_count > 1000)?
Thanks again for your fast response.
D
December 15, 2011 at 9:37 am
What are the page counts on the indexes that don't defrag? (http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-2-size-matters/)
Edit: Trumped while looking for the reference!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 9:39 am
david.searle (12/15/2011)
Thanks for the prompt reply - I've checked out the page counts on each and they seem really low (12 or less) so I'm guessing that could be it.Yes! I love that script, I came accross it in my investigations too and thought it was great - just wanted to keep things simple while I tested and because I was confused about the results....
So basically, I think my sproc works on the high-page indexes but for the rest it is just having no effect. Would you advise skipping the indexes with low page counts (i.e. putting a clause in to say WHERE page_count > 1000)?
Thanks again for your fast response.
D
That proc is in production on 1000s of servers. No risk in using it aside form the extra load on the disks which you need to gage anyways with your own scripts.
December 15, 2011 at 9:39 am
I always skip the ones with low page counts, and usually skip the ones that don't have any range scans. Fragmentation doesn't really impact single-row seek performance, after all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 9:45 am
Understood - I think this is exactly what I am experiencing.
The avg fragmentation on some of these indexes is 100% however they have a page count of 2 so as I understand from your comments and from the article you linked to, rebuilding this is going to make jack-all difference.
Is this the same when re-organizing?
Thanks,
D
December 15, 2011 at 9:47 am
david.searle (12/15/2011)
Understood - I think this is exactly what I am experiencing.The avg fragmentation on some of these indexes is 100% however they have a page count of 2 so as I understand from your comments and from the article you linked to, rebuilding this is going to make jack-all difference.
Is this the same when re-organizing?
Thanks,
D
What hapenned when you tried ;-)?
December 15, 2011 at 9:52 am
😀 Hehe, ok-ok, so I was being lazy - I will try, I have a similar sproc which does the re-organzing although I am likely going to use the FOOL one as it seems to be pretty popular.
Thanks again for your help.
D
December 16, 2011 at 1:13 pm
P.s.
I spotted a mistake in my original code too...
I am joining on the sys.tables and sys.indexes tables when pulling back the index data - this doesn't work because it pulls back from the database you are in when running the script. I've since fixed this.
Fool's script is great but a little involved so I've trimmed it to my needs - still brilliant stuff though.
December 17, 2011 at 5:59 am
david.searle (12/16/2011)
P.s.I spotted a mistake in my original code too...
I am joining on the sys.tables and sys.indexes tables when pulling back the index data - this doesn't work because it pulls back from the database you are in when running the script. I've since fixed this.
Fool's script is great but a little involved so I've trimmed it to my needs - still brilliant stuff though.
I've read it a few times, and I can't say that I found a single line of useless code in there.
I wouldn't play with it unless I was 100% sure I knew all the side effects.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply