June 6, 2011 at 1:04 am
I got the script below not long ago and I've rebuilt the indexes with avg_defrag > 30 for all the tables in one database.
After the execution, I try to run the same script and checked for avg_defrag > 30 again and it shows me 95% of the tables still satisfy the condition.
Please help me determine what I did wrong?
I'm running this script in SQL 2008.
I have a maintenance plan to rebuild the index but it's always failing so I tried the script below.
SET NOCOUNT ON;
declare @qry nvarchar(MAX)
DECLARE cur_index CURSOR FOR
SELECT 'ALTER INDEX ' + i.name + ' ON ' + quotename(s.name) + '.' + quotename(object_name(i.object_id)) + ' REBUILD WITH (FILLFACTOR = 90) '
from sys.dm_db_index_physical_stats (DEFAULT, NULL, NULL, NULL, DEFAULT) dmdbips
inner join sys.indexes i
on dmdbips.object_id = i.object_id
and dmdbips.index_id = i.index_id
join
sys.objects o
on o.object_id = i.object_id
join
sys.schemas s
on s.schema_id = o.schema_id
where avg_fragmentation_in_percent > 30
AND i.name is not null
OPEN cur_index
FETCH NEXT FROM cur_index INTO @qry
WHILE @@FETCH_STATUS = 0
BEGIN
--print @qry
exec sp_executesql @qry
FETCH NEXT FROM cur_index INTO @qry
END
CLOSE cur_index
DEALLOCATE cur_index
Thank you in advance!
June 6, 2011 at 1:20 am
What are your index_id and page_count values?
They might be heaps or cointains very few pages. You could look for a better script that ignores heaps and small tables.
__________________________
Allzu viel ist ungesund...
June 6, 2011 at 1:32 am
Hi check the script added in my signature.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 6, 2011 at 1:38 am
Also try running dm_db_index_physical_stats with the detailed mode, it takes longer but is a more accurate way of verifying your reindex script.
sys.dm_db_index_physical_stats (DEFAULT, NULL, NULL, NULL, 'DETAILED')
To agree and expand on Mr Holio's points, try adding the the following to your WHERE clause:
AND dmdbips.index_type_desc != 'HEAP'
AND dmdbips.page_count > 500
rebuilding the a clustered index will also rebuild any non-clustered indexes on that table. Edit: Not true, see below
June 6, 2011 at 1:56 am
Jason L (6/6/2011)
rebuilding the a clustered index will also rebuild any non-clustered indexes on that table.
It does not.
Rebuilding the clustered index rebuilds just the clustered index. To rebuild all indexes on a table you need to do
ALTER INDEX ALL ON <Table Name> REBUILD
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 2:07 am
GilaMonster (6/6/2011)
Jason L (6/6/2011)
rebuilding the a clustered index will also rebuild any non-clustered indexes on that table.It does not.
Rebuilding the clustered index rebuilds just the clustered index. To rebuild all indexes on a table you need to do
ALTER INDEX ALL ON <Table Name> REBUILD
To specify...Quotes from
[Edited as per Gail's suggestion]
June 6, 2011 at 2:10 am
sqlzealot-81 (6/6/2011)
To specify...Quotes from "Paul"
May I suggest you cut that quote down to just the portion needed and reference it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 2:31 am
Rebuilding a unique clustered index: This is the same as SQL Server 2000 - the cluster keys aren't changing and so the non-clustered indexes are not rebuilt.
Rebuilding a non-unique clustered index: Aha! This is different from SQL Server 2000. SQL Server 2005 will RE-USE the old uniquifier values so the cluster keys don't change. This means that non-clustered indexes are NOT rebuilt in this case - that's very cool!
So it is 🙂
June 6, 2011 at 5:56 pm
Thank you all for your quick reply.
I included this in my script - ALTER INDEX ALL ON <table> REBUILD WITH (FILLFACTOR = 80).
I have also modified the script to be - sys.dm_db_index_physical_stats (DEFAULT, NULL, NULL, NULL, 'DETAILED')
and added this in the where clause - AND dmdbips.index_type_desc != 'HEAP'
The rebuild ran for 2hrs 5mins which is more or less the same time as the maintenance plan completion time when it was still running successfully.
My question now is when I ran the same script to check whether there are still remaining tables with avg_fragmentation_in_percent > 30, I still got 423 rows.
When I've checked the page_count, I got a maximum of 48.
Is this alright?
Thanks.
June 6, 2011 at 6:40 pm
A page count of 48 is too small to worry about. From my own testing small indexes don't appear to defragment that well, or it could be that with small indexes the fragmentation calculations are not as reliable.
Did a search on page count and only found Tibor Karaszi suggesting a page count of 500 - 1000, I guess it depends on the tables and index contents as to what cut off is ideal, but 48 is too small to worry about.
Edit: forgot to add, when you changed the script to rebuild all did you also add a group by so that you aren't defragging the same indexes more than once? e.g: GROUP BY s.name, dmdbips.object_id
June 6, 2011 at 6:51 pm
Ok, that's a relief 😀
Yes I did add the group by.
Thank you for your help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply