Sql-server – Does SQL Server cache data results for DAO

All queries executed by SQL Server need to be compiled first. Stored Procedures are no different in this respect, they don't get any kind of additional optimisation.

They are parsed when created to check invalid syntax but they don't actually get an execution plan generated until first executed. When the plan is compiled it will use the parameter values passed in this initial execution to generate it. This can lead to parameter sniffing issues.

All execution plans generated will remain in the plan cache generally until either evicted due to memory pressure or they becomes invalid due to schema changes or it gets an optimality based recompile. (For adhoc queries the "optimize for ad hoc workloads" option can change this behaviour).

The main benefit of stored procedures is that for subsequent similar queries the plan is more likely to be re-used. For adhoc queries this is dependant on auto parametrisation which is quite conservative so you may well end up with lots of compilations for very similar queries but with different parameter values that all end up in the same plan anyway.

I have left out a lot of details in this answer. See this plan caching white paper for full details

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS. Very likely, your connection from SSMS has different settings from what your .Net application. This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.