1

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
  • Can you post the actual execution plan on PasteThePlan.com and then add the link to your question? – Brent Ozar Jan 25 '17 at 16:09
  • Also is the beginning of a month really in the other partition? Is your partition defined as RANGE LEFT or RANGE RIGHT? Would seem odd for January 1st at midnight to be in one partition and January first at 00:00:00:003 to be in a different partition. Perhaps you meant >= and < rather than > and <=? – Aaron Bertrand Jan 25 '17 at 16:12
  • @BrentOzar - I tried PasteThePlan and it said 'The supplied XML is not a valid SQL Server query plan xml.'. I am using SQL 2016 SP1 with latest SSMS build. – Adrian Sugden Jan 25 '17 at 16:27
  • @AaronBertrand partition is defined as RANGE LEFT – Adrian Sugden Jan 25 '17 at 16:30
  • 1
    Perhaps I'm missing something obvious, but since your table (and even your additional non-clustered index) are partitioned on EventCreatedDateTime, I don't see how you're going to achieve partition elimination by specifying a predicate of SystemCreateDt. Can't a row with a SystemCreateDt of today 'live' in any partition? Have you tried creating your NCI and partition on SystemCreateDt? – Scott Hodgin Jan 27 '17 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.