FIX: Cardinality estimates may be incorrect for a table created and populated in the same transaction (308822)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 7.0

This article was previously published under Q308822

BUG #: 355019 (SHILOH_BUGS)
BUG #: 101947 (SQLBUG_70)

SYMPTOMS

If you create a table in a transaction and the same transaction then populates the table, the cardinality estimates assume that the table still has zero rows. This affects the performance of any query that you execute against this table in the same transaction.

For example, you might create and populate a table with millions of rows of data, but the optimizer still considers the table empty. Subsequent queries in the same transaction that use the table may estimate the return of an incorrect number of rows, which leads to poor query performance.

CAUSE

SQL Server does not update the rowcnt column of the sysindexes system table until the transaction is committed.

RESOLUTION

To resolve this problem, follow these steps:

Obtain the latest service pack for SQL Server 2000. For information on how to obtain the latest SQL Server 2000 Service Pack, see the following article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
Turn on trace flag 3913.
Execute complex queries that will not use trivial plans. For trivial plans, the cardinality estimate is still 1.0 for a scan of such a table.

Note When you turn on trace flag 3913, the optimizer gets row count information from in-memory metadata that is saved to sysindexes system table when the transaction commits.

WORKAROUND

To work around the problem, commit the transaction and then query the table.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

The following script demonstrates that a table created and populated in the same transaction estimates that the table has zero rows. Any query in that transaction estimates the return of an incorrect number of rows.

begin tran
CREATE TABLE #t
   (
            error      int NOT NULL,
            severity smallint NOT NULL,
            description nvarchar(510)
            PRIMARY KEY CLUSTERED (error)
   )

insert into #t (error, severity,  description) select error, severity, description from master.dbo.sysmessages
 
select rows as 'Number of Rows being seen by the optimizer' from tempdb.dbo.sysindexes where id = object_id('#t')
select count(*) as 'Actual rows in table' from #t

--This query estimates the return of an incorrect number of rows:
select * from #t join master.dbo.sysmessages m on #t.error = m.error order by #t.error

rollback tran

NOTE: The actual rows returned are correct and as expected; only the estimated rows used to prepare the execution plan are incorrect.

Modification Type:	Major	Last Reviewed:	11/29/2004
Keywords:	kbbug kbpending KB308822