Sql-server – Is having Estimated number of Rows > Actual Number of Rows an issue to worry about

execution-planindexperformancesql servert-sql

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.

enter image description here

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.

Best Answer

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:

Seek predicates

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

Related Question