So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisR
It's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> for
> suddenly
> use
> enough
> would
>
|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon
|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
http://www.microsoft.com/technet/pro...idbp.mspx#EEAA
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does[vbcol=seagreen]
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
ReIndexing[vbcol=seagreen]
I[vbcol=seagreen]
anyone
>
|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
Showing posts with label indexdefrag. Show all posts
Showing posts with label indexdefrag. Show all posts
Friday, March 23, 2012
much luck with IndexDefrag?
So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> for
> suddenly
> use
> enough
> would
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA[
/url]
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
ReIndexing[vbcol=seagreen]
I[vbcol=seagreen]
anyone[vbcol=seagreen]
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> for
> suddenly
> use
> enough
> would
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA[
/url]
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
ReIndexing[vbcol=seagreen]
I[vbcol=seagreen]
anyone[vbcol=seagreen]
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
much luck with IndexDefrag?
So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
> for
>> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly
>> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
>> have the higher up's convinced to let me take a crack at it. Does anyone
> use
>> this regularly? I would imagine yes do to the benifits it has over
>> DBReIndex. But do you guys get many benifits from it? Does it defrag
> enough
>> to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
> would
>> have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> > It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> > particularly
> > for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> > which will help with concurrency. However, note it doesn't deal with
> > extent
> > hops so it doesn't remove all fragmentation.
> >
> >
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> >> So I started working for a place that hasn't done any sort of
ReIndexing
> > for
> >> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> > suddenly
> >> going to DBReIndex freaks people out. I've never done IndexDefrag, but
I
> >> have the higher up's convinced to let me take a crack at it. Does
anyone
> > use
> >> this regularly? I would imagine yes do to the benifits it has over
> >> DBReIndex. But do you guys get many benifits from it? Does it defrag
> > enough
> >> to warrant the time spent on it?
> >> Also, I would imagine that if I do it at the slowest times of day, it
> > would
> >> have a greater affect? Am I incorrect in that thinking?
> >>
> >>
> >> TIA, ChrisR
> >>
> >>
> >>
> >>
> >
> >
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
>> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
>> suddenly going to DBReIndex freaks people out. I've never done
>> IndexDefrag, but I have the higher up's convinced to let me take a crack
>> at it. Does anyone use this regularly? I would imagine yes do to the
>> benifits it has over DBReIndex. But do you guys get many benifits from
>> it? Does it defrag enough to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
>> would have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
> for
>> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly
>> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
>> have the higher up's convinced to let me take a crack at it. Does anyone
> use
>> this regularly? I would imagine yes do to the benifits it has over
>> DBReIndex. But do you guys get many benifits from it? Does it defrag
> enough
>> to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
> would
>> have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> > It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> > particularly
> > for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> > which will help with concurrency. However, note it doesn't deal with
> > extent
> > hops so it doesn't remove all fragmentation.
> >
> >
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> >> So I started working for a place that hasn't done any sort of
ReIndexing
> > for
> >> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> > suddenly
> >> going to DBReIndex freaks people out. I've never done IndexDefrag, but
I
> >> have the higher up's convinced to let me take a crack at it. Does
anyone
> > use
> >> this regularly? I would imagine yes do to the benifits it has over
> >> DBReIndex. But do you guys get many benifits from it? Does it defrag
> > enough
> >> to warrant the time spent on it?
> >> Also, I would imagine that if I do it at the slowest times of day, it
> > would
> >> have a greater affect? Am I incorrect in that thinking?
> >>
> >>
> >> TIA, ChrisR
> >>
> >>
> >>
> >>
> >
> >
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
>> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
>> suddenly going to DBReIndex freaks people out. I've never done
>> IndexDefrag, but I have the higher up's convinced to let me take a crack
>> at it. Does anyone use this regularly? I would imagine yes do to the
>> benifits it has over DBReIndex. But do you guys get many benifits from
>> it? Does it defrag enough to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
>> would have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>
Friday, March 9, 2012
mssql2k: rebuilding vs reorganizing indexes?
Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
REINDEX?
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>This article is for 2000, but the essentials applies to 2005 as well. DBREIN
DEX is REBUILD and
INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 20
05 (EE only).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Uri Dimant wrote:
> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
> REINDEX?
Hmmm. According to BOL (admittedly the one that comes with SQL Server
Express 2005), I can use, for example:
ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
or:
ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
...to reoagnize or rebuild respectively.
Is this not the case? The server in question is a production SQL Server
2000 server.
Thanks for your reply!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||Tibor Karaszi wrote:
> This article is for 2000, but the essentials applies to 2005 as well. DBRE
INDEX is REBUILD and
> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE
only).
Hi Tibor,
Sorry, which article, exactly? Were you intending to include a link?
Thanks for your reply!
Much warmth,
planetthoughtful
--
"Lost in thought"
http://www.planetthoughtful.org|||Sorry, here's the URL: http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
> Hi Tibor,
> Sorry, which article, exactly? Were you intending to include a link?
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "Lost in thought"
> http://www.planetthoughtful.org
>|||ALTER INDEX is not available in SQL 2000; it is new for SQL 2005.
USE DBCC REINDEX to rebuild and DBCC INDEXDEFRAG to reorganize, and then the
article Tibor provided is perfectly relevant.
HTH
Kalen Delaney, SQL Server MVP
<planetthoughtful@.gmail.com> wrote in message
news:1151478353.212906.225050@.i40g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> Hmmm. According to BOL (admittedly the one that comes with SQL Server
> Express 2005), I can use, for example:
> ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
> or:
> ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
> ...to reoagnize or rebuild respectively.
> Is this not the case? The server in question is a production SQL Server
> 2000 server.
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Hi All,
Just wondering if anyone can give me some general advice on the
practical differences between rebuilding and reorganizing indexes in
sql server 2000?
Basically, we have a database that occupies approx 28gb, of which
approximately 4gb is indexes. In the 2 or so years that our app has
been using this server, the indexes have never been rebuilt /
reoganized to the best of our knowledge.
In particular, we are hoping to minimize downtime while we see to this,
so I'm wondering if anyone has any advice to give about whether a
rebuild is generally quicker than a reorganize, or vice versa?
Any help / advice appreciated!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||This article is for 2000, but the essentials applies to 2005 as well. DBREIN
DEX is REBUILD and
INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 20
05 (EE only).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Uri Dimant wrote:
> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
> REINDEX?
Hmmm. According to BOL (admittedly the one that comes with SQL Server
Express 2005), I can use, for example:
ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
or:
ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
...to reoagnize or rebuild respectively.
Is this not the case? The server in question is a production SQL Server
2000 server.
Thanks for your reply!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||Tibor Karaszi wrote:
> This article is for 2000, but the essentials applies to 2005 as well. DBRE
INDEX is REBUILD and
> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE
only).
Hi Tibor,
Sorry, which article, exactly? Were you intending to include a link?
Thanks for your reply!
Much warmth,
planetthoughtful
--
"Lost in thought"
http://www.planetthoughtful.org
REINDEX?
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>This article is for 2000, but the essentials applies to 2005 as well. DBREIN
DEX is REBUILD and
INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 20
05 (EE only).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Uri Dimant wrote:
> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
> REINDEX?
Hmmm. According to BOL (admittedly the one that comes with SQL Server
Express 2005), I can use, for example:
ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
or:
ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
...to reoagnize or rebuild respectively.
Is this not the case? The server in question is a production SQL Server
2000 server.
Thanks for your reply!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||Tibor Karaszi wrote:
> This article is for 2000, but the essentials applies to 2005 as well. DBRE
INDEX is REBUILD and
> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE
only).
Hi Tibor,
Sorry, which article, exactly? Were you intending to include a link?
Thanks for your reply!
Much warmth,
planetthoughtful
--
"Lost in thought"
http://www.planetthoughtful.org|||Sorry, here's the URL: http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
> Hi Tibor,
> Sorry, which article, exactly? Were you intending to include a link?
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "Lost in thought"
> http://www.planetthoughtful.org
>|||ALTER INDEX is not available in SQL 2000; it is new for SQL 2005.
USE DBCC REINDEX to rebuild and DBCC INDEXDEFRAG to reorganize, and then the
article Tibor provided is perfectly relevant.
HTH
Kalen Delaney, SQL Server MVP
<planetthoughtful@.gmail.com> wrote in message
news:1151478353.212906.225050@.i40g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> Hmmm. According to BOL (admittedly the one that comes with SQL Server
> Express 2005), I can use, for example:
> ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
> or:
> ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
> ...to reoagnize or rebuild respectively.
> Is this not the case? The server in question is a production SQL Server
> 2000 server.
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Hi All,
Just wondering if anyone can give me some general advice on the
practical differences between rebuilding and reorganizing indexes in
sql server 2000?
Basically, we have a database that occupies approx 28gb, of which
approximately 4gb is indexes. In the 2 or so years that our app has
been using this server, the indexes have never been rebuilt /
reoganized to the best of our knowledge.
In particular, we are hoping to minimize downtime while we see to this,
so I'm wondering if anyone has any advice to give about whether a
rebuild is generally quicker than a reorganize, or vice versa?
Any help / advice appreciated!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||This article is for 2000, but the essentials applies to 2005 as well. DBREIN
DEX is REBUILD and
INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 20
05 (EE only).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Uri Dimant wrote:
> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
> REINDEX?
Hmmm. According to BOL (admittedly the one that comes with SQL Server
Express 2005), I can use, for example:
ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
or:
ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
...to reoagnize or rebuild respectively.
Is this not the case? The server in question is a production SQL Server
2000 server.
Thanks for your reply!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||Tibor Karaszi wrote:
> This article is for 2000, but the essentials applies to 2005 as well. DBRE
INDEX is REBUILD and
> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE
only).
Hi Tibor,
Sorry, which article, exactly? Were you intending to include a link?
Thanks for your reply!
Much warmth,
planetthoughtful
--
"Lost in thought"
http://www.planetthoughtful.org
Subscribe to:
Posts (Atom)