SQL Server Execution Plan – Actual Number of Rows Too High

execution-plansql serversql-server-2005

I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high whereas the estimated number of rows is less. And the query plan uses a nested loop. [Query plan diagram is given below.] Since these are new tables, I think the usual stale statistics is not the reason here.

I can rewrite this query by using NOT EXISTS (as shown in Query 2) and the actual number of rows is reduced. But I have other requirements to fetch details from LWTest table using INNER JOIN – there the high number of actual rows is a problem.

So, any clue why is the actual number of rows is so high even though there is index and statistics? What can be done to bring it down?

Note: Number of rows in TransmittedManifests –904. Number of rows in LWTest — 829785

UPDATE

Note2: Compatibility_Level is 90. Elapsed time for Query 1 is 64 ms. Query 2 elapsed time is 6 ms.

Note3: Tried OPTION (RECOMPILE), rebuild index and UPDATE STATISTICS WITH FULLSCAN on these two tables. But the actual number of rows is still high.

Thansk to Martin Smith for details of difference in count per execution (estimate) and total count (actual).The actual rows is count across all executions, the estimated is count per execution. Estimated rows, actual rows and execution count.

For Query 1, the ActualExecutions="904" for LWTest table.

Query

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON 
PRINT 'BEGIN -----------------------------------------'

--Query 1
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL

PRINT 'QUERY 1 COMPLETED -----------------------------------------'



--Query 2
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
                  WHERE L.Manifest = T.Manifest
                 )

PRINT 'QUERY 2 COMPLETED -----------------------------------------'


--Query 3
SELECT  T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest

Table Schema

enter image description here

Query Plan 1
enter image description here

Query Plan 2

enter image description here

Best Answer

The number of records in TransmittedManifests table is much less than the number of records in LWTest. In such scenario, good solution is to use NOT EXISTS approach (as in Query 2) to reduce the number of actual rows. Refer Joins without JOIN - Rob Farley

Now, Martin Smith’s comment helped me demystifying the count per execution (estimate) and total count (actual). For Query 1, the ActualExecutions="904" for LWTest table. So 128385/904 = 142 is the actual rows per execution which is somewhat close to the estimate 104.

In SQL Server 2005, the ActualExecutions can be seen by making a profiler trace adding performance events as shown in the diagram below. [In SQL Server 2012, I could see this information captured in execution plan diagram itself].

XML

enter image description here

SQL Server 2005 Trace Settings

enter image description here

Related Question