dbvalid time dramatically increasing

[Former Member]

Hi Community,

a customer starts dbvalid every night on a 68 GB Backup of SQL Anywhere DB. Up to 20.7.2013 that takes 3 hours. Since 20.7.2003 it takes 12 to 15 hours. Increasing cache from 4 to 6 GB leads to duration of 10 hours. What can be the reason for this dramatically increase of time?

Some more information: Sql Anywhere 10.0.1.3415 64 bit. VM, quad core, 2.93 GHz, Intel Xeon, Win2008 R2, 24 GB RAM. Backup-DB ist startet by: dbeng -c 6g -ca 0 . Validation is startet by: dbvalid -q -fx . During validation resource-monitor shows reading of dbeng by 53,000,000 B/min.

[MCMartin]

53 MBytes per minute mean < 1MBytes per second, so just the reading of the DB file would last more than 18 hours. I would check the underlying IO system, maybe you have a degraded performance there.

[johnsmirnios]

I agree. With -fx, I believe most of the IO should be sequential since the index probes (which can require a lot of random IO if the cache is too small) would be skipped. Sequential IOs should be fast. Maybe another process in this VM or another VM was doing IOs to the same drive? That would introduce randomness to the IO and drastically lower performance.

[reimer_pods]

Copying the database to a "real" machine (not a VM), preferably with no other activity for that time, and running the validation there should give you the chance to compare results.

[Former Member]

Thank you all, dbvalid on backup.db and productiv.db are running on the same server since years. We have to clearify with the local administrators if there are other processes in this VM or another VM that are doing IOs to the same drive.

[Breck_Carter]

One wild guess is that the database was subject to some massive operation (deletes and inserts, for example) that suddenly caused it to become internally fragmented.

The builtin procedures sa_index_levels, sa_index_density, sa_table_fragmentation and sa_table_stats can be used to tell you if that is true. Here are two "value added" procedures that display columns named "concerns" and "fragmentation" to warn you of possible problems; the sample output comes from the V10 demo database (one "concern", no "fragmentation"):

CREATE PROCEDURE p_index_fragmentation ( IN @owner_name VARCHAR ( 128 ) )
RESULT ( table_name       VARCHAR ( 128 ),
         index_name       VARCHAR ( 128 ),
         rows             UNSIGNED BIGINT,
         leaf_pages       UNSIGNED INTEGER,
         levels           INTEGER,
         density          NUMERIC ( 8, 6 ),
         concerns         VARCHAR ( 100 ) )
BEGIN
SELECT sa_index_levels.TableName  AS table_name,
       sa_index_levels.IndexName  AS index_name,
       SYSTABLE.count             AS rows,
       sa_index_density.LeafPages AS leaf_pages,
       sa_index_levels.Levels     AS levels,
       sa_index_density.Density   AS density,
       STRING (
          IF levels > 2
             THEN 'deep'
             ELSE ''
          ENDIF,
          IF levels > 1 AND density < 0.5
             THEN IF levels > 2
                     THEN ', low density'
                     ELSE 'low density'
                   ENDIF
              ELSE ''
          ENDIF ) AS concerns
  FROM dbo.sa_index_levels ( owner_name = @owner_name )
       INNER JOIN dbo.sa_index_density ( owner_name = @owner_name )
               ON sa_index_density.TableName = sa_index_levels.TableName
              AND sa_index_density.IndexName = sa_index_levels.IndexName
       INNER JOIN sys.SYSTABLE
               ON SYSTABLE.table_name = sa_index_density.TableName
 WHERE USER_NAME ( SYSTABLE.creator ) = @owner_name
 ORDER BY table_name,
       index_name;
END;

CHECKPOINT;
CALL p_index_fragmentation ( 'GROUPO' ); -- ***** USE THE CORRECT OWNER NAME

table_name,index_name,rows,leaf_pages,levels,density,concerns
'Contacts','ContactsKey',60,1,1,0.239990,''
'Contacts','FK_CustomerID_ID',60,1,1,0.185303,''
'Customers','CustomersKey',126,1,1,0.417725,''
'Customers','IX_customer_name',126,1,1,0.789795,''
'Departments','DepartmentsKey',5,1,1,0.092529,''
'Departments','FK_DepartmentHeadID_EmployeeID',5,1,1,0.093262,''
'Employees','EmployeesKey',75,1,1,0.281738,''
'Employees','FK_DepartmentID_DepartmentID',75,1,1,0.164795,''
'FinancialCodes','FinancialCodesKey',7,1,1,0.099121,''
'FinancialData','FinancialDataKey',84,1,1,0.343262,''
'FinancialData','FK_Code_Code',84,1,1,0.174316,''
'Products','IX_product_color',10,1,1,0.102051,''
'Products','IX_product_description',10,1,1,0.130127,''
'Products','IX_product_name',10,1,1,0.106445,''
'Products','IX_product_size',10,1,1,0.102783,''
'Products','ProductsKey',10,1,1,0.105957,''
'SalesOrderItems','FK_ID_ID',1097,3,2,0.806315,''
'SalesOrderItems','FK_ProductID_ID',1097,3,2,0.456380,'low density'
'SalesOrderItems','SalesOrderItemsKey',1097,4,2,0.936584,''
'SalesOrders','FK_CustomerID_ID',648,2,2,0.586548,''
'SalesOrders','FK_FinancialCode_Code',648,1,1,0.715576,''
'SalesOrders','FK_SalesRepresentative_EmployeeID',648,1,1,0.758545,''
'SalesOrders','SalesOrdersKey',648,2,2,0.950562,''

CREATE PROCEDURE p_table_fragmentation ( IN @owner_name VARCHAR ( 128 ) )
RESULT ( table_name                  VARCHAR ( 128 ),
         rows                        UNSIGNED INTEGER,
         row_segments                UNSIGNED BIGINT,
         segments_per_row            NUMERIC ( 20, 4 ),
         table_pages                 UNSIGNED BIGINT,
         extension_pages             UNSIGNED BIGINT,
         rows_per_table_page         NUMERIC ( 20, 4 ),
         extension_pages_per_segment NUMERIC ( 20, 4 ) )
BEGIN

SELECT *
  INTO #sa_table_fragmentation
  FROM sa_table_fragmentation ( owner_name = @owner_name );

SELECT #sa_table_fragmentation.TableName     AS table_name,
       #sa_table_fragmentation.rows          AS rows,
       #sa_table_fragmentation.row_segments  AS row_segments,
       #sa_table_fragmentation.segs_per_row  AS segments_per_row,
       sa_table_stats.table_page_count       AS table_pages,
       sa_table_stats.ext_page_count         AS extension_pages,
       IF table_pages = 0
          THEN 0
          ELSE CAST ( rows AS NUMERIC ( 20, 4 ) ) 
             / CAST ( table_pages AS NUMERIC ( 20, 4 ) )
       ENDIF                                 AS rows_per_table_page,
       IF rows = 0
          THEN 0
          ELSE CAST ( extension_pages AS NUMERIC ( 20, 4 ) ) 
               / CAST ( row_segments AS NUMERIC ( 20, 4 ) )
       ENDIF                                 AS extension_pages_per_segment
  FROM #sa_table_fragmentation
       INNER JOIN sa_table_stats()
               ON sa_table_stats.table_name = #sa_table_fragmentation.TableName
 WHERE sa_table_stats.creator = @owner_name
 ORDER BY table_name;

END;

SELECT table_name,
       rows,
       row_segments,
       segments_per_row,
       table_pages,
       extension_pages,
       rows_per_table_page,
       extension_pages_per_segment,
       IF rows >= 1000
       AND ( segments_per_row >= 1.05 
             OR extension_pages_per_segment >= 2 )
          THEN 'Fragmentation!'
          ELSE ''
       ENDIF AS fragmentation
  FROM p_table_fragmentation ( 'GROUPO' ) -- ***** USE THE CORRECT OWNER NAME
 ORDER BY table_name;

table_name,rows,row_segments,segments_per_row,table_pages,extension_pages,rows_per_table_page,extension_pages_per_segment,fragmentation
'Contacts',60,60,1.0000,2,0,30.0000,0.0000,''
'Customers',126,126,1.0000,4,0,31.5000,0.0000,''
'Departments',5,5,1.0000,1,0,5.0000,0.0000,''
'Employees',75,75,1.0000,3,0,25.0000,0.0000,''
'FinancialCodes',7,7,1.0000,1,0,7.0000,0.0000,''
'FinancialData',84,84,1.0000,1,0,84.0000,0.0000,''
'Products',10,10,1.0000,1,33,10.0000,3.3000,''
'SalesOrderItems',1097,1097,1.0000,7,0,156.7143,0.0000,''
'SalesOrders',648,648,1.0000,6,0,108.0000,0.0000,''

[Former Member]

Thank you Breck,

unfortunately the connection was lost after 1 h waiting for result of CALL p_index_fragmentation