Very slow request on temporary table

Question

On my MS SQL Server 2008, I've created a temporary table like this :

create table #test
(
A varchar(50),
B int,
)

insert into #test select 'a',45
insert into #test select 'b',587
insert into #test select 'a',4
insert into #test select 'b',58

Now the following request take forever

select A,SUM(B) from #test group by A -- takes 4 seconds to execute

while the following requests are instantaneous

select * from #test order by A
select SUM(B) from #test

All other requests (including huge requests) that don't use temporary tables are running fine, while each request using a temporary table seems to encouter the same performance issue. Those requests all ran usually fast yesterday, and I can't think of anything unusual that could have happend since.

I've checked that my tempdb wasn't full using spaceused (70MB free out of 85 MB)

I've also retrieved the execution plan :

What could cause this very poor performance ? Is there something I should to to troubleshoot the issue ?

Answer 1

The intermediate sort is expensive + you can see an exclamation mark in the query plan, probably missing stats

If you add an index (or primary key) it will run quicker because data will be "pre-sorted" and you'll have statistics on it

Answer 2

Your best bet is probably to add an index on A or to set A as the primary key of the table.