Question on optimization done by Enterprize manager.

Hi I have set up a scheduled dbase optimation to be done 1 per week.  Just
wondering if anyone knows exactly what it does?  How does it shrink the
database and how does this help performance?
thanks.
This is what it lists as being done.
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 50 MB. Leave 10 percent of data space
as free space.

--
Paul G
Software engineer.

Paul,

What you're refering to is a Database Maintanence Plan.  The plan will
create several jobs (depending on the options selected) for the database.  A
reorg rebuilds the indexes applying a new fillfactor to remove
fragmentation.  A shrink database reduces the size of the database.  While I
would recommend the former, I would not recommend performing an automatic
shrink of the database.  Also, I would recommend that you periodically
backup the transaction log as part of the plan to keep the size of the log
managable.

HTH

Jerry
"Paul" <Paul@discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C- E53E9035C54D@microso
ft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week.  Just
> wondering if anyone knows exactly what it does?  How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data
> space
> as free space.
>
> --
> Paul G
> Software engineer.

ok the database is not that large so think I will remove this option.  Thank
s.
--
Paul G
Software engineer.


"Jerry Spivey" wrote:

> Paul,
>
> What you're refering to is a Database Maintanence Plan.  The plan will
> create several jobs (depending on the options selected) for the database. 
 A
> reorg rebuilds the indexes applying a new fillfactor to remove
> fragmentation.  A shrink database reduces the size of the database.  While
 I
> would recommend the former, I would not recommend performing an automatic
> shrink of the database.  Also, I would recommend that you periodically
> backup the transaction log as part of the plan to keep the size of the log
> managable.
>
> HTH
>
> Jerry
> "Paul" <Paul@discussions.microsoft.com> wrote in message
> news:F8635F69-6A9C-4985-B22C- E53E9035C54D@microso
ft.com... 
>
>
>

Hi Paul,

Jerry is right on shrinking of log file.
But did u think that reindexing will make any effect on ur performance.
Reindexing takes lot of resources and u should not go for indexing
daily.

make it once a week or one time in two weeks.
once ur database will grow it will take lot of buffer size which will
effect ur database performance.

hope u understand

from
Doller

Reorganize (defragment indexes):
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Paul" <Paul@discussions.microsoft.com> wrote in message
news:F8635F69-6A9C-4985-B22C- E53E9035C54D@microso
ft.com...
> Hi I have set up a scheduled dbase optimation to be done 1 per week.  Just
> wondering if anyone knows exactly what it does?  How does it shrink the
> database and how does this help performance?
> thanks.
> This is what it lists as being done.
> Reorganize data and index pages, changing the free space to 10 percent of
> the original space.
> Shrink database when it grows beyound 50 MB. Leave 10 percent of data spac
e
> as free space.
>
> --
> Paul G
> Software engineer.

The index rebuild frequency will depend on a number of things but mostly
how often data is changed.

If your tables, on average, have huge amounts of change each day (eg. >
25% of the indexed data changes on a daily basis) then you will want to
reorganise your indexes more often (perhaps daily in the example
given).  If, on the other hand, your tables, on average, are mostly
static (eg. < 1% of the indexed data changes on a daily basis) then you
won't need to reorganise as often (in that example once a month would
probably be fine).  This should ensure your indexes have a satisfactory
fragmentation (i.e. low) and a reasonably even distribution throughout
the b-tree.

If you have specific tables that have quite a different change rate from
the other tables you may want to implement the reindexing through T-SQL
code you have more control over (rather than using the DB Maint
Wizard).  For example, if you have a dozen large tables with lots of
indexes that are mostly static and you had a couple large tables with
lots of indexes that had a huge amount of daily change, then you'd want
to rebuild the indexes on those couple tables quite often (perhaps
daily) and not so often for the rest (perhaps monthly).  You'd do that
by scheduling a couple SQLAgent jobs to run the DBCC DBREINDEX (or DBCC
DEFRAGINDEX) statement on specific tables - one job for those very
dynamic tables that runs daily and the other job for the relatively
static tables that runs monthly.

You can't lay a blanket rule down for every database, but you do start
with a general plan (like through the DB Maint Wizard) and
monitor/modify as appropriate for each database.

Modifying indexes does result in significant I/O & CPU load on the
server though, as Doller mentioned.  So you should do the reindexing out
of hours.  Also, moving all those index pages around results in a large
volume of data logged in the transaction log.  So you'll want to keep an
eye on your transaction log size and make sure it doesn't get out of
hand.  (Sometimes, in certain circumstances, it's even beneficial to
change the DB recovery model to simple, do your reindexing, change the
recovery model back to full or bulk-logged, and do a full DB backup.
Sometimes a full DB backup is smaller than the transaction log after a
substantial reindexing session.)

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com



doller wrote:

>Hi Paul,
>
>Jerry is right on shrinking of log file.
>But did u think that reindexing will make any effect on ur performance.
>Reindexing takes lot of resources and u should not go for indexing
>daily.
>
>make it once a week or one time in two weeks.
>once ur database will grow it will take lot of buffer size which will
>effect ur database performance.
>
>hope u understand
>
>from
>Doller
>
>
>