3

All the examples I've seen online imply that OPTIMIZE FOR UNKNOWN is applied on a specific query.

Can 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?

5

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.

7

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 OPTION-related measures.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.