Fast updates in SQL Server

Hi
In Oracle there is a concept that allows one to perform an update without
using the rollback logs so you can try to improve the performance of an
update. Does such functionality exist in SQL Server 2000? I cannot seem to
find anything on it in BOL
Thanks
NHi
In SQL Server every DML operations are logged. If you explain us a little
bit more about your requiremnts we will be able to help you .
How much data are you going to update?
Do you have any indexes defined on the table?
"Nesaar" <nesaarATprescientdotcodotza> wrote in message
news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi
> In Oracle there is a concept that allows one to perform an update without
> using the rollback logs so you can try to improve the performance of an
> update. Does such functionality exist in SQL Server 2000? I cannot seem to
> find anything on it in BOL
> Thanks
> N
>
>|||Look at this thread there, for some operations there is a minimized
transaction protocol, like TRUNCATE. Other details were discussed in
here:
http://www.mcse.ms/archive89-2005-2-1441624.html
HTH, jens Suessmeyer.|||He has a temp table that has about 25 million rows in it. This is joined to
a transaction table with about 110 million rows on it. The transaction table
has a clustered index and a few other indexes as well.
I have just told the developer he should create an index on his temporary
table for the columns he is joining on as a first step to try and increase
performance.
Thanks
N
I've created a temp table to store transactions (+-25mill rows)
then it joins from there onto our transaction table (110 mill rows; lots of
indexes) to update.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uhRnAM$HGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi
> In SQL Server every DML operations are logged. If you explain us a little
> bit more about your requiremnts we will be able to help you .
> How much data are you going to update?
> Do you have any indexes defined on the table?
>
>
> "Nesaar" <nesaarATprescientdotcodotza> wrote in message
> news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
without
to
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
--
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>> bummer.
>> In that case, maybe I should ask for advice on how to do this. I'm
>> trying to add a new column to a table that has 9 cols and 34 million
>> records. I'm trying to add an identity col called RowID, using alter
>> table. Is there a better way to do this? Should I drop all indexes
>> before running this command?
>> Thanks, Andre
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
Andre
> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>
|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre
|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>
|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>
|||Hi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>

fast kill/rollback

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.
Thanks in advance.
AndreHi
Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).
It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.
It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.
You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.
The only way to avoid the rollback is to restore from backup.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uinG%23kWDGHA.272@.TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
> Thanks in advance.
> Andre
>|||bummer.
In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?
Thanks, Andre|||Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.
Andrew J. Kelly SQL MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.
Happy Holidays
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
> bummer.
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
> Thanks, Andre
>|||I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.
I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.
Thanks again,
Andre
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@.TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Andre" <no@.spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@.TK2MSFTNGP14.phx.gbl...
>