SQL Server Performance

Mystery - Table design modification

Discussion in 'Performance Tuning for DBAs' started by sql_er, Dec 28, 2006.

  1. sql_er New Member

    Guys,

    I have a table with 17 columns (a mixture of int, varchar, char, datetime) and 13 million records. It has no indexes and is not referenced by any other object (i.e. it was created for the testing purposes). One of its fields is CHAR(1). I want to change it to CHAR(10). When I make a change and save the table, it takes forever to finish. More specifically, I waited for over 3 hours already and it is still not done. I am currently trying to explore the possible reasons and need advise from you guys.

    This table sits within a database which is located on a busy server.

    A few questions arose in the exploration of this issue:

    1. What happens internally when a data type is changed in a table definition? Is something dropped and recreated?

    2. I am currently thinking that there could be IO issues involved. I remember reading somewhere how one can explore IO problems, but cannot locate this article. Could anyone suggest how I can explore if I have IO issues with my server?

    3. I was considering to try to change CHAR(1) to VARCHAR(10) instead of CHAR(10). I am not sure if it would make a difference. My reasoning was that for CHAR(10), exactly 10 bytes would need to be allocated for each such record, forcing all records, for every row, to be shifted over. Is this true or it would not matter? And in general, from what I've learned, it looks like using VARCHAR is always more advantageous. Could anyone specify when using CHAR could be more advantageous?


    Any suggestion is appreciated.

    Thank you very much
  2. Argyle New Member

    Don't make the change via Enterprise Manager because Enterprise Manager will create a new table with your new definition and copy all data over. This can take a really long time.

    Make the change via Query Analyzer instead to avoid this.

  3. mmarovic Active Member

    If you have 13 millions rows with only one char long column, it is much better to change the type to varchar(10) instead of char(10). With char(10) you will have 13 million values with one usefull character and the rest padded with blanks. Alter table also should be much faster in that case (see suggestion above).
  4. MohammedU New Member

    I don't think chaging the datatype CHAR to VARCHAR is any faster because it has to remove padded blanks from 13 mil. rows...but mmarovic mentioned it better to use Varchar instead of Char...

    Where as increasing the column size with in same data type using the ALTER TABLE command is instantaneous...it will be in few milli seconds...


    Mohammed U.
  5. mmarovic Active Member

    Muhamed, I don't get what you mean. Currently there is char(1) column, so there won't be removing padded blanks, because there is no any.
  6. sql_er New Member

    Guys,

    Changing with the ALTER command cut the time in half. It is still not instantaneous, but definitely much faster. As for the CHAR and VARCHAR, I decided to keep it as CHAR (i.e. change it to CHAR(10)). The reason, as mentioned by someone on another post, is that I should not choose a data type for the sake of making a faster conversion in this case. Data type is chosen based on the business needs.

    Since we will always have 10 characters exactly populating this field, CHAR(10) is more efficient, as VARCHAR, apparently, adds extra bytes, just to keep track of the exact number of bytes within a field.

    I still need advice on identifying IO issues though. For those of you fluent with Profiler, which items would you advise to trace in order to identify possible IO problems or anything else that might cause slow performance (e.g. locks?).

    Thank you
  7. mmarovic Active Member

    quote:Originally posted by sql_er
    Since we will always have 10 characters exactly populating this field, CHAR(10) is more efficient, as VARCHAR, apparently, adds extra bytes, just to keep track of the exact number of bytes within a field.
    Except that you already have 13 millions rows with only one char in that column. In that case I guess you are going to expand current value and take more space, which is odd.
  8. sql_er New Member

    Mmarovic,

    You are correct, but there appears to be a paradox - as such, either way would have cons and pros.

    During yet another testing of changing CHAR(1) to CHAR(10) on my test table, it took about 2 hours using Enterprise Manager and 1 hour using Query Analyzer. During that time my log files grew enormously. It even led to us running out of hard disk, which further resulted in backup jobs to fail (as there was not enough hard disk).

    I am still exploring IO issues. Lately, we have the following messages very often: SQL Server has encountered 14017 occurrence(s) of IO requests taking longer than 15 seconds to complete on file (although usually it is 1 occurrence). This number of occurences is the worst one which occurs during our Defragmentation job.

    Anyone who encountered this error message before - any suggestion on most probably reason and how to explore more?


    Thanks a lot
  9. mmarovic Active Member

    quote:Originally posted by sql_er
    During yet another testing of changing CHAR(1) to CHAR(10) on my test table, it took about 2 hours using Enterprise Manager and 1 hour using Query Analyzer. During that time my log files grew enormously. It even led to us running out of hard disk, which further resulted in backup jobs to fail (as there was not enough hard disk).
    What is recovery model on this database? If you use EM script, you should modify it to insert values into new table in batches. If recovery model is full, then schedule frequent (5 minutes or so) tran log backups during that process.


    quote:
    I am still exploring IO issues. Lately, we have the following messages very often: SQL Server has encountered 14017 occurrence(s) of IO requests taking longer than 15 seconds to complete on file (although usually it is 1 occurrence). This number of occurences is the worst one which occurs during our Defragmentation job.
    Not sure about that, but probably it is issue with expanding the size of transaction log. If you do what I mentioned above you might avoid that problem too.
  10. sql_er New Member

    The recovery model on this DB is full. Are you saying that using EM script and inserting in batches should be more efficient as compared to just using the ALTER TABLE ALTER COLUMN command?

    Transaction log backups occur every 1 hour throughout the day - I guess I could schedule them more frequently during the defrag job.
  11. mmarovic Active Member

    quote:Are you saying that using EM script and inserting in batches should be more efficient as compared to just using the ALTER TABLE ALTER COLUMN command?

    It would prevent log file growth and exhausting disk space when you change the type from char(1) to char(10). However, I still don't understand why you don't change the type to varchar(10).
  12. MohammedU New Member

    When you are altering the table it runs under a single transaction using either QA or EM...
    using QA is should be much faster when you are increasing the size of the column with same data type compared with EM...

    EM creates new table copies the data, creates the indexes and renames the table... where QA is not...


    Mohammed U.
  13. sql_er New Member

    Mohammed: I am now aware of the advantage when Query Analyzer with an ALTER TABLE ALTER COLUMN is used. As stated earlier, it cut my time by 1/2, although still takes 1 hour.

    Mmarovic: As stated earlier, the reasons for sticking to Char(10) are:
    1. Logic - we know it will always be 10 chars
    2. Efficiency (in the future) - as varchar would add 2 extra bytes to every row (I believe).

    I am aware that the 2nd point will become valid only after #new rows * 2 bytes will exceed 9*13 million (which will take a while), which is why I am reconsidering using CHAR(10) and considering VARCHAR(10).


    Thanks for the suggestions
  14. jezemine New Member

    I would suggest this:

    1. bcp out the data from the table with the char(1) column.
    2. create an empty table with the char(10) column
    3. bcp in to the table you just created.
    4. drop the char(1) table
    5. rename the char(10) table

    this will certainly be faster than using EM to alter the column.

    www.elsasoft.org
  15. sql_er New Member

  16. MohammedU New Member

Share This Page