Tuesday, May 24, 2011

Heaps of Heaps

Recently, I was looking into a stored procedure that was running poorly.  As part of my analysis, I examined the underlying tables and their indexes.  By using Kimberly Tripp's (blog | twitter) rewrite of  sp_help, I was able to quickly determine that one of the tables was a heap.

Heaps


A Heap is a table without a Clustered Index.  This is bad for a few reasons:

  • When you update a Heap, you can get forwarded records

  • When you insert to the Heap, an 8-byte uniquifier will be added

  • Can compromise insert performance

How Many Heaps in a Pile

Like most things in nature, when there is one, there is another, and another, and another...

Knowing this, I set about to discover how many other tables in this database were heaps.

First, let's look at our overall count of heaps in relation to the total table count.

[sql]

-- percentage of heaps
declare @heapCount numeric;
declare @tableCount numeric;

select @heapCount = count(t.object_id)
from sys.tables as t
join sys.indexes as i
on t.object_id = i.object_id
where i.type_desc = 'HEAP';

select @tableCount = COUNT(*) from sys.tables;

select @heapCount as 'Heap Count',
@tableCount as 'Table Count',
CAST((@heapCount / @tableCount * 100) as numeric(5, 2)) as 'Percentage of Heaps';
go

[/sql]

Next, let's enumerate all the tables that are heaps.

[sql]

-- list all the heaps in the currently selected database
select OBJECT_NAME(t.object_id) as 'Table Name', i.type_desc
from sys.tables as t
join sys.indexes as i
on t.object_id = i.object_id
where i.type_desc = 'HEAP'
order by OBJECT_NAME(t.object_id);
go

[/sql]

Time to Make the Clusters

What I found was startling to say the least.  On this particular server, all three databases contain quite a few heaps.

Heaps of Heaps

Obviously, this is going to be where I start my tuning efforts.  After seeing how many heaps existed on a strange database, I think it would be wise to make this a standard check when first looking at any unfamiliar database.

3 comments:

  1. [...] In a previous post, I discussed how to obtain the overall Heap count and percentage, as well as the listing of the individual Heaps. [...]

    ReplyDelete
  2. [...] In a previous post, I discussed how to obtain the overall Heap count and percentage, as well as the listing of the individual Heaps. [...]

    ReplyDelete
  3. [...] catches my eye in this example, aside from all the heaps, are any clustered indexes that are not in the data filegroup, and any non-clustered indexes that [...]

    ReplyDelete