Skip to content

Fix for Reorder and Rebuild Does Not Defragment in SQL Server 2012 – Heap / Non-clustered Indexes

by on May 5, 2014

Rebuilding Index does not work properly for tables having no clustered indexes, referred to as “heap”s.Create a reorder task and it will fail, but reindex does not fail – it simply does nothing to the heap table, as it is doing what it can in the situation, and ends with the operation succeeding as far as it can.

NOTE: Assess if you can add appropriate clustered index, as this allows Seek rather than Scan of the entire table (scan is not logical and must search entire table, seek is logical and faster, pointing query to correct records faster).

In my case, it was a third party application, so the option of adding a clustered index was out until the company recommended and/or changed it.

  • No error, pages greater than 1000, fragmentation percent >99%
  • Run reorder maintenance task, fails, change settings for Plan to output verbose logs – may show locking tables, but changing the setting does not help
  • run rebuild maintenance task, succeeds, does not change table fragmentation or pages statistics

NOTE: Rebuild online may not work in Standard version

Solution:

Rebuild the table itself, since there is no clustered index to help reindex the table in a logical order set by the index – without doing this, heaps will not compress, etc. Rebuilding will rebuild all indexes on the table as well as heaps, while compressing the db! It is available in SQL Server 2008 and up.

NOTE: Rebuilding will grow your transaction log – it is recommended to to a Transaction Log backup for Full recovery model, and a Full backup for Simple recovery model databases after this query is run. Also, try it out in testing or development environments first!

Getting The Fragmentation Information:

NOTE: You can also just rebuild a specific table as needed with the below items. You may want to recompile the stored procedures afterward:

— rebuild an individual table

alter table dbo.mytablehere rebuild

–recompile stored procedures for a table

exec sp_recompile N’dbo.mytablehere’

Just Look At The Fragmentation Levels:

May be able to simplify with the following as select and using object_id number – this is directly from MS 

“The following example returns all statistics for all tables and indexes within the instance of SQL Server by specifying the wildcard NULL for all parameters. Executing this query requires the VIEW SERVER STATE permission.”

I have added a WHERE clause as is appropriate for our environment – you may want page count to be 100 – 1000.

SELECT avg_fragmentation_in_percent as AvgPercent, avg_fragment_size_in_pages as AvgSize, fragment_count as Fragments, *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
Where avg_fragmentation_in_percent >=30 and page_count >= 1000;
GO

Get The Fragmentation Details

SELECT

DB_NAME(database_id) as DBName

, OBJECT_SCHEMA_NAME(object_id, database_id)

+ N’.’

+ OBJECT_NAME(object_id, database_id) as ObjectName

, avg_fragmentation_in_percent as AvgFragPercent

, avg_fragment_size_in_pages as AvgFragSize

, fragment_count as Fragments

–uncomment below for additional info

–, *

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

Where

–change page count (100-1000), percent 20-50 as needed

avg_fragmentation_in_percent >=30 and

page_count >= 100

ORDER BY FRAG.avg_fragmentation_in_percent DESC

A more complex pull which was a starting point for my final query:

— set minimum page count the index should have

— to be considered for a rebuild.

— indexes smaller than 100, 120 pages usually don’t gain anything from rebuilding.

declare @IPageCnt int = 100;

— set the minimum fragmentation level of an index

— at which threshold it should be rebuild. (value in % of fragmentation)

declare @IMinFragmentation int = 30;

SELECT

OBJECT_SCHEMA_NAME(FRAG.[object_id]) + ‘.’ + OBJECT_NAME(FRAG.[object_id]) as [table]

,SIX.[name] as [index]

,FRAG.avg_fragmentation_in_percent as [fragmentation]

,FRAG.page_count as [pages]

,’alter index ‘ + SIX.[name]

+ ‘ on ‘+OBJECT_SCHEMA_NAME(FRAG.[object_id])

+ ‘.’ + OBJECT_NAME(FRAG.[object_id])

+ ‘ rebuild;’ as [rebuild_sql]

FROM

sys.dm_db_index_physical_stats

(

db_id(), –use the currently connected database

0, –Parameter for object_id.

DEFAULT, –Parameter for index_id.

0, –Parameter for partition_number.

DEFAULT –Scanning mode. Default to "LIMITED", which is good enough

) FRAG

INNER JOIN sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id]

AND FRAG.index_id = SIX.index_id

WHERE

— consider only those indexes that need treatment

FRAG.page_count > @IPageCnt

AND FRAG.avg_fragmentation_in_percent > @IMinFragmentation

ORDER BY

FRAG.avg_fragmentation_in_percent DESC

–next run the lines below for each table you want to rebuild

–alter table dbo.mytablehere rebuild

–exec sp_recompile N’dbo.mytablehere’

Fragmentation Fix SQL Final

declare @tablename varchar(100);

declare @dbname varchar(100);

declare @sqlstring varchar(100);

declare tablefragcursor cursor for

(

SELECT

DB_NAME(database_id) as DBName

, OBJECT_SCHEMA_NAME(object_id, database_id)

+ N’.’

+ OBJECT_NAME(object_id, database_id) as ObjectName

–uncomment  below for testing

–, avg_fragmentation_in_percent as AvgFragPercent

–, avg_fragment_size_in_pages as AvgFragSize

–, fragment_count as Fragments

–, *

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

–change page count (100-1000), percent 20-50 as needed

Where

avg_fragmentation_in_percent >=30 and

page_count >= 100

–cannot order here – selection purpost only, uncomment for testing

–ORDER BY FRAG.avg_fragmentation_in_percent DESC

)

Open tablefragcursor;

fetch next from tablefragcursor

into @dbname, @tablename

while @@fetch_status = 0

BEGIN

–Print db_id();

Print ‘Rebuilding table…’+ @dbname +’.’+@tablename;

set @sqlstring = ‘alter table ‘+ @dbname +’.’+@tablename +’ rebuild;’

exec (@sqlstring)

–Print @sqlstring

set @sqlstring = ‘use ‘+@dbname +’; exec sp_recompile ”’+@tablename+””;

–print @sqlstring

exec (@sqlstring)

fetch next from tablefragcursor

into @dbname, @tablename;

End;

Close tablefragcursor;

Deallocate tablefragcursor;

GO

Print ‘Complete’;

 

Read More:

http://www.patrickkeisler.com/2013/03/dealing-with-fragmented-heap.html 

http://blog.heinozunzer.com/rebuilding-fragmented-indexes-and-tables/

Returning information for all databases ( example c – scroll down quite a bit)

http://technet.microsoft.com/en-us/library/ms188917.aspx

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: