Reorganize vs. REbuild Index Question

OK, I am assuming that by reorganizing my indexes it will do just that to
optimize the indexes vs. rebuild will do the same but it will also let me
adjust the amount of free space for the index. If I continue to use the
default free space % would these tasks not have the exact same result? Also,
the time it takes to do a reorganize vs. a rebuild would be the same? Is
there anything I am misunderstanding about this maintainenace plan task that
I want to select one or the other?DBCC DBREINDEX does a somewhat better job, but locks the table.
DBCC INDEXDEFRAG does almost as well, but can run while the users accesses
the table/index
However, I beleive you're looking at the SQL Server 2000 maintenance Plan
Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
DROP/CREATE INDEX to rebuild.
Note that the amount of benifite you'll get depends on how the indexes are
being used. OLTP, not so much, DSS potentially a lot.
I suppose they would both take the same amount of time, however, you would
not change the free space % for that reason. Change free space % to a larger
value if the table is insert heavy and to a smaller value the more static
the table is. It goes to performance when using the system.
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
> OK, I am assuming that by reorganizing my indexes it will do just that to
> optimize the indexes vs. rebuild will do the same but it will also let me
> adjust the amount of free space for the index. If I continue to use the
> default free space % would these tasks not have the exact same result?
> Also,
> the time it takes to do a reorganize vs. a rebuild would be the same? Is
> there anything I am misunderstanding about this maintainenace plan task
> that
> I want to select one or the other?
>|||> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in 2000 MP regarding how you
want the "optimization" to be done. MP does DBREINDEX for all indexes in the database.
May I also suggest for Richard below reading:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
> DBCC DBREINDEX does a somewhat better job, but locks the table.
> DBCC INDEXDEFRAG does almost as well, but can run while the users accesses the table/index
> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
> Note that the amount of benifite you'll get depends on how the indexes are being used. OLTP, not
> so much, DSS potentially a lot.
> I suppose they would both take the same amount of time, however, you would not change the free
> space % for that reason. Change free space % to a larger value if the table is insert heavy and to
> a smaller value the more static the table is. It goes to performance when using the system.
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that to
>> optimize the indexes vs. rebuild will do the same but it will also let me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result? Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same? Is
>> there anything I am misunderstanding about this maintainenace plan task that
>> I want to select one or the other?
>|||Thank you Tibor, I was close. Still wrong, but close.
I read the link a while ago and found it to be excellent reading, well worth
your time.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D4C5F3BF-ABFC-4A96-B47F-CD5467322302@.microsoft.com...
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
> DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in
> 2000 MP regarding how you want the "optimization" to be done. MP does
> DBREINDEX for all indexes in the database.
> May I also suggest for Richard below reading:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> DBCC DBREINDEX does a somewhat better job, but locks the table.
>> DBCC INDEXDEFRAG does almost as well, but can run while the users
>> accesses the table/index
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
>> Note that the amount of benifite you'll get depends on how the indexes
>> are being used. OLTP, not so much, DSS potentially a lot.
>> I suppose they would both take the same amount of time, however, you
>> would not change the free space % for that reason. Change free space % to
>> a larger value if the table is insert heavy and to a smaller value the
>> more static the table is. It goes to performance when using the system.
>> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
>> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that
>> to
>> optimize the indexes vs. rebuild will do the same but it will also let
>> me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result?
>> Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same?
>> Is
>> there anything I am misunderstanding about this maintainenace plan task
>> that
>> I want to select one or the other?
>>
>

Reorganize table

Hi there .
1. Is there any way to reorganize table ?
2. How difference between truncate table and delete * from AAA
Regards.TO re-org DBCC DBREINDEX is best method to go.

The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

Reorganize Index Task - SelectedDatabases expression format

Hi

I am trying to configure the SelectedDatabases property of the Reorganize Index Task using an expression.

The Expressions property of the task provides the ability to configure the SelectedDatabases property of the task using an expression. The properties pane shows that the type of the SelectedDatabases property should be a "(Collection)" (which is edited using the 'Object Collection Editor').

How do I create an expression to configure the SelectedDatabases property? Can I build the collection in text? Or do I need to provide a variable of type System.Object that contains a collection type (and if so exactly what type should it contain)?

TIA . . . Ed

No, you cannot execute an expression successfully for the SelectedDatabases property of the Reorganize Index Task, and to generalize, any "(Collection)" property, and even more generally, any non value-type task property (excepting strings).

The workaround for these types of properties (like the StringCollection

being referred to in this question) is to use the self-modifying

package technique as outlined here:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

Or course, one could roll a custom task with an expression-able property of type string, which depending on your familiarity with custom tasks, may be a reasonable choice.

Reorganize index pages never completes

There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury"Tracy McKibben" wrote:

> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.

> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.

> - is your database configured to auto-grow when needed?
Yes.

> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.

> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
Maury|||There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:

> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.

> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.

> - is your database configured to auto-grow when needed?
Yes.

> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.

> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:

> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.

> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury|||"Tracy McKibben" wrote:

> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.

> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury

Reorganize index pages never completes

I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
MauryThere are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and the
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury

reorganize data files

Just added a new datafile to my database. How do I distribute all the
data evenly across the two files now?
I tried dbcc dbreindex on all tables, this results in 75%/25%
I also tried creating 2 new files and emptying the main file dbcc
shrinkdb. I could not empty the main file completely though so had to
empty the 3rd file again and drop it.. this gave me a 40%/60%
Anyway to reorganize the datafiles and have 50/50 distribution?The cleanest way to ensure even distribution between all files is to export
the data, truncate the tables and import it back in again. The reason none
of the techniques worked is that SQL Server adds data to individual files
within the same file group by the percentage of free space in the file. So
if you start with 4 files of the same size and empty all the existing data
you have 4 files with equal amounts of free space. Then when you insert the
data it will use the proportional fill algorithm to fill them in in an even
manor. But when some of the files already have data in them the
distribution will be uneven due to the uneven amount of free space.
Eventually the amount of free space will even out. So if you run DBCC
DBREINDEX on all the tables many times it will eventually even out. The key
is to ensure all the files are the same size with PLENTY of free space in
each file. That way Autogrow does not kick in and mess with the even sizes.
Andrew J. Kelly SQL MVP
"Gordon Cowie" <gordy@.dynamicsdirect.com> wrote in message
news:uz%23WtZzBGHA.676@.TK2MSFTNGP10.phx.gbl...
> Just added a new datafile to my database. How do I distribute all the data
> evenly across the two files now?
> I tried dbcc dbreindex on all tables, this results in 75%/25%
> I also tried creating 2 new files and emptying the main file dbcc
> shrinkdb. I could not empty the main file completely though so had to
> empty the 3rd file again and drop it.. this gave me a 40%/60%
> Anyway to reorganize the datafiles and have 50/50 distribution?|||running dbreindex twice evened it out nicely, thanks!
Andrew J. Kelly wrote:
> The cleanest way to ensure even distribution between all files is to expor
t
> the data, truncate the tables and import it back in again. The reason none
> of the techniques worked is that SQL Server adds data to individual files
> within the same file group by the percentage of free space in the file. S
o
> if you start with 4 files of the same size and empty all the existing data
> you have 4 files with equal amounts of free space. Then when you insert th
e
> data it will use the proportional fill algorithm to fill them in in an eve
n
> manor. But when some of the files already have data in them the
> distribution will be uneven due to the uneven amount of free space.
> Eventually the amount of free space will even out. So if you run DBCC
> DBREINDEX on all the tables many times it will eventually even out. The k
ey
> is to ensure all the files are the same size with PLENTY of free space in
> each file. That way Autogrow does not kick in and mess with the even size
s.
>

Reorganize data and index pages option

We have a 40GB database and we, on a weekly basis, run a maintainance plan
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>

Reorganize data and index pages

Hello all,

SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Hi

If you profile the maintainance plan is seems that choosing causes a call to
DBCC DBREINDEX which is documented in books online. The 10% free space value
is reversed to be a 90% fillfactor as described by:

fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as
the new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.

If you look at the Table and Index Architecture section of books online you
will see how clustered and non-clustered indexes are composed.
or at:
http://msdn.microsoft.com/library/d...ar_da2_8sit.asp
http://msdn.microsoft.com/library/d...ar_da2_1tbn.asp
http://msdn.microsoft.com/library/d...ar_da2_75mb.asp

You may want to get "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 which talks about this sort of thing in detail.

John

"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:rF97e.5$Nk2.347@.news.uswest.net...
> Hello all,
> SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
> gives me an option to reorganize data and index pages. When I check on
> it, it populates the line "change free space per page percentage to" and
> puts in 10 in there. Is this the default for free space? Is it the
> data pages that will have 10% free space or just the index pages? Are
> data and index on the same pages?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reorganize data and index pages

I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
Check your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com|||There's no such functionality. If the table is a clustered index, then the i
ndex and the table is
the same. If not, there is no organization between the rows, so you would ha
ve to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...agreen">
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TAB
LE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.com|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
--
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.
Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>