jeudi 26 février 2015

statistics are up to date, but estimate is incorrect


When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698:


enter image description here


For this query:



SELECT * FROM Reports_Documents WHERE ReportID = 18698 option(recompile)


I get a query plan that makes a Clustered Index Seek on PK_Reports_Documents as expected. But what baffles me is the incorrect value for Estimated Number of Rows:


enter image description here


According to this:



When the sample query WHERE clause value is equal to a histogram RANGE_HI_KEY value, SQL Server will use the EQ_ROWS column in the histogram to determine the number of rows that are equal to



This is also the way I would expect it to be, however it seems not to be the case in real life. I also tried some other RANGE_HI_KEY values that were present in the histogram provided by show_statistics and experienced the same. This issue in my case seems to cause some queries to very unoptimal execution plans resulting in an execution time of a few minutes whereas I can get it to run in 1 sec with a query hint.


All in all: Can someone explain me why is EQ_ROWS from the histogram not being used for the Estimated Number of Rows and where does the incorrect estimate come from?


A bit more (possibly helpful) info: * Auto create statistics is on and all the statistics are up to date. * The table being queried has about 80 million rows. The table I am querying has about 80 million rows, auto statistics is





Aucun commentaire:

Enregistrer un commentaire