question

BenjaminShaw-2231 avatar image
0 Votes"
BenjaminShaw-2231 asked SeeyaXi-msft commented

Estimated rows and actual rows very different

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 :)

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.


0 Votes 0 ·

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)

0 Votes 0 ·

Hi @BenjaminShaw-2231,

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.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AddWebSolution-0525 avatar image
0 Votes"
AddWebSolution-0525 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft edited

Hi @BenjaminShaw-2231,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.