Microsoft KB Archive/308822

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

Article ID: 308822

Article Last Modified on 11/29/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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.

Additional query words: selectivity cardinality

Keywords: kbbug kbpending KB308822

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.