When an adhoc query is submitted to Engine for execution, Engine will check plan cache, for any query which will matches with the given adhoc query. If any query matches including white spaces,same case, then it will use the plan of that query. Otherwise, it will recompile again and creates a plan and execute that query using that plan, If the query differs by even a single space or single differenct case character, which will recompile again

Suppose, The below 3 queries, First query and 3rd query are same, so they both choose same plan , while second query chooses a different plan.

select * from Purchasing.Vendor where VendorID = 1
GO
select * from Purchasing.Vendor where VendorId = 11
GO
select * from Purchasing.Vendor where VendorID = 1

In third query, if i change VendorID to VendorId, then again query will recompile.

Parameterized statements will use the existing plan, when they are having exactly have same query, with change in values of parameters suppose, The below 2 queries, First query and 2nd query have different parameters, still they both use same plan. Which reduces the cost of recompilation for 2nd query,

 EXEC sp_executesql N'SELECT * FROM Purchasing.Vendor
    WHERE VendorID = @p', N'@p int', 1;
    GO
    EXEC sp_executesql N'SELECT * FROM Purchasing.Vendor
    WHERE VendorID = @p', N'@p int', 11;

We can use the DMV sys.dmexeccached_plans to track whether these queries are recompiling or using the existing plan by checking usecount column

So finally Adhoc queries suffers from Query recompilation every time and where as Prepared statements will get the benefit of plan reuse. Only chink in case of prepared statements is parameter sniffing(Plan used for one parameter value might not be optimal for another). Which we can avoid by using recompile or other hints in that particular case.

As my personal opinion I would prefer Parameterized query over Ad-Hoc as you can use existing plan and remove overhead of re-compilation again and again in parameterized query, apart from that, you don't need to worry about formating the value especially date and single quote, also no need to worry as much about SQL Injection as you used to do in Ad-Hoc query.

when you execute the query, SQL Server will prepare execution plan, SQL Server can do the good job in case it knows thee actual value. In parameterized query, actual value is unknown and SQL Server estimates values based on available data statistics and tries to generate good plan. This may be/may not be good for many queries.

at the same time, in Ad-Hoc query, whenever there is change in character case, spaces etc., it is being recompiled all the time. you can use DMV sys.dmexeccached_plans to see whether query is recompiled or not.

this is the reason of performance gap.

there is good white paper given here for more information.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Why is there a performance gap between AdHoc and Parameterized queries ?

To answer in simple word, the cost of re-compilation scores parameterized queries over Ahhoc queries.( reusability )

Parameterization greatly increases the likelihood that an existing plan can be reused, avoiding the overhead of compilation. It is especially important to avoid (re)compilation in high-volume OLTP environments. Once the SQL Server Query Optimizer has come up with a plan ( after Parsing, algebrizing, Optimizing ), , which may have taken a considerable amount of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does this by caching the plan it just created, and taking steps to ensure that the plan is reused as widely as possible. It does this by using parameterization.

sys.syscacheobjects

To determine whether a query has been parameterized, we can search for it in the DMV sys.syscacheobjects (after first executing the query to ensure it is cached). If the SQL column of this DMV shows that the query has been parameterized,we will see that any literals from the query have been replaced by variables, and those variables are declared at the beginning of the batch. The cacheobjtype column of this virtual table shows cacheobjtype = "Compiled Plan", the row refers to a query plan. When cacheobjtype = "Executable Plan", the row refers to an execution context. objtype column: it indicates the type of object whose plan is cached (for example, "Adhoc", "Prepared", and "Proc").

Reference: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Parameterized query can use existing plan so there is no recompilations. In case of Adhoc query, teh query has to be recompiled and optimized everytime.

A common answer will be Ad-hoc needs to recompile everytime and parameterized queries will not have to re-compile everytime.

Now the reality is that even Ad-Hoc plans are compiled too.

Run following query.

DBCC FREEPROCCACHE
SELECT 
    c.TEXT AS sql_text, 
    a.usecounts, 
    d.query_plan, 
    a.memory_object_address AS CompiledPlan_MemoryObject, 
    b.pages_allocated_count, 
    b.TYPE, b.page_size_in_bytes, *
FROM sys.dm_exec_cached_plans a 
INNER JOIN sys.dm_os_memory_objects b 
    ON a.memory_object_address = b.memory_object_address       
        OR a.memory_object_address = b.parent_address  
        CROSS APPLY sys.dm_exec_sql_text (plan_handle) c  
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) d  
WHERE cacheobjtype = 'Compiled Plan'
GO
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail 
WHERE SalesOrderDetailID = 5580
GO
SELECT *
FROM Sales.SalesOrderDetail 
WHERE SalesOrderDetailID = 5
GO 5
SELECT 
    c.TEXT AS sql_text, 
    a.usecounts, 
    d.query_plan, 
    a.memory_object_address AS CompiledPlan_MemoryObject, 
    b.pages_allocated_count, 
    b.TYPE, b.page_size_in_bytes, *
FROM sys.dm_exec_cached_plans a 
INNER JOIN sys.dm_os_memory_objects b 
    ON a.memory_object_address = b.memory_object_address      
     OR a.memory_object_address = b.parent_address  
     CROSS APPLY sys.dm_exec_sql_text (plan_handle) c  
     CROSS APPLY sys.dm_exec_query_plan(plan_handle) d  
WHERE cacheobjtype = 'Compiled Plan'
GO

Pay special attention to UserCounts column. You will find following results.

You will see that even ad-hoc queries are compiled only once and the plan is reused just like any parameterized query.

So now coming to the real question - Where is the performance difference.

If you have multiple parameters you will have separate entry for each of those parameters. If you have parameterized query you will have single entry for the query.

This is the only difference between them.

In recent consultancy I have found that our user have to pass only one kind of parameter all the time and they used the ad-hoc query and there was no issue at all. It just worked like parameterized query and had only one entry. Add additional overhead is build when the param is varied and also this will start taking places in your memory cache reduced the performance for whole server.

I hope this is clear now.

The performance gap is primarily in the (re)compilcation of the execution plan itself. Should the query have an exitsiting plan cached, performance will prevail. You can take a look any given cached plans with a query similar to the following:

select
   db_name(st.dbid) db,
   objectid,
   object_name(st.objectid) name,
   cacheobjtype,
   objtype,
   usecounts,
   encrypted,
   plan_handle,
   text
from
   sys.dm_exec_cached_plans x
   cross apply
   sys.dm_exec_sql_text(plan_handle) st
where
   cacheobjtype=N'Compiled Plan'
order by
   objtype

Ad Hoc plans will be labeled as such and you can see the number of times they have been used...

Paramzterized queries can sometimes really sucks ass.

For instance, in order to have optionnal filter one could think on putting something like this in a where clause :

( @MyBit = 1 AND (....) )
OR
( @MyBit = 0 AND (....) )

Or use something that looks even more harmful like
SELECT ...
WHERE ( @MyBit = 1 AND (....) )
UNION ALL
SELECT ...
WHERE ( @MyBit = 0 AND (....) )

(Rem: @MyBit is a BIT value)

SQL Server might act like the dumbest making query plans were the @MyBit check is evaluated last in order to have "parameter indepedent" plans.

Which of course might result in a overkill no human would fall for.

There is a performance gap between adhoc and parametrized queries (assuming both produce good plans) because adhoc queries have poor plan re-use, and thus have to be parsed, compiled, and optimized over and over again. Parse and compile time predominantly consumes CPU, so adhoc-heavy environments suffering CPU bottlenecks may be able to delay hardware upgrades if they convert their adhoc SQL to stored procedures (or if it is dynamic inside of SPs, then use sp_executesql instead of execute). Increasing proc usage is also good for reducing vulnerability to sql-injection and also the ability to utilize DMVs instead of profiler traces (because they stay in plan cache longer) for analyzing for performance worst offenders.

First, When you use adhoc ever when you change the input parameter(s) and it could not be found in the cache for plan execution. All query should be recompiled.

Second, Instead the parametrized queries no matter what input parameter(s) you enter, they always will specified into the cache for plan execution and don't have to be recompiled again.

For an ad-hoc query, "...unless the query (including the values of the where clause) is exactly the same, SQL server must recompile the statement. On a small database, with little traffic this will mostly go unnoticed. But on a either a larger system or one with a significant amount of traffic your procedure cache will bloat. A side effect of this is that the data in your buffer cache will be pushed out resulting in more data being read directly from disk instead of from memory. This will cause a serious IO bottleneck and most likely cripple your system. ...

While stored procedures are the recommended method for data access clients, it isn't always practical to rewrite everything if your code is already using Ad Hoc SQL. However, you can get almost the same performance benefit from using parameters that you can get from stored procedures. So if you haven't done so there really shouldn't be any excuse for not parameterizing your queries. Your application will scale better and will be more stable."

http://www.codeproject.com/KB/database/ParameterizingAdHocSQL.aspx