Monitoring the Cache Dbspace

Use the sp_iqstatus, sp_iqdbspace, sp_iqfile, and sp_iqcheckdb stored procedures to monitor key performance indicators for cache dbspaces and their dbfiles.

Prerequisites

sp_iqstatus requires the ALTER DATABASE, MANAGE ANY DBSPACE, MONITOR, or SERVER OPERATOR system privilege. Users without one of these system privileges must be granted EXECUTE privilege.
sp_iqdbspace requires the MANAGE ANY DBSPACE system privilege. Users without MANAGE ANY DBSPACE system privilege must be granted EXECUTE privilege to run the stored procedure.
sp_iqfile requires the MANAGE ANY DBSPACE system privilege. Users without the MANAGE ANY DBSPACE system privilege must be granted EXECUTE privilege.

Task

Run sp_iqstatus to view memory usage information about the cache dbspace and its dbfiles.

...
 Main Store Out Of Space: N
 Number of Cache Dbspace Files: 5
 Number of Shared Temp DB Files: 0
 Shared Temp Store Out Of Space: N
 Number of Local Temp DB Files: 1
 Local Temp Store Out Of Space: N
 DB Blocks: 1-25600 IQ_SYSTEM_MAIN
 DB Blocks: 522208-547807 MainUser
 Cache Dbspace Blocks: 1-5120 ssd_dev_1
 Cache Dbspace Blocks: 522208-527327 ssd_dev_2
 Cache Dbspace Blocks: 1044416-1049535 ssd_dev_3
 Cache Dbspace Blocks: 1566624-1571743 ssd_dev_4
 Cache Dbspace Blocks: 2088832-2093951 ssd_dev_5
 Local Temp Blocks: 1-25600 IQ_SYSTEM_TEMP
 Create Time: 2013-08-21 06:27:05.444
 Update Time: 2013-08-21 06:27:14.035
 Main IQ Buffers: 1588, 100Mb
 Temporary IQ Buffers: 1588, 100Mb
 Main IQ Blocks Used: 5250 of 38400, 13%=20Mb, Max Block#: 5313
 Cache Dbspace IQ Blocks Used: 197 of 25600, 0%=0Mb, Max Block#: 0
 Shared Temporary IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0
 Local Temporary IQ Blocks Used: 65 of 12800, 0%=0Mb, Max Block#: 0
 Main Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb
 Local Temporary Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 ...

Measurement	Description
Number of Cache Dbspace Files	Number of cache dbspace dbfiles in the database.
Cache Dbspace Block	Identifies the cache dbspace blocks and the corresponding storage device dbfile name.
Cache Dbspace IQ Blocks Used	Number of IQ blocks used, compared to the total number of IQ blocks. Usage is also shown as a percentage. If the percentage is high, consider adding additional storage.

See sp_iqstatus Procedure in Reference: Building Blocks, Tables, and Procedures for detailed information.

Run sp_iqdbspace to view detailed information about the cache dbspace, including its read/write status and online/offline status. View the row for your cache dbspace name, as you specified in the CREATE DBSPACE statement (myDAS in this example). A cache dbspace displays CACHE in the DBSpaceType column:

IQ_SYSTEM_MAIN MAIN       T T 21 100M 0B 1 1 T 1K 1H,5088F,32D,128M N
IQ_SYSTEM_TEMP TEMPORARY  T T 1  100M 0B 1 1 T 1K 1H,64F,16A N
myDAS CACHE               T T 1  100M 0B 5 5 T 1K 5H,192F Y
MainUser MAIN             T T 1  100M 0B 1 1 T 1K 1H Y

Column	Description
Writable	T (writable) or F (not writable).
Online	T (online) or F (offline).
Usage	Percent of dbspace currently in use by all files in the dbspace.
TotalSize	Total size of all files in the dbspace in the units B (bytes), K (kilobytes), M (megabytes), G (gigabytes), T (terabytes), or P (petabytes).
Reserve	Total reserved space that can be added to all files in the dbspace.
NumFiles	Number of files in the dbspace.
NumRWFiles	Number of read/write files in the dbspace.
StripingOn	T (On) or F (Off).
StripeSize	Always 1, if disk striping is on.
BlkTypes	Space used by both user data and internal system structures.
OkToDrop	"Y" indicates the dbspace can be dropped; otherwise "N".

Run sp_iqfile to display detailed information about each dbfile in the cache dbspace. View the rows for your cache dbspace name, as you specified in the CREATE DBSPACE statement (myDAS in this example):

IQ_SYSTEM_MAIN IQ_SYSTEM_MAIN exampleDB.iq MAIN RW T 21 100M 0B 1K 1H,5088F,32D,128M 1 25600
MainUser MainUser exampleDB.iq2 MAIN RW T 1 100M 0B 1K 1H 522208 547807
myDAS ssd_dev_1 /dev/raw/ssd_dev_1 CACHE RW T 2 20M 0B 1K 1H,64F 1 5120
myDAS ssd_dev_2 /dev/raw/ssd_dev_2 CACHE RW T 1 20M 0B 1K 1H,32F 522208 527327
myDAS ssd_dev_3 /dev/raw/ssd_dev_3 CACHE RW T 1 20M 0B 1K 1H,32F 1044416 1049535
myDAS ssd_dev_4 /dev/raw/ssd_dev_4 CACHE RW T 1 20M 0B 1K 1H,32F 1566624 1571743
myDAS ssd_dev_5 /dev/raw/ssd_dev_5 CACHE RW T 1 20M 0B 1K 1H,32F 2088832 2093951
IQ_SYSTEM_TEMP IQ_SYSTEM_TEMP exampleDB.iqtmp TEMPORARY RW T 1 100M 0B 1K 1H,64F,16A 1 25600

Column	Description
DBFileName	Logical file name.
Path	Location of the physical file or raw partition.
SegmentType	CACHE.
RWMode	Mode of the dbspace: read-write (RW) or read-only (RO).
Online	T (online) or F (offline).
Usage	Percent of dbspace currently in use by this file in the dbspace. When run against a secondary node in a multiplex configuration, this column displays NA.
DBFileSize	Current size of the file or raw partition. For a raw partition, this size value can be less than the physical size.
Reserve	Reserved space that can be added to this file in the dbspace.
StripeSize	Always 1, if disk striping is on.
BlkTypes	Space used by both user data and internal system structures.
FirstBlk	First IQ block number assigned to the file.
LastBlk	Last IQ block number assigned to the file.
OkToDrop	"Y" indicates the file can be dropped; otherwise "N".

See sp_iqfile Procedure in Reference: Building Blocks, Tables, and Procedures for detailed information.

Next

You can use the sp_iqcheckdb stored procedure to perform database verifications against the cache dbspace. See sp_iqcheckdb Procedure in Reference: Building Blocks, Tables, and Procedures.