Advertisements

How to solve the issue caused by a poor estimate of cardinality in SQL Server.

Details:
The table statistics indicate that the tables involved have very few rows, but in reality, the row count is much higher.

Solution:
Updating statistics for the tables involved will improve performance.

Rebuild Table Statistics:

-- Execute the following query to rebuild the statistics held for the 
-- Proseware.Campaign and Proseware.CampaignResponse tables.
ALTER TABLE Proseware.Campaign REBUILD
GO
ALTER TABLE Proseware.CampaignResponse REBUILD;
GO

Lets re-run the query and take the execution plan.

Compare the Execution Plan:

Note that the estimated and actual row counts now match almost exactly. The query will execute faster than it did previously. The execution plan includes a suggestion for an index that would further improve query performance.

Advertisements