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,''
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.