Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Location
    china
    Posts
    21

    Red face Unanswered: About processes' memory usage in sybase sql server

    hi, everybody
    how can I get the information about the memory usage of every processes in sybase sql server.
    thanks very much
    Walking .......

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    There is no such thing as memory usage per process. Memory is allocated on a per server basis and this is then divided into stack size. Each spid works in it's own stack area and cannot consume more than memory than that pre-allocated size. if at all a pointer crosses the stack area it goes into a guard area. This will result in Guardword Corruption and shutdown dataserver.

  3. #3
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    Originally posted by willy_and_the_ci
    There is no such thing as memory usage per process. Memory is allocated on a per server basis and this is then divided into stack size. Each spid works in it's own stack area and cannot consume more than memory than that pre-allocated size. if at all a pointer crosses the stack area it goes into a guard area. This will result in Guardword Corruption and shutdown dataserver.
    thank you very much firstly!
    I think so
    but my boss want to know the memory size of the process allocated.
    and is the table sysprocesses' column "memusage" what i want ?
    Walking .......

  4. #4
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    help
    Walking .......

  5. #5
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    19

    Lightbulb

    Hi,

    The following command will already display some memory information

    dbcc traceon (3604)
    go
    dbcc memusage
    go

    Output shows information about how ASE memory is used. It also display info about the procs/tables that are in memory

    Better test this procedure first in a development as it sometimes locks up.
    Hope this helps,
    Best Regards,

    Toon Eysakkers


    -> Sample output

    Memory Usage:







    Meg. 2K Blks Bytes




    Configured Memory: 976.5625 500000 1024000000




    Code size: 12.0735 6182 12659978

    Kernel Structures: 37.6038 19254 39430456

    Server Structures: 102.6220 52543 107606996

    Cache Memory: 776.1777 397403 813881344

    Proc Buffers: 1.7553 899 1840608

    Proc Headers: 46.3281 23720 48578560










    Buffer Cache Memory, Top 20:




    Cache Buf Pool DB Id Object Id Index Id Meg.




    default data c 13 653245382 0 2.7344

    2K 13 653245382 0 0.0156

    16K 13 653245382 0 2.7188

    default data c 132063502580 0 0.3398

    2K 132063502580 0 0.2930

    16K 132063502580 0 0.0469

    default data c 132063502580 2 0.0312

    16K 132063502580 2 0.0312

    default data c 1 8 0 0.0156

    4K 1 8 0 0.0156

    default data c 8 8 0 0.0078

    4K 8 8 0 0.0078

    default data c 13 8 0 0.0078

    4K 13 8 0 0.0078

    default data c 2 8 0 0.0039

    4K 2 8 0 0.0039

    default data c 3 8 0 0.0039

    4K 3 8 0 0.0039

    default data c 4 8 0 0.0039

    4K 4 8 0 0.0039

    default data c 5 8 0 0.0039

    4K 5 8 0 0.0039

    default data c 6 8 0 0.0039

    4K 6 8 0 0.0039

    default data c 7 8 0 0.0039

    4K 7 8 0 0.0039

    default data c 9 8 0 0.0039

    4K 9 8 0 0.0039

    default data c 10 8 0 0.0039

    4K 10 8 0 0.0039

    default data c 11 8 0 0.0039

    4K 11 8 0 0.0039

    default data c 12 8 0 0.0039

    4K 12 8 0 0.0039

    default data c 14 8 0 0.0039

    4K 14 8 0 0.0039

    default data c 15 8 0 0.0039

    4K 15 8 0 0.0039

    default data c 16 8 0 0.0039

    4K 16 8 0 0.0039

    default data c 17 8 0 0.0039

    4K 17 8 0 0.0039










    Procedure Cache, Top 20:




    Database Id: 13

    Object Id: 620021440

    Object Name: maak_caktie

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 0

    Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)

    Number of plans: 1

    Size of plans: 1.168146 Mb, 1224890.000000 bytes, 600 pages

    Bytes lost for alignment 61037 (Percentage of total: 4.983060)




    ----

    Database Id: 31513

    Object Id: 1728009187

    Object Name: sp_helpdevice

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 0

    Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)

    Number of plans: 1

    Size of plans: 0.612455 Mb, 642206.000000 bytes, 315 pages

    Bytes lost for alignment 11359 (Percentage of total: 1.768747)




    ----

    Database Id: 8

    Object Id: 234483914

    Object Name: oas_sp_ilb001_v7_ILB

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.516924 Mb, 542034.000000 bytes, 266 pages

    Bytes lost for alignment 34278 (Percentage of total: 6.323958)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 266484028

    Object Name: oas_sp_ilb001_v7_ILL

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.454532 Mb, 476611.000000 bytes, 234 pages

    Bytes lost for alignment 22983 (Percentage of total: 4.822172)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 218483857

    Object Name: oas_sp_ilb001_v7

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.447932 Mb, 469691.000000 bytes, 231 pages

    Bytes lost for alignment 23617 (Percentage of total: 5.028199)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 250483971

    Object Name: oas_sp_ilb001_v7_ILB_UD

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.449528 Mb, 471364.000000 bytes, 231 pages

    Bytes lost for alignment 28027 (Percentage of total: 5.945936)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 186483743

    Object Name: oas_sp_ilb001_ILB

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.303920 Mb, 318683.000000 bytes, 157 pages

    Bytes lost for alignment 12199 (Percentage of total: 3.827942)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 202483800

    Object Name: oas_sp_ilb001_ILR

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.304234 Mb, 319012.000000 bytes, 157 pages

    Bytes lost for alignment 11575 (Percentage of total: 3.628390)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 1933965966

    Object Name: emu_conversion_2001_06

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.265342 Mb, 278231.000000 bytes, 137 pages

    Bytes lost for alignment 7279 (Percentage of total: 2.616171)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 8

    Object Id: 1949966023

    Object Name: emu_conversion_2001_07

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.265340 Mb, 278229.000000 bytes, 137 pages

    Bytes lost for alignment 6982 (Percentage of total: 2.509444)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

    Database Id: 1

    Object Id: 173243672

    Object Name: sp_configure

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 0

    Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)

    Number of plans: 1

    Size of plans: 0.263327 Mb, 276118.000000 bytes, 136 pages

    Bytes lost for alignment 5482 (Percentage of total: 1.985383)




    ----

    Database Id: 8

    Object Id: 954486479

    Object Name: transfer50pay

    Version: 1

    Uid: 1

    Type: stored procedure

    Number of trees: 1

    Size of trees: 0.235008 Mb, 246424.000000 bytes, 121 pages

    Bytes lost for alignment 4205 (Percentage of total: 1.706408)

    Number of plans: 0

    Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages

    Bytes lost for alignment 0 (Percentage of total: 0.000000)




    ----

  6. #6
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    Yes .
    I know we use the command "dbcc memusage" to know memory statistics of the sybase sql server.but I want to know the detail.
    I want to know the size of every process used.
    And the system table sysprocesses show some information about every process.
    But I don't know how to chang the value of this table's column memusage to kilobytes.
    And Make sure this value is the total size of the process used.
    Thanks in advance.
    Sorry for my bad english.
    Walking .......

  7. #7
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    help me !
    Walking .......

  8. #8
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) Never use dbcc memusage if you're using several engines
    2) ASE is a multitreaded process, and not multiprocesses => easier to manage on OS side, and uses less resource (opposite = E.g. Oracle)
    3) Each user connection uses ~100K (in v.12) of RAM : this is the soze of the allocated mem structure. Each connection is sharing the available resource depending its needs

  9. #9
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    Originally posted by fadace
    1) Never use dbcc memusage if you're using several engines
    2) ASE is a multitreaded process, and not multiprocesses => easier to manage on OS side, and uses less resource (opposite = E.g. Oracle)
    3) Each user connection uses ~100K (in v.12) of RAM : this is the soze of the allocated mem structure. Each connection is sharing the available resource depending its needs
    thanks very much !
    but what i really want to know is not that .
    I am afraid I have no time now.
    I only know the column 'memusage' means memory usage now.
    once one process is created by sybase sql server.that column's value is
    1 (version 11.5).and if the space is not enough ,the value add 1 increasingly.

    and It is like this
    1(in sybase sql server)=100KB mermory splace.
    ???
    It is what i want to know!

    sorry for my poor english!

    thanks for your help
    Walking .......

  10. #10
    Join Date
    Dec 2003
    Location
    china
    Posts
    21
    help
    Walking .......

  11. #11
    Join Date
    Dec 2003
    Location
    china
    Posts
    21

    Arrow

    I know now .
    the sybase sql server have a value to configure the memory of each process.


    'memory per worker process'
    Walking .......

  12. #12
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    No.
    The memory per worker process is specific for parallelism tasks. The worker process is a really specific process.

    A normal user connection spend a little bit more than 100Kb since ASE 12.5 (96K on ASE 11.9.2)

    You can't say that a specific connection use a specific amouth of memory (despite your boss's wish). The ressource is dispatched by the ASE on demand !

Posting Permissions

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