ALTER how long should it take?

The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PERSON ADD
FL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFY
DEFAULT '',
CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEED
DEFAULT 0
GO
COMMIT

Thanks for any tips on this issue....

Re: ALTER how long should it take?

(cuneyt.barutcu@illinois.gov) writes:
> The following ALTER takes about 2 hours in my environment. total
> number of records is about 2.8 million. IS this typical? Is there a
> way to speed up this process.

When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that does take some time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.

I don't have much experience of ALTER TABLE myself, because I almost
always take the long way in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.

This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.

But there is certainly one thing you should check for: blocking. Maybe
some other process is blocking ALTER TABLE from running at all. Check this
with sp_who.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: ALTER how long should it take?

On Jun 8, 4:17 pm, Erland Sommarskog wrote:
> (cuneyt.baru...@illinois.gov) writes:
> > The followingALTERtakes about 2 hours in my environment. total
> > number of records is about 2.8 million. IS this typical? Is there a
> > way to speed up this process.
>
> When you add non-nullable columns, SQL Server needs to rebuild the entire
> table to make room for the columns, and that doestakesome time. But
> I two hours for 2.8 million rows is more than I execpt. Then again,
> it depends not only on the number of the rows, but also how wide they
> are.
>
> I don't have much experience ofALTERTABLE myself, because I almost
> alwaystakethelongway in my update scripts. That is, I rename the
> existing table, create the table with the new definition, copy the
> data, recreate indexes, triggers, and foreign keys, move referencing
> foreign keys to the new table and finally drop the old definition.
> When I copy data, I have a loop, so that I copy some 50000 rows at
> a time.
>
> This way of altering a table gives more flexibility to place columns
> where you want, or make changes like replacing a bit column with
> a char(1) column. But it also requires more care, since there are
> so many steps. I have a tool that generates this for me. If you do it
> by hand, you have to be very careful.
>
> But there is certainly one thing youshouldcheck for: blocking. Maybe
> some other process is blockingALTERTABLE from running at all. Check this
> with sp_who.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools. Can you also tell me how long typically takes for you
to administer this type of change.
I appreciate your help. Thanks again.

Re: ALTER how long should it take?

(cuneyt.barutcu@illinois.gov) writes:
> Thanks a lot for your answer Erland,
> I was wondering about the tool you were using to accomplish the tasks
> you mentioned. Can you tell me what it is called. and the names of
> similar tools.

It's an inhouse tool that I developed myself.

As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.

Many people use Red Gate's SQL Compare to generate their change scripts.

There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.

> Can you also tell me how long typically takes for you
> to administer this type of change.

There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the production databases
there is an unknown number of test databases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: ALTER how long should it take?

On Jun 11, 5:28 pm, Erland Sommarskog wrote:
> (cuneyt.baru...@illinois.gov) writes:
> > Thanks a lot for your answer Erland,
> > I was wondering about the tool you were using to accomplish the tasks
> > you mentioned. Can you tell me what it is called. and the names of
> > similar tools.
>
> It's an inhouse tool that I developed myself.
>
> As for commercial tools on the market, I don't have a very good overview
> what is available. But Microsoft offers "DataDude", that is Visual Studio
> Team Suite for Database Professionals. I believe the price tag is hefty.
>
> Many people use Red Gate'sSQLCompareto generate their change scripts.
>
> There is something called SQLFarms, which looks interesting, but I have
> looked very very little on it.
>
> > Can you also tell me how long typically takes for you
> > to administer this type of change.
>
> There are two steps: 1) Implement the change script. 2) Running it.
> Implementing the change script takes quite some time. But I usually
> implement a whole bunch of changes at a time. Our system is a product,
> which runs at some 20 customer sites, and beside the productiondatabases
> there is an unknown number of testdatabases. How long time it takes
> running the change script depends on the size of the data base. We are
> lucky in that our customers are not 24/7 shops, but if a script needs
> to run for 24 hours, this is permissible. Again, keep in mind that a
> script includes several table changes. Typically I would not accept two
> hours to reload 2.8 million rows.
>
> --
> Erland Sommarskog,SQLServerMVP, esq...@sommarskog.se
>
> Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi there,

you may want to check out our xSQL Object (http://
www.xsqlsoftware.com) for generating those change scripts - we have a
free lite edition available also.

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com