Adding integer column to large table SQL Server 2012 taking more time than characterHow do I...

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Adding integer column to large table SQL Server 2012 taking more time than character type. During that time database getting hang. Why is the difference. My database size is more than 200 GB and the table size is more than 10GB, number of record in that table is more than 2242607.

Adding Integer Column(null) approximate 10 min
Adding Char/varchar column(null) approximate 2 min

Posted 8-May-14 23:24pm

SunilKumarMahanta

Add a Solution

Comments

Maciej Los 9-May-14 6:34am

Does your database is indexed?

SunilKumarMahanta 16-May-14 8:18am

Yes but not for the column we are adding.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2014-05-09T03:33:00

Solution 2

Whenever we did schema changes against our tables in my previous employ, we ran scripts to drop all indexes first. You might need to check to see if you have any constraints on the table also. This link might be useful for that sqlservercentral[^] (just realised you have to register on the site - I already have - I've found it useful in the past)

Useful article from msdn blogs[^] on performance during schema changes.

As to the difference in timings based on column types ... this could just be a feature of the order you did them in (int first, varchar 2nd) in which case caching and paging, disk free space, network activity etc etc could be having an effect. Use monitoring tools to find out what is actually happening

Posted 9-May-14 3:33am

CHill60

Comments

SunilKumarMahanta 16-May-14 8:17am

I have already gone through the article, my main doubt is when we are adding nvarchar(20-250) it's adding quickly but for int(null) case it's taking much more time. For my test DB it's nearly 10 min and in production it's 5-6 hours.
During adding int column the transactional log size going top of the roof.
Is the behaviour of SQL server for character & integer data types

SunilKumarMahanta 16-May-14 8:20am

In production we are adding columns from the UI, one column at a time either nvarchar or int

SunilKumarMahanta 16-May-14 8:30am

I have tested as you said dropped all indexes, added column(time reduced by 30%), but after that recreating all indexes in production DB taking hours(as production DB size is 30TB+)
If we add total time then it's more then previous.

Maciej Los 16-May-14 12:00pm

+5!

Torakami · Answer 2 · 2014-05-09T01:47:00

Solution 1

First of all your database is so larger in size . shrink your database file and log file to reduce the size . try not store all details in log files. To maintain log file is not a great idea , retrieve data from log files is tedious job and the best practice is to take daily backup.

Posted 9-May-14 1:47am

Torakami

Comments

Maciej Los 9-May-14 8:10am

Good advice, but not directly related to the question, a4!
I agree that bad performance is the most popular reason of long time execution of ALTER TABLE statement. But why it is deffer for different data types?

SunilKumarMahanta 16-May-14 8:25am

After shrinking also it's not working already tried in test db. It only reduces the time little bit but my main concern is if any character column in getting added in 2-3 minutes for production db then why int column is taking in hours.

phil.o · Answer 3 · 2014-06-18T13:07:00

Ok, I'm so late on this one.

The difference could be due to the physical layout of data in ?df files.
It's just a guess, though; I'm only trying to understand what could eventually explain such a behaviour.

Imagine integral types are stored preferentially at the beginning of the file, opposed to varchars which would be stored at the end.
In this case, adding an integer column means inserting an integer column and the end of existing integer colums (i.e., moving all remaining data); whereas adding a varchar column only means adding a new column at the end of the file.

But, as I told, it's just a guess. I don't know the internals of MSSQL files storage, and I'm not even sure things could be described so simply/naively.

Cheers.

Adding integer column to large table SQL Server 2012 taking more time than characterHow do I...

3 solutions

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us