4

I wonder why I have actual number of rows greater than estimated number of rows?

The table has a clustered primary key defined as :

CONSTRAINT [PK_AIRQUALITYTS] PRIMARY KEY CLUSTERED 
(
 [FeatureID] ASC,
 [ParameterID] ASC,
 [MeasurementDateTime] DESC
)

enter image description here

Though I have updated STATISTICS on MeasurementDateTime column and rebuild index also.

Questions:

  • Why actual number of rows greater than estimated number of rows? And does it have any performance hit?

  • Should I always try to get actual number of rows equal estimated number of rows? Or how much variations in count of rows in actual and estimated should not bother us?

TT.
  • 15,774
  • 6
  • 47
  • 88
user641812
  • 335
  • 5
  • 19
  • 1
    what script did you used to update your statistic? did you uses WITH FULLSCAN? – d.popov Oct 31 '16 at 09:48
  • the answer is in your question , one of the figures is an estimate - the estimate is based on statistical sampling, statistics can become out of date, but that isn't the only reason the estimate can be out - you could try updating statistics with UPDATE STATISTICS as an experiment.
     – Cato Oct 31 '16 at 10:11

2 Answers2

0

Q1: It depends on how many rows in total there are in this table. Because that's how the SQL decides what operations to use while building it's execution plan. AFIK, the SQL query optimizer will decide to use SCAN over SEEK operation if it estimates around 1/3-rd or more of the table/index will have to be retrieved [cardinality estimate question on SO]

So the short question is: It depends on the specific case.

Q2: The general rule is to try to optimize only queries that you know have performance problems.

Community
  • 1
  • 1
d.popov
  • 4,175
  • 1
  • 36
  • 47
  • 30%... I think it's more like 2 - 3%. – TT. Oct 31 '16 at 09:59
  • Useful information in this article on sqlmag.com: [**The Tipping Point** - Understanding how SQL Server's query optimizer makes decisions](http://sqlmag.com/database-administration/tipping-point) – TT. Oct 31 '16 at 10:07
  • @TT., check also this one: http://stackoverflow.com/questions/4579358/at-what-cardinality-does-sql-server-switch-to-an-index-scan-vs-seek – d.popov Oct 31 '16 at 11:48
-3

Two main reasons:

  1. Statistics out of date
  2. Bad cached plan being used

With regards to 2, this is often due to parameter sniffing issues which is in turn due to certain types of queries.

We don't know what your workload is - query or stored procedure.

There are many ways to work out if you are using a stale plan, and many ways to flush it out and they are all on google.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91