Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: where a table locate?

    I have a database which base on several database disks.
    I want to know where one table locate , database disk 1 or database disk 2....

    How shall I do?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: where a table locate?

    Originally posted by ianywhere
    I have a database which base on several database disks.
    I want to know where one table locate , database disk 1 or database disk 2....

    How shall I do?

    Thanks
    Assuming the table is created as follows:

    create table tab_1 ( a int, b varchar(25))
    go

    The table will exist on the "default" segment. All databases have a default segment. This allows ASE to allocate space to any device included in the default segment. When creating or altering a database to include a device, the default segment is automatically extended on to that device. The DBO (normally the SA or user with sa_role) may optionally drop the default segment from the device fragment.

    Potentially any devices included within the default segment may contain the data within tab_1.

    ASE allocates space to tables in terms of "extents". An extent is 8 pages, irrespective of page size. ASE allocates an extent for the table when the table is created and then continues to allocate space, an extent at a time, from the devices bound to the segment as required. As data is added to the table, it grows in chunks of 8 pages.

    If you have multiple devices each and the default segment spans both devices, it is possible to have data on both devices. This is not necessarilly a bad thing and can be used to improve performance by parallelising the IO.

    Since the space allocated to the table is allocated in chunks of 8 pages from any device, it is a little difficult to determine physically where the data for a given table is. The first extent for the table may be on the first device and second extent may be on the second device.

    The following describes a table scan on an APL table.

    ASE finds the first page by retrieving the first column from sysindexes for the clustered or non-clustered index. This page is read and the page pointer to the next page is followed and the page is read. This process continues until the end of the page chain is reached.

    ASE knows where to look for the data by following page pointers (Assuming APL tables). The 8th page has a pointer pointing to the 9th. All that ASE does is read the pointer and use the page number to find the next page. If that page is on the second device, so be it. The only thing that can be guaranteed is the group of 8 pages in an extent will be on the same device.

    You can simulate the process by running dbcc pglinkage.

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208

    Re: where a table locate?

    You have to think a bit crooked here;

    Rather than finding where the table is located. You would have to work out what devices has what objects on them and then search for the object of your interest in the output.

    For Eg:
    On test_dat1 is a data device.
    10MB device. 4MB. is free.
    vstart = 150994944
    low=150994944 high=151015423
    lstart=0 so last page is 0+5120

    Now you have to perform a allocation dump of all allocation pages starting from 0+256 incrementing it by 256 each time.

    You can put it in a loop and the resulting output spool to the same file.

    In the resulting output search for the object_id of the table or the object_name that you are looking for...

    Yeah!!! a bit crooked .. but not impossible.

    There maybe some easier method, why don't you contact Sybase Support?

    Cheers
    Willy

    Originally posted by ianywhere
    I have a database which base on several database disks.
    I want to know where one table locate , database disk 1 or database disk 2....

    How shall I do?

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    62

    Re: where a table locate?

    1. Find first page of the table
    select first, indid from sysindexes where id = object_id ('<tablename>')

    2. Find the page range for the table
    dbcc traceon (3604)
    dbcc pglinkage (dbid, <result from first>, 0, 2, 0, 1)

    NB. You may find out the dbid from: select db_id('<db name>')

    3. Take note of lstart and size in the SYSUSAGES
    use master
    select * from sysusages where dbid = <dbid>

    NB. There will be at least 1 or more entires.

    4. Examine the page range from lstart and size to determine the vstart
    i.e. Use the result from (2) and see where the page fits into the range of lstart and start.

    5. Using the vstart number specified above we can query SYSDEVICES to determine the physical device on which the fragment exists.
    select name, phyname from sysdevices where <vstart> between low and high

    Originally posted by ianywhere
    I have a database which base on several database disks.
    I want to know where one table locate , database disk 1 or database disk 2....

    How shall I do?

    Thanks

Posting Permissions

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