SQL execution plan caching

Question

I have a few questions regarding Microsoft SQL Server 2008 performance, mainly about execution plans.

According, to MSDN, stored procedures have better performance compared to direct SQL queries, because:

The database can prepare, optimize, and cache the execution plan so that the execution plan can be reused at a later time.

My first question is why this is the case. I have previously read that when using parameterized queries (prepared statements), the execution plan is cached for subsequent executions with potentially different values (execution context). Would a stored procedure still be more efficient? If so, is a stored procedure's execution plan only recreated on demand, or is it just less likely to be cleared from the cache? Is a parameterized query treated as an ad-hoc query, meaning that the execution plan is more likely to be cleared from the cache?

Also, since I am still a novice in this field, I am wondering if there are certain commands that only work in T-SQL. I have a query that takes ~12 seconds to complete on the first run and then ~3 seconds after that, in both Microsoft SQL Management Studio and ADO.NET. The query is supposed to be ineffective as part of my presentation. The thing is that in my query, I use both CHECKPOINT and DBCC DROPCLEANBUFFERS as per this article and also OPTION (RECOMPILE). However, at least the two first do not seem to make a difference, as the query will still take 3 seconds. My guess would be that it is due to the data cache not being cleared. Any ideas why the cache does not seem to be cleared, or any ideas as to why my query is significantly faster after the first execution?

Those are the questions I could think of for now.

Answer 1

"Would a stored procedure still be more efficient?": Essentially no. It saves very little. From a performance standpoint, you can pretty much use SQL literals in your app (except if they are HUGE). SQL Server will match the string you send to it to a cached plan just fine.

" I have a query that takes ~12 seconds to complete on the first run and then ~3 seconds after " Considering that you cleared all caches, this is probably a statistics issue. SQL Server is auto-creating statistics the first time you access a column. I guess this is what happened once to you. Try running sp_updatestats (before you clear the caches).