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.

[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