Performance

Another reason to leverage parameters is performance. In a busy SQL Server system, particularly one that has a primarily Online Transaction Processing (OLTP) workload, we may have hundreds or even thousands of queries executing per second.

Assume that each one of these queries took about 100 ms to compile and consumed about the same amount of CPU. This would mean that each second on the system, the server could be consuming hundreds of seconds of CPU time just compiling queries. That's a lot of resources to consume in just preparing the queries for execution, and it doesn't leave a lot of overhead for actually executing them.

Also recall that when plans are not reused, the procedure cache can become very large and consume memory that in turn won't be available for storing data and executing queries. In short, a system that spends too much time compiling queries may become CPU- and/or memory-bound and may perform poorly.