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

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

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
ALTER TABLE Proseware.CampaignResponse REBUILD;

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.