PRB: Poor Performance on a Heap

Article translations Article translations
Article ID: 297861 - View products that this article applies to.
This article was previously published under Q297861
Expand all | Collapse all

On This Page

SYMPTOMS

One or more of the following symptoms may accompany poor performance during inserts to a large table on SQL Server:
  • Unexplained high CPU usage by SQL Server, sometimes up to 100%.

  • SQL Profiler or SHOWPLAN indicates that singleton inserts have wide variations in performance.

  • The number of reads is out of proportion to the number or size of the indexes and triggers on the table.

  • Sporadic timeouts.

  • The FreeSpace Scans/Sec counter from the SQL Server:Access Methods object in Performance Monitor is excessively high given all the factors involved in your specific environment.

CAUSE

A common cause for these symptoms is that inserts to a heap (a table without a clustered index) are often slower than inserts to a table with a clustered index (a clustered table).

WORKAROUND

This article outlines some reasons why a clustered index can often improve performance in this type of situation. Microsoft recommends that you add a clustered index to the table and test the effect of the clustered index on performance.

MORE INFORMATION

In SQL Server 7.0, and later, SQL Server generally optimizes inserts into a heap with the assumption that saving space is more important than performance. That is the tradeoff you choose to make when you decide to leave a table as a heap. Therefore, an insert into a heap often spends time searching for a location to insert a new row. On the other hand, an insert to a clustered table does not have to spend time searching for space. For more details about how the space is allocated, see the "Managing Space Used by Objects" topic in SQL Server Books Online.

In SQL Server 2000, there are more variations of situations where you can better optimize an insert to a heap than there are in SQL Server 7.0. With any high insert environment, it is best to test your particular scenario both with and without a clustered index to determine which is best for your situation.

The considerations of which columns to include in your clustered index are too expansive to include here. SQL Server Books Online, "Inside Microsoft SQL Server 7.0", and "Inside Microsoft SQL Server 2000" are very valuable resources that will help you decide which columns to include in the clustered index for your environment. However, one of the most important factors is that the first column of your clustered key must be as selective as possible. If there are few distinct values in the key column then many more reads are necessary for each query.

REFERENCES

SQL Server Books Online

Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999.

Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000.

Properties

Article ID: 297861 - Last Review: October 16, 2003 - Revision: 3.4
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB297861

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com