Issues running "Index Fragmentation" report (4301081)

• "Too many databases requested. For SQL versions lower than 2012, can only produce fragmentation report on one database at a time"
• "Failed to generate the report. If this issue persists please contact Quest Support"

ERROR   [runReportTask-0] script.system:dbwc_mssql_reports_index20fragmentation20report.callGetIndexFragmentationReportRMI - Failed to call getIndexFragmentationReport in RMI, reason: RemoteException occurred in server thread; nested exception is: 
com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run INDEX_FRAGMENTATION_REPORT collection. Failed to execute query on instance [INSTANCENAME], user account [DOMAIN\USERNAME]. Reason : [[Foglight][SQLServer JDBC Driver]Execution timeout expired.

ERROR   [runReportTask-31] script.system:dbwc_mssql_reports_index20fragmentation20report.callGetIndexFragmentationReportRMI - Failed to call getIndexFragmentationReport in RMI, reason: RemoteException occurred in server thread; nested exception is: 
com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run INDEX_FRAGMENTATION_REPORT collection. Failed to execute query on instance [INSTANCENAME], user account [DOMAIN\USERNAME]. Reason : [[Foglight][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.

Sometimes there may also be no results when some are expected.

CAUSE 1

As indicated in the error message and on the pop-up window "Run Report", for SQL Server versions lower than 2012, only 1 database can be selected in the "Databases Include List" to run the "Index Fragmentation" report.

Based on the errors found in the Foglight Management Server log, query execution or lock request timeouts happen when information for the report was being collected from the SQL Server instance. High load on the SQL Server instance could cause these types of timeouts.

The number of operations per day may be filtering out results. This can be expected when the instance is a standby database server where user_scans, system_scans and user_updates may be null or 0.

RESOLUTION 1

Add a new configuration to the SQL Server database agent for "INDEX_FRAGMENTATION_REPORT" to increase the timeout value for the on-demand collection

1. Navigate to Administration | Agents | Agent Status
2. Select the Database agent
3. Click the Edit Properties button
4. Click the link to Modify the private properties for this agent.
5. Scroll down to Collection Details
6. Click the Edit button next to On demand Collection Details
7. Confirm that the Query Timeout is set to 14400 (or higher)
8. Click the Save Changes button

The following script can be run in the FMS Script Console to list the current settings of the INDEX_FRAGMENTATION_REPORT timeout value for all SQL Server agents in the FMS. The first row listed is the default collection for new SQL Server agents. 

//############ Global Params #################//
/*
namespace and agent type
*/
agentNameSpace = "DB_SQL_Server";
agentType = "DB_SQL_Server";
/*
Max rows per collection
*/
limit = "14400";
/*
Specify collection name to change specific collection ; if empty change all collections limit
*/
collectionName = "INDEX_FRAGMENTATION_REPORT";
//############################################//
out = new StringBuilder();
configService = server.ConfigService;
modifyASP(agentType);
return out.toString();
def modifyASP(agentTypeName) {
def cloneNames = configService.getKnownNamesForSharingName(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails");
for (cloneName in cloneNames) {
def collectionDetails = configService.getSecondaryAsp(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails", cloneName); 
if (collectionDetails != null) {
def rows = collectionDetails.getRows();
for (def row : rows) {
def colName = row.getString("colName");
if (collectionName.isEmpty() || colName.equals(collectionName)) {
out.append(String.format("%s ASP column for agent type %s set on %s to %s \n", cloneName, agentTypeName, colName, row.getString("colTimeout")));
}
}
}else{
out.append("Unable to find collection details names: ").append(cloneName).append("\n");
}
}
}

The following script can be run from the Script Console in the FMS to update the INDEX_FRAGMENTATION_REPORT timeout setting for all SQL Server agents.

//############ Global Params #################//
/*
namespace and agent type
*/
agentNameSpace = "DB_SQL_Server";
agentType = "DB_SQL_Server";
/*
Max rows per collection
*/
limit = "14400";
/*
Specify collection name to change specific collection ; if empty change all collections limit
*/
collectionName = "INDEX_FRAGMENTATION_REPORT";
//############################################//
out = new StringBuilder();
configService = server.ConfigService;
modifyASP(agentType);
return out.toString();
def modifyASP(agentTypeName) {
def cloneNames = configService.getKnownNamesForSharingName(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails");
for (cloneName in cloneNames) {
def collectionDetails = configService.getSecondaryAsp(agentNameSpace, agentTypeName, "colOnDemandCollectionDetails", cloneName); 
if (collectionDetails != null) {
def rows = collectionDetails.getRows();
for (def row : rows) {
def colName = row.getString("colName");
if (collectionName.isEmpty() || colName.equals(collectionName)) {
def oldValue = row.getString("colTimeout");
row.setValueByString("colTimeout", limit);
out.append(String.format("Modify %s ASP for agent type %s. %s Timeout column changed from %s to %s \n", cloneName, agentTypeName, colName, oldValue, row.getString("colTimeout")));
}
}
configService.saveConfig(collectionDetails);
}else{
out.append("Unable to find collection details names: ").append(cloneName).append("\n");
}
}
}

RESOLUTION 3

When the report creation fails with an "Execution timeout" message, the following can be used to increase the timeout.

1. From the Agent Status dashboard and select the problematic SQL Server agent
2. Click on Edit Properties to access the Agent Status Properties
3. Click on Modify the private properties for this agent.
4. Change the "Query timeout for on-demand collections" from 30 to 300 (seconds)
5. Save changes

If the report is still taking too long to complete, attempt to increase the report parameters "Minimal Partition Size" (E.g. from default "10" to "100") and "Minimal partition scan or update operations" (E.g. from the default "5" to "10") until the query completes in a reasonable amount of time.

RESOLUTION 4

By using the -1 for the Minimum Operations per Day it bypasses the filter completely. This prevents the query from filtering out any indexes based on usage so the condition becomes true for all of the data no matter the number of scans, seeks, or updates. NULL values are reasonable if the index hasn't been used since the last SQL Server restart or the statistics were reset. Index Usage Stats only shows data since the last Instance restart. 

The user can also run a simply query to check the contents of sys.dm_db_index_usage_stats directly for the specific database and indexes to see if any usage data exists. If a row isn't present in the output then Index hasn't been used since the last restart or statistics update. 

The user should also check that the report runs on another monitored instance, to narrow down the investigation to a general report issue or specific to a host.

It is also helpful to confirm the collation of the monitored instance?

SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));  

And compare results with the outputs from SQL script which are run in the database that has fragmented indexes 

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

and

SELECT
i.name AS IndexName,
ps.avg_fragmentation_in_percent,
us.user_seeks,
us.user_lookups,
us.user_scans
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('mytablename'), NULL, NULL, NULL) AS ps
JOIN sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS us ON us.object_id = i.object_id AND us.index_id = i.index_id;