SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index Maintenance Plan with Default Amount of free space


Rebuild Index Maintenance Plan with Default Amount of free space

Author
Message
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26004 Visits: 4895
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/
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 599
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



.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 599
That being said, for normal maintenance you might want to look into this site.

.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95853 Visits: 33013
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26004 Visits: 4895
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search