Database reorganization

Database reorganization
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--060703000702060505050207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
>> Database reorganization
>> We use SQL 2000 and our application database is around 2 Gb.
>> A third party consultant (Microsoft Certified) counseled us to perform
>> database reorganization, at least once each year.
>> The way he recommend us to execute the database reorganization is the
>> following:
>> 1) backup the app database
>> 2) delete the app database
>> 3) restore the app database with another name oldDB
>> 4) create a new app database
>> 5) export all objects and data from oldDB to app DB by DTS
>> The questions I would like to ask you are:
>> Is that really necessary?
>> Is that really effective?
>> Is there another way to perform database reorganization?
>>
>> Thanks,
>> Marcos
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator
--060703000702060505050207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tracy McKibben wrote:
<blockquote cite="miduJIjCXGoGHA.4432@.TK2MSFTNGP05.phx.gbl" type="cite"><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mripplinger74@.yahoo.com.br">mripplinger74@.yahoo.com.br</a>
wrote:
<br>
<blockquote type="cite">Database reorganization
<br>
<br>
We use SQL 2000 and our application database is around 2 Gb.
<br>
<br>
A third party consultant (Microsoft Certified) counseled us to perform
<br>
database reorganization, at least once each year.
<br>
<br>
The way he recommend us to execute the database reorganization is the
<br>
following:
<br>
<br>
1) backup the app database
<br>
2) delete the app database
<br>
3) restore the app database with another name oldDB
<br>
4) create a new app database
<br>
5) export all objects and data from oldDB to app DB by DTS
<br>
<br>
The questions I would like to ask you are:
<br>
<br>
Is that really necessary?
<br>
<br>
Is that really effective?
<br>
<br>
Is there another way to perform database reorganization?
<br>
<br>
<br>
Thanks,
<br>
<br>
Marcos
<br>
<br>
</blockquote>
<br>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
<br>
<br>
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
<br>
<br>
<br>
</blockquote>
<font size="-1"><font face="Arial">..well...nobody said that this
consultant was certified on anything related to SQL server...<span
class="moz-smiley-s3"><span> ;-) </span></span><br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--060703000702060505050207--|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delete
> my database to restore it back again. What happens if your restore does not
> work? If you really wanted to do that I would do 2 and 3 the other way round
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you start
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
> after this database it was 150GB and it had never had a reorg job run on it,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D|||mripplinger74@.yahoo.com.br wrote:
> Tracy,
> I agree that our database is not big, but neither our servers are. In
> someplace the database is running in a workstation or in a laptop,
> that's why I do care about it.
> We have a maintenance plan to reorganize data and index page and we
> schedule it to run every weekend, but I questioned if is it enough?
> What the procedure detailed above give me more than this maintenance
> plan?
>
> Regards,
> Marcos
>
There are basically four things that will cause a database to "slow down":
1. Bad queries - poorly written code, cursors, lack of supporting
indexes, these things are by far the most common reason for a database
to suddenly slow down.
2. Index fragmentation - as indexes grow due to new data being added to
tables, they suffer from page splits. These page splits lead to
fragmented indexes, which hinder performance because the query engine
has to jump around looking for the next index page. DBCC DBREINDEX will
rebuild those indexes, putting them back together into contiguous blocks.
3. Disk fragmentation - repeated shrinking/growing of a database or
transaction log file will cause it to become fragmented on disk. Just
as index fragmentation hurts performance, so do physical fragmentation.
This can be prevented by properly sizing the data files so that they
don't need to grow often, and NEVER shrink them.
4. Outdated statistics - when decided what indexes to use, the query
engine looks at database statistics to get a sampling of how data is
distributed in the tables. If these stats are not current, then indexes
can't be fully utilized, leading to table or index scans, which are the
slowest form of data access. Generally, turning on the auto-stats
options on your databases will be enough to prevent this, but
inserting/deleting large amounts of data may require manually updating
the stats.
That's it. If you make it a point to stay on top of those four things,
you should not see a performance degradation. You can easily manage
these without a maintenance plan. There is nothing in those wizards
that will make up for insufficient hardware, they simply provide a
point-and-click front-end for managing the 4 tasks above (also backups
and CHECKDB). Learn how to manage these things without the aid of the
wizards, you'll learn alot, and be better equipped to deal with problems
when they do arise.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 07 Jul 2006 08:26:25 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>There are basically four things that will cause a database to "slow down":
2gb database might "speed up" if you run it on machines with at least
that much RAM, which maybe you (OP) haven't been doing?
Josh