Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Can changing decimal precision degrade update performance?

    I have a DTS job that has been running fine for weeks. Last week we had a manual entry error that result in a value of 200 million being entered in a field that usually less than 1000. I didn't catch it until after I had increased all the decimal fields in the table from (9,2) to (14,2). Once I got that fixed updates are taking much longer. I'm not sure if there is any consistency. Today I just killed an update that was going on an hour that was taking several minutes. Yesterday it probably took 30 minutes.

    I have two tables. One is live and one is being updated. Once the updated one is finished it gets swapped with the live one. So the roles are swapped. The old live table now becomes the table being updated. Once I fixed the decimal problem and saw the performance was degraded I start messing with indexes and recreating tables. I guess to start does anyone know if changing the decimal precision could be the problem? From there I can go into more detail as to the other things I tried. Just remember things were working, I fixed the decimal and then performance was slow, then I tried indexes and stuff.

    One last note. The two tables above are updated from another table which I changed all the decimals to 14,2 as well. I thought maybe there was some conversions going on.

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Conversions would only come into play, if there are indexes on the decimal column. That is a fairly rare event, though. More likely, the table is getting fragmented to heck by the increased size of the field. On the next run of the update, run perfmon to see how many page splits you are getting. I expect the number will be significant (i.e. hundreds or thousands).

    If it is not, then it may be that the table is possibly so fragmented to the point that there may be only one row per page. If the table has millions of rows, this can be a serious problem for performance. If that is the case, you can try rebuilding all the indexes. If the increase in the row size has pushed the total row size over 4030 bytes, then you are pretty much stuck with the new performance, though.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This shouldn't have caused a problem SQL wise as far as I can see. Although you have changed the size of the columns, it is more of a change to a constraint\ meta data than anything that should affect data allocation. DECIMAL(9, 2) and DECIMAL(14, 2) should both be 9 bytes.
    http://msdn.microsoft.com/en-us/libr...SQL.80%29.aspx

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    I changed the field back to (9,2) and performance is much better. I will try the perf monitor. Pootle, I too looked at that table you referenced and I think it does have something to do with it along with what MCrowley is talking about. I believe the 9 would be in the 4 byte tier since only precision is taken into account. Then 14 would be in the second tier. I'll let everyone know what I found out. Thanks for the input.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh Lord - I can't count. Yes, that is exactly it.

    This current thread is very, very similar. See if you get any ideas:
    http://www.dbforums.com/microsoft-sq...-best-way.html

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    I ran the perfmon and did not see any page splits which makes sense because none of the indexed fields are being updated. Can you think of any other counters I could monitor?

    I tried decimal(19,2) thinking since it was the max for the 2nd tier maybe it was more efficient than going halfway with (14,2). I also tried (10,2) with no success.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The page splits would have occurred when you ran the update statement. This is a fixed precision datatype so you will not cause any splits by changing the values of the data.

    As such, your table is likely very highly fragmented.

    I whipped up some proof as much to satisfy myself that this was not simply a metadata change in the header as much as anything else:
    Code:
    USE test
    GO 
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.splitter')) 
    BEGIN
        DROP TABLE dbo.splitter
    END
    
    CREATE TABLE dbo.splitter
        (
              inty      INT             NOT NULL
            , deccy     DECIMAL (9, 2)  NOT NULL
            , filler    VARCHAR(200)    NOT NULL
            , CONSTRAINT pk_splitter PRIMARY KEY CLUSTERED (inty) WITH (FILLFACTOR = 100)
        )
    GO
    
    INSERT INTO dbo.splitter
    SELECT    numbers.number
            , numbers.number
            , REPLICATE('a', 200)
    FROM    dbo.numbers
    
    
    SELECT    object_id
            , index_type_desc
            , index_level
            , avg_fragmentation_in_percent
            , avg_page_space_used_in_percent
            , page_count
            , avg_record_size_in_bytes
    FROM    sys.dm_db_index_physical_stats(DB_ID('test'), OBJECT_ID('dbo.splitter'), DEFAULT, DEFAULT, 'DETAILED')
    
    ALTER TABLE dbo.splitter
    ALTER COLUMN deccy  DECIMAL (38, 2)  NOT NULL
    
    SELECT    object_id
            , index_type_desc
            , index_level
            , avg_fragmentation_in_percent
            , avg_page_space_used_in_percent
            , page_count
            , avg_record_size_in_bytes
    FROM    sys.dm_db_index_physical_stats(DB_ID('test'), OBJECT_ID('dbo.splitter'), DEFAULT, DEFAULT, 'DETAILED')
    
    MERGE INTO dbo.splitter  
    USING   (
                SELECT  inty  = numbers.number
                FROM    dbo.numbers
            ) AS da_source
    ON  da_source.inty  = splitter.inty
    WHEN MATCHED THEN UPDATE 
    SET     deccy   = da_source.inty;
    
    SELECT    object_id
            , index_type_desc
            , index_level
            , avg_fragmentation_in_percent
            , avg_page_space_used_in_percent
            , page_count
            , avg_record_size_in_bytes
    FROM    sys.dm_db_index_physical_stats(DB_ID('test'), OBJECT_ID('dbo.splitter'), DEFAULT, DEFAULT, 'DETAILED')

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hang on - this isn't a heap is it? If it is then you won't get page splits but you will get row overflow linky things (MCrowley can probably give us the proper name).

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Oracle does Row Chaining. SQL Server only does page splits. It does not matter if the page is a member of a heap or index. If you have a table defined with variable length fields (most notably varchar or nvarchar), and you update a table so that the data itself grows to a point where it no longer fits in the page, then a page split results.

    Code:
    create table test1
    (col1 int not null primary key,
     col2 varchar(8000))
    
    insert into test1 values (1, 'Hello')
    insert into test1 values (2, 'Later')
    
    -- First, confirm that the table in fact has only one page for the data
    select page_count, fragment_count, index_type_desc
    from sys.dm_db_index_physical_stats(db_id(), object_id('test1'), 1, 0, default)
    -- Update the table, so that the two rows combined go over the 
    -- SQL Server limit of 8060 bytes per page
    update test1
    set col2 = replicate(col2, 1000)
    -- Do we still have one page?
    select page_count, fragment_count, index_type_desc
    from sys.dm_db_index_physical_stats(db_id(), object_id('test1'), 1, 0, default)
    You can remove the primary key constraint on the table to see that it also works with heaps. In this case, the data is potentially being increased by a byte per row per column affected (careful of that multiplying effect, now). I am not sure, but I think the ALTER TABLE statement does not touch all the rows to make sure that there is enough room for the expansion, but does affect any new inserts or updates. Thus, when the DTS package runs to update a pile of values, there may be plenty of page splits, if the data is very closely packed to start with. Where that theory falls down, of course, is where he says he reverts the column type, and it all goes back to normal. With the same data, no less. You may need to run a profiler trace to see where this process is bogging down. For all we know, the DTS package itself may be choking on the memory required to hold the extra byte per column. How many columns are we talking about, anyway?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's a couple of things wrong there MCrowley.

    Your proof is what happens to variable length columns but the decimal is a fixed length column so the additional bytes are added to the row at the time of altering the table. Updating the decimal won't cause splits.
    Also, Heaps don't page split:
    Back To Basics: Heaps | SQLServerPedia

    Agreed though that this might be DTS.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Er....Heaps can suffer from page splits.

    [quote = Pootle's article]
    There is another way that can cause page splits. When an existing record that has a variable length column is updated with a larger value than the existing one, SQL Server has to move half of the records to a new page to accommodate the increased column length. [/quote]

    As mentioned above, you can delete the words "Primary Key" from my example above, and see the result of the heap's page splitting. Remember. A row in SQL Server (whether it be a row of a heap, a clustered index, or a non-clustered index) can only exist on one page.

    You are right that I am wrong about when the "new" bytes are added to the data pages. They are added immediately at the time of the ALTER TABLE command.
    Code:
    create table test1  -- 74 bytes per row
    (col1 int,
     col2 decimal (6, 2),
     col3 decimal (6, 2),
     col4 decimal (6, 2),
     col5 decimal (6, 2),
     col6 decimal (6, 2),
     col7 decimal (6, 2),
     col8 decimal (6, 2),
     col9 decimal (6, 2),
     col10 decimal (6, 2),
     col11 decimal (6, 2),
     col12 decimal (6, 2),
     col13 decimal (6, 2),
     col14 decimal (6, 2),
     col15 decimal (6, 2))
    
    insert into test1
    select top 90 object_id, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0, 1000.0
    from sys.objects
    
    select page_count, fragment_count, index_type_desc
    from sys.dm_db_index_physical_stats(db_id(), object_id('test1'), 0, 0, default) 
    
    alter table test1 alter column 	col2 decimal (21, 2)
    alter table test1 alter column 	col3 decimal (21, 2)
    alter table test1 alter column 	col4 decimal (21, 2)
    alter table test1 alter column 	col5 decimal (21, 2)
    alter table test1 alter column 	col6 decimal (21, 2)
    alter table test1 alter column 	col7 decimal (21, 2)
    alter table test1 alter column 	col8 decimal (21, 2)
    alter table test1 alter column 	col9 decimal (21, 2)
    alter table test1 alter column 	col10 decimal (21, 2)
    alter table test1 alter column 	col11 decimal (21, 2)
    alter table test1 alter column 	col12 decimal (21, 2)
    alter table test1 alter column 	col13 decimal (21, 2)
    alter table test1 alter column 	col14 decimal (21, 2)
    alter table test1 alter column 	col15 decimal (21, 2)
    
    select page_count, fragment_count, index_type_desc
    from sys.dm_db_index_physical_stats(db_id(), object_id('test1'), 0, 0, default)
    So in short, we are both right and both wrong, but what is more important is that you are wrong ;-).

  12. #12
    Join Date
    Jan 2004
    Posts
    145
    Hey guys, thanks for the input so far. Here are some more details. My table has a date field that is clustered, 4 varchar fields that along with the date are indexed and used in the joins. The fields that are updated consist of 26 Decimal(9,2) and 1 Decimal(9,6). There are a series of updates usually updating one or two fields at a time. I inherited this DTS and think it can be re-wrote, but I really want to understand what is happening as opposed to re-writing it which is what I should/will be doing. With that being said here is one update query that performance really degrades.

    Code:
    UPDATE 
    	I_DAILY_SUMMARY 
    SET  
    	I_DAILY_SUMMARY.DS_MARKETED_SALES =
    		(SELECT 
    			SUM(ISNULL(I_DS_TOTALS.DS_MS_MKTD_SALES,0)) 
    		FROM         
    			I_DS_TOTALS 
    		WHERE 
    			I_DS_TOTALS.DS_DT=I_DAILY_SUMMARY.DS_DT AND
    			I_DS_TOTALS.DS_SRC_CD = I_DAILY_SUMMARY.DS_SRC_CD AND 
    			I_DS_TOTALS.DS_ORD_METH_I = I_DAILY_SUMMARY.DS_ORD_METH_I AND 
    			I_DS_TOTALS.DS_SRC_METH_CD = I_DAILY_SUMMARY.DS_SRC_METH_CD AND 
    			I_DS_TOTALS.DS_SC_METH_I = I_DAILY_SUMMARY.DS_SC_METH_I
    			)
    FROM         
    	I_DS_TOTALS INNER JOIN I_DAILY_SUMMARY ON 
    		I_DS_TOTALS.DS_DT = I_DAILY_SUMMARY.DS_DT AND 
    		I_DS_TOTALS.DS_SRC_CD = I_DAILY_SUMMARY.DS_SRC_CD  AND
    		I_DS_TOTALS.DS_ORD_METH_I = I_DAILY_SUMMARY.DS_ORD_METH_I AND 
    		I_DS_TOTALS.DS_SRC_METH_CD = I_DAILY_SUMMARY.DS_SRC_METH_CD AND  
    		I_DS_TOTALS.DS_SC_METH_I = I_DAILY_SUMMARY.DS_SC_METH_I
    I'm pretty new to perfmon. Can either of you tell me what counters to track? Just to double check, we are all in agreement that updating these decimal fields will not cause splitting because they are fixed width correct?

    Thanks again
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Correct, but the process of altering the column will cause lots of splits. Run the sys.dm_db_index_physical_stats dmf MCrowley and I used to view your fragmentation. You are interested in the avg_fragmentation_in_percent and avg_page_space_used_in_percent columns. The former wants to be low, the latter high.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Before UPDATE can be performed, the page containing the value to be updated, along with any index pages that satisfy JOIN and WHERE clauses, have to be loaded into memory. The ALTER from dec(9,2) to dec(14,2) increased the size of each row by 104 bytes plus index-related overhead. This means that on a mass UPDATE we should see an increase of memory utilization by a multiple of 104 times the number of affected rows (of course accounting for 8K page size, because the whole page needs to be loaded even if just 1 byte is needed from the page). So, it doesn't have anything to do with page splits, but rather with memory management.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jan 2004
    Posts
    145
    rdjabarov what you are saying makes sense but I can verify it on the server. Perfmon keeps erring out on some counters and is filling the application log in event viewer. If I ever am able to confirm I'll update this thread. Does it matter that only a field or two at a time is being updated? I supposed it makes sense the whole row still has to be loaded, but I'm not sure.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •