I've been doing this DBA thing for a while. I'm having to deal with
more production issues as I'm working in more complex businesses now.
One of the systems that we recently put into production is being
maintained by a non-DBA type whom I had a recent chat with. He told me
that they've found that the performance of the system seems to degrade
over a week as queries seem to take longer to execute as the week goes
on. So to combat the performance loss, he scheduled nightly reindexing
of the database.
Nightly reindexing of a database seems okay when you have the luxury
of not being a 24x7 business use application. However, I wouldn't have
ordinarily expected such harsh degredation over a weeks time period
(40 sec delays per query issued in some cases). This is a database
that is relatively more insert/update than select/report. I'm just
curious as to what your experiences have been? Have you found cases
where you've run indexing every night? If so why? If not, do you have
any opinions on what I should take a look at to investigate the
performance decrease?
Thanks in advance,
OsolageHi
"Osolage" wrote:
> I've been doing this DBA thing for a while. I'm having to deal with
> more production issues as I'm working in more complex businesses now.
> One of the systems that we recently put into production is being
> maintained by a non-DBA type whom I had a recent chat with. He told me
> that they've found that the performance of the system seems to degrade
> over a week as queries seem to take longer to execute as the week goes
> on. So to combat the performance loss, he scheduled nightly reindexing
> of the database.
> Nightly reindexing of a database seems okay when you have the luxury
> of not being a 24x7 business use application. However, I wouldn't have
> ordinarily expected such harsh degredation over a weeks time period
> (40 sec delays per query issued in some cases). This is a database
> that is relatively more insert/update than select/report. I'm just
> curious as to what your experiences have been? Have you found cases
> where you've run indexing every night? If so why? If not, do you have
> any opinions on what I should take a look at to investigate the
> performance decrease?
> Thanks in advance,
> Osolage
>
The amount of degredation is depending on the amount of change that the data
undergoes, therefore if there are a high number of transaction on the
database you may need to re-index more often. This can be made worse by
having poorly thought out fill factors or possibly even poor programming or
datatype selection.
Look at outputing what DBCC SHOWCONTIG gives before you re-index. You may
want to try and follow something similar to the example on the DBCC
SHOWCONTIG topic in books online which will only re-index when the
fragmentation gets above a certain level.
Also watch out for database shrinking (either auto or manual) which can also
cause fragmentation.
John|||Yes, I've seen this.
Generally, there was some sloppy code and design, such that the
database ran well as long as it was squeezed into available RAM, but
with even a little fragmentation the missing indexes and bad plans
caused an exponential degradation in performance.
There was initially a lot of inserting to tables with (nonsequential)
GUID clustered PKs, after that was changed to simple identity int's
much of the problem went away.
So, it was split between some sloppy code, and some scalability issues
that called for specific redesigns.
Some of these problems can be a real pain to diagnose, when they only
occur on the production box, with full-scale data, and different
critical points because of a different processor count and different
RAM layout than any dev environments.
J.
On 29 Jan 2007 22:15:25 -0800, "Osolage" <osolage@.gmail.com> wrote:
>I've been doing this DBA thing for a while. I'm having to deal with
>more production issues as I'm working in more complex businesses now.
>One of the systems that we recently put into production is being
>maintained by a non-DBA type whom I had a recent chat with. He told me
>that they've found that the performance of the system seems to degrade
>over a week as queries seem to take longer to execute as the week goes
>on. So to combat the performance loss, he scheduled nightly reindexing
>of the database.
>Nightly reindexing of a database seems okay when you have the luxury
>of not being a 24x7 business use application. However, I wouldn't have
>ordinarily expected such harsh degredation over a weeks time period
>(40 sec delays per query issued in some cases). This is a database
>that is relatively more insert/update than select/report. I'm just
>curious as to what your experiences have been? Have you found cases
>where you've run indexing every night? If so why? If not, do you have
>any opinions on what I should take a look at to investigate the
>performance decrease?
>Thanks in advance,
>Osolage|||Osolage wrote:
> I've been doing this DBA thing for a while. I'm having to deal with
> more production issues as I'm working in more complex businesses now.
> One of the systems that we recently put into production is being
> maintained by a non-DBA type whom I had a recent chat with. He told me
> that they've found that the performance of the system seems to degrade
> over a week as queries seem to take longer to execute as the week goes
> on. So to combat the performance loss, he scheduled nightly reindexing
> of the database.
> Nightly reindexing of a database seems okay when you have the luxury
> of not being a 24x7 business use application. However, I wouldn't have
> ordinarily expected such harsh degredation over a weeks time period
> (40 sec delays per query issued in some cases). This is a database
> that is relatively more insert/update than select/report. I'm just
> curious as to what your experiences have been? Have you found cases
> where you've run indexing every night? If so why? If not, do you have
> any opinions on what I should take a look at to investigate the
> performance decrease?
> Thanks in advance,
> Osolage
>
Depends on how the indexes are defined, how many inserts/updates across
index keys are occurring, etc. Also, as John mentioned, make sure
you're not shrinking the database - that will effectively "undo" any
reindexing that you've done.
Rather than rebuild EVERY index, consider rebuilding only those that are
fragmented. Here's a script to get you started. It needs a couple of
tweaks that I haven't gotten around to making, in particular it needs to
exclude index ID = 255, but it should get you started.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You can look at Reorganize indexes and Reindex only highly fragmented
indexes.
BOL has good Example for it.
On Jan 30, 3:43 pm, JXStern <JXSternChange...@.gte.net> wrote:
> Yes, I've seen this.
> Generally, there was some sloppy code and design, such that the
> database ran well as long as it was squeezed into available RAM, but
> with even a little fragmentation the missing indexes and bad plans
> caused an exponential degradation in performance.
> There was initially a lot of inserting to tables with (nonsequential)
> GUID clustered PKs, after that was changed to simple identity int's
> much of the problem went away.
> So, it was split between some sloppy code, and some scalability issues
> that called for specific redesigns.
> Some of these problems can be a real pain to diagnose, when they only
> occur on the production box, with full-scale data, and different
> critical points because of a different processor count and different
> RAM layout than any dev environments.
> J.
> On 29 Jan 2007 22:15:25 -0800, "Osolage" <osol...@.gmail.com> wrote:
>
> >I've been doing this DBA thing for a while. I'm having to deal with
> >more production issues as I'm working in more complex businesses now.
> >One of the systems that we recently put into production is being
> >maintained by a non-DBA type whom I had a recent chat with. He told me
> >that they've found that the performance of the system seems to degrade
> >over a week as queries seem to take longer to execute as the week goes
> >on. So to combat the performance loss, he scheduled nightly reindexing
> >of the database.
> >Nightly reindexing of a database seems okay when you have the luxury
> >of not being a 24x7 business use application. However, I wouldn't have
> >ordinarily expected such harsh degredation over a weeks time period
> >(40 sec delays per query issued in some cases). This is a database
> >that is relatively more insert/update than select/report. I'm just
> >curious as to what your experiences have been? Have you found cases
> >where you've run indexing every night? If so why? If not, do you have
> >any opinions on what I should take a look at to investigate the
> >performance decrease?
> >Thanks in advance,
> >Osolage- Hide quoted text -
> - Show quoted text -|||Hi, everyone,
I thank you for your comments so far. They validate my suspicions.
The procedures for checking index fragmentation and what-not can be
very tedious. This is something I've traditionally avoided except in
serious cases, because of the tedium. I'd be more inclined to dive in
if there are tools that help speed up the discovery of problem spots.
Have any of you used any tools to help speed up the research or to
even automate the discovery of problemmatic indexes? Please share or
link to your experiences if you are willing to share. I would love to
learn from you as it will make my job easier.
Sincere thanks,
Osoalge|||On Feb 5, 4:38 pm, "Osolage" <osol...@.gmail.com> wrote:
> Hi, everyone,
> I thank you for your comments so far. They validate my suspicions.
> The procedures for checking index fragmentation and what-not can be
> very tedious. This is something I've traditionally avoided except in
> serious cases, because of the tedium. I'd be more inclined to dive in
> if there are tools that help speed up the discovery of problem spots.
> Have any of you used any tools to help speed up the research or to
> even automate the discovery of problemmatic indexes? Please share or
> link to your experiences if you are willing to share. I would love to
> learn from you as it will make my job easier.
> Sincere thanks,
> Osoalge
http://www.realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
... Try your own manual migration and come up with a white
... Read below link