Database optimization - the missing step

Does your intelligence or your magical database optimization scripts really optimize the database? The traditional wisdom is to optimize indexes and statistics, but how about optimizing a HEAP (a table without clustered index) by removing fragmentation in your database?

Database optimization, more precisely Index optimization is one of the major tasks every DBA performs on a regular basis regardless the size of the database. Based on daily data changes and fragmentation threshold, DBA decides to optimize indexes such as REBUILD, RE-ORGANIZE.

Free automated script:

A lot of automated index optimization scripts are freely available to help improve your database performance. But if you carefully review these index optimization statements, you will discover that those scripts cautiously avoid optimizing HEAP. If so, then your database is not fully optimized.

Generally, if a HEAP table is part of the OLTP system, then it is highly recommended that it should be clustered. However, there are business logic, reasons and mistakes as well to not create clustered Index on a table and keep the table as a HEAP. As a result, executing queries against these tables become very resource intensive.

Starting form SQL 2008, a HEAP can be optimized as well with the “ALTER TABLE <xxxxx> REBUILD” option. Therefore, to optimize a HEAP we no longer need to create a clustered index and dropping it afterward.

Key points to remember:

1.      If the HEAP contains non-clustered indexes, all indexes will be rebuilt (dropped and recreated).

2.      Fill Factor can’t be set on a HEAP.

3.      All or a specific partition can be optimized.

4.      Data compression (PAGE or ROW level) can be set or changed.

Script to check HEAP fragmentation:

select  o.name ,

        ips.index_type_desc ,

        ips.avg_fragmentation_in_percent ,

        ips.record_count ,

        ips.page_count ,

        ips.compressed_page_count

from    sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') ips

        join sys.objects o on o.object_id = ips.object_id

where   ips.index_id = 0

        and ips.avg_fragmentation_in_percent > 0

order by ips.avg_fragmentation_in_percent desc;

T-SQL Statement example to optimize HEAP:

ALTER TABLE tblLarge REBUILD

ALTER TABLE tblLarge REBUILD with (MAXDOP = 4)

ALTER TABLE tblLarge REBUILD WITH (DATA_COMPRESSION = PAGE)

ALTER TABLE tblLarge REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE)

ALTER TABLE tblLarge REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) )

Comments:

You should not rely on only index optimization; make sure that you are taking care of HEAP as well. In my experience, I have seen many DBAs - even SQL experts – overlooking this area.

Learn More:

A SQL Server DBA myth a day: (29/30) fixing heap fragmentation

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/

ALTER TABLE (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.100).aspx