Best way to handle Index Rebuilding/Reindexing.

Hi,
Currently we defrage our indexes on a regular basis and defrag then when
statics are at a certain level. What I want to find out is:
Is there a way to add speed control during rebuilding and defragging
process? What I mean by this is that typically I run this at slow times but
if something comes up and database gets busy, I want to make sure most of
the recourses (including i/o) are taken away for the defrag/reindexing
processes and given back to data processing. Perhsp if server stays busy for
a certain amount of time, simply cancel the defrag and/or reindex job. In my
environment, I simply cannot afford to not have i/o available for realtime
transactions. I need to do this for both SQL server 2000 and for SQL server
2005. Any ideas?
Thank you.Not in current versions of SQL Server. You can affect how many CPU's are
used by rebuilding indexes with the MAXDOP. So if you don't want an index
rebuild to monopolize all the processors you can set the MAXDOP to less than
the max. But in terms of I/O there is no way to govern that. Bottom line is
that if your requirements are such that you need to reindex and do other I/O
you need to ensure your hardware can handle the max load you need to
sustain.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thank you Andrew.
Is there a way to quantify non-index-rebuilt / non-index-defrag activity? I
am thinking while performing index maintenance, I can run another job that
keeps track of other traffic. If it exceeds a pre-set threshold, it will
kill index jobs.
Unfortunately changing hardware to support all my load is currently not
possible as I have over 150 SQL servers, each at a different site and
replacing them will mean $$$$$$.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
> Not in current versions of SQL Server. You can affect how many CPU's are
> used by rebuilding indexes with the MAXDOP. So if you don't want an index
> rebuild to monopolize all the processors you can set the MAXDOP to less
> than the max. But in terms of I/O there is no way to govern that. Bottom
> line is that if your requirements are such that you need to reindex and do
> other I/O you need to ensure your hardware can handle the max load you
> need to sustain.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||first, SQL 2005 Enterprise provides some option for online index operation,
these options are optimized to reduce the impact on the server.
second, when a server is "IDLE" starting an index defrag will change the
status from idle to heavy IO, so this impact your other processes. if you
want to stop the index action when there is activity, you will be stopped
because you generate activities with the defrag himself. so its hard to
detect if the activity is user based or job based.
(you can use sys.dm_exec_connections to know what is the IO generated by the
defrag process, but you have to do a lot of calculations and you have to
know what is the connectionid of the job... start to become complicated)
Maybe you can test the number of users connected to the database, if there
is too many users, wait a minute and test again...
so you have a first job which is the defrag job himself.
then you create another job, a monitoring activity job, this one will stop
the first job if there is too many users connected to the database; also you
can add a schedule to start again the first job 1hour later for example
using the monitoring job task.
you can also test the wait duration of the user sessions, if the wait
duration is too high and you defrag job is in progress, then you'll stop the
job. (sys.dm_os_waiting_tasks dynamic view)
hope this will give you some starting point...
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thank you Jeje for your suggestions. I like the wait duration concept. I
will have to give it a try.
Unfortunately, I will not b e able to use the "number of users" method. None
of my users connect directly to my databases. We have application servers
that sit in the middle. All my users connect to application server which
in-turn talk to databases. So, my number of connections are pretty much
same.
Thanks.
"Jeje" <willgart@.hotmail.com> wrote in message
news:AECDD977-BA04-46A6-8BA6-6871E2DB3786@.microsoft.com...
> first, SQL 2005 Enterprise provides some option for online index
> operation, these options are optimized to reduce the impact on the server.
> second, when a server is "IDLE" starting an index defrag will change the
> status from idle to heavy IO, so this impact your other processes. if you
> want to stop the index action when there is activity, you will be stopped
> because you generate activities with the defrag himself. so its hard to
> detect if the activity is user based or job based.
> (you can use sys.dm_exec_connections to know what is the IO generated by
> the defrag process, but you have to do a lot of calculations and you have
> to know what is the connectionid of the job... start to become
> complicated)
>
> Maybe you can test the number of users connected to the database, if there
> is too many users, wait a minute and test again...
> so you have a first job which is the defrag job himself.
> then you create another job, a monitoring activity job, this one will stop
> the first job if there is too many users connected to the database; also
> you can add a schedule to start again the first job 1hour later for
> example using the monitoring job task.
> you can also test the wait duration of the user sessions, if the wait
> duration is too high and you defrag job is in progress, then you'll stop
> the job. (sys.dm_os_waiting_tasks dynamic view)
> hope this will give you some starting point...
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||Killing a DBREINDEX job will roll back all the changes so it would probably
be better to let it finish. If you go the DEFRAG route you can stop that at
any time with no ill effect. But be aware that this command is always fully
logged and depending on how fragmented the table is to begin with it may log
much more than the index size itself when running. The Defrag operation is
always single threaded so you don't have CPU contention on servers with
multiple CPU's (not counting HT) but you still have lots of I/O. The best
defense for this kind of issue is to make sure the log file are on their own
Raid 1 or 10 and the data is on another Raid 10. If tempdb is on yet another
raid you can use the sort in Tempdb option for the rebuilds. But again the
bottom line is the hardware must be up to the task, you can't squeeze blood
from a stone. Another key to minimizing these types of issues are to only
rebuild or defrag the indexes that absolutely need it. Don't rebuild them
all each time. Look first to see which are fragmented beyond a certain point
and only do those. There are examples in BOL under DBCC SHOWCONTIG and the
2005 replacement for how to do this.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
> Thank you Andrew.
> Is there a way to quantify non-index-rebuilt / non-index-defrag activity?
> I am thinking while performing index maintenance, I can run another job
> that keeps track of other traffic. If it exceeds a pre-set threshold, it
> will kill index jobs.
> Unfortunately changing hardware to support all my load is currently not
> possible as I have over 150 SQL servers, each at a different site and
> replacing them will mean $$$$$$.
> Thanks.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's are
>> used by rebuilding indexes with the MAXDOP. So if you don't want an index
>> rebuild to monopolize all the processors you can set the MAXDOP to less
>> than the max. But in terms of I/O there is no way to govern that. Bottom
>> line is that if your requirements are such that you need to reindex and
>> do other I/O you need to ensure your hardware can handle the max load you
>> need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Thank you Andrew.
Currently we have a scheduled job that determines whether or not an index
needs to be defraged. If it does, it performs the defrag. Rebuilding is done
manually only. I would like to add some sort of intelligence into my defrag
script.
Say for example, I have 100 indexes on between all my databases and scripts
see that 5 of them need to be defraged. This might not be an issue but if
one day 20 of them need to be defraged, this might be an issue. Is there any
other way to control I/O a little better? I am not CPU bound by any mean on
any of my sites. Disk bound? Yes for sure. But short of replacing all
existing systems and adding clusters ($ in millions), I need to come up with
some method better than what I have.
Thanks for any help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Killing a DBREINDEX job will roll back all the changes so it would
> probably be better to let it finish. If you go the DEFRAG route you can
> stop that at any time with no ill effect. But be aware that this command
> is always fully logged and depending on how fragmented the table is to
> begin with it may log much more than the index size itself when running.
> The Defrag operation is always single threaded so you don't have CPU
> contention on servers with multiple CPU's (not counting HT) but you still
> have lots of I/O. The best defense for this kind of issue is to make sure
> the log file are on their own Raid 1 or 10 and the data is on another Raid
> 10. If tempdb is on yet another raid you can use the sort in Tempdb option
> for the rebuilds. But again the bottom line is the hardware must be up to
> the task, you can't squeeze blood from a stone. Another key to minimizing
> these types of issues are to only rebuild or defrag the indexes that
> absolutely need it. Don't rebuild them all each time. Look first to see
> which are fragmented beyond a certain point and only do those. There are
> examples in BOL under DBCC SHOWCONTIG and the 2005 replacement for how to
> do this.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag activity?
>> I am thinking while performing index maintenance, I can run another job
>> that keeps track of other traffic. If it exceeds a pre-set threshold, it
>> will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's are
>> used by rebuilding indexes with the MAXDOP. So if you don't want an
>> index rebuild to monopolize all the processors you can set the MAXDOP to
>> less than the max. But in terms of I/O there is no way to govern that.
>> Bottom line is that if your requirements are such that you need to
>> reindex and do other I/O you need to ensure your hardware can handle the
>> max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||As has already been noted, once you're in a rebuild, not much. However, I've
been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY & @.@.IO_BUSY
to determin server load before starting something running. Haven't tried it
yet.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thanks Jay.
Do you know of a way to pause defrag? Perhaps I can put it through a loop
and let it run for a few seconds and then pause it for a second so that
other transactions can get through... Just an idea.
"Jay" <spam@.nospam.org> wrote in message
news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
> As has already been noted, once you're in a rebuild, not much. However,
> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
> @.@.IO_BUSY to determin server load before starting something running.
> Haven't tried it yet.
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||not possible to pause a defrag.
use the reorganize option:
alter index reorganize
which is an online operation and don't lock the end user.
or use
alter index rebuild with (online = on)
I don't remember which one use a transaction and which one don't use a
transaction.
so without a transaction, stopping the defrag process don't loose the
current defragmented pages, while in a transaction... its lost.
online operation are designed to reduce the impact to the end user, there is
no lock.
so if you want to pause the defrag process for just 1 second, its not
relevant to try to stop the process.
do you suffer locking issues today when you do your defrag?
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
> Thanks Jay.
> Do you know of a way to pause defrag? Perhaps I can put it through a loop
> and let it run for a few seconds and then pause it for a second so that
> other transactions can get through... Just an idea.
> "Jay" <spam@.nospam.org> wrote in message
> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Well a Cluster will do absolutely nothing for performance, it is a hardware
fail over option only. Not being CPU bound is usually another indication
that the disks are a problem. If the data can't read or write fast enough
then the CPU's can't work as hard. You can't reduce the I/O that is required
for a Defrag operation when it is doing work. As I stated you can stop the
defrag process at any time. You may want to run it for a period of time and
wait x many seconds or minutes in between to give others a chance to catch
up.
What is the current disk situation like? How many drives, what type of
arrays, where are the files etc?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Thank you Andrew.
> Currently we have a scheduled job that determines whether or not an index
> needs to be defraged. If it does, it performs the defrag. Rebuilding is
> done manually only. I would like to add some sort of intelligence into my
> defrag script.
> Say for example, I have 100 indexes on between all my databases and
> scripts see that 5 of them need to be defraged. This might not be an issue
> but if one day 20 of them need to be defraged, this might be an issue. Is
> there any other way to control I/O a little better? I am not CPU bound by
> any mean on any of my sites. Disk bound? Yes for sure. But short of
> replacing all existing systems and adding clusters ($ in millions), I need
> to come up with some method better than what I have.
> Thanks for any help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this command
>> is always fully logged and depending on how fragmented the table is to
>> begin with it may log much more than the index size itself when running.
>> The Defrag operation is always single threaded so you don't have CPU
>> contention on servers with multiple CPU's (not counting HT) but you still
>> have lots of I/O. The best defense for this kind of issue is to make sure
>> the log file are on their own Raid 1 or 10 and the data is on another
>> Raid 10. If tempdb is on yet another raid you can use the sort in Tempdb
>> option for the rebuilds. But again the bottom line is the hardware must
>> be up to the task, you can't squeeze blood from a stone. Another key to
>> minimizing these types of issues are to only rebuild or defrag the
>> indexes that absolutely need it. Don't rebuild them all each time. Look
>> first to see which are fragmented beyond a certain point and only do
>> those. There are examples in BOL under DBCC SHOWCONTIG and the 2005
>> replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want an
>> index rebuild to monopolize all the processors you can set the MAXDOP
>> to less than the max. But in terms of I/O there is no way to govern
>> that. Bottom line is that if your requirements are such that you need
>> to reindex and do other I/O you need to ensure your hardware can handle
>> the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to make
>> sure most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford
>> to not have i/o available for realtime transactions. I need to do this
>> for both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||Since I have so many SQL servers at various different sites installed over
the years, each site is different when it comes to disk situcation. In old
days, we used Legato based clustering which is a disk hog on its own. In
that case we may have only 6 disks in a flat raid 5. I know this is probably
the worst setup you can do, but this is how it is on those sites and short
of adding an external array with more disks, there is only so much I can do.
Newer sites use MS clustering and typically have 14-24 disks in various
config.
My concerns is not with sites that are on MS Clustering, but instead with
older sites (with Legato Clustering). In most cases each server is dual CPU
single core or dual core in newer sites. So I am sure none of the sites are
CPU bound. Disk bound is a different issue.All my Legato based sites are
disk bound. In Legato clusters, there are no shared disks but instead one of
the logical drives is mirrored accross servers to create a cluster. This
mirroring create additional load on the i/o subsystem. This is why I said
that short of installing new clusters, I can't do much. All my sql servers
are some type of cluster for redundency. Most are still on SQL 2000.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Well a Cluster will do absolutely nothing for performance, it is a
> hardware fail over option only. Not being CPU bound is usually another
> indication that the disks are a problem. If the data can't read or write
> fast enough then the CPU's can't work as hard. You can't reduce the I/O
> that is required for a Defrag operation when it is doing work. As I stated
> you can stop the defrag process at any time. You may want to run it for a
> period of time and wait x many seconds or minutes in between to give
> others a chance to catch up.
> What is the current disk situation like? How many drives, what type of
> arrays, where are the files etc?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an index
>> needs to be defraged. If it does, it performs the defrag. Rebuilding is
>> done manually only. I would like to add some sort of intelligence into my
>> defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this command
>> is always fully logged and depending on how fragmented the table is to
>> begin with it may log much more than the index size itself when running.
>> The Defrag operation is always single threaded so you don't have CPU
>> contention on servers with multiple CPU's (not counting HT) but you
>> still have lots of I/O. The best defense for this kind of issue is to
>> make sure the log file are on their own Raid 1 or 10 and the data is on
>> another Raid 10. If tempdb is on yet another raid you can use the sort
>> in Tempdb option for the rebuilds. But again the bottom line is the
>> hardware must be up to the task, you can't squeeze blood from a stone.
>> Another key to minimizing these types of issues are to only rebuild or
>> defrag the indexes that absolutely need it. Don't rebuild them all each
>> time. Look first to see which are fragmented beyond a certain point and
>> only do those. There are examples in BOL under DBCC SHOWCONTIG and the
>> 2005 replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>> an index rebuild to monopolize all the processors you can set the
>> MAXDOP to less than the max. But in terms of I/O there is no way to
>> govern that. Bottom line is that if your requirements are such that
>> you need to reindex and do other I/O you need to ensure your hardware
>> can handle the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to
>> make sure most of the recourses (including i/o) are taken away for
>> the defrag/reindexing processes and given back to data processing.
>> Perhsp if server stays busy for a certain amount of time, simply
>> cancel the defrag and/or reindex job. In my environment, I simply
>> cannot afford to not have i/o available for realtime transactions. I
>> need to do this for both SQL server 2000 and for SQL server 2005. Any
>> ideas?
>> Thank you.
>>
>>
>>
>|||Thank you jeje. Unfortunately online reindexing is only available in SQL
2005 Ent. Most of my servers are still on SQL 2000 Std.
I experience a lot of delays in response when defrag is active. At that time
I am disk bound. I know I need faster/better disk system but until that is
done (over 100 servers), I need to find some type of software solution.
"Jeje" <willgart@.hotmail.com> wrote in message
news:36C4CB13-2DEE-4E42-8FAC-55C41AA78B23@.microsoft.com...
> not possible to pause a defrag.
> use the reorganize option:
> alter index reorganize
> which is an online operation and don't lock the end user.
> or use
> alter index rebuild with (online = on)
> I don't remember which one use a transaction and which one don't use a
> transaction.
> so without a transaction, stopping the defrag process don't loose the
> current defragmented pages, while in a transaction... its lost.
> online operation are designed to reduce the impact to the end user, there
> is no lock.
> so if you want to pause the defrag process for just 1 second, its not
> relevant to try to stop the process.
> do you suffer locking issues today when you do your defrag?
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a loop
>> and let it run for a few seconds and then pause it for a second so that
>> other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||alter index reorganize is the option which not loose any job when you stop
it.
and the BOL says it's always done online. so the online option cannot be
changed and this method will works on both std and ent. editions.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:ulAY6rY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thank you jeje. Unfortunately online reindexing is only available in SQL
> 2005 Ent. Most of my servers are still on SQL 2000 Std.
> I experience a lot of delays in response when defrag is active. At that
> time I am disk bound. I know I need faster/better disk system but until
> that is done (over 100 servers), I need to find some type of software
> solution.
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:36C4CB13-2DEE-4E42-8FAC-55C41AA78B23@.microsoft.com...
>> not possible to pause a defrag.
>> use the reorganize option:
>> alter index reorganize
>> which is an online operation and don't lock the end user.
>> or use
>> alter index rebuild with (online = on)
>> I don't remember which one use a transaction and which one don't use a
>> transaction.
>> so without a transaction, stopping the defrag process don't loose the
>> current defragmented pages, while in a transaction... its lost.
>> online operation are designed to reduce the impact to the end user, there
>> is no lock.
>> so if you want to pause the defrag process for just 1 second, its not
>> relevant to try to stop the process.
>> do you suffer locking issues today when you do your defrag?
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a
>> loop and let it run for a few seconds and then pause it for a second so
>> that other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to make
>> sure most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford
>> to not have i/o available for realtime transactions. I need to do this
>> for both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>>
>|||Well you are right in that a single Raid 5 with all the files on it is the
worst situation in terms of performance short of a single drive with no
array. Raid 5's are notoriously bad for high write type operations. How
large are these tables? If they are small enough you can try changing the
recovery mode to SIMPLE or Bulk Logged and use DBCC DBREINDEX. This will
generate minimal activity in the log unlike a Defrag which can have a
tremendous amount of activity. The operation will be cleaner and potentially
faster depending ont he table size, number of indexes and how badly
fragmented they are over a defrag operation. The down side is this is an off
line operation in 2000. But again if the tables are small enough they may
only be inaccessible for brief periods of time and cause minor blocking.
This will reduce some of the I/O due to the decreased Log activity.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:uG1ZZmY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Since I have so many SQL servers at various different sites installed over
> the years, each site is different when it comes to disk situcation. In old
> days, we used Legato based clustering which is a disk hog on its own. In
> that case we may have only 6 disks in a flat raid 5. I know this is
> probably the worst setup you can do, but this is how it is on those sites
> and short of adding an external array with more disks, there is only so
> much I can do. Newer sites use MS clustering and typically have 14-24
> disks in various config.
> My concerns is not with sites that are on MS Clustering, but instead with
> older sites (with Legato Clustering). In most cases each server is dual
> CPU single core or dual core in newer sites. So I am sure none of the
> sites are CPU bound. Disk bound is a different issue.All my Legato based
> sites are disk bound. In Legato clusters, there are no shared disks but
> instead one of the logical drives is mirrored accross servers to create a
> cluster. This mirroring create additional load on the i/o subsystem. This
> is why I said that short of installing new clusters, I can't do much. All
> my sql servers are some type of cluster for redundency. Most are still on
> SQL 2000.
> Thanks.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well a Cluster will do absolutely nothing for performance, it is a
>> hardware fail over option only. Not being CPU bound is usually another
>> indication that the disks are a problem. If the data can't read or write
>> fast enough then the CPU's can't work as hard. You can't reduce the I/O
>> that is required for a Defrag operation when it is doing work. As I
>> stated you can stop the defrag process at any time. You may want to run
>> it for a period of time and wait x many seconds or minutes in between to
>> give others a chance to catch up.
>> What is the current disk situation like? How many drives, what type of
>> arrays, where are the files etc?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an
>> index needs to be defraged. If it does, it performs the defrag.
>> Rebuilding is done manually only. I would like to add some sort of
>> intelligence into my defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this
>> command is always fully logged and depending on how fragmented the
>> table is to begin with it may log much more than the index size itself
>> when running. The Defrag operation is always single threaded so you
>> don't have CPU contention on servers with multiple CPU's (not counting
>> HT) but you still have lots of I/O. The best defense for this kind of
>> issue is to make sure the log file are on their own Raid 1 or 10 and
>> the data is on another Raid 10. If tempdb is on yet another raid you
>> can use the sort in Tempdb option for the rebuilds. But again the
>> bottom line is the hardware must be up to the task, you can't squeeze
>> blood from a stone. Another key to minimizing these types of issues are
>> to only rebuild or defrag the indexes that absolutely need it. Don't
>> rebuild them all each time. Look first to see which are fragmented
>> beyond a certain point and only do those. There are examples in BOL
>> under DBCC SHOWCONTIG and the 2005 replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently
>> not possible as I have over 150 SQL servers, each at a different site
>> and replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>> an index rebuild to monopolize all the processors you can set the
>> MAXDOP to less than the max. But in terms of I/O there is no way to
>> govern that. Bottom line is that if your requirements are such that
>> you need to reindex and do other I/O you need to ensure your hardware
>> can handle the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Currently we defrage our indexes on a regular basis and defrag then
>>> when statics are at a certain level. What I want to find out is:
>>>
>>> Is there a way to add speed control during rebuilding and defragging
>>> process? What I mean by this is that typically I run this at slow
>>> times but if something comes up and database gets busy, I want to
>>> make sure most of the recourses (including i/o) are taken away for
>>> the defrag/reindexing processes and given back to data processing.
>>> Perhsp if server stays busy for a certain amount of time, simply
>>> cancel the defrag and/or reindex job. In my environment, I simply
>>> cannot afford to not have i/o available for realtime transactions. I
>>> need to do this for both SQL server 2000 and for SQL server 2005.
>>> Any ideas?
>>>
>>> Thank you.
>>>
>>
>>
>>
>|||All my servers are in Simple mode. There is no way I have have it full and
have enough storage. Table sizes differ greatlely. It is a high transaction
environment so data gets added and changes a lot. Most of the data is
removed from the system after 3 months via (daily) automated jobs. This
keeps the system a bit lean. Total disk usage for all my databases is never
more than 50GB (per server, per site). This is 24/7/365 operation so most of
the offline stuff can't be done. I have only "slow" times but no close
times. I can probably reindex small tables but for larger ones I have to
think twice. This is why we try to defrag indexes as much as possible (only
the ones that need it) and reindex only when defrag is useless.
I know it seems like I am trying to make it hard for anyone to propose any
solution, but unfortunatley this is the environment I have to deal with.
Thanks for all your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eZVRqha8HHA.5404@.TK2MSFTNGP02.phx.gbl...
> Well you are right in that a single Raid 5 with all the files on it is the
> worst situation in terms of performance short of a single drive with no
> array. Raid 5's are notoriously bad for high write type operations. How
> large are these tables? If they are small enough you can try changing the
> recovery mode to SIMPLE or Bulk Logged and use DBCC DBREINDEX. This will
> generate minimal activity in the log unlike a Defrag which can have a
> tremendous amount of activity. The operation will be cleaner and
> potentially faster depending ont he table size, number of indexes and how
> badly fragmented they are over a defrag operation. The down side is this
> is an off line operation in 2000. But again if the tables are small enough
> they may only be inaccessible for brief periods of time and cause minor
> blocking. This will reduce some of the I/O due to the decreased Log
> activity.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:uG1ZZmY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Since I have so many SQL servers at various different sites installed
>> over the years, each site is different when it comes to disk situcation.
>> In old days, we used Legato based clustering which is a disk hog on its
>> own. In that case we may have only 6 disks in a flat raid 5. I know this
>> is probably the worst setup you can do, but this is how it is on those
>> sites and short of adding an external array with more disks, there is
>> only so much I can do. Newer sites use MS clustering and typically have
>> 14-24 disks in various config.
>> My concerns is not with sites that are on MS Clustering, but instead with
>> older sites (with Legato Clustering). In most cases each server is dual
>> CPU single core or dual core in newer sites. So I am sure none of the
>> sites are CPU bound. Disk bound is a different issue.All my Legato based
>> sites are disk bound. In Legato clusters, there are no shared disks but
>> instead one of the logical drives is mirrored accross servers to create a
>> cluster. This mirroring create additional load on the i/o subsystem. This
>> is why I said that short of installing new clusters, I can't do much. All
>> my sql servers are some type of cluster for redundency. Most are still on
>> SQL 2000.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well a Cluster will do absolutely nothing for performance, it is a
>> hardware fail over option only. Not being CPU bound is usually another
>> indication that the disks are a problem. If the data can't read or write
>> fast enough then the CPU's can't work as hard. You can't reduce the I/O
>> that is required for a Defrag operation when it is doing work. As I
>> stated you can stop the defrag process at any time. You may want to run
>> it for a period of time and wait x many seconds or minutes in between to
>> give others a chance to catch up.
>> What is the current disk situation like? How many drives, what type of
>> arrays, where are the files etc?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an
>> index needs to be defraged. If it does, it performs the defrag.
>> Rebuilding is done manually only. I would like to add some sort of
>> intelligence into my defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you
>> can stop that at any time with no ill effect. But be aware that this
>> command is always fully logged and depending on how fragmented the
>> table is to begin with it may log much more than the index size itself
>> when running. The Defrag operation is always single threaded so you
>> don't have CPU contention on servers with multiple CPU's (not counting
>> HT) but you still have lots of I/O. The best defense for this kind of
>> issue is to make sure the log file are on their own Raid 1 or 10 and
>> the data is on another Raid 10. If tempdb is on yet another raid you
>> can use the sort in Tempdb option for the rebuilds. But again the
>> bottom line is the hardware must be up to the task, you can't squeeze
>> blood from a stone. Another key to minimizing these types of issues
>> are to only rebuild or defrag the indexes that absolutely need it.
>> Don't rebuild them all each time. Look first to see which are
>> fragmented beyond a certain point and only do those. There are
>> examples in BOL under DBCC SHOWCONTIG and the 2005 replacement for how
>> to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a
>> pre-set threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently
>> not possible as I have over 150 SQL servers, each at a different site
>> and replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>>> Not in current versions of SQL Server. You can affect how many CPU's
>>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>>> an index rebuild to monopolize all the processors you can set the
>>> MAXDOP to less than the max. But in terms of I/O there is no way to
>>> govern that. Bottom line is that if your requirements are such that
>>> you need to reindex and do other I/O you need to ensure your
>>> hardware can handle the max load you need to sustain.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Currently we defrage our indexes on a regular basis and defrag then
>>> when statics are at a certain level. What I want to find out is:
>>>
>>> Is there a way to add speed control during rebuilding and
>>> defragging process? What I mean by this is that typically I run
>>> this at slow times but if something comes up and database gets
>>> busy, I want to make sure most of the recourses (including i/o) are
>>> taken away for the defrag/reindexing processes and given back to
>>> data processing. Perhsp if server stays busy for a certain amount
>>> of time, simply cancel the defrag and/or reindex job. In my
>>> environment, I simply cannot afford to not have i/o available for
>>> realtime transactions. I need to do this for both SQL server 2000
>>> and for SQL server 2005. Any ideas?
>>>
>>> Thank you.
>>>
>>>
>>
>>
>>
>>
>|||Dragon (noSpam_Badill@.hotmail.com) writes:
> All my servers are in Simple mode. There is no way I have have it full
> and have enough storage. Table sizes differ greatlely. It is a high
> transaction environment so data gets added and changes a lot. Most of
> the data is removed from the system after 3 months via (daily) automated
> jobs. This keeps the system a bit lean. Total disk usage for all my
> databases is never more than 50GB (per server, per site). This is
> 24/7/365 operation so most of the offline stuff can't be done. I have
> only "slow" times but no close times. I can probably reindex small
> tables but for larger ones I have to think twice. This is why we try to
> defrag indexes as much as possible (only the ones that need it) and
> reindex only when defrag is useless.
A 24/7/365 high-transaction environment with simple recovery? Interesting.
Very interesting. "Yes, we have a lot of transactions, but if a disk
crashes, we can easily recreate them once we restored a backup. Or we
don't care that we lose them."
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||For what ever reasons you have let the environment drive the other factors
such as recovery mode, maintenance etc. As we have been saying over and over
again you simply can't achieve peak performance and concurrency without the
proper hardware to begin with no matter how much you want to. The fact that
you have all of the files on a single Raid 5 will limit the I/O you can do
with SQL Server. At the very least you need to separate the Logs from the
Data onto their own separate physical Raid 1 or Raid 10 array. Adding just
two disks to a system like this and moving all the log files will likely
yield a tremendous gain in throughput. If these systems have caching
controllers then make sure the cache is set to 100% write back and 0% read.
Add as much cache as possible. But other than that there is little you can
do other than what has already been suggested. Erland highlights an
excellent point about the logs. Running in Simple mode can not be driven by
lack of disk space. It is the business requirements that must drive these
decisions. Can you really afford to loose a days worth of transactions?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99A56ECB396FDYazorman@.127.0.0.1...
> Dragon (noSpam_Badill@.hotmail.com) writes:
>> All my servers are in Simple mode. There is no way I have have it full
>> and have enough storage. Table sizes differ greatlely. It is a high
>> transaction environment so data gets added and changes a lot. Most of
>> the data is removed from the system after 3 months via (daily) automated
>> jobs. This keeps the system a bit lean. Total disk usage for all my
>> databases is never more than 50GB (per server, per site). This is
>> 24/7/365 operation so most of the offline stuff can't be done. I have
>> only "slow" times but no close times. I can probably reindex small
>> tables but for larger ones I have to think twice. This is why we try to
>> defrag indexes as much as possible (only the ones that need it) and
>> reindex only when defrag is useless.
> A 24/7/365 high-transaction environment with simple recovery? Interesting.
> Very interesting. "Yes, we have a lot of transactions, but if a disk
> crashes, we can easily recreate them once we restored a backup. Or we
> don't care that we lose them."
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Pause a defrag, no. Defrag one index at a time and spread the load over n
days, sure.
But then, so do you.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
> Thanks Jay.
> Do you know of a way to pause defrag? Perhaps I can put it through a loop
> and let it run for a few seconds and then pause it for a second so that
> other transactions can get through... Just an idea.
> "Jay" <spam@.nospam.org> wrote in message
> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Thank you Jay.
"Jay" <spam@.nospam.org> wrote in message
news:ezYWDxn8HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Pause a defrag, no. Defrag one index at a time and spread the load over n
> days, sure.
> But then, so do you.
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a loop
>> and let it run for a few seconds and then pause it for a second so that
>> other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||I think you have to understand the the hardware environment to decide what
can be added. Currently if I have a Legato cluster (which I do in most of my
systems) it means I do not have an external shared storage device. Legato
mirror's a server's internal drives across servers. When this was
implemented this was the best/cost saving/redundency providing solution out
there. All our new system do not have this configuration. new systems have
MS clustering with some type of shared array or SAN and have proper disk
configuration for data, logs, indexes, temp dB etc.
In older Legato system, we do have raid controllers with cache (256-512MB)
and they are currently set to 100% write. There is no possiblity of adding
additional disks as these server support a maximum of 6 disks and that's
what they got. If I want to add any additional hardware performance, I have
to go change the OS to enterprise as we as SQL to Enterprise (in SQL 2000),
add a shared disk array and create a cluster. This is a no brainer for newer
sites but older ones (and lots of them) will take time.
I know if there is no 'soft' solution or patching-up for the performance
then their isn't one. This is why I want to make sure I ask all the experts
here if there is anything I can do.
As for simple recovery vs full recover, full recovery will require me to
perform multiple backups all day long. In most cases I do not have enough
storage to make these backups. Even if I do, I am addinging additional I/O
on the disks (for backups) which in my case can be a killer. Currently I
perform one full SQL DB backups everyday which then get backed up to tape
drives. Unfortunately, I am stuck with this environment until replacement
hardware is installed at all these sites. This could be 2-3 years before all
are changed.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23JQaYgi8HHA.600@.TK2MSFTNGP05.phx.gbl...
> For what ever reasons you have let the environment drive the other factors
> such as recovery mode, maintenance etc. As we have been saying over and
> over again you simply can't achieve peak performance and concurrency
> without the proper hardware to begin with no matter how much you want to.
> The fact that you have all of the files on a single Raid 5 will limit the
> I/O you can do with SQL Server. At the very least you need to separate the
> Logs from the Data onto their own separate physical Raid 1 or Raid 10
> array. Adding just two disks to a system like this and moving all the log
> files will likely yield a tremendous gain in throughput. If these systems
> have caching controllers then make sure the cache is set to 100% write
> back and 0% read. Add as much cache as possible. But other than that there
> is little you can do other than what has already been suggested. Erland
> highlights an excellent point about the logs. Running in Simple mode can
> not be driven by lack of disk space. It is the business requirements that
> must drive these decisions. Can you really afford to loose a days worth of
> transactions?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99A56ECB396FDYazorman@.127.0.0.1...
>> Dragon (noSpam_Badill@.hotmail.com) writes:
>> All my servers are in Simple mode. There is no way I have have it full
>> and have enough storage. Table sizes differ greatlely. It is a high
>> transaction environment so data gets added and changes a lot. Most of
>> the data is removed from the system after 3 months via (daily) automated
>> jobs. This keeps the system a bit lean. Total disk usage for all my
>> databases is never more than 50GB (per server, per site). This is
>> 24/7/365 operation so most of the offline stuff can't be done. I have
>> only "slow" times but no close times. I can probably reindex small
>> tables but for larger ones I have to think twice. This is why we try to
>> defrag indexes as much as possible (only the ones that need it) and
>> reindex only when defrag is useless.
>> A 24/7/365 high-transaction environment with simple recovery?
>> Interesting.
>> Very interesting. "Yes, we have a lot of transactions, but if a disk
>> crashes, we can easily recreate them once we restored a backup. Or we
>> don't care that we lose them."
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Dragon (noSpam_Badill@.hotmail.com) writes:
> As for simple recovery vs full recover, full recovery will require me to
> perform multiple backups all day long. In most cases I do not have enough
> storage to make these backups. Even if I do, I am addinging additional I/O
> on the disks (for backups) which in my case can be a killer.
That would be log backups. Which would not take as much resources as
full backups, unless your databases are being reloaded all the time
with new data.
The real killer question is: what if a database goes belly up? What
would it cost your organisation to lose one day of data? What will
your manager say when you tell him "sorry, that data is gone forever"?
Of course, it's apparent from your description that your systems are
suffering from a severly underdimensioned budget. And I'm afraid that
the best advice I can give is that you take the political fight in
your organisation to change this - before disaster strikes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx