Another query running badly on SSC

  • Ok, here's another one that I don't understand why it's performing so poorly.

    Here's the query (it's really an update when used for real, but converted to a select for the purposes of testing and optimising):

    SELECT *

    FROM dbo.EmailRecipients er

    INNER JOIN Emails e ON er.EmailID = e.EmailID

    WHERE er.SentDate IS NULL

    AND er.SendingComputerName IS NULL

    AND (CASE WHEN e.EmailTypeName LIKE 'System:%' THEN 1 WHEN e.IsTest = 1 THEN 1 ELSE 2 END) = 1

    The execution plan is attached. You'll notice that it recommends that creating a particular index would help significantly. However if we look at the definitions of the tables, you'll see that that index already exists.

    EmailRecipients

    CREATE TABLE [dbo].[EmailRecipients]

    (

    [EmailRecipientID] [int] NOT NULL IDENTITY(1, 1),

    [EmailID] [int] NOT NULL,

    [UserID] [int] NULL,

    [SentDate] [datetime] NULL,

    [SendingComputerName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL,

    [ArbitraryRecipientName] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL,

    [ArbitraryRecipientEmailAddress] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL

    )

    CREATE NONCLUSTERED INDEX [SSC_SentDate] ON [dbo].[EmailRecipients] ([SentDate], [SendingComputerName]) INCLUDE ([EmailID], [EmailRecipientID])

    GO

    ALTER TABLE [dbo].[EmailRecipients] ADD CONSTRAINT [PK_EmailRecipients] PRIMARY KEY CLUSTERED ([EmailRecipientID])

    GO

    CREATE NONCLUSTERED INDEX [IX_EmailAndRecipients] ON [dbo].[EmailRecipients] ([EmailID], [EmailRecipientID]) INCLUDE ([SendingComputerName], [SentDate])

    GO

    CREATE NONCLUSTERED INDEX [IX_EmailRecipients_UserID_SentDate] ON [dbo].[EmailRecipients] ([UserID], [SentDate]) INCLUDE ([EmailID], [EmailRecipientID], [SendingComputerName])

    GO

    ALTER TABLE [dbo].[EmailRecipients] ADD CONSTRAINT [FK_EmailRecipients_EmailRecipients] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE

    GO

    ALTER TABLE [dbo].[EmailRecipients] ADD CONSTRAINT [FK_EmailRecipients_Emails] FOREIGN KEY ([EmailID]) REFERENCES [dbo].[Emails] ([EmailID]) ON DELETE CASCADE

    GO

    Emails

    CREATE TABLE [dbo].[Emails]

    (

    [EmailID] [int] NOT NULL IDENTITY(1, 1),

    [RecordType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL,

    [EmailTypeName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,

    [IsTest] [bit] NULL,

    [UseClickTracking] [bit] NOT NULL,

    [SubjectText] [varchar] (500) COLLATE Latin1_General_CI_AS NOT NULL,

    [BodyHTML] [text] COLLATE Latin1_General_CI_AS NOT NULL,

    [BodyPlainText] [text] COLLATE Latin1_General_CI_AS NOT NULL,

    [SenderName] [varchar] (500) COLLATE Latin1_General_CI_AS NULL,

    [SenderEmailAddress] [varchar] (500) COLLATE Latin1_General_CI_AS NOT NULL,

    [Status] [int] NULL,

    [ScheduleForDate] [datetime] NULL,

    [ApprovedForSending] [bit] NULL

    )

    GO

    ALTER TABLE [dbo].[Emails] ADD CONSTRAINT [PK_Emails] PRIMARY KEY CLUSTERED ([EmailID])

    GO

    CREATE NONCLUSTERED INDEX [ssp_996] ON [dbo].[Emails] ([RecordType], [EmailTypeName], [IsTest]) INCLUDE ([ApprovedForSending], [EmailID], [ScheduleForDate], [SenderEmailAddress], [SenderName], [Status], [SubjectText], [UseClickTracking])

    GO

    CREATE NONCLUSTERED INDEX [ssc_316] ON [dbo].[Emails] ([RecordType], [IsTest], [EmailTypeName]) INCLUDE ([EmailID])

    GO

    CREATE NONCLUSTERED INDEX [ssp_48] ON [dbo].[Emails] ([ScheduleForDate]) INCLUDE ([ApprovedForSending], [EmailID], [EmailTypeName], [IsTest])

    GO

    CREATE NONCLUSTERED INDEX [ssc_390] ON [dbo].[Emails] ([Status], [ApprovedForSending], [ScheduleForDate]) INCLUDE ([EmailID], [EmailTypeName])

    GO

    CREATE NONCLUSTERED INDEX [ssc_26] ON [dbo].[Emails] ([SubjectText]) INCLUDE ([EmailID])

    GO

    As a guide, EmailRecipients has about 6 million rows at the moment, and emails has about 3 million rows. As before, any thoughts welcome! Anything you can help with will directly improve the stability and performance of the site!

    Mel HarbourProject ManagerRed Gate Software

  • Is this functionally the same ???

    SELECT *

    FROM dbo.EmailRecipients er

    INNER JOIN Emails e ON er.EmailID = e.EmailID

    WHERE er.SentDate IS NULL

    AND er.SendingComputerName IS NULL

    AND (e.EmailTypeName LIKE 'System:%' or

    e.IsTest = 1)



    Clear Sky SQL
    My Blog[/url]

  • I'm not normally a fan of hints, but what happens if you force that index?

    SELECT *

    FROM dbo.EmailRecipients er WITH (INDEX= SSC_SentDate)

    INNER JOIN Emails e ON er.EmailID = e.EmailID

    WHERE er.SentDate IS NULL

    AND er.SendingComputerName IS NULL

    AND (CASE WHEN e.EmailTypeName LIKE 'System:%' THEN 1 WHEN e.IsTest = 1 THEN 1 ELSE 2 END) = 1

    Also a possibility, change that index so that EmailID is the third column of the key rather than an included column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • @dave-3,

    Yep, that would be equivalent.

    @Gail,

    Execution plan of that version attached. Definitely different. Here's the performance statistics from the original:

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 9 ms, elapsed time = 9 ms.

    (3 row(s) affected)

    Table 'Emails'. Scan count 0, logical reads 17829, physical reads 0, read-ahead reads 0, lob logical reads 15, lob physical reads 0, lob read-ahead reads 0.

    Table 'EmailRecipients'. Scan count 1, logical reads 78775, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 953 ms, elapsed time = 989 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Followed by those for the revised version:

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    (23 row(s) affected)

    Table 'Emails'. Scan count 0, logical reads 17890, physical reads 0, read-ahead reads 0, lob logical reads 121, lob physical reads 0, lob read-ahead reads 0.

    Table 'EmailRecipients'. Scan count 1, logical reads 17935, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 63 ms, elapsed time = 125 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Execution plan attached. Interesting that it still moans about the lack of the index, even when it's being told explicitly to use it.

    Mel HarbourProject ManagerRed Gate Software

  • So whats the plan like for

    SELECT *

    FROM dbo.EmailRecipients er

    INNER JOIN Emails e ON er.EmailID = e.EmailID

    WHERE er.SentDate IS NULL

    AND er.SendingComputerName IS NULL

    AND (e.EmailTypeName LIKE 'System:%' or

    e.IsTest = 1)

    and just out of interest , very surprised if the index is not used here

    SELECT *

    FROM dbo.EmailRecipients er

    WHERE er.SentDate IS NULL

    AND er.SendingComputerName IS NULL



    Clear Sky SQL
    My Blog[/url]

  • @dave-3,

    Plan for the first one is identical. Running the second one, it does an index scan on the primary key, and still ignores the index!

    Mel HarbourProject ManagerRed Gate Software

  • Mel, can you try rebuilding that index, see if it makes a difference.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If rebuilding the index doesnt help the post the plan , interesting to see if it stopped with GoodEnoughPlanFound ?



    Clear Sky SQL
    My Blog[/url]

  • @Gail,

    There's already a job in place on the server that updates the statistics for that index every 2 hours. Is rebuilding the index any different to that?

    Mel HarbourProject ManagerRed Gate Software

  • Total fragmentation before rebuilding the index was 34.7%. After the rebuild, Dave's plain select statement started using the index, as did the original query, which therefore doesn't need the hint.

    What are your recommendations for managing this table then?

    Mel HarbourProject ManagerRed Gate Software

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply