Qlik Talend Data Integration: Task execution was blocked/suspended due to high fragmentation percent with PAGEIOLATCH_SH wait status

[wei_guo]

A task containing tMysqlOutput component, which performs insert/update operations, has been blocked/suspended due to a PAGEIOLATCH_SH wait type status and has been pending for several hours.

Cause

PAGEIOLATCH_SH wait type usually comes up as the result of fragmented or unoptimized index.

Often reasons for excessive PAGEIOLATCH_SH wait type are:

• I/O subsystem has a problem or is misconfigured
• Overloaded I/O subsystem by other processes that are producing the high I/O activity
• Bad index management
• Logical or physical drive misconception
• Network issues/latency
• Memory pressure
• Synchronous Mirroring and AlwaysOn AG

To resolve the issue of high PAGEIOLATCH_SH wait type, you can check the following:

• SQL Server, queries and indexes, as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
• For memory pressure before jumping into any I/O subsystem troubleshooting

Always keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected.

Based on the SQL query check we figured out the avg_fragmentation_in_percent showing  90%+ , which means the index is maintenance badly. 

USE DBName;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table.
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DBName'),
OBJECT_ID(N'dbo.TableName'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

==Detecting Fragmentation==

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

Column	Description
avg_fragmentation_in_percent	The percent of logical fragmentation (out-of-order pages in the index)
fragment_count	The number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages	Average number of pages in one fragment in an index

 

Resolution

After the degree of fragmentation is known, refer to the table below to determine the most effective method to correct the fragmentation.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. Very low levels of fragmentation (less than 5 percent) should not be addressed by either of these commands because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index.

In general, fragmentation on small indexes is often not controllable. The pages of small indexes are sometimes stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

Environment

• Talend Data Integration 7.3.1, 8.0.1