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.
Edit 26/01/2017
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.
Edit 27/01/2017
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
>=
and<
rather than>
and<=
? – Aaron Bertrand Jan 25 '17 at 16:12