December 19, 2011 at 4:35 am
Hello people,
I have a question about SQL Server 2008 structure changes.
Our problem is that sql takes to much time to end a structure change,
for example,
a table with 209 fields and 24.000.000 recods, we need to append a field in the middle of the table (not in the last position)
and sql server takes about 8 hours :w00t: to end.
the hardware where the server is running is
*Dell storage with 32Gb Ram (16 for SQL) and 8 cores (5 for SQL)
*The database and logs are in diferents disk (inside the storage)
We note that the disk speed (i/o, transfer)o f the storage are very slow (25/30 mb per sec). The IT people said that this is normal for the storage :w00t::w00t::w00t:
That why I'm asking to find out if is a problem in some config of sql-
Is normal that a structure change takes sooooo long. ???
Thank for your help (ask if don't understand my english, i'm out of practice)
December 19, 2011 at 4:43 am
we need to append a field in the middle of the table (not in the last position)
Why so? What’s the harm in adding it in last position? You can set the order of columns in SELECT statement.
December 19, 2011 at 4:46 am
thanks for reply.
The reason for adding in an especific position is that we have a framework made by us, that needs certains fields in certains positions to work properly.
I know that sql will do faster if I add the fields in last position, but is normal that takes too long if I not add in the last position?
December 19, 2011 at 4:53 am
When you add a column using UI, it actually drops and recreates the table. That is why so much time is taken.
If you add the column using ALTER TABLE query, it adds the column at the end quickly.
December 19, 2011 at 4:54 am
It should not be any issue until you are referring columns by their order number in reports. All the Front End Languages support column mapping between record set & resultset
It’s very bad design at framework level & you might be in trouble (maintenance pain) in future if it’s not corrected now. If you have any control on design, please correct it.
December 19, 2011 at 5:04 am
Thanks both for reply.
When use ALTER TABLE command I don't have the chance to select the position of the column (like mysql)
For out framework is not strictly necessary the order in fields (my bad) but is a practice we mantein. The colums in certain order.
Not necessary in large tables, but we have a tool that generates abm's automaticaly, in that tables is strictly necesary the order (but thats not the point)
If there is no other options, we must add our new fields in the last, but, you think is normal that takes to long?
I'm thinking I have other kind of problem (disk performace).
December 19, 2011 at 5:09 am
It would defenitely take time in your case.
However a question, Do you have indexes on the table?
December 19, 2011 at 5:12 am
gheinze (12/19/2011)
If there is no other options, we must add our new fields in the last, but, you think is normal that takes to long?
Perfectly normal. What happens to add a column in the middle is Create new table, copy data over (and if its millions of rows that will take a long time), apply all constraints and indexes and then rename the new table.
That's why we're suggesting add the columns at the end. Depending on the properties of the column, that can be a metadata operation that takes very little time (making no actual data changes).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 5:22 am
Thanks for reply.
Yes we have indexes (32) and table partition.
That was what I want to know. So if you said that is normal, I will be a little quiet.
But I don't believe that takes too long, sorry for my insistence. You don't think it will be for the disk performance (I think so, but now you said that is normal I dont know what to think)
December 19, 2011 at 5:29 am
Why not rename the table and create a view of the same name with the columns in the required order?
December 19, 2011 at 6:33 am
I never use views, I will read some and see if it fits.
All the answers are consistent in that we have to add columns at the end-
I thinked that I was doing something wrong or something wrong in the configuration of sql or something wrong in the storage.
Thanks for all reply and help
December 19, 2011 at 7:06 am
This is what you would do and it would be transparent to any coding youve done.
exec sp_rename 'old_tablename', 'new_tablename'
GO
CREATE VIEW dbo.old_tablename AS
SELECT column1, column2, column6 /* moved column */, column3, column4, column5
FROM dbo.new_tablename
GO
/* Assign permissions to view to match what they are on table */
December 19, 2011 at 7:18 am
Thanks a lot for the example! we will use it that way if there no other solution.
December 19, 2011 at 8:32 am
gheinze (12/19/2011)
You don't think it will be for the disk performance (I think so, but now you said that is normal I dont know what to think)
It could be. It could also be CPU, memory, locks, latches or a bunch of other things. That's some investigation you'll have to do, look at wait types to start with. However recreating a 24 million row table with partitioning and 32 indexes (which sounds rather high) is not going to be quick.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 7:14 am
Thanks,
We tested the server in many aspects, and find bottlenecks with disk. If we put less memory than 16Gb the performance decrease.
However recreating a 24 million row table with partitioning and 32 indexes (which sounds rather high) is not going to be quick.
As you said, that was my first question.-
Now we are testing the server hardware performace, and sql server performance, but the recomendation of use views or put the new fields at the end is very valuable for me.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply