July 15, 2009 at 7:24 am
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
July 15, 2009 at 7:36 am
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)
July 15, 2009 at 7:40 am
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
July 15, 2009 at 7:52 am
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
July 15, 2009 at 7:56 am
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
July 15, 2009 at 8:00 am
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
July 15, 2009 at 8:09 am
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
July 15, 2009 at 8:11 am
If rebuilding the index doesnt help the post the plan , interesting to see if it stopped with GoodEnoughPlanFound ?
July 15, 2009 at 8:17 am
@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
July 15, 2009 at 8:22 am
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