Parameters can help with speeding up data retrieval in a relational DB. They help by reusing pre-executed query plans. If a TSQL command like ‘select name from users where id=1’ is used again and again with only the ‘id’ field changing, then parameterization of the query will help use the same query plan for where id=1 or for any other value of id (eg. id=15).
I was looking into this feature in detail. Some of the queries that I came by are:
dbcc flushprocindb db_id; // flushes the cache only for the DB specified.
alter database_name set parameterization forced with NO_WAIT;
select [sql], usecounts from sys.syscacheobjects where sql like ‘%table_name%’;
There are two types of parameterization that can be set at the database level: simple and forced. Let me read more on these in BOL.