How does SQL Server compile and cache execution plans for stored procedures, and how does this affect performance compared to ad hoc queries?

When a stored procedure runs for the first time, SQL Server compiles an execution plan and caches it for reuse on subsequent calls, avoiding the overhead of recompiling the same logic repeatedly, which generally gives stored procedures a performance advantage over ad hoc queries that may generate a new, uncached plan every time they run with slightly different text.

-- View cached execution plans for stored procedures
SELECT p.name, s.execution_count, s.total_worker_time
FROM sys.procedures p
JOIN sys.dm_exec_procedure_stats s ON p.object_id = s.object_id;