Agenda with SQL (slowness)

Question

I have a commercial management system and a much used module is the agenda. The programming (.net+Sql) worked very well, but now the table has about 1M of records, and is becoming almost impractical to use the tool. The number of simultaneous users is around 70.

The information:
Database: SQL Web 2008
Table: 1 million records
Users: 70 simultaneous users
Server: Amazon Ec2 C3.xlarge (2ghz 4vCPU, and 8GB RAM)

O select:

select pf.prof_id,pf.prof_nome,[dbo].[FN_AGENDA](pf.prof_id,'2016-10-18') from profissionais pf where pf.prof_ativo=1 and pf.prof_unidade=0 order by pf.prof_nome

And the function FN_AGENDA:

BEGIN
DECLARE @concatValues varchar(Max);
SET @concatValues ='';

SELECT @concatValues = @concatValues
+ CAST(ag.agenda_hr_inicio as varchar(5))
+ ';' + CAST(ag.agenda_hr_termino as varchar(5))
+ ';'+ CAST(ag.agenda_id as varchar(12))
+ ';'+ CAST(ag.agenda_status as varchar(2))
+ ';'+ CAST(ag.agenda_comanda as varchar(10))
+ ';'+ CAST(cli.cliente_nome as varchar(28))
+ ';'+ CAST(sv.servico_nome as varchar(30))
+ ';'+ CAST(Coalesce(cm.comanda_status,3) as varchar(1))
+ ';'+ CAST(Coalesce(cm.comanda_codigo,0) as varchar(10))
+ '|'
from agenda ag
inner join clientes cli on (cli.cliente_id=ag.agenda_cliente)
inner join servicos sv on (sv.servico_id=ag.agenda_servico)
left join comandas cm on (cm.comanda_id=ag.agenda_comanda)
where
ag.agenda_profissional = @id_sala and
ag.agenda_data=@data and
ag.agenda_status<>5
;  

RETURN(@concatValues)
END

As I said, everything was going well so far, where the table "agenda" reached 1M of records, and it gets very slow. In performance tests, consultation takes up to 1 minute!

Much of this is due to the use of the CPU which, at various times, gets to be 100% used. The average is 80%. I honestly don’t think the problem is on Amazon, and I also don’t know if you really need to enlarge the instance vCPU number.

What else can you do? Change the syntax? Increase the number of vCPU? Abandon everything and sell popsicles on the beach?