SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to Parameterization in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/07/2007 :  08:39:00  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

In a previous article I showed how to look at what

query plans are in SQL Server's memory
.  In this article I'll look at
how they got there and how and when they get reused.  This is commonly
called compiling a query plan.  More specifically I'll look at how and when
SQL Server can parameterize dynamic SQL to reuse the query plan.




Read Introduction to Parameterization in SQL Server

JimInFL
Starting Member

1 Posts

Posted - 08/14/2007 :  08:56:18  Show Profile  Reply with Quote
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)
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 08/14/2007 :  10:45:51  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 08/14/2007 :  12:02:56  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000