How can I not have my stored procedures not to recompile whenever ?
The reason being is that when they recompile, they at times choose sub
optimal plans and causes a lot of pain to our applications. Is there any way
i can prevent sprocs not to recompile at a database or server level ? Using
SQL 2000
ThanksUnless you put WITH RECOMPILE option when you create the stored
procedure. The auto-recompile only happens under two conditions:
1) The first time a stored procedure is run after Microsoft=AE SQL
Server=99 2000 is restarted.
2) It also occurs if an underlying table used by the stored procedure
changes.
And it is mainly for optimization purpose (to keep the executation plan
efficient and up to date).
I don't think you can stop the auto-recompile behaviour. But you can
influence the executation plan by adding HINTS in your stored
procedure.
Mel|||Another option is to upgrade to 2005 where you can associate a plan with it
or to use a hint that allows you to optimize for a specific value.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any
> way i can prevent sprocs not to recompile at a database or server level ?
> Using SQL 2000
> Thanks
>|||Hi Hassan
Although you can force a procedure to recompile, you can't stop it from reco
mpiling when it thinks it needs to. All you can do is try to reduce the numb
er of situations where it wants to recompile.
There are many situations that will cause a procedure to recompile.
Take a look at these two KB articles:
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is directed to SQL Server 2005, but much
of it is also useful for SQL 2000:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Hassan" <Hassan@.hotmail.com> wrote in message news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl.
.
> How can I not have my stored procedures not to recompile whenever ?
>
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any w
ay
> i can prevent sprocs not to recompile at a database or server level ? Usin
g
> SQL 2000
>
> Thanks
>
>
Showing posts with label procs. Show all posts
Showing posts with label procs. Show all posts
Friday, March 9, 2012
Dont want stored procs to recompile..
How can I not have my stored procedures not to recompile whenever ?
The reason being is that when they recompile, they at times choose sub
optimal plans and causes a lot of pain to our applications. Is there any way
i can prevent sprocs not to recompile at a database or server level ? Using
SQL 2000
ThanksUnless you put WITH RECOMPILE option when you create the stored
procedure. The auto-recompile only happens under two conditions:
1) The first time a stored procedure is run after Microsoft=AE SQL
Server=99 2000 is restarted.
2) It also occurs if an underlying table used by the stored procedure
changes.
And it is mainly for optimization purpose (to keep the executation plan
efficient and up to date).
I don't think you can stop the auto-recompile behaviour. But you can
influence the executation plan by adding HINTS in your stored
procedure.
Mel|||Another option is to upgrade to 2005 where you can associate a plan with it
or to use a hint that allows you to optimize for a specific value.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any
> way i can prevent sprocs not to recompile at a database or server level ?
> Using SQL 2000
> Thanks
>|||This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C66093.387A0E60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Hassan
Although you can force a procedure to recompile, you can't stop it from =recompiling when it thinks it needs to. All you can do is try to reduce =the number of situations where it wants to recompile.
There are many situations that will cause a procedure to recompile.
Take a look at these two KB articles:
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737=20
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is directed to SQL Server 2005, but =much of it is also useful for SQL 2000:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server =2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Hassan" <Hassan@.hotmail.com> wrote in message =news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> > The reason being is that when they recompile, they at times choose sub =
> optimal plans and causes a lot of pain to our applications. Is there =any way > i can prevent sprocs not to recompile at a database or server level ? =Using > SQL 2000
> > Thanks > >
--=_NextPart_000_038B_01C66093.387A0E60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Hassan
Although you can force a procedure to =recompile, you can't stop it from recompiling when it thinks it needs to. All you =can do is try to reduce the number of situations where it wants to recompile.
There are many situations that will =cause a procedure to recompile.
Take a look at these two KB =articles:
How to identify the cause of =recompilation in an SP:Recompile event
Troubleshooting stored =procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is =directed to SQL Server 2005, but much of it is also useful for SQL =2000:
Batch Compilation, Recompilation, and =Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.msp=x-- HTHKalen Delaney, SQL Server =MVPwww.solidqualitylearning.com
"Hassan" =wrote in message news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...> =How can I not have my stored procedures not to recompile whenever ?> > =The reason being is that when they recompile, they at times choose sub => optimal plans and causes a lot of pain to our applications. Is there any =way > i can prevent sprocs not to recompile at a database or server =level ? Using > SQL 2000> > Thanks > >
--=_NextPart_000_038B_01C66093.387A0E60--
The reason being is that when they recompile, they at times choose sub
optimal plans and causes a lot of pain to our applications. Is there any way
i can prevent sprocs not to recompile at a database or server level ? Using
SQL 2000
ThanksUnless you put WITH RECOMPILE option when you create the stored
procedure. The auto-recompile only happens under two conditions:
1) The first time a stored procedure is run after Microsoft=AE SQL
Server=99 2000 is restarted.
2) It also occurs if an underlying table used by the stored procedure
changes.
And it is mainly for optimization purpose (to keep the executation plan
efficient and up to date).
I don't think you can stop the auto-recompile behaviour. But you can
influence the executation plan by adding HINTS in your stored
procedure.
Mel|||Another option is to upgrade to 2005 where you can associate a plan with it
or to use a hint that allows you to optimize for a specific value.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any
> way i can prevent sprocs not to recompile at a database or server level ?
> Using SQL 2000
> Thanks
>|||This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C66093.387A0E60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Hassan
Although you can force a procedure to recompile, you can't stop it from =recompiling when it thinks it needs to. All you can do is try to reduce =the number of situations where it wants to recompile.
There are many situations that will cause a procedure to recompile.
Take a look at these two KB articles:
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737=20
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is directed to SQL Server 2005, but =much of it is also useful for SQL 2000:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server =2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Hassan" <Hassan@.hotmail.com> wrote in message =news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> > The reason being is that when they recompile, they at times choose sub =
> optimal plans and causes a lot of pain to our applications. Is there =any way > i can prevent sprocs not to recompile at a database or server level ? =Using > SQL 2000
> > Thanks > >
--=_NextPart_000_038B_01C66093.387A0E60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Hassan
Although you can force a procedure to =recompile, you can't stop it from recompiling when it thinks it needs to. All you =can do is try to reduce the number of situations where it wants to recompile.
There are many situations that will =cause a procedure to recompile.
Take a look at these two KB =articles:
How to identify the cause of =recompilation in an SP:Recompile event
Troubleshooting stored =procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is =directed to SQL Server 2005, but much of it is also useful for SQL =2000:
Batch Compilation, Recompilation, and =Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.msp=x-- HTHKalen Delaney, SQL Server =MVPwww.solidqualitylearning.com
"Hassan"
--=_NextPart_000_038B_01C66093.387A0E60--
Tuesday, February 14, 2012
Does Update stats cause all stored procs to recompile
Do all stored procs in a database get recompiled once sp_updatestats is run
against a database ? Using SQL 2000According to the BOL, the procs should be recompiled (i.e. new plans should
be generated). See the following excerpt from the BOL:
Recompiling Execution Plans
... The conditions that cause a plan to be invalidated include:
a.. New distribution statistics generated either explicitly from a
statement such as UPDATE STATISTICS or automatically.
...
However, my test didn't seem to confirm this to be always the case. When I
ran UPDATE STATISTICS on pubs..authors, I did see the following simple proc
got recompiled:
create proc pr_authors
as
select au_id from authors where au_id = '172-32-1176'
More specifically, I didn't see SP:Recompile event class in SQL Trace, which
you would see if you do a sp_recompile authors.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u8Kvj9vgDHA.3144@.tk2msftngp13.phx.gbl...
> Do all stored procs in a database get recompiled once sp_updatestats is
run
> against a database ? Using SQL 2000
>|||If the structure of the table(s) that a stored procedure uses is
changed, or when the statistics of the table(s) is changed, then the
stored procedure is marked for compilation. The next time the stored
procedure is used it will be (re)compiled.
Gert-Jan
Hassan wrote:
> Do all stored procs in a database get recompiled once sp_updatestats is run
> against a database ? Using SQL 2000
against a database ? Using SQL 2000According to the BOL, the procs should be recompiled (i.e. new plans should
be generated). See the following excerpt from the BOL:
Recompiling Execution Plans
... The conditions that cause a plan to be invalidated include:
a.. New distribution statistics generated either explicitly from a
statement such as UPDATE STATISTICS or automatically.
...
However, my test didn't seem to confirm this to be always the case. When I
ran UPDATE STATISTICS on pubs..authors, I did see the following simple proc
got recompiled:
create proc pr_authors
as
select au_id from authors where au_id = '172-32-1176'
More specifically, I didn't see SP:Recompile event class in SQL Trace, which
you would see if you do a sp_recompile authors.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u8Kvj9vgDHA.3144@.tk2msftngp13.phx.gbl...
> Do all stored procs in a database get recompiled once sp_updatestats is
run
> against a database ? Using SQL 2000
>|||If the structure of the table(s) that a stored procedure uses is
changed, or when the statistics of the table(s) is changed, then the
stored procedure is marked for compilation. The next time the stored
procedure is used it will be (re)compiled.
Gert-Jan
Hassan wrote:
> Do all stored procs in a database get recompiled once sp_updatestats is run
> against a database ? Using SQL 2000
Does this make sense ?
We have one publication having all the tables, then another publication for
stored procs, yet another for views and one for indexed views. And all set
up for transactional replication. I understand the need for the tables
portion. Right now, the stored procs, views and indexed views are just to
have their schemas on the subscriber as a one time. I believe its an
overkill to have those set up as transactional replication right ? We could
maybe set them up as snapshot replication ... right ?
Thanks
Hassan,
for stored procedures and views and UDFs in many ways it doesn't make any
difference, however sp_addscriptexec can be useful in the case of incorrect
dependencies, and it is not available with snapshot replication. For this
reason I'd recommend you use transactional. NB be sure to select the option
to have an independant distribution agent, so problems synchronizing the
views publication won't block the tables one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Other than Paul's excellent advise - keep in mind the sp_addscritpexec
only works for subscribers deployed through UNCs.
You may also need to run sp_refreshsubscriptions before re-running any
publications which contain procs, views, and functions which you have
modified. IIRC Paul pointed out to me that replication is not aware of
changes to procedures which it is publishing and you need to drop and
recreate these publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
stored procs, yet another for views and one for indexed views. And all set
up for transactional replication. I understand the need for the tables
portion. Right now, the stored procs, views and indexed views are just to
have their schemas on the subscriber as a one time. I believe its an
overkill to have those set up as transactional replication right ? We could
maybe set them up as snapshot replication ... right ?
Thanks
Hassan,
for stored procedures and views and UDFs in many ways it doesn't make any
difference, however sp_addscriptexec can be useful in the case of incorrect
dependencies, and it is not available with snapshot replication. For this
reason I'd recommend you use transactional. NB be sure to select the option
to have an independant distribution agent, so problems synchronizing the
views publication won't block the tables one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Other than Paul's excellent advise - keep in mind the sp_addscritpexec
only works for subscribers deployed through UNCs.
You may also need to run sp_refreshsubscriptions before re-running any
publications which contain procs, views, and functions which you have
modified. IIRC Paul pointed out to me that replication is not aware of
changes to procedures which it is publishing and you need to drop and
recreate these publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
Subscribe to:
Posts (Atom)