Best way to copy database: indexes

Showing posts with label indexes. Show all posts

Tuesday, March 20, 2012

Big Mistake? - Indexes with includes on partition tables

I recently tried adding include columns to two indexes, one on each of two
partitioned tables. The tables are modest-sized, fact tables used for
aggregation. Each week, we slide the partitions.
The slide process is normally very fast. However, after adding include
columns to the indexes, the command to split the range in the partition
functions resulted in what appears to be runaway processes that gobbled up
system resources and maxxed out the transaction log. In fact, even after
emptying the tables of data and dropping the indexes, this happened. It was
only after I deleted the index partition scheme that the problem went away.
So... is there a problem with creating indexes with include columns on
partitioned tables where the partitions will slide? Is there something
exponential that goes on?
Thanks
--
Thanks,
CGWI can't think of why simply including columns would show down the split.
Can you please post the DDL, including partition schemes and functions?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>I recently tried adding include columns to two indexes, one on each of two
> partitioned tables. The tables are modest-sized, fact tables used for
> aggregation. Each week, we slide the partitions.
> The slide process is normally very fast. However, after adding include
> columns to the indexes, the command to split the range in the partition
> functions resulted in what appears to be runaway processes that gobbled up
> system resources and maxxed out the transaction log. In fact, even after
> emptying the tables of data and dropping the indexes, this happened. It
> was
> only after I deleted the index partition scheme that the problem went
> away.
> So... is there a problem with creating indexes with include columns on
> partitioned tables where the partitions will slide? Is there something
> exponential that goes on?
> Thanks
> --
> Thanks,
> CGW|||That could ba a lot of work for both of us. Are you just mildly curious, or
do you think you could really puzzle it out?
--
Thanks,
CGW
"Dan Guzman" wrote:
> I can't think of why simply including columns would show down the split.
> Can you please post the DDL, including partition schemes and functions?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
> >I recently tried adding include columns to two indexes, one on each of two
> > partitioned tables. The tables are modest-sized, fact tables used for
> > aggregation. Each week, we slide the partitions.
> >
> > The slide process is normally very fast. However, after adding include
> > columns to the indexes, the command to split the range in the partition
> > functions resulted in what appears to be runaway processes that gobbled up
> > system resources and maxxed out the transaction log. In fact, even after
> > emptying the tables of data and dropping the indexes, this happened. It
> > was
> > only after I deleted the index partition scheme that the problem went
> > away.
> >
> > So... is there a problem with creating indexes with include columns on
> > partitioned tables where the partitions will slide? Is there something
> > exponential that goes on?
> >
> > Thanks
> > --
> > Thanks,
> >
> > CGW
>|||> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
I'm quite interested in this problem and would like to try to help you
figure out what's going on. However, I'll need DDL to do so since I can't
think of anything obvious based on the information given. The only causes I
can think of isn't related to included columns.
One gotcha I've seen is that splitting a partition function will change all
the related partition schemes. If you have multiple tables using the same
scheme, all corresponding partitions will be split. I've run into an issue
where a staging table (with same scheme) wasn't empty when the function was
split so a lot of data needed to be moved in the staging table partitions
even though no data needed to be moved into the newly created partition of
the main table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:00984490-298D-43AD-B820-BAA95680012F@.microsoft.com...
> That could ba a lot of work for both of us. Are you just mildly curious,
> or
> do you think you could really puzzle it out?
> --
> Thanks,
> CGW
>
> "Dan Guzman" wrote:
>> I can't think of why simply including columns would show down the split.
>> Can you please post the DDL, including partition schemes and functions?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:2CDF1207-375E-4ABD-9723-D69A9BE1B010@.microsoft.com...
>> >I recently tried adding include columns to two indexes, one on each of
>> >two
>> > partitioned tables. The tables are modest-sized, fact tables used for
>> > aggregation. Each week, we slide the partitions.
>> >
>> > The slide process is normally very fast. However, after adding include
>> > columns to the indexes, the command to split the range in the partition
>> > functions resulted in what appears to be runaway processes that gobbled
>> > up
>> > system resources and maxxed out the transaction log. In fact, even
>> > after
>> > emptying the tables of data and dropping the indexes, this happened. It
>> > was
>> > only after I deleted the index partition scheme that the problem went
>> > away.
>> >
>> > So... is there a problem with creating indexes with include columns on
>> > partitioned tables where the partitions will slide? Is there something
>> > exponential that goes on?
>> >
>> > Thanks
>> > --
>> > Thanks,
>> >
>> > CGWsql

Monday, March 19, 2012

Big Mistake? - Indexes with includes on partition tables

Tuesday, February 14, 2012

Best way to keep track of SQL server changes...!

What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...
I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...
Thanks in advance...
Hi,
Have you thought about server side tracing?
If you do not wish to do the above and is ready to spend some money, then
there are few third-party tools available in the market, For ex: Red-gate.
Thanks
Yogish
|||check out www.dbghost.com for SQL Server database change management with
source control intergration.
"SQLDBA" wrote:

> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...
>

Best way to keep track of SQL server changes...!

What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...
I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...
Thanks in advance...Hi,
Have you thought about server side tracing?
If you do not wish to do the above and is ready to spend some money, then
there are few third-party tools available in the market, For ex: Red-gate.
Thanks
Yogish|||check out www.dbghost.com for SQL Server database change management with
source control intergration.
"SQLDBA" wrote:

> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...
>

Best way to keep track of SQL server changes...!

What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...
I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...
Thanks in advance...Hi,
Have you thought about server side tracing?
If you do not wish to do the above and is ready to spend some money, then
there are few third-party tools available in the market, For ex: Red-gate.
--
Thanks
Yogish|||check out www.dbghost.com for SQL Server database change management with
source control intergration.
"SQLDBA" wrote:
> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...
>

Best way to keep track of SQL Server Changes

What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...

I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...

Thanks in advance..."SQLDBA" <pg.242w@.gmail.com> wrote in message
news:1104953120.142642.99410@.z14g2000cwz.googlegro ups.com...
> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...

There's lot of information on the web - Google 'sql 2000 source control' or
something similar to find more information. You might also want to check out
the thread in this group from yesterday called 'Backing up design changes',
as well as this approach from Erland:

http://www.abaris.se/abaperls/index.html

To get started quickly, you can script your objects to files using
Enterprise Manager, check them all in to VSS, then start using source
control for all changes. You'll need to do some basic work on source control
processes, but there's lot of stuff on the web, and you may already have
something in your organization that you can re-use.

It's also very useful to have some sort of comparison tool (I've used Red
Gate's SQL Compare before) to check the differences between databases, and
synchronize them as needed.

DTS is a bit more problematic, because packages are binary objects, not text
files, but DTS does support multiple package versions anyway, so you might
just decide to keep the last 5 versions in a package or whatever.

Simon|||Thank you very much... We do have VSS but my knowledge in VSS is
limited. So I would appreciate if you could give me a drill down on how
I can set it up. Is there any way I can link these scripts to SQL
Server objects so that everytime when someone try to make any changes
to these scripts, it needs to be checked out to get modified? Because I
need to enforce a method among the developers to follow...!|||SQLDBA (pg.242w@.gmail.com) writes:
> Thank you very much... We do have VSS but my knowledge in VSS is
> limited. So I would appreciate if you could give me a drill down on how
> I can set it up. Is there any way I can link these scripts to SQL
> Server objects so that everytime when someone try to make any changes
> to these scripts, it needs to be checked out to get modified? Because I
> need to enforce a method among the developers to follow...!

There might be means to in Visual Studio.

But in my opinion, that's flawed. Checking out - and in - should be
a concious decision, and the source for the check-in should be a file,
not an object in a database.

That's the core of configuration management: if you need to change a
file, you go the version-control system and check it out, do your
change, and then check it in. If the change is not in the version-
control system, it does simply not exists. All builds and deployments
are made from the version-control system.

Of course, if there is only one development database, this policy may
be somewhat difficult to enforce, not the least if people use tools
like Query Analyzer where it's easier to load a stored procedure to
the database than saving it to disk.

In our shop there are an unknown number of development databases, so it
becomes quite apparent that there must be a central clearinghouse for
the code, and that is is SourceSafe.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you all for your input. In my situation, I just need to enforce
some good practice methodology among the developers. We already have
some third party tool (Redgate SQL compare) that does a wonderful job
in migrating changes among different databases.. This is just to keep
track of who is changing what and to keep the process going without any
flaws... So anyone has any suggestions to a situation like this?
Thanks again..!

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

Tuesday, March 20, 2012

Big Mistake? - Indexes with includes on partition tables

Big Mistake? - Indexes with includes on partition tables

Monday, March 19, 2012

Big Mistake? - Indexes with includes on partition tables

Tuesday, February 14, 2012

Best way to keep track of SQL server changes...!

Best way to keep track of SQL server changes...!

Best way to keep track of SQL server changes...!

Best way to keep track of SQL Server Changes

Best way to handle Index Rebuilding/Reindexing.

Best way to copy database

Blog Archive

About Me