4,707 questions
Cause can be
- Parameter sniffing: Is that a parameterized query or stored procedure?
- Outdate statistics => update them with
UPDATE STATISTICS dbo.yourTable WITH FULLSCAN - Bad cached query plan; use WITH RECOMPILE
Estimated rows and actual rows very different
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(179.20000000000002, 81%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Benjamin Shaw
141
Reputation points
Hi,
I have a table with a clustered index.
The query plans says the estimated number of rows from this table is 3574489 and the actual is 3460.
There are no missing statistics. I cannot understand why the estimated and actual are so different.
Does anyone have any ideas.
Thanks :)
Developer technologies | Transact-SQL
SQL Server | Other
14,499 questions
{count} votes
-
Dan Guzman 9,406 Reputation points2021-06-21T12:31:39.043+00:00 Upload the actual execution plan to Paste The Plan and add the link to your question. We can make some educated guesses as to why the estimate versus actual is so far off but the plan might reveal additional details to better answer your question.
-
Ronen Ariely 15,206 Reputation points2021-06-21T12:38:25.693+00:00 Hi,
As Dan said, we need to get the actual Execution Plan and we need the full XML information and NOT a screenshot of the graphical GUI which SSMS presents.
In addition please provide the DDL related to the table (queries to create the exact same table structure and the related entities)
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Seeya Xi-MSFT 16,586 Reputation points2021-06-25T07:01:28.123+00:00 Hi @Benjamin Shaw ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Sign in to comment
3 answers
Sort by: Most helpful
-
Olaf Helper 47,441 Reputation points2021-06-21T10:51:43.05+00:00 -
AddWebSolution 171 Reputation points2021-06-21T11:17:44.277+00:00 The impact is on algorithms and resource allocation for the query. SQL Server has multiple algorithms that it can use for things like JOINs and GROUP BYs. The (estimated) size of the data is one of the primary items of information that it uses to choose the appropriate algorithm.
-
Seeya Xi-MSFT 16,586 Reputation points
2021-06-22T05:46:17.867+00:00 Hi @Benjamin Shaw ,
I cannot understand why the estimated and actual are so different.
The execution plan is always estimated with the latest statistics.
The measures that can be taken are as follows:
1.Rebuild the cluster index
2.Update statistics- Update all statistics of the specified table:
update statistics [tableName]
- Update the statistics of a single index of the specified table:update statistics [tableName] [indexName]
- Perform a full scan of the table and update statistics:update statistics [tableName (columnName)] with fullscan
3.If the above method does not work, you can try Option recompile.Please refer to this: https://dba.stackexchange.com/questions/160076/wrong-estimated-number-of-rows
Best regards,
Seeya
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.