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 good average use counts, but also pretty atrocious 1 Use count plans.. 16k
I can expand the search to show some more information, but that is not really my object with the post. If you are looking for a bit more detail look at Show Me the SP.
Now there are some ways you can minimise your caching, most of which will require you to adjust your code, but adding OPTION(OPTIMIZE FOR UNKNOWN) to a well formed query can really make life better.
What it does it to force the query to use unknown parameters, thus using the same execution plan every time.. now isn’t that nice.
That means less RAM and quicker queries.