I'm modifying a huge table containing approx 69 million records.The
table has around 25 columns mostly of datatype guid and few nvarchar.
The table has got 3 non-clustered indexes and one clustered index. I'm
using ALTER table command to delete few columns.
Also on a similar table having 4 columns but 50 million records, I'm
trying to use ALTER table command for changing datatype from nvarchar
(3999) to nvarchar(max). The total time taken for execution of both
these commands exceeds 1.25 hrs.

Is there any way to improve performance of ALTER table commands. Any
help appreciated!

Thanks in advance,
Umesh

There are a lot of variables that will make these Alter statements make multiple passes through your table and make heavy use of TempDB and depending on efficiency of TempDB it could be very slow. Examples include whether or not the column you are changing is in the index (especally clustered index since non-clustering key carries the clustering index). There is no real way to tell you that my following recommendation is quicker without monitoring your box so you may have to try it. Create new table with changes and Insert into select from with minimal logging if using SS2008. Drop old table and rename new table to old table name.

Adding a new column that is NOT NULL (which requires a default value) will be very painful, since SQL Server will have to insert the default value into all of the existing rows when you add the column.

In many cases, you are much better off to create a new table with the desired schema, and then copy all of the data from the old table into it. These two SQLCAT blog posts explain which types of schema changes are the most expensive.

http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx

http://blogs.msdn.com/sqlcat/archive/2006/03/31/566046.aspx

---

http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.

Umesh,

Adding few more details..

During this whole operation, the table will be locked until the statement completes.

Buffer pool is limited and every piece of data that you read/write has to go thru buffer pool and you have already mentioned the table has lot of GUID's which take 16 bytes. With the information on the number of columns and the datatypes, the table size seems to be over 300+Gigs. And it has to complete the physical IO writing back the data back to disk to complete this operation which is very intensive.

And adding not null columns using alter table on large tables is highly intensive and is not advised. This can cause heavy page splits too.
Bestway to handle these is to add the column with out NOT NULL and update the column in batches and at last set the NOT NULL property.

---

| Sankar Reddy | http://sankarreddy.spaces.live.com/ |

Whatare your ALTER TABLE script commands? Are you actually using ALTER TABLE or trying to drop the columns through the designer?

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Following are the ALTER TABLE commands

ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] [nvarchar](MAX)

ALTER TABLE [Table_Name] DROP COLUMN [Column_Name_1], [Column_Name_2]

ALTER TABLE [Table_Name] ADD [Column_Name] [uniqueidentifier] NULL

ALTER TABLE [Table_Name] ADD [Column_Name] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000')

I'm not using designer for ALTER TABLE commands. I'm running these commands using SMO .NETapplication.

There are a lot of variables that will make these Alter statements make multiple passes through your table and make heavy use of TempDB and depending on efficiency of TempDB it could be very slow. Examples include whether or not the column you are changing is in the index (especally clustered index since non-clustering key carries the clustering index). There is no real way to tell you that my following recommendation is quicker without monitoring your box so you may have to try it. Create new table with changes and Insert into select from with minimal logging if using SS2008. Drop old table and rename new table to old table name.

Adding a new column that is NOT NULL (which requires a default value) will be very painful, since SQL Server will have to insert the default value into all of the existing rows when you add the column.

In many cases, you are much better off to create a new table with the desired schema, and then copy all of the data from the old table into it. These two SQLCAT blog posts explain which types of schema changes are the most expensive.

http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx

http://blogs.msdn.com/sqlcat/archive/2006/03/31/566046.aspx

---

http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.

Umesh,

Adding few more details..

During this whole operation, the table will be locked until the statement completes.

Buffer pool is limited and every piece of data that you read/write has to go thru buffer pool and you have already mentioned the table has lot of GUID's which take 16 bytes. With the information on the number of columns and the datatypes, the table size seems to be over 300+Gigs. And it has to complete the physical IO writing back the data back to disk to complete this operation which is very intensive.

And adding not null columns using alter table on large tables is highly intensive and is not advised. This can cause heavy page splits too.
Bestway to handle these is to add the column with out NOT NULL and update the column in batches and at last set the NOT NULL property.

---

| Sankar Reddy | http://sankarreddy.spaces.live.com/ |