SQL Execution Plan Caching

Question

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

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

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

My first question is why this is so. Earlier, I read that when using parameterized queries (prepared statements), the execution plan is cached for subsequent executions with potentially different values ​​(execution context). Will the stored procedure still be more efficient? If so, is the stored procedure execution plan recreated only on demand or is it less likely to be removed from the cache? Is the parameterized query processed as an ad-hoc query , which means that the execution plan will most likely be removed from the cache?

Also, since I'm still new to this field, I am wondering if there are specific commands that work only in T-SQL. I have a query that takes ~ 12 seconds to complete the first run, and then ~ 3 seconds after that in Microsoft SQL Management Studio and ADO.NET. The request should be ineffective as part of my presentation. The fact is that in my query I use both CHECKPOINT and DBCC DROPCLEANBUFFERS in accordance with this article , as well as OPTION (RECOMPILE) . However, at least the first two seem to have no meaning, as the request will still take 3 seconds. I assume this is due to the fact that the data cache is not cleared. Any ideas why the cache is not cleared, or any ideas as to why my request is much faster after the first execution?

These are the questions that I could think of.

Answer 1

“Will the stored procedure be even more efficient?”: Essentially not. This saves very little. In terms of performance, you can pretty much use SQL literals in your application (unless they are HUGE). SQL Server will exactly match the lines you send to it in the cache plan.

"I have a query that takes ~ 12 seconds to complete the first run, and then ~ 3 seconds after." Given that you have cleared all caches, this is probably a statistics problem. SQL Server automatically creates statistics the first time a column is accessed. Perhaps this is what happened once with you. Try running sp_updatestats (before flushing caches).