Optimization for Date Correlation doesn’t change plan

Question

I have a reporting requirement from the following tables. I created a new database with these tables and imported data from the live database for reporting purpose.

The report parameter is a date range. I read the following and found that DATE_CORRELATION_OPTIMIZATION can be used to make the query work faster by utilizing seek instead of scan. I made the required settings – still the query is using same old plan and same execution time. What additional changes need to be made to make the query utilize the date correlation?

Note: I am using SQL Server 2005

REFERENCES

1. Optimizing Queries That Access Correlated datetime Columns
2. The Query Optimizer: Date Correlation Optimisation

SQL

--Database change made for date correlation
ALTER DATABASE BISourcingTest
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

--Settings made
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

--Test Setting
IF (  (sessionproperty('ANSI_NULLS') = 1) AND
      (sessionproperty('ANSI_PADDING') = 1) AND 
      (sessionproperty('ANSI_WARNINGS') = 1) AND 
      (sessionproperty('ARITHABORT') = 1) AND 
      (sessionproperty('CONCAT_NULL_YIELDS_NULL') = 1) AND 
      (sessionproperty('QUOTED_IDENTIFIER') = 1) AND 
      (sessionproperty('NUMERIC_ROUNDABORT') = 0)  
    )
   PRINT 'Everything is set'
ELSE
   PRINT 'Different Setting'

--Query
SELECT C.ContainerID, C.CreatedOnDate,OLIC.OrderID
FROM ContainersTest C
INNER JOIN OrderLineItemContainers OLIC
    ON OLIC.ContainerID = C.ContainerID
WHERE C.CreatedOnDate > '1/1/2015'
AND C.CreatedOnDate < '2/01/2015'

TABLES

CREATE TABLE [dbo].[ContainersTest](
    [ContainerID] [varchar](20) NOT NULL,
    [Weight] [decimal](9, 2) NOT NULL DEFAULT ((0)),
    [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [XPKContainersTest] PRIMARY KEY CLUSTERED 
(
    [CreatedOnDate] ASC,
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[OrderLineItemContainers](
    [OrderID] [int] NOT NULL,
    [LineItemID] [int] NOT NULL,
    [ContainerID] [varchar](20) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_POLineItemContainers] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [LineItemID] ASC,
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_OrderLineItemContainers] UNIQUE NONCLUSTERED 
(
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderLineItemContainers]  WITH CHECK ADD  CONSTRAINT [FK_POLineItemContainers_Containers] FOREIGN KEY([ContainerID])
REFERENCES [dbo].[Containers] ([ContainerID])
GO
ALTER TABLE [dbo].[OrderLineItemContainers] CHECK CONSTRAINT [FK_POLineItemContainers_Containers]

Plan

--

Answer 1

According to the docs: https://technet.microsoft.com/en-us/library/ms177416(v=sql.105).aspx

If any one of the datetime columns for which correlation statistics are maintained is not the first or only key of a clustered index, consider creating a clustered index on it. Doing this generally leads to better performance on the types of queries covered by correlation statistics. If a clustered index already exists on the primary key columns, you can modify a table so that the clustered index and primary key use different column sets.

Since your OrderLineItemContainers table has no suitable index by which to filter on the Date, it really can't do anything. Try adding a nonclustered index on the OrderLineItemContainers.CreatedOnDate to see if it will then switch the plan.

It would be better to have it be clustered, but there are other considerations... note you could make the primary key nonclustered, and use the clustered for this new date index if this is the dominant query and this makes it worth it.

So this is optimal:

CREATE TABLE [dbo].[OrderLineItemContainers](
      [OrderID] [int] NOT NULL,
      [LineItemID] [int] NOT NULL,
      [ContainerID] [varchar](20) NOT NULL,
      [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
   CONSTRAINT [PK_POLineItemContainers] PRIMARY KEY NONCLUSTERED -- NONCLUSTERED PRIMARY KEY!!
        (
            [OrderID] ASC,
            [LineItemID] ASC,
            [ContainerID] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
         CONSTRAINT [IX_OrderLineItemContainers] UNIQUE NONCLUSTERED 
        (
            [ContainerID] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]

CREATE CLUSTERED INDEX ON OrderLineItemContainers(CreatedOnDate)

OR you could just try a new NONCLUSTERED index:

CREATE NONCLUSTERED INDEX ON OrderLineItemContainers(CreatedOnDate)