I have an event table that is partitioned by month on EventCreatedDateTime and contains 1bn+ rows.
CREATE TABLE [dbo].[Events]( [EventtId] [bigint] IDENTITY(1,1) NOT NULL, [EventCreatedDateTime] [datetime] NOT NULL, ... ... [SystemCreatedDt] [datetime] NOT NULL, [SystemUpdatedDt] [datetime] NOT NULL, CONSTRAINT [PK_dbo_Events_EventCreatedDateTime_EventtId] PRIMARY KEY CLUSTERED ( [EventCreatedDateTime] ASC, [EventtId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_monthly_date]([EventCreatedDateTime]) ) ON [ps_monthly_date]([EventCreatedDateTime])
If I query the table using SystemCreatedDt or SystemUpdatedDt in the WHERE clause I can't seem to get any partition elimination and the query plan shows a clustered index scan of all 1bn rows, even if I create a partition aligned index on SystemCreatedDt or SystemUpdatedDt.
SELECT EventtId ,EventCreatedDateTime ... ... ,SystemCreatedDt ,SystemUpdatedDt FROM dbo.Events WHERE SystemCreatedDt > '2015-01-01 00:00:00' AND SystemCreatedDt <= '2015-01-02 00:00:00'
Struggling to tune this query and make use of the partitioning so I would be grateful of any help with index tuning a partitioned table.
Here is an anoymized plan for the query https://www.brentozar.com/pastetheplan/?id=HyvMM5wPe
To give some context to this Column1 = EventtId Column5 = EventCreatedDateTime Column18 = SystemCreatedDt Column20 = SystemUpdatedDt
I had to use SSMS 2014 to create a plan that I could paste into pastetheplan. One bit of info missing from this for the index scan is Estimated Number of rows to be read which is 1,173,200,000. There is a suggested missing index for all 20 columns in the query, but that wouldn't give us any partition elimination I don't think.
DDL for Index
CREATE NONCLUSTERED INDEX [IX_dbo_Events_SystemCreatedDt] ON [dbo].[Events] ( [SystemCreatedDt] ASC, )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_monthly_date]([EventCreatedDateTime]) GO