Optimize query

Hi,
below is the Sp ,execution plan and all indexes..can anyone help me in
optimizign this query..
and what are the impacts and can we really do antying on this query.
Db size is around 20 GB.
SP
--
select
ee.emailtypeid
,ee.Active
,ee.EventEmailID
, ee.IsDefaultMessageSent
, ee.IsEmailRecipientTable
,ee.SendDate
FROM dbo.EventEmail ee
JOIN dbo.EmailType et
ON ee.EmailTypeID = et.EmailTypeID -- Hash join
AND et.isExternal = 1
Execution Tree
--
Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([et].[EmailTypeID])=([ee].[EmailTypeID]))
|--Parallelism(Broadcast)
| |--Clustered Index
Scan(OBJECT:([EventsSpar0802].[dbo].[EmailType].[PK_EmailType] AS [et]),
WHERE:(Convert([et].[IsExternal])=1))
|--Clustered Index
Scan(OBJECT:([EventsSpar0802].[dbo].[EventEmail].[PK_EventEmail] AS [ee]))
--
Index name (EventEmail)
--
IX_EmailTypeID nonclustered located on PRIMARY EmailTypeID,
Active
IX_SendDate nonclustered located on PRIMARY
SendDate
IXU_EventIDEmailTypeID nonclustered, unique located on PRIMARY
EventID, EmailTypeID
NC_emailTypeId nonclustered located on PRIMARY EmailTypeID
PK_EventEmail clustered, unique, primary key located on PRIMARY
EventEmailID
========== Index Name(emailtype)
IX_Cover1 nonclustered located on PRIMARY EmailTypeID, Active,
Description
IX_Cover2 nonclustered located on PRIMARY Description,
EmailTypeID, Active
IXU_Code nonclustered, unique located on PRIMARY Code
IXU_Description nonclustered located on PRIMARY Description
PK_EmailType EmailTypeIDA hash join usually indicates there is no usable index, but you have an
index on emailtype on both tables. Perhaps SQL is not using the index
because there are so few emailtypes...
Ensure index statistics are up to date first. then re-try existing join...
1. Try forcing the join to a LOOP join ( lookup join in books on line.) and
compare the performance.
2. see if re-writing the query makes better performance ( ie using IN for
example
select yada yada yada from eventemail where emailtypeid in ( select
emailtypeid from emailtype where isExternal = 1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:2DFA7C7B-AA8C-45D9-87F5-E0E098B1D84E@.microsoft.com...
> Hi,
> below is the Sp ,execution plan and all indexes..can anyone help me in
> optimizign this query..
> and what are the impacts and can we really do antying on this query.
> Db size is around 20 GB.
> SP
> --
> select
> ee.emailtypeid
> ,ee.Active
> ,ee.EventEmailID
> , ee.IsDefaultMessageSent
> , ee.IsEmailRecipientTable
> ,ee.SendDate
> FROM dbo.EventEmail ee
> JOIN dbo.EmailType et
> ON ee.EmailTypeID = et.EmailTypeID -- Hash join
> AND et.isExternal = 1
> Execution Tree
> --
> Parallelism(Gather Streams)
> |--Hash Match(Inner Join,
HASH:([et].[EmailTypeID])=([ee].[EmailTypeID]))
> |--Parallelism(Broadcast)
> | |--Clustered Index
> Scan(OBJECT:([EventsSpar0802].[dbo].[EmailType].[PK_EmailType] AS [et]),
> WHERE:(Convert([et].[IsExternal])=1))
> |--Clustered Index
> Scan(OBJECT:([EventsSpar0802].[dbo].[EventEmail].[PK_EventEmail] AS [ee]))
> --
>
> Index name (EventEmail)
> --
> IX_EmailTypeID nonclustered located on PRIMARY EmailTypeID,
> Active
> IX_SendDate nonclustered located on PRIMARY
> SendDate
> IXU_EventIDEmailTypeID nonclustered, unique located on PRIMARY
> EventID, EmailTypeID
> NC_emailTypeId nonclustered located on PRIMARY EmailTypeID
> PK_EventEmail clustered, unique, primary key located on PRIMARY
> EventEmailID
> ==========> Index Name(emailtype)
> IX_Cover1 nonclustered located on PRIMARY EmailTypeID, Active,
> Description
> IX_Cover2 nonclustered located on PRIMARY Description,
> EmailTypeID, Active
> IXU_Code nonclustered, unique located on PRIMARY Code
> IXU_Description nonclustered located on PRIMARY Description
> PK_EmailType EmailTypeID
>|||If EmailTypeID is the primary key of table EmailType, then make it the
primary key with the PRIMARY KEY constraint. A unique index will be
created to enforce the constraint. Currently, you probably only have
non-unique indexes.
If it is the primary key, I would suggest you make define the PRIMARY
KEY as clustered.
If parallelism causes a slowdown, then you could try running the query
with the hint OPTION (MAXDOP 1).
Hope this helps,
Gert-Jan
Sanjay wrote:
> Hi,
> below is the Sp ,execution plan and all indexes..can anyone help me in
> optimizign this query..
> and what are the impacts and can we really do antying on this query.
> Db size is around 20 GB.
> SP
> --
> select
> ee.emailtypeid
> ,ee.Active
> ,ee.EventEmailID
> , ee.IsDefaultMessageSent
> , ee.IsEmailRecipientTable
> ,ee.SendDate
> FROM dbo.EventEmail ee
> JOIN dbo.EmailType et
> ON ee.EmailTypeID = et.EmailTypeID -- Hash join
> AND et.isExternal = 1
> Execution Tree
> --
> Parallelism(Gather Streams)
> |--Hash Match(Inner Join, HASH:([et].[EmailTypeID])=([ee].[EmailTypeID]))
> |--Parallelism(Broadcast)
> | |--Clustered Index
> Scan(OBJECT:([EventsSpar0802].[dbo].[EmailType].[PK_EmailType] AS [et]),
> WHERE:(Convert([et].[IsExternal])=1))
> |--Clustered Index
> Scan(OBJECT:([EventsSpar0802].[dbo].[EventEmail].[PK_EventEmail] AS [ee]))
> --
> Index name (EventEmail)
> --
> IX_EmailTypeID nonclustered located on PRIMARY EmailTypeID,
> Active
> IX_SendDate nonclustered located on PRIMARY
> SendDate
> IXU_EventIDEmailTypeID nonclustered, unique located on PRIMARY
> EventID, EmailTypeID
> NC_emailTypeId nonclustered located on PRIMARY EmailTypeID
> PK_EventEmail clustered, unique, primary key located on PRIMARY
> EventEmailID
> ==========> Index Name(emailtype)
> IX_Cover1 nonclustered located on PRIMARY EmailTypeID, Active,
> Description
> IX_Cover2 nonclustered located on PRIMARY Description,
> EmailTypeID, Active
> IXU_Code nonclustered, unique located on PRIMARY Code
> IXU_Description nonclustered located on PRIMARY Description
> PK_EmailType EmailTypeID
--
(Please reply only to the newsgroup)