cancel
Showing results for 
Search instead for 
Did you mean: 

How to prevent ALTER TABLE followed with UPDATE statement to fill log segment

Former Member
0 Kudos

-- SYBASE VERSION --

Adaptive Server Enterprise/15.7.0/EBF 24718 SMP SP64 /P/Sun_svr4/OS 5.10/ase157x/3384/64-bit/FBO/Thu Jun 25 11:22:09 2015

-- END SYBASE VERSION --

Hi all,

I request your advices to find a way to limit the increase of my log segment to complete my SQL statement.

I have a database with 40Gb of log segment with one table changing its structure.

The table information is

1> sp_spaceused TABLE_A                                                                                                     

2> go                                                                                                                           

name         rowtotal reserved    data        index_size unused                                                              

------------ -------- ----------- ----------- ---------- ------                                                                

TABLE_A 30318974 31208544 KB 30318976 KB 888360 KB  632 KB

My initial sql statements are:

alter table TABLE_A add M_COMMENT_2BT char(100) default ' ' not null

go

alter table TABLE_A replace M_COMMENT_2BT default null

go

update TABLE_A set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

go

Executing them with isql with -p and -e option gives me a total execution time of 1959862 ms.

My goal is to reduce this execution time

My remarks about the 1st SQL:

Because we're adding a 'non null' column, the ALTER will act like a SELECT INTO + INDEXES recreation. this is confirmed if I checked the values of syspartition.datoampage and syspartitions.indoampage before and after the statement --> values changed.
Moreover activating 'set statistics io' before the statements returns the following.

2> alter table TABLE_A add M_COMMENT_2BT char(100) default ' ' not null

Table: #syb__altab scan count 0, logical reads: (regular=35139295 apf=0

total=35139295), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: TABLE_A scan count 1, logical reads: (regular=7801974 apf=0

total=7801974), physical reads: (regular=3517 apf=429892 total=433409), apf IOs

used=429849

Non-clustered index (index id = 2) is being rebuilt.

Non-clustered index (index id = 3) is being rebuilt.

Total writes for this command: 1342908

Execution Time (ms.): 1617204 Clock Time (ms.): 1617204

2> update TABLE_A set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

Table: TABLE_A scan count 0, logical reads: (regular=0 apf=0 total=0),

physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: TABLE_A scan count 1, logical reads: (regular=7642524 apf=0

total=7642524), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Total writes for this command: 89284

Execution Time (ms.): 342622 Clock Time (ms.): 342622

I checked with our development team whether there's a reason to have a 'non null' column and they couldn't answer me.

Therefore I changed by original SQL with

alter table TRN_HDRF_DBF add M_COMMENT_2BT char(100)  null

go

update TRN_HDRF_DBF set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

go

This time the execution time is: 1165890 ms which is a bit better (I expected more) but this test raised two remarks

1.I faced a LOG SUSPEND during 'update TRN_HDRF_DBF set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null'.

I added 20Gb of log to my database then I executed every 30s "select lct_admin('logsegment_freepages',db_id('${DBASE}'))" in a file and analyzed the output. I reached a maximum usage of 61Gb of my logsegment.

Question: How couldd the same UPDATE statement filled my log segment? My assumption is that in this case, my ALTER statement was a fully logged operation and it filled the log segment that couldn't empied fast enough to allow the UPDATE to execute successfully. Am I right?, is there a way to prevent this behavior?

2. Looking at the timing the UPDATE statement was very long

2> update TABLE_A set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

Table: TABLE_A scan count 0, logical reads: (regular=31 apf=0 total=31),

physical reads: (regular=6 apf=0 total=6), apf IOs used=0

Table: TRN_HDRF_DBF scan count 1, logical reads: (regular=7642489 apf=0

total=7642489), physical reads: (regular=197672 apf=43521 total=241193), apf IOs

used=43521

Total writes for this command: 682190

Execution Time (ms.): 1165870 Clock Time (ms.): 1165870

Why do I have more writes? does is explain the differences?

Thanks all for your valuable inputs

Mark_A_Parsons
Active Participant
0 Kudos

I'm wondering if a 3rd option may be faster ...

====================

-- add storage settings to the select/into as needed

select *, convert(char(100) not null, isnull(M_COMMENT,'')) as M_COMMENT_2BT

into TABLE_A_new lock {allpages|datapages|datarows)

from TABLE_A

-- order by PK columns if has a clustered index ?

-- verify row counts

-- verify no issues with data

-- ?? any RI constraints to drop ??

-- drop old table, rename new table

drop table TABLE_A

exec sp_rename TABLE_A_new, TABLE_A

-- (re)grant permissions

grant ... on TABLE_A to ...

-- (re)create indexes; decide if parallel operations would be helpful?

create index ... on TABLE_A

create index ... on TABLE_A

-- recreate triggers (as needed)

create trigger ... on TABLE_A for ...

-- ?? any RI constraints to (re)create ??

-- update stats; decide if parallel/hashing/sampling options would be helpful?

update (index) statistics ...

-- if original table was marked for replication then you'll

-- need to remark the new table; also determine if any

-- repdefs/subscriptions need to be modified; and of course

-- decide on how to sync the replicate copy of the table

====================

Obviously (?) you'd want to run the above during a time when you can ensure there's no user activity (otherwise they may get unwanted errors).

Sure, a bit more work, but you're talking about less logging (a heck of a lot less logging compared to the deferred update!), and likely a reduction in total time, ymmv ...

Mark_A_Parsons
Active Participant
0 Kudos

... and if the 2nd attempt did result in a lot of page splits (or row forwardings), then your table is (likely) highly fragmented which in turn means a) requires more disk space and b) likely affects (negatively) query processing; so now you'll want/need to run a reorg (more time) followed by updating stats

Obviously (?) the 1st and 3rd options would eliminate the heavy fragmentation at the data page level, thus eliminating the need for a follow-on reorg.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi all,

I executed my test again with SHOWPLAN enabled

Another point that might have an important impact on my test. I just noticed that I forgot to bind my syslogs to my dedicated cache every time I reloaded my dump to execute the test, very bad...

First of all, below are the configuration of the different caches I'm using

1> sp_cacheconfig

2> go

Cache Name         Status   Type             Config Value   Run Value

------------------ -------- ---------------- -------------- ------------

default data cache Active   Default           81920.00 Mb    81920.00 Mb

log cache          Active   Log Only          10000.00 Mb    10000.00 Mb

tempdb cache       Active   Mixed, HK Ignore  10000.00 Mb    10000.00 Mb

                                            ------------ ------------

                            Total     101920 Mb    101920 Mb

==========================================================================

Cache: default data cache,   Status: Active,   Type: Default

      Config Size: 81920.00 Mb,   Run Size: 81920.00 Mb

      Config Replacement: strict LRU,   Run Replacement: strict LRU

      Config Partition:            4,   Run Partition:            4

(1 row affected)

IO Size  Wash Size     Config Size  Run Size     APF Percent

-------- ------------- ------------ ------------ -----------

     4 Kb     245760 Kb  51920.00 Mb  51920.00 Mb     10

    32 Kb     245760 Kb  30000.00 Mb  30000.00 Mb     10

==========================================================================

Cache: log cache,   Status: Active,   Type: Log Only

      Config Size: 10000.00 Mb,   Run Size: 10000.00 Mb

      Config Replacement: strict LRU,   Run Replacement: strict LRU

      Config Partition:            4,   Run Partition:            4

IO Size  Wash Size     Config Size  Run Size     APF Percent

-------- ------------- ------------ ------------ -----------

     4 Kb     245760 Kb   2000.00 Mb   2000.00 Mb     10

    32 Kb     245760 Kb   8000.00 Mb   8000.00 Mb     10

==========================================================================

Cache: tempdb cache,   Status: Active,   Type: Mixed, HK Ignore

      Config Size: 10000.00 Mb,   Run Size: 10000.00 Mb

      Config Replacement: strict LRU,   Run Replacement: strict LRU

      Config Partition:            4,   Run Partition:            4

IO Size  Wash Size     Config Size  Run Size     APF Percent

-------- ------------- ------------ ------------ -----------

     4 Kb     245760 Kb      0.00 Mb   7000.00 Mb     10

    32 Kb     245760 Kb   3000.00 Mb   3000.00 Mb     10

1> sp_helpcache

2> go

Cache Name         Config Size   Run Size   Overhead

------------------ ------------- ---------- ----------

default data cache 81920.0 Mb    81920.0 Mb 5152.10 Mb

log cache          10000.0 Mb    10000.0 Mb  630.51 Mb

tempdb cache       10000.0 Mb    10000.0 Mb  630.51 Mb

Memory Available For      Memory Configured

Named Caches              To Named Caches

--------------------       ----------------

101920 Mb                   101920 Mb

------------------ Cache Binding Information: ------------------

Cache Name           Entity Name                Type               Index Name                    Status

----------           -----------                ----               ----------                    ------

log cache            CONVERSION_MX.dbo.syslogs  table                                              V

tempdb cache         tempdb                     database                                           V

1> sp_logiosize 'all'

2> go

Cache name: default data cache

Data base                       Log I/O Size

------------------------------  ------------

master                           4 Kb

model                            4 Kb

TEST                             4 Kb

CONVERSION_DM                    4 Kb

sybsystemdb                      4 Kb

sybsystemprocs                   4 Kb

Cache name: log cache

Data base                       Log I/O Size

------------------------------  ------------

CONVERSION_MX                    32 Kb

Cache name: tempdb cache

Data base                       Log I/O Size

------------------------------  ------------

tempdb                           4 Kb

Here's a summary of my timing, all the details are below

SQLOriginal SQL exec (ms)Optimized SQL exec (ms)Comment
ALTER TABLE266190129
UPDATE TABLE2205221592671showplan identical for both cases
TOTAL TIMING28824231592700potential gain of 44%

My assumption about the difference in the UPDATE timing is because with the original SQL, the table is in the cache so no physical reads are necessary for the UPDATE while with the optimized SQL, the alter did not load the table in the cache therefore the update will have to read the data from the disk.

Last, maybe I should add 'set statement cache off' at the beginning of the script to make sure I'm not reusing a cached plan.

Below is the output of the original SQL executed with set showplan activated on the ALTER TABLE + UPDATE statement:

2> alter table TRN_HDRF_DBF add M_COMMENT_2BT char(100) default ' ' not null

QUERY PLAN FOR STATEMENT 1 (at line 2).

Optimized using Serial Mode

Optimized using the Abstract Plan in the PLAN clause.

    STEP 1

        The type of query is ALTER TABLE.

  2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |INSERT Operator (VA = 1)

       |   |  The update mode is direct.

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  TRN_HDRF_DBF

       |   |   |  Using Clustered Index.

       |   |   |  Index : TRN_HDRF_ND0

       |   |   |  Forward Scan.

       |   |   |  Positioning at index start.

       |   |   |  Using I/O Size 32 Kbytes for index leaf pages.

       |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.

       |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  #syb__altab

       |   |  Using I/O Size 32 Kbytes for data pages.

    STEP 2

        Catalog updates.

Table: #syb__altab scan count 0, logical reads: (regular=35139390 apf=0

total=35139390), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: TRN_HDRF_DBF scan count 1, logical reads: (regular=7801974 apf=0

total=7801974), physical reads: (regular=9897 apf=957364 total=967261), apf IOs

used=957321

The sort for TRN_HDRF_DBF is done in Serial

Non-clustered index (index id = 2) is being rebuilt.

The sort for TRN_HDRF_DBF is done in Parallel

Non-clustered index (index id = 3) is being rebuilt.

The sort for TRN_HDRF_DBF is done in Serial

Total writes for this command: 132803

(30318974 rows affected)

Execution Time (ms.): 2661901 Clock Time (ms.): 2661901

2> update TRN_HDRF_DBF set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

QUERY PLAN FOR STATEMENT 1 (at line 2).

    STEP 1

        The type of query is EXECUTE.

        Executing a previously cached statement (SSQL_ID = 2002200255).

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is UPDATE.

  2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |UPDATE Operator (VA = 1)

       |   |  The update mode is direct.

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  TRN_HDRF_DBF

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  TRN_HDRF_DBF

       |   |  Using I/O Size 4 Kbytes for data pages.

QUERY PLAN IS RECOMPILED DUE TO TABMISSING.

THE RECOMPILED QUERY PLAN IS:

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is UPDATE.

  2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |UPDATE Operator (VA = 1)

       |   |  The update mode is direct.

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  TRN_HDRF_DBF

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  TRN_HDRF_DBF

       |   |  Using I/O Size 4 Kbytes for data pages.

Table: TRN_HDRF_DBF scan count 0, logical reads: (regular=0 apf=0 total=0),

physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Table: TRN_HDRF_DBF scan count 1, logical reads: (regular=7642524 apf=0

total=7642524), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

Total writes for this command: 3719

(30318974 rows affected)

Execution Time (ms.): 220522 Clock Time (ms.): 220522

Now, below is is the output of my optimized SQL executed with set showplan activated on the ALTER TABLE + UPDATE statement:

2> alter table TRN_HDRF_DBF add M_COMMENT_2BT char(100) null

QUERY PLAN FOR STATEMENT 1 (at line 2).

    STEP 1

        The type of query is ALTER TABLE.

        TO TABLE

        TRN_HDRF_DBF

        Using I/O Size 4 Kbytes for data pages.

Total writes for this command: 2

Execution Time (ms.):     29 Clock Time (ms.):     29

2> update TRN_HDRF_DBF set M_COMMENT_2BT = M_COMMENT where M_COMMENT is not null

QUERY PLAN FOR STATEMENT 1 (at line 2).

    STEP 1

        The type of query is EXECUTE.

        Executing a previously cached statement (SSQL_ID = 2002200255).

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is UPDATE.

  2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |UPDATE Operator (VA = 1)

       |   |  The update mode is direct.

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  TRN_HDRF_DBF

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  TRN_HDRF_DBF

       |   |  Using I/O Size 4 Kbytes for data pages.

QUERY PLAN IS RECOMPILED DUE TO TABMISSING.

THE RECOMPILED QUERY PLAN IS:

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is UPDATE.

  2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |UPDATE Operator (VA = 1)

       |   |  The update mode is direct.

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  TRN_HDRF_DBF

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 32 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

       |   |

       |   |  TO TABLE

       |   |  TRN_HDRF_DBF

       |   |  Using I/O Size 4 Kbytes for data pages.

Table: TRN_HDRF_DBF scan count 0, logical reads: (regular=31 apf=0 total=31),

physical reads: (regular=6 apf=0 total=6), apf IOs used=0

Table: TRN_HDRF_DBF scan count 1, logical reads: (regular=7642489 apf=0

total=7642489), physical reads: (regular=854929 apf=115425 total=970354), apf

IOs used=115425

Total writes for this command: 2628

(30318974 rows affected)

Execution Time (ms.): 1592671 Clock Time (ms.): 1592671

simon_ogden
Participant
0 Kudos

Not directly related to your request but out of interest you can get the ASE to return you the SQL it issues under the covers during an operation like 'alter table' by using traceflag 1704.

This flag is normally recommended by tech. support to show you the full SQL executed during each step of an upgrade.

dbcc traceon (3604,1704)

go

alter table <blah>

go

dbcc traceoff (3604, 1704)

go

former_member188958
Active Contributor
0 Kudos

Hi Simon,

Is TRN_HDRF_DBF an exact copy of TABLE_A (same data, same indexes)?

Any chance you added an index on the M_COMMENT column that you forgot to mention?  That io value of 31 seems far too small for a table scan.  (though it seems maybe about right for the ALTER TABLE adding a nullable column)

-bret

Former Member
0 Kudos

Hi Bret,

I initially didn't want to show the original table name (we never know, I don't want to be blamed if my company noticed I'm giving some information about our software on internet...) but I probably forgot to change one occurence of TRN_HDRF_DBF to TABLE_A.

So to answer your question: TRN_HSRF_DBF and TABLE_A are the same object

Now regarding your index on M_COMMENT, actually one is created AFTER my update statement.

I'll give a try by placing it before.

Best,

Simon