Monitor DBCC Checkstorage

[Former Member]

Hi

I am currently running a DBCC checkstorage on a 700GB database.

How can I know what objects DBCC checkstorage is analyzing or how can I know the % progress of this command?

I tried to look at MDA tables like monProcessObject but I only see the DBCCDB objects "scan_ws" and "text_ws".

@@version:

Adaptive Server Enterprise/15.7.0/EBF 22594 SMP SP62 /P/Sun_svr4/OS 5.10/ase157sp6x/3350/64-bit/FBO/Fri Apr 18 07:29:33 2014

Thanks for your help

Vincent

[former_member188958 · Accepted solution]

Hi Vincent,

I don't know of anything that will give you progress status on checkstorage.

Checkstorage doesn't check user tables directly.  It works by first copying page header metadata from every page in the database to rows in the scan and text workspaces. Checkstorage then analyzes that header data from the workspaces to avoid contention on the original pages. That is why you only see the scan and test workspace tables referenced in monProcessObject.

Checkstorage performance is usually pretty consistent from one execution to the next on the same database provided there aren't changes in resources (worker processes available, cache sizes, etc.).

-bret

[Former Member · Accepted solution]

Thanks Bret!

Your explanation is very helpful.

So I realize I made a wrong assumption. We just moved our database from 1 SAN to another and I thought that DBCC checkstorage+checkcatalog will scan all db objects and force all db blocks to "warm up" to go to SSD disks that are faster.

1) Any idea how I could scan all objects or at least all tables to force db blocks becoming hot blocks. Perhaps "dbcc checkdb"? It could take a long time to scan 700GB....

2) How can I make the link between my 2K pages and the number of physical blocks?

Vincent

[former_member188958 · Accepted solution]

Hi Vincent,

Well, Checkstorage may actually work just fine for that purpose.  In it's first phase, it uses a number of worker processes to read all the pages in the database (just once per page) to copy the header information into the workspace table in dbccdb.  I doubt the SAN cares whether that i/o came from checkstorage or any other read from ASE (but don't know for certain).  Checkstorage is actually a good choice for this if it works, as it is designed to minimize concurrency issues with other activity in ASE.

Checkcatalog won't really add anything - it pretty much just reads the system tables, which are small.  So you would get some additional i/os on pages belonging to system tables; if the SAN needs multiple i/os on a page to move the page to SSD then that would help - but regular activity on the database is going to be hitting these same system tables often anyway.

I'm not really sure what your second question is asking.

-bret

[Former Member · Accepted solution]

Thank you so much Bret for your time!

Regarding my 2nd question, you said that checkstorage worker processes will read all the pages in the database. Does it mean that all the disk blocks will be read? (in other term what is the link between pages and disk blocks?)