I need to send email from SQL Server SP. Until now I used
sp_send_cdosysmail. Is it better to use
sp_send_dbmail? And the main question - which mail server is used by
sp_send_dbmail - is it configured somewhere?
Database mail depends on what server, profile is configured in Database Mail Ref: Configure Database Mail
as for using
sp_send_dbmail, see sp_send_dbmail (Transact-SQL)
This db mail functionality is available from SQL Server 2005.
See also these example database mail configuration scripts by Dan Guzman.