Sunday, September 6, 2009

Clear cache before generating the execution plan in SQL Server

Hi,

Are you doing SQL query tuning ???, so you must be generating execution plan...... Ok ..so before generating execution plan of your SQL query you shoud flush the proc out of your database and clear cache/buffers...below are the script for that.

1. Command to flush the proc out of database

DBCC FLUSHPROCINDB (db_id)

here db_id you can find by using following query in you database
Select db_id() -- this will return int number that number you can pass as db_id

2. Clear Cache
DBCC FREEPROCCACHE

2. Clear Buffers
DBCC DROPCLEANBUFFERS
After running above 3 commands in your database, you can generate execution plan, 
now you will  get exact execution plan.

No comments:

Post a Comment