Horrible sql server performance when capturing result in variable

Question

I'm using SQL Server 2012.

When I run this query...

select 
    count(*)
from 
    MembershipStatusHistory msh
join 
    gym.Account a on msh.AccountID = a.AccountID
join 
    gym.MembershipType mt on a.MembershipTypeID = mt.MembershipTypeID
join 
    MemberTypeGroups mtg on mt.MemberTypeGroupID = mtg.MemberTypeGroupID
where 
    mtg.MemberTypeGroupID IN (1,2)
    and msh.NewMembershipStatus = 'Cancelled'
    and year(msh.ChangeDate) = year(getdate())
    and month(msh.ChangeDate) = month(getdate())
    and day(msh.ChangeDate) = day(getdate())

...it returns almost instantly. Great. Now, when I run the same exact query like this:

declare @CancellationsToday int

SET @CancellationsToday = (
    select  
        count(*)
    from MembershipStatusHistory msh
    join gym.Account a
    on msh.AccountID = a.AccountID
    join gym.MembershipType mt
    on a.MembershipTypeID = mt.MembershipTypeID
    join MemberTypeGroups mtg
    on mt.MemberTypeGroupID = mtg.MemberTypeGroupID
    where mtg.MemberTypeGroupID IN (1,2)
    and msh.NewMembershipStatus = 'Cancelled'
    and year(msh.ChangeDate) = year(getdate())
    and month(msh.ChangeDate) = month(getdate())
    and day(msh.ChangeDate) = day(getdate())
)

...it takes 1.5 MINUTES to return. Consistently, every time.

What the **** is going on? I have to use a variable because I need to sum the result later on in my stored proc. I am storing the results of other queries in the same proc and they are fast. I am stumped.

Here is the execution plan from the SLOW query:

And here is the execution plan from the FAST query:

I'll be honest, I don't know what these execution plans mean or what I need to correct.

Answer 1

Very strange but try something like this....

declare @CancellationsToday int;

select @CancellationsToday = count(*)
from MembershipStatusHistory msh
join gym.Account a
on msh.AccountID = a.AccountID
join gym.MembershipType mt
on a.MembershipTypeID = mt.MembershipTypeID
join MemberTypeGroups mtg
on mt.MemberTypeGroupID = mtg.MemberTypeGroupID
where mtg.MemberTypeGroupID IN (1,2)
and msh.NewMembershipStatus = 'Cancelled'
and year(msh.ChangeDate) = year(getdate())
and month(msh.ChangeDate) = month(getdate())
and day(msh.ChangeDate) = day(getdate())