Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

Question

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way.

Generally Estimated Number of Rows skews as compared to Actual by in this scenario its totally ironic. Moreover "Number Of Execution" is 1.

Query :

SELECT 
ord.paymentmode AS [HowTheyPaid],
ord.id AS [OrderId],
ord.ISBackEndSystemMigrated,
ord.ISDuplicate,
ord.ISMerged
FROM 

DedicatedDentalPlans_Stage.dbo.[order] ord
WHERE 
ISBackEndSystemMigrated=0 
AND ISDuplicate = 1

Table

USE [DedicatedDentalPlans_Stage]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Order_ISBackEnd]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF_Order_ISBackEnd]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISDuplica__382F5661]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISDuplica__382F5661]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISMerged__39237A9A]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISMerged__39237A9A]
END

GO



/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Order](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SubscriberId] [int] NOT NULL,
    [BillingAddressId] [int] NOT NULL,
    [ShippingAddressId] [int] NULL,
    [OrderStatusId] [int] NOT NULL,
    [ShippingStatusId] [int] NOT NULL,
    [PaymentStatusId] [int] NOT NULL,
    [PaymentMethodSystemName] [nvarchar](max) NULL,
    [OrderDiscount] [decimal](18, 4) NOT NULL,
    [OrderTotal] [decimal](18, 4) NOT NULL,
    [SystemUserId] [int] NULL,
    [UserType] [int] NULL,
    [PaymentMode] [varchar](20) NULL,
    [Deleted] [bit] NOT NULL,
    [AutomaticRenewal] [bit] NULL,
    [CreatedOnUtc] [datetime] NOT NULL,
    [ETLControlId] [int] NULL,
    [ETLGenerationId] [int] NULL,
    [ISBackEndSystemMigrated] [bit] NULL,
    [CheckNumber] [varchar](20) NULL,
    [ISDuplicate] [bit] NULL,
    [ISMerged] [bit] NULL,
 CONSTRAINT [PK__Order__3214EC0722AA2996] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_ISBackEnd]  DEFAULT ((0)) FOR [ISBackEndSystemMigrated]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISDuplicate]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISMerged]
GO

Solution Opted so Far with no result: Updated Statistics related to same index and clustered index with FULL SCAN.

Answer 1

In the particular scenario presented:

No, the difference between estimated and actual number of rows is not important. There are two key pieces of information to support that statement:

1. The query has qualified for a trivial plan; and
2. The query has further qualified for simple parameterization

This is clear from the Seek Predicate text, where the literal values for ISBackEndSystemMigrated and ISDuplicate have been replaced with the parameter markers @1 and @2:

This means that the plan selected is the same one SQL Server would choose for all possible literal values. Trivial plan reduces compilation time to a minimum for simple queries; simple parameterization promotes execution plan reuse.

In addition, the apparent cardinality mismatch is due to SQL Server producing an estimate for the average case (over all possible literal values).

In other cases, of course it can matter a great deal that cardinality estimates are inaccurate. The trick is knowing when it is important or not. There is no general safe rule of thumb here, much of it comes down to experience. Query tuning is not all science; there is room for art too :)

Answer 2

For a small difference no, it is nothing to worry about. It is only an estimate so not expected to be accurate in all cases.

If there is a large difference then the inaccuracy may have caused the query planner to pick a sub-optimal method of executing your query, so then there is a problem.

For some operations the estimate is made based on the statistics help for relevant indexes and these do occasionally become inaccurate and need rebuilding. You can see the stats held for an index using DBCC SHOW_STATISTICS, for example DBCC SHOW_STATISTICS ("Person.Person", PK_Person_BusinessEntityID);. I'll not go over the meaning of that output here as that would be OTT for this question - the documentation contains a chunk of detail and you'll find many useful references from a chick search if you want to delve deeper. In summary though: the two most common cases for stats getting out of sync with reality (and so reducing cardinality estimate accuracy) are when there have been many changes to the index columns or for indexes on always-incrementing values in very large tables (see http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys/ or one of many other references for more information on that). Neither of those will be an issue for the case you describe but the matter is worth understanding for future optimisation issues.

There are some operations where there are no stats available so the planner has to explicitly guess, for instance with the output of table-valued functions. There were changes in such cardinality estimates in SQL Server 2014 so a good way to find information about how the old and new methods work (and differ) search for that. https://www.youtube.com/watch?v=oPaCdznJN08 is a useful talk on the matter. Again, this will not be an issue for the specific case you describe but the matter is worth understanding for future optimisation issues.

Answer 3

The difference is purely based the cardinality of the data at the point when the stats were updated, and the modifications made prior to running your query.

SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.order'),2)

Execute the above right after you have updated the stats being used, and note the modification_counter value. It should be zero. Now run the original query, then the above again. The difference between the estimated rows minus the last modification_counter value (assuming you are not deleting rows), then you should have your answer. The stats aren't the same because your data has changed.