All the examples I've seen online imply that
OPTIMIZE FOR UNKNOWN is applied on a specific query.
OPTIMIZE FOR UNKNOWN be applied to a stored procedure as a whole (and not just to a specific query)? If yes, what would be the syntax?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
It is not possible to set
OPTIMIZE FOR UNKNOWN at the stored procedure level in SQL Server. You could use
WITH RECOMPILE, which I don't recommend using unless you are on SQL Server 2005 or lower, but it doesn't achieve the same thing. In my opinion,
OPTIMIZE FOR UNKNOWN should be avoided. Spend the time to figure out why you are experiencing parameter sniffing. There could be a way to fix it without working around it. I prefer
OPTIMIZE FOR @variable_name but have also had to resort to
RECOMPILE at the query level.
This article may be helpful.
Not exactly, no.
OPTIMIZE FOR is a statement-level directive and as such, it can only be applied to statement-level queries - there's no way to apply an
OPTIMIZE FOR directive directly to an entire stored procedure.
However, there is a directive available for stored procedures which may produce the results you are looking for. First, let's consider what
OPTIMIZE FOR actually does.
When you use the
OPTIMIZE FOR hint ( and that's what it is at the end of the day, a query plan hint, not entirely different than going out of your way to suggest a
HASH JOIN ), you're dictating what the optimizer should do and that can come with a lot of baggage you might not be interested in carrying. This is not to say "don't do it," but merely as a caution to be sure you know the implications of going down this path. In the specific case of
OPTIMIZE FOR UNKNOWN, you're telling the optimizer to build a query plan for the statement that will be suitable for any parameter that is passed. When included in a stored procedure, the stored procedure's query plan retains the generalized plan of the statement. Once complied and executed, the plan is stored in the plan cache and will be reused for subsequent executions, even if the optimizer might have been able to produce a better plan for a different parameter set, through removing the optimizer's ability to regenerate the plan.
Sometimes, the stored procedure is called very frequently and this technique can improve performance by letting the optimizer know it doesn't have to worry about generating plans for different parameter sets, allowing it to skip recompiling and use the "good enough for whatever" plan to produce results reasonably quickly. More often than not, it is used to avoid the more nefarious effects of parameter sniffing.
CREATE PROCEDURE dbo.SomeProc @Name sysname AS BEGIN SELECT TOP 10 * FROM sys.objects WHERE name = @Name OPTION ( OPTIMIZE FOR UNKNOWN ); END; GO
If the need to tune a procedure falls into the realm of eliminating parameter sniffing, another technique which can be applied - on the entire stored procedure level, if desired - is the
RECOMPILE option. This option is basically the absolute opposite approach from
OPTIMIZE FOR, which instead of caching a general version of the query plan which can be reused later, tells to optimizer to go ahead and generate a new query plan every single time the procedure is executed, allowing the optimizer to produce an optimal plan for each of those executions. Generating query plans is CPU intensive and can be quite expensive ( you usually -want- the plan in the plan cache ), but if the stored procedure isn't executed particularly often and / or involves dynamic SQL, using the
RECOMPILE option can be a legitimate tactic. This can be helpful in specific cases where CPU is cheaper than I/O as well, making the processor work harder to alleviate a little disk contention.
CREATE PROCEDURE dbo.SomeProc @Name sysname WITH RECOMPILE AS BEGIN SELECT TOP 10 * FROM sys.objects WHERE name = @Name; END; GO
You can use
OPTIMIZE FOR ( @variable_name = UNKNOWN ) at the statement level or the old-school method of re-declaring parameters as local variables and then using those throughout the procedure (
SET @p_Name = @Name;,
WHERE name = @p_Name; ), both which direct the optimizer to use statistic densities instead of statistic histograms, but often it's better to just let the optimizer be and turn your attention to where the issue frequently is: The plan cache itself. Clearing the plan cache of a specific stored procedure that is giving you grief will often alleviate any immediate problems and may even buy you time to properly analyze and address why your stored procedure is suffering from desperate performance, without resorting to
SELECT ProcName = OBJECT_NAME( st.objectid ), PlanHandle = cp.plan_handle FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text( cp.plan_handle ) st WHERE cp.objtype = 'Proc' AND st.objectid = OBJECT_ID( 'dbo.SomeProc', 'P' ); GO DBCC FREEPROCCACHE ( <PlanHandle from above results> ); GO