December 3, 2013 at 11:16 am
I Rebuilt Indexes on a Database with the Maintenance Plan and specified free up with the default amount of free space.
After the re-index I have 18 tables that are fragments from 66.66666667 to 90 %?
What can I do to defrag the indexes?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2013 at 12:24 pm
you can use a script like the one below to just do rebuilds setting it back to 100 if that's your goal.
SELECT object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count, partition_number, i.index_id,
'ALTER INDEX [' + i.name + '] ON [' + Schema_Name(o.schema_id) + '].[' + object_Name(i.object_id) +
'] REBUILD WITH (FILLFACTOR = 100, ONLINE = ON)' as Rebuild_Script
--You can remove the fillfactor option if you wish to use the setting applied to index.
--online operations will only work with enterprise. This does not currently take into account partitions.
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id > 0 --This is in place to ignore Heap tables.
and fill_factor != 100
and fill_factor != 0
-- I would suggest a page count of 1000 for normal databases. Adjust to your environment.
and o.schema_id <> 4
you can use the one below to just do rebuilds anyways.
SELECT object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count, partition_number, i.index_id,
'ALTER INDEX [' + i.name + '] ON [' + Schema_Name(o.schema_id) + '].[' + object_Name(i.object_id) +
'] REBUILD WITH (FILLFACTOR = 100, ONLINE = ON)' as Rebuild_Script
--You can remove the fillfactor option if you wish to use the setting applied to index.
--online operations will only work with enterprise. This does not currently take into account partitions.
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id > 0 --This is in place to ignore Heap tables.
and avg_fragmentation_in_percent > 5 --feel free to change this to any other number. This number is low for real world use.
-- I would suggest a fragmentation of 30% just because this is for point in time use. This is not a replacement for a maintenance job.
and page_count > 20 --feel free to change this to any other number. This number is low for real world use.
-- I would suggest a page count of 1000 for normal databases. Adjust to your environment.
and o.schema_id <> 4
.
December 3, 2013 at 12:25 pm
December 4, 2013 at 6:43 am
How big are those tables? Tables that are less than a single extent in size won't defrag at all. Tables that are smallish, say 2-3 extents, may not defrag fully. But at those small sizes, who cares.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2013 at 6:54 am
The tables are small.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply