ASE FAQ: Which device is my table on?
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
[…]
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
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
[…]
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
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 @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
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))
Good stuff Bret as usual. Thanks for the info!
Thanks Bret, my customer asked me this question today!
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
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
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
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
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
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
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
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
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
Bret,
Thanks for your time looking into this issue for me.
Anil