Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index Maintenance Plan with Default Amount of free space Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 11:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 4,234, Visits: 4,286
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/

Post #1519333
Posted Tuesday, December 3, 2013 12:24 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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




.
Post #1519365
Posted Tuesday, December 3, 2013 12:25 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
That being said, for normal maintenance you might want to look into this site.

.
Post #1519366
Posted Wednesday, December 4, 2013 6:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:46 AM
Points: 13,752, Visits: 28,148
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1519603
Posted Wednesday, December 4, 2013 6:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 4,234, Visits: 4,286
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/

Post #1519612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse