Article ID: 297861
Article Last Modified on 10/16/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q297861
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.
Keywords: kbprb KB297861
