OPTION (RECOMPILE) is Always Faster; Why?

by

There are times that using OPTION(RECOMPILE) makes sense. In my experience the only time this is a viable option is when you are using dynamic SQL. Before you explore whether this makes sense in your situation I would recommend rebuilding your statistics. This can be done by running the following:

EXEC sp_updatestats

And then recreating your execution plan. This will ensure that when your execution plan is created it will be using the latest information.

Adding OPTION(RECOMPILE) rebuilds the execution plan every time that your query executes. I have never heard that described as creates a new lookup strategy but maybe we are just using different terms for the same thing.

When a stored procedure is created (I suspect you are calling ad-hoc sql from .NET but if you are using a parameterized query then this ends up being a stored proc call) SQL Server attempts to determine the most effective execution plan for this query based on the data in your database and the parameters passed in (parameter sniffing), and then caches this plan. This means that if you create the query where there are 10 records in your database and then execute it when there are 100,000,000 records the cached execution plan may no longer be the most effective.

In summary – I don’t see any reason that OPTION(RECOMPILE) would be a benefit here. I suspect you just need to update your statistics and your execution plan. Rebuilding statistics can be an essential part of DBA work depending on your situation. If you are still having problems after updating your stats, I would suggest posting both execution plans.

And to answer your question – yes, I would say it is highly unusual for your best option to be recompiling the execution plan every time you execute the query.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Unable to Subtract Dates SQL Server
  3. CROSS JOIN vs INNER JOIN in SQL
  4. How to cast the DateTime to Time
  5. How can I schedule a job to run a SQL query daily?
  6. Reset identity seed after deleting records in SQL Server
  7. How to avoid the “divide by zero” error in SQL?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. How to update Identity Column in SQL Server?
  11. Common Table Expression, why semicolon?
  12. T-SQL split string based on delimiter
  13. Concatenate values based on ID
  14. Splitting delimited values in a SQL column into multiple rows
  15. Detect consecutive dates ranges using SQL
  16. Split values over multiple rows [duplicate]
  17. How to write UPDATE SQL with Table alias in SQL Server 2008?
  18. SQL Server 2008- Get table constraints
  19. Restrict an SQL Server connection to a specific IP address
  20. How to do an inner join on row number in sql server
  21. Why are logical reads for windowed aggregate functions so high?
  22. Stored procedure that exports data into csv files only exports to one file
  23. SQL Server: should I use information_schema tables over sys tables?
  24. how to separate string into different columns?
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. SQL take just the numeric values from a varchar
  27. How to execute a stored procedure inside a select query
  28. Using CASE Statement inside IN Clause
  29. Single or multiple databases
  30. SQL Unpivot multiple columns Data

Leave a Comment