SQL server

Using parameters.

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 freeproccache;

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.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s