Upgrade Tip – Run DBCC GAM(dbid,0,0,’check’) after upgrade to 15.x

Recommendation: After upgrading a pre-15.0 server or database to 15.x, run DBCC GAM(dbid,0,0,’check’) as a postupgrade step against each upgraded database.

Why:  To minimize the server downtime for the upgrade, some upgrade steps are deferred to a later time.  Prior to 15.0, extent records on allocation pages contained the slice id for partitioned tables.  The upgrade to 15.0 unpartitions all tables, but the old slice information is left in the extent records.  The extent records with old slice information are subsequently corrected by whatever process first acesses the allocation page after upgrade.

In general, this deferred processing just causes a slight one-time slowing of performance as the extent records are corrected.  However, DBCC CHECKSTORAGE in particular experiences two types of problems the first time it is run after the upgrade (when accessing extent records with the old slice information).  The first is greatly decreased performance, the second is spurious 100016 faults (page not used error). On very large databases, the number of faults reported can be overwhelming.

Sample output with spurious 100016 faults:

1> dbcc checkstorage(testdb)

2> go

Checking testdb: Logical pagesize is 2048 bytes

DBCC CHECKSTORAGE for database ‘testdb’ sequence 7 completed at April 18 2013

2:33PM. 0 faults and 1777 suspect conditions were located. 0 checks were

aborted. You should investigate the recorded faults, and plan a course of action

that will correct them.

Suspect conditions are to be treated as faults if the same suspect condition

persists in subsequent CHECKSTORAGE operations, or if they are also detected by

other DBCC functions.

1> sp_dbcc_faultreport

2> go

Database Name : testdb

Table Name               Index          PartitionId

         Fault Type

         Page Number

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

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

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

test                        0                 32000114

         100025 (row count error)

                      NULL

test                      255                 32000114

         100016 (page not used error)

                       491

test                      255                 32000114

         100016 (page not used error)

                       881

test                      255                 32000114

         100016 (page not used error)

                       887

[snip]

In both cases, the problem is not seen on subsequent executions of CHECKSTORAGE on the same database.  One case with a 120 GB database reported CHECKSTORAGE taking 26 times longer for the first execution than subsequent executions.  CHECKSTORAGE, for reasons as yet unknown, is very inefficient at upgrading the extent records, while DBCC GAM(dbid,0,0,’check’) has proven to be the fastest method for upgrading all the extent records.  So I recommend running DBCC GAM after the upgrade, before running DBCC CHECKSTORAGE.  It isn’t critical that you do so though, as the problem does clear itself up over time or by the first running of CHECKSTORAGE.

This issue is being tracked under change requests (CR) 681056 (spurious faults) and 632800 (slowness)

About DBCC GAM:

The GAM (Global Allocation Map) is an ASE datastructure used to quickly identify which allocation units have at least one free extent on them.  It is essentially a large bitmap with one bit for each allocation page.  The bit is set on when all 32 extent records are allocated and cleared when less than 32 extent records are allocated.  The ‘check’ option of DBCC GAM reads each allocation page in the database to verify that the corresponding GAM bit is set correctly.   Prior to ASE 15.7 ESD 4, the ‘check’ option incorrectly outputs a message saying that the database must be in single user mode.  This message should be ignored as the check is performed regardless.