Skip to Content

Which device is my table on?

Every so often I see a question from someone interested in knowing which tables are stored on which devices, or which devices have allocations for table X.  I’m not sure how the information would be useful as the information is dynamic.  ASE handles allocation automatically and in general makes no attempt to keep all the allocations for an object on the same device (though user defined segments can be used to keep a table entirely on a device).  All the same, here is a method for answering the question “which objects are on (have allocations on) device Y?”.
ASE divides each device up into 256-page allocation units; each allocation unit consists of 32 8-page extents.  Each extent can be allocated to only one index id for only one object or partition id.  Index id 0 is used for pages containing actual data rather than index keys.  Index id 255 is used for LOB data (text, image, unitext, java classes).
ASE has an undocumented diagnostic command dbcc usedextents that dumps information about each extent structure in the database.   The command reads every allocation page in the database. It is fairly fast and does minimal blocking;  I have no concerns over running it on production systems. The output for a single extent structure looks like this:
set switch on 3604
go
dbcc usedextents(dbname,0,0)
go
[…]
Allocation bitmap: 0xff ( 2384 2385 2386
OAMPG: 328 Extent ID 2384 on allocation page 2304
Object ID is 5
Index ID is 0
Partition ID is 52387 2388 2389 2390 2391 )
Dealloc bitmap: 0x00 ( )
Forward bitmap: 0x00 ( )
Reserve bitmap: 0x00 ( )
status: 0x00 (EX_DEALL_NOSTATUS )
Sort bit is off
Reference bit is off
Spacebits bitmap: 0x88888888
Page: 2384 (0x08 (Less than 100% occupied))
Page: 2385 (0x08 (Less than 100% occupied))
Page: 2386 (0x08 (Less than 100% occupied))
Page: 2387 (0x08 (Less than 100% occupied))
Page: 2388 (0x08 (Less than 100% occupied))
Page: 2389 (0x08 (Less than 100% occupied))
Page: 2390 (0x08 (Less than 100% occupied))
Page: 2391 (0x08 (Less than 100% occupied))
Buddy Page for extent (se_extbuddypage): 0
[…]
If you run dbcc usedextents(<database_name>, 0,0) and save the output in a file, you can then filter out the extentid, object id, and indexid using a simple awk script:
isql -Usa -P  -o usedextents_output.txt << EOF
set switch on 3604
go
dbcc usedextents(mydatabase,0,0)
go
EOF

awk ‘$1==”OAMPG:” {printf (“%d\t”, $5) } \
$1==”Object” {printf (” %d \t”, $4)} \
$1==”Index” {printf (” %d \n”, $4)} ‘
usedextents_output.txt > usedextents_awk.txt

This yields simple tabular data with one line per extent
[…]
174064 591338140 2
174096 591338140 2
[…]
(This shows that extent 174064 is allocated to the table with object id  591338140  for use by the index with indid 2)
You can then import that data into a table in ASE and then use various queries to answer questions about allocation such as “what tables are on device data_dev_1”.
isql -Usa -P << EOF
use tempdb
go
create table usedextents (extent int, objectID int, indid tinyint)
go
exit
EOF

bcp tempdb..usedextents in usedextents_awk.txt -Usa -P -c

The following query is for 15.x and above.  Prior versions of ASE used a different relation between sysusages and sysdevices.

Which user tables in database “mydatabase” are on device “datadev1”?

declare    @dbname      varchar(255)
select     @dbname      = “mydatabase”  — <—- your db name here
declare    @devicename  varchar(255)
select     @devicename  = “datadev”     — <—-
your device name here

select distinct
     x.objectID as “objectID”,
     object_name( objectID, db_id(@dbname) ) as “name”
from
     tempdb..usedextents x,
     master..sysusages u,
     master..sysdevices d
where
     u.dbid = db_id(@dbname)
and  d.name = @devicename
and  x.extent between u.lstart and (u.lstart+u.size)
and  u.vdevno = d.vdevno
and  x.objectID > 99  — User tables only
order by
     object_name(x.objectID, db_id(@dbname))

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Anil Thakur

    Bret,

    This is most useful!

    I have noticed something strange while using this.

    I am using ASE 15.0.3 ESD#4 EBF 17779 on a Sun Solaris 10.

    I have a test database of 4.7Gb (3.6Gb Data + 1.1Gb Log) on 3 ‘data only’ devices + 1 ‘log only device’. So there is no data+log situation in this database.

    Dbcc checkstorage returns no errors.

    However, when i run usedextents on this database and generate an Object/Device map, I see quite a few tables which reside on extents that belong to the log device! I was expecting only Object ID 8 (syslogs) to be associated with the extents belonging to the log device.

    Is this normal? Or am I missing something in this picture?

    Thanks in advance.

    Anil

    (0) 
    1. Bret Halford Post author

      Hi Anil,

      I would expect checkstorage to raise 100028 faults (misplaced object fault) in such a case.

      I’m assuming that the database actually is mapped out so that only data segments are the datadevices and only log segments are mapped to the log device.  You can tell from sp_helpdb.  For instance, here my database test has a log fragment on a data device (data and log is not “mixed” though, each fragment is either all data or all log):

      1> sp_helpdb test
      2> go
      name db_size       owner dbid created      durability lobcomplvl inrowlen
               status
      —- ————- —– —- ———— ———- ———- ——–
               ————–
      test        6.0 MB sa       5 Jul 18, 2014 full                0     NULL
               no options set

      (1 row affected)
      device_fragments               size          usage
               created                   free kbytes
      —————————— ————- ——————–
               ————————- —————-
      master                                3.0 MB data only
               Jul 18 2014  2:05PM                   1292
      datadev1                              3.0 MB log only
               Jul 18 2014  2:05PM       not applicable

      Another possibility is that sp_dbcc_exclusions has been used to put 100028 on the exclusion list.  To find out, run

            sp_dbcc_exclusions null, ‘listall’

      Write again if neither of those ideas explains it.

      -bret

      (0) 
      1. Anil Thakur

        Bret,

        Thanks for responding.

        Like I had said in my original post and I checked once again; there is no mixing of data and log devices in this database.

        1> sp_helpdb TEST_TIAD

        2> go

        name      db_size       owner dbid created      status                                                      

        ——— ————- —– —- ———— ————————————————————-

        TEST_TIAD     4700.0 MB sa       8 Jun 16, 2014 select into/bulkcopy/pllsort, trunc log on chkpt, single user

        (1 row affected)

        device_fragments               size          usage                created                   free kbytes    

        —————————— ————- ——————– ————————- —————-

        data12                              700.0 MB data only            Jun 16 2014 11:12AM                   4002

        data120                             200.0 MB data only            Jun 16 2014 11:12AM                   1030

        log1                                400.0 MB log only             Jun 16 2014 11:12AM       not applicable 

        data120                            1700.0 MB data only            Jun 16 2014 11:12AM                 626332

        log1                                700.0 MB log only             Jun 16 2014 11:12AM       not applicable 

        data120                             100.0 MB data only            Jun 16 2014 11:12AM                  86994

        data121                             900.0 MB data only            Jun 16 2014 11:12AM                 909422

                                                                      

        ————————————————————–

        log only free kbytes = 1121964                               

        device  segment  

        ——- ———-

        data12  default  

        data12  system   

        data120 default  

        data120 system   

        data121 default  

        data121 system   

        log1    logsegment

        (return status = 0)

        I went one step further and checked that each individual device listed for this database is not being accidently used adversely in some other database.

        Also, 100028 is not in the DBCC exclusion list either:

        1>  sp_dbcc_exclusions null, ‘listall’

        2> go

        Scope                          Type    Table                                                                                                                                                                                                                                                           Fault    

        —————————— ——- ————————————————————————————————————————————————————————————————————————————————————— ———-

        Default Exclusions             Fault                                                                                                                                                                                                                                                                   100035   

        Default Exclusions             Fault                                                                                                                                                                                                                                                                   100037   

        Default Exclusions             Fault                                                                                                                                                                                                                                                                   100040   

        (1 row affected, return status = 0)

        Lastly, I ran a dbcc checkalloc with the ‘fix’ option. It made no difference to the output.

        That said, I am stumped!

        Thanks,

        Anil

        (0) 
        1. Bret Halford Post author

          Hi Anil,

          Ok.  Lets modify the mapping query to drill down and identify

          a few specific extents.

          declare    @dbname      varchar(255)
          select     @dbname      = “test”  — <—- your db name here
          declare    @devicename  varchar(255)
          select     @devicename  = “log1”     — <—- your device name here

          select  top 20
               x.objectID as “objectID”,
               object_name( objectID, db_id(@dbname) ) as “name”,
               x.extent
          from
               tempdb..usedextents x,
               master..sysusages u,
               master..sysdevices d
          where
               u.dbid = db_id(@dbname)
          and  d.name = @devicename
          and  x.extent between u.lstart and (u.lstart+u.size)
          and  u.vdevno = d.vdevno
          and  x.objectID != 8 — non-syslogs tables only
          order by
            x.extent

          (0) 
          1. Anil Thakur

            Hi Bret,

            I ran a slightly modified version of what you suggested. There are 2135 entries where the extent # for a table is on a log-only device ‘log1’. Here is an excerpt of the output.

            objectName                       indexID extentNo    deviceName

            ——————————– ——-    ———– ——————————–

            .

            .

            .

            TB_ALLOCATION             0          1387120  data120 (–> mapped correctly)

            TB_ALLOCATION             0          1601584  log1

            TB_ALLOCATION             0          1602136  log1

            TB_ALLOCATION             0          1602688  log1

            TB_ALLOCATION             0          1603240  log1

            TB_ALLOCATION             0          1603856  log1

            TB_ALLOCATION             0          1604992  log1

            TB_ALLOCATION             0          1605456  log1

            TB_ALLOCATION             0          1605904  log1

            TB_ALLOCATION             0          1606352  log1

            TB_ALLOCATION             0          1606712  log1

            .

            .

            .

            TB_BLOCK_TRADE          0          1381512  data120 (–> mapped correctly)

            TB_BLOCK_TRADE          0          1633680  log1

            TB_BLOCK_TRADE          0          1634272  log1

            TB_BLOCK_TRADE          0          1639160  log1

            TB_BLOCK_TRADE          0          1639768  log1

            TB_BLOCK_TRADE          0          1641240  log1

            TB_BLOCK_TRADE          0          1642056  log1

            TB_BLOCK_TRADE          0          1643736  log1

            TB_BLOCK_TRADE          0          1644472  log1

            TB_BLOCK_TRADE          0          1644904  log1

            TB_BLOCK_TRADE          0          1645200  log1

            .

            .

            .

            Here is the output of sysusages along with name from sysdevices for the said database.

            1> select a.*, b.name as ‘devname’ from sysusages a, sysdevices b where a.dbid=8 and a.vdevno = b.vdevno

            2> go

            dbid   segmap      lstart      size        vstart      pad    unreservedpgs crdate              vdevno      devname                      

            —— ———– ———– ———– ———– —— ————- ——————- ———– ——————————

                  8           3           0      358400      665600   NULL          2009 Jun 16 2014 11:12AM          18 data12                       

                  8           3     1894400       51200      972800   NULL         43497 Jun 16 2014 11:12AM         140 data120                      

                  8           3      665600      870400      102400   NULL        313166 Jun 16 2014 11:12AM         140 data120                      

                  8           3      358400      102400           0   NULL           515 Jun 16 2014 11:12AM         140 data120                      

                  8           3     1945600      460800           0   NULL        454711 Jun 16 2014 11:12AM         141 data121                      

                  8           4     1536000      358400      563200   NULL        339983 Jun 16 2014 11:12AM          22 log1                         

                  8           4      460800      204800      358400   NULL        204852 Jun 16 2014 11:12AM          22 log1                         

            (7 rows affected)

            Anil

            (0) 
            1. Anil Thakur

              Hi Bret,

              Just ordering the 2nd output the correct way…

              1> select a.*, b.name as ‘devname’ from sysusages a, sysdevices b where a.dbid=8 and a.vdevno = b.vdevno order by a.lstart

              2> go

              dbid   segmap      lstart      size        vstart      pad    unreservedpgs crdate              vdevno      devname                      

              —— ———– ———– ———– ———– —— ————- ——————- ———– ——————————

                    8           3           0      358400      665600   NULL          2009 Jun 16 2014 11:12AM          18 data12                       

                    8           3      358400      102400           0   NULL           515 Jun 16 2014 11:12AM         140 data120                      

                    8           4      460800      204800      358400   NULL        204852 Jun 16 2014 11:12AM          22 log1                         

                    8           3      665600      870400      102400   NULL        313166 Jun 16 2014 11:12AM         140 data120                      

                    8           4     1536000      358400      563200   NULL        339983 Jun 16 2014 11:12AM          22 log1                         

                    8           3     1894400       51200      972800   NULL         43497 Jun 16 2014 11:12AM         140 data120                      

                    8           3     1945600      460800           0   NULL        454711 Jun 16 2014 11:12AM         141 data121                      

              (7 rows affected)

              Anil

              (0) 
              1. Anil Thakur

                Bret,

                I have some good news and some strange news!

                Good news; the problem ‘seems’ to have been addressed.

                Strange news: What fixed it was that I ran ‘reorg rebuild’ on the entire database!

                Go figure 😕

                Just for kicks, I will reload the old dump, and run ‘reorg rebuild’ on only those tables where this mismatch occurs.

                But the bottom line is…. How did that happen in the first place when the database fragments are correctly mapped to different data and log devices and how was it fixed with a ‘reorg rebuild’ alone.

                Anil

                (0) 
                1. Anil Thakur

                  Bret,

                  Just confirmed after loading the old screwed-up dump, executing ‘reorg rebuild’ on only the reported tables other than syslogs fixes the data-pages-on-log-devices problem.

                  But…… the question still remains.

                  Anil

                  (0) 
                  1. Bret Halford Post author

                    The most likely explanation is that some time in the past the device fragment was either used for data or mixed data and log, and the data segment was dropped.   Dropping the segment does not cause any existing allocations to move.  Reorg rebuild does cause all the allocations to move and they can only be moved to device fragments that currently have the data segment.

                    What I find mysterious is that checkstorage didn’t raise any misplaced object faults for the situation.

                    But I’m glad you were able to clean it up with REORG.

                    -bret

                    (0) 

Leave a Reply