SQL: what is this OPTION(OPTIMIZE FOR UNKNOWN)?

If you are into caching and reuse of existing query execution plans, which no doubt excites you as much as it does me, you probably use OPTION(OPTIMIZE FOR UNKNOWN) in most of your queries.

If, however, you are not tickled by SQL performance optimization, shame on you, you might want to have a look at this to help make some of your queries run a bit smoother.

You can read more about this at OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature, thanks Peter Scharlock

The reason this is of particular interest to me to write about this is that I get annoyed at people remarking that SQL server is this “RAM hungry monster” that consumes all our virtualized memory in order to do a couple of select statements. Of course this is not really the case, yes SQL loves RAM, but a lot of that is taken up with execution plan caching. A simple example is one of our email systems, even though only about 25 different stored procedures gets fired daily, it will generate 5GB worth of execution plan caching after a day. Let me throw some code at you

SELECT'Caching Plans'
,objtypeAS[CacheType]
,COUNT_BIG(*)AS[Total Plans]
,SUM(CAST(size_in_bytesasdecimal(18,2)))/1024/1024 AS[Total MBs]
,AVG(usecounts)AS[Avg Use Count],SUM(CAST((CASEWHENusecounts= 1 THENsize_in_bytesELSE 0 END)asdecimal(18,2)))/1024/1024 AS[Total MBs - USE Count 1]
,SUM(CASEWHENusecounts= 1 THEN 1 ELSE 0 END)AS[Total Plans - USE Count 1
FROMsys.dm_exec_cached_plansGROUPBYobjtypeORDERBY[Total MBs - USE Count 1]DESC

And this is what I get out of this one:

CacheType    Total Plans    Total MBs         Avg Use Count    Total MBs – USE Count 1    Total Plans – USE Count 1 Prepared        20347            4406.687500    3306                     4001.695312                       16498

Well, there is a good average use count that indicates a healthy level of usage, but alongside that, we also encounter some pretty atrocious one-use count plans, with instances reaching as high as 16,000. This disparity highlights a significant issue within the query execution plans that can lead to inefficiencies and performance bottlenecks in the system. It’s essential to analyze these counts closely to understand the underlying causes and to address them effectively.

I can expand the search to show some more information regarding these usage counts, but that is not really my primary objective with this post. If you are looking for a bit more detail on the subject, I highly recommend checking out the resource titled Show Me the SP. This resource provides a wealth of information that can help you dive deeper into the specifics of stored procedures and their execution plans, offering insights that can be crucial for optimizing performance.

Now, let’s discuss some strategies that can help you minimize your caching issues. Many of these strategies will require you to make adjustments to your codebase, which can be a bit of a challenge but is often necessary for achieving better performance. One particularly effective method is to add the OPTION(OPTIMIZE FOR UNKNOWN) directive to a well-formed SQL query. This simple addition can significantly enhance the performance of your queries and improve overall system efficiency.

So, what exactly does this directive do? Essentially, it forces the SQL Server to use unknown parameters when generating the execution plan. This approach means that the server will rely on a more generic execution plan that can be reused across multiple executions of the query, regardless of the specific parameter values provided at runtime. Now, isn’t that nice? By employing this method, you can achieve a more consistent performance profile, as the same execution plan is utilized every time the query is run.

This consistency translates into less RAM consumption and quicker query execution times, which is a significant advantage in high-load environments where performance is critical. By reducing the overhead associated with generating new execution plans for every unique parameter set, you can streamline your database operations and ensure that your applications run more smoothly and efficiently. In summary, leveraging the OPTION(OPTIMIZE FOR UNKNOWN) directive can be a game-changer in optimizing your SQL queries and enhancing the overall performance of your database system.

#cache #performance #tuning