Optimization

In db maintenance when you schedule optimizations, in addition to rebuilding
all the indexes does sql recompile all tables as well, which in turn should
recompile all stored procs accessing those tables to use the latest and most
efficient query plan? Thanks.
Hi
SQL does not recompile the tables, there is no such functionality.
During a Index re-build, the statistics are updated if the setting is on for
the DB.
SP's are not automaticlly re-compiled. Unless you set your SP to use "with
recompile", (which is not optimal as this would happen everytime the SP gets
executed), the SP will only get re-compiled when SQL Server's criteria are
met (aged out of procedure cache or the self tuning detects that the
statistics have changed enough).
Regards
Mike
"mrdj" wrote:

> In db maintenance when you schedule optimizations, in addition to rebuilding
> all the indexes does sql recompile all tables as well, which in turn should
> recompile all stored procs accessing those tables to use the latest and most
> efficient query plan? Thanks.
|||I believe Mike meant
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DE531A23-A31B-4EB2-9C7D-492C6656762A@.microsoft.com...
> Hi
> SQL does not recompile the tables, there is no such functionality.
> During a Index re-build, the statistics are updated if the setting is on
for
> the DB.
> SP's are not automaticlly re-compiled. Unless you set your SP to use "with
> recompile", (which is not optimal as this would happen everytime the SP
gets[vbcol=seagreen]
> executed), the SP will only get re-compiled when SQL Server's criteria are
> met (aged out of procedure cache or the self tuning detects that the
> statistics have changed enough).
> Regards
> Mike
>
> "mrdj" wrote:
rebuilding[vbcol=seagreen]
should[vbcol=seagreen]
most[vbcol=seagreen]

Optimization

In db maintenance when you schedule optimizations, in addition to rebuilding
all the indexes does sql recompile all tables as well, which in turn should
recompile all stored procs accessing those tables to use the latest and most
efficient query plan? Thanks.Hi
SQL does not recompile the tables, there is no such functionality.
During a Index re-build, the statistics are updated if the setting is on for
the DB.
SP's are not automaticlly re-compiled. Unless you set your SP to use "with
recompile", (which is not optimal as this would happen everytime the SP gets
executed), the SP will only get re-compiled when SQL Server's criteria are
met (aged out of procedure cache or the self tuning detects that the
statistics have changed enough).
Regards
Mike
"mrdj" wrote:

> In db maintenance when you schedule optimizations, in addition to rebuildi
ng
> all the indexes does sql recompile all tables as well, which in turn shoul
d
> recompile all stored procs accessing those tables to use the latest and mo
st
> efficient query plan? Thanks.|||I believe Mike meant
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DE531A23-A31B-4EB2-9C7D-492C6656762A@.microsoft.com...
> Hi
> SQL does not recompile the tables, there is no such functionality.
> During a Index re-build, the statistics are updated if the setting is on
for
> the DB.
> SP's are not automaticlly re-compiled. Unless you set your SP to use "with
> recompile", (which is not optimal as this would happen everytime the SP
gets[vbcol=seagreen]
> executed), the SP will only get re-compiled when SQL Server's criteria are
> met (aged out of procedure cache or the self tuning detects that the
> statistics have changed enough).
> Regards
> Mike
>
> "mrdj" wrote:
>
rebuilding[vbcol=seagreen]
should[vbcol=seagreen]
most[vbcol=seagreen]

Optimization

In db maintenance when you schedule optimizations, in addition to rebuilding
all the indexes does sql recompile all tables as well, which in turn should
recompile all stored procs accessing those tables to use the latest and most
efficient query plan? Thanks.Hi
SQL does not recompile the tables, there is no such functionality.
During a Index re-build, the statistics are updated if the setting is on for
the DB.
SP's are not automaticlly re-compiled. Unless you set your SP to use "with
recompile", (which is not optimal as this would happen everytime the SP gets
executed), the SP will only get re-compiled when SQL Server's criteria are
met (aged out of procedure cache or the self tuning detects that the
statistics have changed enough).
Regards
Mike
"mrdj" wrote:
> In db maintenance when you schedule optimizations, in addition to rebuilding
> all the indexes does sql recompile all tables as well, which in turn should
> recompile all stored procs accessing those tables to use the latest and most
> efficient query plan? Thanks.|||I believe Mike meant
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DE531A23-A31B-4EB2-9C7D-492C6656762A@.microsoft.com...
> Hi
> SQL does not recompile the tables, there is no such functionality.
> During a Index re-build, the statistics are updated if the setting is on
for
> the DB.
> SP's are not automaticlly re-compiled. Unless you set your SP to use "with
> recompile", (which is not optimal as this would happen everytime the SP
gets
> executed), the SP will only get re-compiled when SQL Server's criteria are
> met (aged out of procedure cache or the self tuning detects that the
> statistics have changed enough).
> Regards
> Mike
>
> "mrdj" wrote:
> > In db maintenance when you schedule optimizations, in addition to
rebuilding
> > all the indexes does sql recompile all tables as well, which in turn
should
> > recompile all stored procs accessing those tables to use the latest and
most
> > efficient query plan? Thanks.

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

---

Is

quote:

---

> it worth my while making the main view into a user-defined function so

---

that

quote:

---

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

---

GROUP

quote:

---

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

---

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

---

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>