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, 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...
>