Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Thursday, March 29, 2012
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
EXEC WITH RECOMPILE
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
Tuesday, March 27, 2012
exec sp_executesql vs. sp_executesql and performance
This is a odd problem where a bad plan was chosen again and again, but
then not.
then not.
Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:
exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @.Parm1)',
N'@.Parm1 int', @.Parm1 = 8609
t2 is a foreign key column, and is indexed.
I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.
I then fiddled with it to see what would result in a good plan.
1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@.Parm1 int',
@.Parm1 = 8609
After doing this, re-executing the original form still gave bad
results.
2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.
After that (surprise!) it also performed well in the original form.
What's going on here?elRoyFlynn (lit@.twcny.rr.com) writes:
> t2 is a foreign key column, and is indexed.
> I took the statement into query analyzer and executed it there. The
> query plan showed that it was doing a scan of the primary key index,
> which is clustered. That's a bad choice.
Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.
> 2) I restored the use of the parm, but removed the 'exec' from the
> start.
> It performed well.
> After that (surprise!) it also performed well in the original form.
Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.
But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.
Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.
But thanks for the response, I'll think about it.|||elRoyFlynn (lit@.twcny.rr.com) writes:
> In this case, the "bad plan" clearly is: a 6-second response time vs.
> a sub-second response when the "best" plan is used.
It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.
> Coincidence , unrelated to the different execution form, that just at
> that moment mss figured out that the other plan was better? I'm
> skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
> had an unintended effect.
It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.
What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Wednesday, March 21, 2012
Excluding databases from a maintenance plan
Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van Brakel
Hi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>
|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel
|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van Brakel
Hi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>
|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel
|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John
Excluding databases from a maintenance plan
Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
Johnsql
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
Johnsql
Excluding databases from a maintenance plan
Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van BrakelHi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>|||John,
Thanks for the quick respons.
> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).
> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John
Monday, March 19, 2012
Exclude a Table from SQL 2005 Backup Maintenance Plan
Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
Rick B
Have you considered moving this tables to their own file group. You can then skip backing up this file group. Other choice may to move these tables to another database?
Thanks
|||Supporting Sunil's suggestion to move the table to a different FILEGROUP there is a Books Online topic on this:Partial Backups (link to it).
Regards,
Boris.|||
Thanks Sunil and Boris. I guess I was leaning towards that way. THanks you for taking the time to reply.
Regards,
Rick Broider
Exclude a Table from SQL 2005 Backup Maintenance Plan
Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
Rick B
What about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/
|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegr oups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
Rick B
What about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/
|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegr oups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
Exclude a Table from SQL 2005 Backup Maintenance Plan
Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that
one has complete
knowledge of the implications such strategy has on the restore process. I'd
move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that
one has complete
knowledge of the implications such strategy has on the restore process. I'd
move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
Exclude a Table from SQL 2005 Backup Maintenance Plan
Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>
Subscribe to:
Posts (Atom)