6

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.

Nate
  • 30,286
  • 23
  • 113
  • 184
ba0708
  • 10,180
  • 13
  • 67
  • 99
  • 2
    Good questions - I would split this post into a couple posts, maybe one post for the question about stored-proc vs parameterized queries, one post for multiple runs of a query, etc – J Cooper Jan 24 '12 at 20:46
  • Perhaps you are right; my mind was thinking not to spam. :-) – ba0708 Jan 24 '12 at 21:00
  • The stackoverflow idea is to keep Questions focused so that in turn the answers can be focused as well - so don't worry about loading the site up with a bunch of questions – J Cooper Jan 24 '12 at 21:05
  • Plan caching can be tricky. I do not remember the link, but the one point that I do remember is that fully qualifying all column references increases the chances of reuse - something I am often guilty of not doing. Parameterization may also improve reuse. – ron tornambe Jan 24 '12 at 21:58
  • 1
    Just in case you haven't seen this discussion before - http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode – vmvadivel Jan 25 '12 at 05:21

1 Answers1

3

"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).

usr
  • 168,620
  • 35
  • 240
  • 369
  • Do you know if the executions plans for stored procedures and parameterized queries are "treated" the same way in the cache? I mean if one is more likely to be flushed than the other. Clearing the statistics does not seem to have much of an effect. – ba0708 Jan 25 '12 at 12:18
  • I don't think they have different flushing characteristics. Flushing happens only when memory is short so this might not matter to you at all. – usr Jan 25 '12 at 16:59
  • Keeping all code in sprocs for performance reasons is really kind of obsolete. – usr Jan 25 '12 at 19:08