query plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query plan

Author

Topic

gblake
Starting Member

1 Post

Posted - 2010-03-18 : 11:33:44

I have two slightly different queries that generate different plans. I am wondering why the plans are different. The only difference between the two queries is that in one case (B) the comparison for sent_ts is against a literal date and in the other case (A) the sent_ts is compared to a variable. The table has 2 million rows in it. Case B causes 208932 logical reads and case A causes 20165 logical reads - obviously index seek is a better plan than index scan. I can change case A to use an index hint to force it to use the index seek, but I was trying to avoid over-riding the optimizer. Any other ideas on how to get the plan to use an index seek for case A?

The queries are
A)
declare @today datetime
select @today='3/18/10'

select count(*)
from email_queue with (nolock)
where sent_ts>@today
and ltrim(rtrim(isnull(exception,'')))=''
and email_notification_type_id=1
and email_notification_subtype_id=0

with plan

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
|--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
|--Clustered Index Scan(OBJECT:([PFServicing].[dbo].[email_queue].[PK_email_queue]), WHERE:([PFServicing].[dbo].[email_queue].[sent_ts]>[@today] AND [PFServicing].[dbo].[email_queue].[email_notification_type_id]=(1) AND [PFServicing].[dbo].[email_queue].[email_notification_subtype_id]=(0) AND ltrim(rtrim(isnull([PFServicing].[dbo].[email_queue].[exception],'')))=''))

and B)
select count(*)
from email_queue with (nolock)
where sent_ts>'3/18/10'
and ltrim(rtrim(isnull(exception,'')))=''
and email_notification_type_id=1
and email_notification_subtype_id=0

with plan
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1007],0)))
|--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))
|--Filter(WHERE:(ltrim(rtrim(isnull([PFServicing].[dbo].[email_queue].[exception],'')))='' AND [PFServicing].[dbo].[email_queue].[email_notification_type_id]=(1) AND [PFServicing].[dbo].[email_queue].[email_notification_subtype_id]=(0)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([PFServicing].[dbo].[email_queue].[record_id]) OPTIMIZED)
|--Index Seek(OBJECT:([PFServicing].[dbo].[email_queue].[IX_email_queue_sent_ts]), SEEK:([PFServicing].[dbo].[email_queue].[sent_ts] > '2010-03-18 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PFServicing].[dbo].[email_queue].[PK_email_queue]), SEEK:([PFServicing].[dbo].[email_queue].[record_id]=[PFServicing].[dbo].[email_queue].[record_id]) LOOKUP ORDERED FORWARD)

the table definition is
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[email_queue](
[record_id] [bigint] IDENTITY(1,1) NOT NULL,
[email_addr] [varchar](255) NOT NULL,
[email_subject] [varchar](255) NOT NULL,
[email_body] [varchar](3000) NOT NULL,
[created_ts] [datetime] NOT NULL CONSTRAINT [DF_email_queue_created_ts] DEFAULT (getdate()),
[sent_ts] [datetime] NULL,
[email_notification_type_id] [int] NOT NULL,
[loan_id] [varchar](15) NOT NULL,
[ref_record_id] [bigint] NOT NULL,
[exception] [varchar](1000) NULL,
[location_id] [bigint] NULL,
[email_notification_subtype_id] [int] NOT NULL CONSTRAINT [DF_email_queue_email_notification_subtype_id] DEFAULT ((0)),
CONSTRAINT [PK_email_queue] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND name = N'IX_email_queue_created_ts')
CREATE NONCLUSTERED INDEX [IX_email_queue_created_ts] ON [dbo].[email_queue]
(
[created_ts] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND name = N'IX_email_queue_email_addr')
CREATE NONCLUSTERED INDEX [IX_email_queue_email_addr] ON [dbo].[email_queue]
(
[email_addr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND name = N'IX_email_queue_loan_id')
CREATE NONCLUSTERED INDEX [IX_email_queue_loan_id] ON [dbo].[email_queue]
(
[loan_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND name = N'IX_email_queue_ref_record_id')
CREATE NONCLUSTERED INDEX [IX_email_queue_ref_record_id] ON [dbo].[email_queue]
(
[ref_record_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[email_queue]') AND name = N'IX_email_queue_sent_ts')
CREATE NONCLUSTERED INDEX [IX_email_queue_sent_ts] ON [dbo].[email_queue]
(
[sent_ts] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'record_id'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'unique record id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'record_id'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'email_addr'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'email address' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'email_addr'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'email_subject'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'subject line' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'email_subject'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'email_body'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'body of the email' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'email_body'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'created_ts'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'date/time the record was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'created_ts'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'sent_ts'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'date/time the email was sent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'sent_ts'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'email_notification_type_id'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the type of event the email is for' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'email_notification_type_id'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'loan_id'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'loan id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'loan_id'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'email_queue', N'COLUMN',N'ref_record_id'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'a reference loan_history record id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'email_queue', @level2type=N'COLUMN',@level2name=N'ref_record_id'

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-18 : 12:20:38

I think this might have something to do with "Parameter Sniffing" or at least the caching of the plans.
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

How to ask good questions that get answers:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Ray Dai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources