Using the OPTION FAST Query Hint

OPTION FAST is a hint that you can to a query to tell the optimizer to build a plan appropriate for a smaller number of rows - even if you don't necessarily have a small number of rows being returned. It's not necessarily the turbo switch, but there are times when it can make a difference in both the generated plan and the resulting performance.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

jasmine on 9/13/2010

This is really a worthful clip

Charlie Bruno on 9/13/2010

Nice.

C Boos on 9/13/2010

I notice you do not clear the cache between your runs with a DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE is this not required?

Tim Roberts on 9/13/2010

Good Tip

Nagarajan on 9/13/2010

Really it's nice and very useful in query performance tuning

Tonci on 9/13/2010

just awesome to know!

josh on 9/13/2010

good info. safeguards/disclaimers were well stated

Kenneth Wymore on 9/13/2010

Good information! I always wondered how this option worked.

Andy Warren on 9/14/2010

Clearing buffer/proc cache not needed, and I prefer to rarely use those. In practice data caching is good and doesn't affect the plan selection, and adding the hint should - in theory - force a new plan on first run.

Rick on 9/14/2010

Learned somthing new!

Robbie on 9/17/2010

I can't get this one to work. No error is raised, but all rows are returned, not just the top 10. Does something need to be enabled somewhere to allow ‘OPTION’ to work? SELECT * FROM BaseData.FactQuestionnaireAnswers OPTION (FAST 10)

Andy Warren on 9/18/2010

Robbie, it's not equivalent to TOP 10. Even with FAST 10 stated, you still get all matching rows, you're just changing the internal process used to retrieve those rows.

Robbie on 9/20/2010

Thanks Andy

Cheryl on 9/29/2010

Never heard of Option (fast n). Great to know!

Benjamin Shaw on 10/13/2010

greate... i love small tips like these.

swetha on 12/17/2010

Useful to the beginners

Satyabodhi Densmore on 12/28/2010

Better if we were shown why the number of reads increased with the Fast (50) hint, while duration decreased.

Raymond on 7/20/2011

Thanks.