Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
|
|
JimInFL
Starting Member
1 Post |
Posted - 2007-08-14 : 08:56:18
|
| Bill - you supply a list of instances where SQL Server will not reuse the plan. One of them is use of an optimizer hint. Does this mean that an sp with a (nolock) hint, will actually be recompiled each time it's called ? (Also - thanks for writing the article - enjoying it) |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-08-14 : 10:45:51
|
| Jim,While I haven't tested that particular situation I think it will. My examples were specifically for SQL outside of stored procedures. Give it a test and let me know how it works :)-Bill===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-14 : 12:02:56
|
quote:
Originally posted by JimInFL
Bill - you supply a list of instances where SQL Server will not reuse the plan. One of them is use of an optimizer hint. Does this mean that an sp with a (nolock) hint, will actually be recompiled each time it's called ? (Also - thanks for writing the article - enjoying it)
I dont think so. We would be dead by now if that were true. Almost EVERY SELECT statement in every proc in our db has NOLOCK hint and ours is an OLTP DB with over hundred transactions per sec. And we dont see any recompiles of procs.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
Article: Introduction to Parameterization in SQL Server